- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 23, 2024 10:30 AM
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}
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 23, 2024 01:32 PM - edited Mar 23, 2024 08:41 PM
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}
))
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 23, 2024 08:33 PM - edited Mar 23, 2024 08:35 PM
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 23, 2024 08:42 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 23, 2024 08:54 PM - edited Mar 23, 2024 09:11 PM
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:
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 23, 2024 08:59 PM
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 23, 2024 09:44 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 24, 2024 07:53 PM
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.