Help

How do I get the Quotes to not show when using a formula for re-using a field output

Topic Labels: Formulas
Solved
Jump to Solution
186 4
cancel
Showing results for 
Search instead for 
Did you mean: 
RonniEloff_VKDe
7 - App Architect
7 - App Architect

I have a lookup field that I use to pick what Client the new record in my Activities Base is for called Contacts

It auto populates the field record properly when I select the new client

However because you can't have the first field contain a Lookup (DUMB BTW especially for rollups and such) I have to put a formula to use the data from the lookup field record to do the rest of my workflow for the client

It is putting the information in "" (see graphic) (Client field)

It did not do that when I just used the old fields of First Name /Last name when I concatenated them (Concatenate field) which used to be the way until we moved into a separate table for Contact information

Is there something I missed in setting up the Account name field or the Contact Field to prevent the " " (quotes) from being shown?

quotes.jpg

1 Solution

Accepted Solutions
Ben_Young1
10 - Mercury
10 - Mercury

Hey @RonniEloff_VKDe

Give this formula a shot:

IF(
    AND(
        {Contact},
        NOT(LOWER({Contact}) = "unnamed record")
    ),
    REGEX_REPLACE(
        TRIM({Contact}),
        '["]', ""
    )
)

See Solution in Thread

4 Replies 4
Ben_Young1
10 - Mercury
10 - Mercury

Hey @RonniEloff_VKDe

Give this formula a shot:

IF(
    AND(
        {Contact},
        NOT(LOWER({Contact}) = "unnamed record")
    ),
    REGEX_REPLACE(
        TRIM({Contact}),
        '["]', ""
    )
)

Thank you.  That worked very well.  Now I have a dashboard that looks right for the Client too 🙂  It does seem strange that you have to regex that but I can see why I didn't think of it...cause I didn't think it would be doing something like this.

The reason why the quotes appear is because there is a comma in your contact name and the formula is taking the contents of an array. When Airtable converts and array to text and an individual item has a comma or quotes, Airtable adds the quotes so you can differentiate the individual item. Airtable even does this when there is only one item in the array.

There are several possible formulas for removing the quotes. Most people use a straight substitution of the quote character with an empty text string. Note that if you ever have quotes that actually belong in the text and want to remove only the extra quote, you will need a more robust formula.


@kuovonne  wrote:

The reason why the quotes appear is because there is a comma in your contact name and the formula is taking the contents of an array

I completely forget these things after I set up the contacts table.  It's a concatenated field (of First/Laste Name) that I am using for the lookup from that table. 

I need to remember that these things.  Thank you for taking the time to reply.