Using a text field containing comma(s) as part of a formula

[David_Grove]

I wanted to make the primary field of a table into a link to another table. (Like using a foreign key as part of a primary key in a database.) Of course, I immediately discovered that was not allowed. So, I created a new field, made it a link, then used a formula in the primary field to copy the value of new field (that was now a link field) to the primary field.

To test whether that would be allowed, I created an exceedingly simple formula in the primary field. It was just the field name of the new field that was created to be the link to another table.

At first, I thought it worked perfectly. But, later I realized that there was a problem. If the text string in the link field (which was the value from the primary field in the other table) contained a comma, Airtable automatically enclosed the whole string in double quotation marks. If the text string in the link field did not contain a comma, Airtable simply copied the text string without adding double quotation marks.

This makes a problem because if you then sort on the primary field, you get the rows without any commas first, in alphabetical order. Then all the rows with quotation marks (that is the text strings with commas). You do not get a primary field in which all the text strings are in alphabetical order because all the strings with quotation marks appear after all strings without them. That is, the quotation marks are used as actual text string content, and are, themselves, part of the alphabetizing process.

I couldn’t find any kind of configuration setting or preference that would either put quotation marks in every row, or else no rows.

That behavior seems strange. IS it supposed to be that way?

I solved it by using an “IF” statement and a “FIND” statement to add quotes if the attempt to FIND a comma failed, and to just use the field name (without concatenating additional quotation marks) if the FIND attempt succeeded. Because Airtable automatically adds the quotation marks if there is a comma, I then achieved the objective of every row having quotation marks. Consequently, alphabetizing now works as I desired-- all rows in alphabetical order.

I don’t really have a question, except to wonder if this is the way Airtable is supposed to work. (Adding quotation marks to text from another field automatically, when there is a comma in the text; and if no comma, not adding quotation marks.

Thank you.

DG

[ScottWorld]

That’s because linked fields are strings of records that are separated by commas. If you manually type in a comma, then Airtable has to put quotes around the string that you typed in, to make sure that your comma isn’t interpreted as a record delimiter.

[David_Grove]

I figured that was why. And, it makes sense. But, then why not put quotes around every text string, uncondittionally? That would be a way to preserve the ability to create sorts on that field that continue to work. The way it worked for me was, that to preserve the ability to sort, I had to manually create a formula to add quotes to every string. Aternatively, Airtable could disregard quotation marks (i.e., not treat the quotation marks as a lexical part of the string) when sorting.

But, since it can be programmed around, it’s not a showstopper.

I was just curious.

Thank you.

DG

[Bill_French]

ScottWorld:

That’s because linked fields are strings of records that are separated by commas.

It’s a little more complex, but this is at the heart of the issue. Linked fields are actually arrays for which its strings are technically arrays as well. But the existence of a comma in a string which is an element in an array (of strings), is not an issue; we do this all the time in almost every programming sense. I think the issue is commas in keys - much the way JSON keys cannot include punctuation and a few other characters.

Airtable is interpreting your keys on the expectation that they are lists of values and lists are separated by commas.

David_Grove:

But, then why not put quotes around every text string, unconditionally?

Then you’d have to decide how to handle strings all the time and escaping issues would abound. Consider this:

let str = "I have defined the term "foo"; it means "nothing, really""

This wouldn’t fly in javascript, of course. The embedded quotes would need to be escaped:

let str = "I have defined the term \"foo\"; it means \"nothing, really\""

Quite a nightmare if you plot that trajectory.