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??

[kuovonne]

Can you provide a larger screen shot that shows both the cell values and the field names at the tops of the columns for all the fields included in the formula?

[Dani_Oliver]

Here you go. Sorry, I had to censor some of this because it’s private information. The whited-out column is dollar amounts. See how Farrar, Straus & Giroux" is the only one adding quotations to the name?

[kuovonne]

Thanks for the screen capture. No worries about the censored info. The screen capture shows that the {Company account} field is a linked record field. The original field in the other table may be a text field, but in this table, the field type is a linked record field.

You can use one of the formulas that I provided in the other thread.

[Dani_Oliver]

Okay amazing. Can I be a total newbie and ask WHERE to put this formula? I added it to the end of my formula and it says it’s invalid:

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

[kuovonne]

Dani_Oliver:

Can I be a total newbie and ask WHERE to put this formula?

Put it in place of the field name. I have broken up your formula into multiple lines to make it easier to read.

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

[Dani_Oliver]

Incredible. Thank you for your patience/explaining!

[Leo_Frishberg]

Thanks @kuovonne for your solution (which I discovered after I tried something similar). I also had this problem, compounded by having three linked record fields (which I didn’t want to solve by creating a combined record solely for creating a “local variable”).
In my case, I’m trying to extract a key piece of text that is in any of these records (separated by an underscore) that flags the time frame for the entry (a “release date” in my case). But when I try to do that with a simple “FIND() MID()” I get the same result others have mentioned:

I solved it using a variant of what you mention above:

Here’s the variant I chose to use, but I suspect it’s a worse solution than simply making a local variable column…
"WW " & DATETIME_FORMAT(Date,‘WW’) & " Release " &

IF(
   FIND('_', 
            SUBSTITUTE({Jonn's OKRs},'"','',1) & SUBSTITUTE({Jane's OKRs},'"','',1) & SUBSTITUTE({Mann's OKRs},'"','',1)
        ),
    MID( SUBSTITUTE({Jonn's OKRs},'"','',1) & SUBSTITUTE({Jane's OKRs},'"','',1) & SUBSTITUTE({Mann's OKRs},'"','',1),
       1,
       FIND('_',
                SUBSTITUTE({Jonn's OKRs},'"','',1)& SUBSTITUTE({Jane's OKRs},'"','',1)&SUBSTITUTE({Mann's OKRs},'"','',1)
           )-1
        )
    ,""
   )

Which translates to: If there’s an underscore in the combination of linked fields, then extract everything from the start of the combined fields up to that underscore, eliminating any " marks along the way.
The problems with this solution include:

1. It’s very difficult to read (and therefore maintain)

2. It may take far more processing than if I’d simply made a local column that combined/substituted

But I don’t know enough about Airtable to know which would be less process-bound.
Thoughts on that last bit would be appreciated.

[kuovonne]

Leo_Frishberg:

Here’s the variant I chose to use, but I suspect it’s a worse solution
…

The problems with this solution include:

1. It’s very difficult to read (and therefore maintain)
2. It may take far more processing than if I’d simply made a local column that combined/substituted

But I don’t know enough about Airtable to know which would be less process-bound.
Thoughts on that last bit would be appreciated.

First, congrats on getting a formula to to produce the results you want.
Second, congrats on recognizing that the formula you have will be hard to maintain.
Third, thank you for including screen captures. I would not have been able to understand your question without them.

It looks like you are trying to do two things: extract just the first digits of the linked record field and remove the quote mark. I think this is a good candidate for the new RegEx functions that Airtable recently released.

IF( {Jonn's OKRs}, 
  REGEX_EXTRACT({Jonn's OKRs}, "[0-9\\.]+"),
IF( {Jane's OKRs}, 
  REGEX_EXTRACT({Jane's OKRs}, "[0-9\\.]+"),
IF( {Mann's OKRs}, 
  REGEX_EXTRACT({Mann's OKRs}, "[0-9\\.]+")
)))

---

By the way, unless the amount of processing time is affecting your workflows, don’t worry about how much processing time your formula takes. The processing is done on Airtable’s servers and Airtable will deal with that. A formula that you understand and can maintain is more important than a formula that runs slightly faster.

[Leo_Frishberg]

And thank you Kuovonne for responding so quickly and succinctly.

Of course Regex is the answer! It’s the answer to almost any string processing! :slightly_smiling_face:

But, even after 20 years of playing around with them, I still find them even more opaque than the code I wrote. (But that’s on me).

Thanks again