Skip to main content

Hi

I have to connect two sets of data, the link are users details which the two systems read differently
The record in system one is first and last name which is simple enough and I can concentrate to get
Billy.Basic etc.
The second system returns two types of result
Basic, Billy (CORP\XXXXXXXX)
Basic, Billy (CORP\billy.basic)
The X’s are a unique user id all user have, some users here log on by name some by thier id,
Where the result is the latter it doesn’t record the id at all, I think the easiest way to proceed is a formula that maps,
Basic, Billy (CORP\XXXXXXXX)** to billy.basic but I’m struggling to do it so any guidance appreciated.
Regards
Rob G

Well, the formula to turn LastName, FirstName to FirstName.LastName is

RIGHT(Name,LEN(Name)-FIND(' ',Name))&'.'&LEFT(Name,FIND(',',Name)-1)

To change everything to lowercase, simply wrap the above in LOWER().

Now, the fun part. To get rid of the '(CORP\XXXXXX)' you will need to use this:

LEFT(Name,FIND(' (',Name)-1)

(Note there is a space character before the ‘(’ in the above formula.) You can either replace each instance of 'Name' in the first formula with the formula above or (my recommendation) do this as a two-step process:

  1. Define a formula field called {TrimName} with this formula:
    LEFT(Name,FIND(' (',Name)-1)
  2. Define a formula field called {User} with this formula:
    LOWER(
    RIGHT(TrimName,LEN(TrimName)-
    FIND(' ',TrimName))&'.'&LEFT(TrimName,FIND(',',TrimName)-1)
    )

(Line-breaks are included just for clarity’s sake; hide {TrimName} if you prefer.)

The two-step process is, to me, easier to maintain; it may also execute faster. (I’ve not been able to determine if Airtable executes the first formula once and returns the resulting value whenever the field is called by another formula or if it executes the first formula on each call.)

Well, the formula to turn LastName, FirstName to FirstName.LastName is

RIGHT(Name,LEN(Name)-FIND(' ',Name))&'.'&LEFT(Name,FIND(',',Name)-1)

To change everything to lowercase, simply wrap the above in LOWER().

Now, the fun part. To get rid of the '(CORP\XXXXXX)' you will need to use this:

LEFT(Name,FIND(' (',Name)-1)

(Note there is a space character before the ‘(’ in the above formula.) You can either replace each instance of 'Name' in the first formula with the formula above or (my recommendation) do this as a two-step process:

  1. Define a formula field called {TrimName} with this formula:
    LEFT(Name,FIND(' (',Name)-1)
  2. Define a formula field called {User} with this formula:
    LOWER(
    RIGHT(TrimName,LEN(TrimName)-
    FIND(' ',TrimName))&'.'&LEFT(TrimName,FIND(',',TrimName)-1)
    )

(Line-breaks are included just for clarity’s sake; hide {TrimName} if you prefer.)

The two-step process is, to me, easier to maintain; it may also execute faster. (I’ve not been able to determine if Airtable executes the first formula once and returns the resulting value whenever the field is called by another formula or if it executes the first formula on each call.)


Thanks very much, I’ll give that a go.

Terms & ConditionsAccessibility statement