Skip to main content
Solved

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

RonniEloff_VKDe
Forum|alt.badge.img+17

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?

Best answer by Ben_Young1

Hey @RonniEloff_VKDe! 

Give this formula a shot:

IF( AND( {Contact}, NOT(LOWER({Contact}) = "unnamed record") ), REGEX_REPLACE( TRIM({Contact}), '["]', "" ) )
View original
Did this topic help you find an answer to your question?

4 replies

Ben_Young1
Forum|alt.badge.img+20
  • Ben_Young1
  • Brainy
  • 520 replies
  • Answer
  • May 12, 2023

Hey @RonniEloff_VKDe! 

Give this formula a shot:

IF( AND( {Contact}, NOT(LOWER({Contact}) = "unnamed record") ), REGEX_REPLACE( TRIM({Contact}), '["]', "" ) )
RonniEloff_VKDe
1 person likes this
  • RonniEloff_VKDe
    RonniEloff_VKDe
RonniEloff_VKDe
Forum|alt.badge.img+17
Ben_Young1 wrote:

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.

kuovonne
Forum|alt.badge.img+27
  • kuovonne
  • Brainy
  • 6002 replies
  • May 12, 2023

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.

RonniEloff_VKDe
Forum|alt.badge.img+17
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. 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.

 

 

Reply


Most helpful members this week

Terms & Conditions