Random quotation mark in calculated field?

[Joel_Ford]

1. I have a field in TableA called “ProductSKU” which is a primary field that concatenates some data from other fields in TableA. The formual is: LEFT(Description,10) &"-" &FinishCode &"-" &Model

2. I have a field in TableB called “SelectKit” that is a link to “ProductSKU”.

3. I have a third field in TableB that concatenates data from the “SelecKit” field. The formula is: LEFT({SelectKit},5)

Now, that we’ve established that. I have found that SelectKit is arbitrarily entering a single quotation mark as in " just before the text that is being pulled from “SelectKit”. This only happens in one case. I’ve checked all the fields from every other table and there is NO quotation mark entered in a field. I’ve checked the formulas and they all appear correct anbd otherwise working, except for one entry.

Why is this question mark being entered??

[Tuur]

My guess: most likely because somewhere in your data there is an instance of ["]. For example to indicate a size in inches…

[Joel_Ford]

Thank you for your guess. Yes, that would be my first suspicion as well. But, as I’ve already stated, that is not the case. That has been double-checked, triple checked, and then reconfirmed… There is NO quotation mark anywhere in any field involved in this curious event. It only happens with one specific product, say Widgets. When Widgets is the product, I get the ( " ) in the formula. There is no explanation by looking at the fields being concatenated and the formula works in all other products.

[Tuur]

I can have a look if you want…

[Joel_Ford]

Thanks, but I’ve resolved it by removing the offending LEFT({SelectKit},5) field from my formula. I still have no explanation, but I am 100% absolute that there is no quotation mark in any of my fields, so it was being arbitrarily generated by code. Why, I do not know, but clearly not an expected result.

[Mary_Herman]

If you include a concatenated field from a linked table in your “New concatenate field”, the value of that field will be enclosed with quotation marks. To avoid these marks the easy way, simply add the original fields via lookup rather than the concatenated value from a linked table.
Example: In my table containing the lookup field called “Full Name” that concatenations the values of First Name and Last Name fields in my linked table. I use my concatenated field to display the value in my table but I add the original fields as lookups and use the following formula to create my “New’ concatenate field, then I simply hide the First Name and Last Name fields.
CONCATENATE({Last Name}&”."&{First Name}&"."&‘Proposal’) will desplay Doe.John.Proposal
CONCATENATE({FullName}&"."&‘Proposal’’) will display as “Doe,John”.Proposal (quotes are because FullName is already a concatenated field).

[W_Vann_Hall]

There must be something else going on in your base, as that’s not my experience, at all.

I just created a scratch base along the lines you describe: {First Name}, {Last Name}, and {Full Name}, where {Full Name} = CONCATENATE({First Name},' ',{Last Name}). I then use a lookup field to pull {Full Name} to my main table, where I concatenate it with '.' and 'proposal'. The resulting text does not have any double quotes – nor could I get it to generate any extraneous quotation marks, try as I might.

I thought it might have something to do with an embedded comma, but that line of query went nowhere, as well. Right now, I can’t say where your quotes are coming from — just that they don’t appear to be the result of looking up a concatenated field…

BTW, your concatenation syntax is sort of a suspenders-and-belt situation, in that if you use the &-sign method, you don’t need to wrap everything in CONCATENATE(). That is to say,

{First Name}&'.'&{Last Name}

does the same thing as

CONCATENATE({First Name},'.',{Last Name})

[Tyler_Yocca]

Hello,

I had a similar issue and have found the culprit.

When attempting to stringify a lookup field, if that lookup field has a comma in it’s string, Airtable places that entire string in double quotes when displayed.

I assume this has something to do with the way Airtable delimits records by commas.

So, in my formula, here is a depiction of the oddity. Note: Account is a linked record field.

CONCATENATE({Name}, " ", {Last Name} , " - ", {Account})

Record from Accounts table

Resulting String concat.

And when the comma is removed from the Accounts Name field…

The result is…

You can quickly test this by adding a formula field that is simply your lookup field.

{Account}

This result is, again, a string wrapped in double quotes.

Hope this is helpful!

[Jeremy_Oglesby]

@Tyler_Yocca is right, the commas are the culprit. Commas are reserved in database parlance for separating items in an array, so Airtable is apparently interpreting the string as an array of items separated by commas in the background of those formulas, and then adding quotes to indicate that it has recast what it handled as an array in the formula back to a string.

Kind of weird, but I’m pretty sure that’s what’s going on.

[Tyler_Yocca]

It does make sense but you’d think Airtable would have a way of resolving it…it really messes up HTTP request integrations when you are trying to search on a specific field and the data coming in is something like “Test, this, string”. If you need to match that in the Airtable database you would have a difficult time.

May be worth requesting support from Airtable.