Skip to main content

Problem with Formula

  • March 23, 2024
  • 7 replies
  • 44 views
S
Forum|alt.badge.img+17

Hi, as a result of my trying to execute a solution from previous post, I created a formula to combine name fields. IT cannot figure out what aim doing wrong. IT seems perfectly logical to me. But it does not work. I have posted the formula and a screenshot of the data below.

The data is First Name, Last Name, SP First Name, SP Last Name. First Name and Last Name are short text fields. SP First Name and SP Last Name are Lookup fields.

Thanks, Scott

TRIM( {First Name} & IF({Last Name} != {SP Last Name}), " " & {Last Name} & " and " & {SP First Name} &" " & {SP Last Name}, IF({Last Name} = {SP Last Name}), " & " {SP First Name} & " " & {Last Name} )

 

 

 

7 replies

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • March 23, 2024

You have your closing parentheses in the wrong locations. This is how your formula should look:

TRIM(
{First Name} & 
IF(
{Last Name} != {SP Last Name}, " " & {Last Name} & " and " & {SP First Name} & " " & {SP Last Name},
IF(
{Last Name} = {SP Last Name}, " " & {SP First Name} & " " & {Last Name}
))
)

 

S
Forum|alt.badge.img+17
  • Scott_Brasted
  • Author
  • Inspiring
  • March 24, 2024

You have your closing parentheses in the wrong locations. This is how your formula should look:

TRIM(
{First Name} & 
IF(
{Last Name} != {SP Last Name}, " " & {Last Name} & " and " & {SP First Name} & " " & {SP Last Name},
IF(
{Last Name} = {SP Last Name}, " " & {SP First Name} & " " & {Last Name}
))
)

 


Hello and thanks. The formula still causes an error. Unfortunately, Airtable does not give you any clue as to what is wrong. So, I'm at a loss. The field names are correct and there are no red error marks in the formula itself. I just says it is an invalid formula. It seems tome this should be a straight forward formula.

I have posted a screenshot of the formula screen. Any help is appreciated. Best, Scott

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • March 24, 2024

Look at the colors to see where things have gone wrong. Look at the green ampersand within green quotes on the final line... that's where your problem is.

I didn't test your formula before... I was just correcting your parentheses. But now I have corrected your formula in my post above.

S
Forum|alt.badge.img+17
  • Scott_Brasted
  • Author
  • Inspiring
  • March 24, 2024

Ok, again thanks. The error are gone but one last issue. The formula is putting an AND after all the First Name Last Name Results.

Here is the formula as it stands now.

 

TRIM( {First Name} & IF( {Last Name} != {SP Last Name}, " " & {Last Name} & " and " & {SP First Name} & " " & {SP Last Name}, IF( {Last Name} = {SP Last Name}, " and " & {SP First Name} & " " & {Last Name}, IF(NOT({SP First Name}), " " & {Last Name} ))) )

 

Screenshot of results:

 

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • March 24, 2024

Another problem is that you can't compare text fields to lookup fields (which are not text fields but are arrays).

You can only compare fields that are the exact same field type.

So at the beginning of each IF() statement, you need to convert any lookup fields to text by changing any references to lookup fields to:

{Lookup Field} & ""

 p.s. If your company has a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld

S
Forum|alt.badge.img+17
  • Scott_Brasted
  • Author
  • Inspiring
  • March 24, 2024

Another problem is that you can't compare text fields to lookup fields (which are not text fields but are arrays).

You can only compare fields that are the exact same field type.

So at the beginning of each IF() statement, you need to convert any lookup fields to text by changing any references to lookup fields to:

{Lookup Field} & ""

 p.s. If your company has a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld


This is for a Rails to Trails that doesn't have money projects like this. I have a lot of years of experience with Access. But. find the formulas in Airtable different enough from Access Basic that I have trouble with them. There are a lot of things to like about Airtable, but the help text available is less than stellar. I did not find anything about lookups being arrays ion reading about this problem. I admit I am still not sure what is wrong or how to apply the fix you mentioned. It seems it should simpler than this. For no code, this is really not even close to easy.

pressGO_design
Forum|alt.badge.img+21

What if you switched up the logic a little and added a field that evaluated whether Last Name = SP Last Name and generated the SP Name from that? So you have a field called SP Salutation (or whatever), that is 

IF(AND(SP Member, SP Last = Last), SP First, IF(AND(SP Member, SP Last != Last)
SP First & “ “ & SP Last, “”))

Then the other formula is 

IF(LEN(SP Salutation) = LEN(SP First), First & “ and “ & SP Salutation & Last, First & “ “ & Last & “ and “ & SP Salutation)

I’m on mobile and it’s late and I just can’t with the brackets - I hope this makes sense.

Terms & ConditionsAccessibility statement