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.