Help

Re: Random quotation mark in calculated field?

2832 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Joel_Ford
5 - Automation Enthusiast
5 - Automation Enthusiast
  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??

28 Replies 28
Tuur
10 - Mercury
10 - Mercury

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

Joel_Ford
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

I can have a look if you want…

Joel_Ford
5 - Automation Enthusiast
5 - Automation Enthusiast

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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).

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
4 - Data Explorer
4 - Data Explorer

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
Screen Shot 2018-06-07 at 12.54.40 PM.jpg

Resulting String concat.
Screen Shot 2018-06-07 at 12.55.02 PM.png

And when the comma is removed from the Accounts Name field…
Screen Shot 2018-06-07 at 1.02.57 PM.png

The result is…
Screen Shot 2018-06-07 at 12.55.28 PM.png

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!

@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.

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.

I also recently ran into a case of unexpected quotation marks in copy/pasted data. In this instance the culprit was an extra space character between the parts of a two-word field name. To make matters even more confusing, in grid view the doubled character is invisible, as white space ordinarily is collapsed automatically by the browser. Only if one expanded the record did the additional space become even perceivable.

Caproic Acid rendered as Caproic Acid",1
but Caproic Acid would render as Caproic Acid

Brett_Snelgrove
6 - Interface Innovator
6 - Interface Innovator

This thread was very helpful, so thank you everyone.

If like me there is a reason why you can’t change the use of commas in a text field, you can use the following simple formula to remove the double quotation marks.

SUBSTITUTE(,’"’,"")

SUBSTITUTE(Field,’"’,"") didn’t work for me but this did—SUBSTITUTE(Field,""","")

I’m having trouble getting this to work for me. The name of my concatenated field that I’m trying to get rid of the quotes from is, “Agency, Program, Module.” I have tried the below combinations based on these comments, but neither worked. Do you know what I’m doing wrong? The error message I receive says it’s an invalid formula and so it doesn’t save.

SUBSTITUTE({Agency, Program, Module},’"’,"")
SUBSTITUTE({Agency, Program, Module},""","")

@Jessica_Hutton
This worked for me; try copying and pasting my formula as displayed below. You may have gotten bit by the Airtable formula editor’s dislike for curly quotes, which often get carried over if copying and pasting formatted text, such as text not inside a code block on these forums.

SUBSTITUTE(Name, '"', '')

That worked great!! Thank you!!

I cant seem to get this to work its giving me an error. Im trying to apply the formula to the lookup field via fx

Figured it out, it was returning error becasue I was trying to modify the string as a fx of LOOKUP. Thanks!

This worked! thank you

Dani_Oliver
6 - Interface Innovator
6 - Interface Innovator

@Casey_Kleiman & @Sam_Aronson This is an issue I’m currently having with commas and quotation marks… where do you insert that substitution text in your formula? Mine currently reads:

DATETIME_FORMAT({Date of Donation}, ‘M/D/YYYY’) & " — " & IF({Donor Account}, {Donor Account},{Company account})

And it’s pulling like this:

Screen Shot 2020-09-21 at 8.20.30 PM