I need to parse out a string that looks like this:
In case it’s not obvious, that’s client/project/agent/$rate. This comes from a field named “ClientProjAgentRate_”.
Getting the rate is fairly easy, because the rate’s at the end of the string, and it follows a dollar sign. There should only ever be 1 dollar sign in the string, so this works:
LEN( CliProjAgentRate_ ) - FIND( "$", CliProjAgentRate_ )
But finding the agent in particular is rather harder. In FileMaker there’s a function POSITION() that more or less corresponds to Airtable’s FIND() – but there’s one important difference. The FileMaker POSITION() function allows me to indicate which occurrence of the search string I am interested in. So if the Airtable function worked like the FileMaker one, I could write
FIND ( “/”, CliProjAgentRate_, 1, 2 )
In other words, start from the beginning of the string (“1”) and find the SECOND occurrence of the slash (“2”). That second slash will always precede the agent value. I could then use the same function but change the occurrence to 3, drop these into a MID() function, and get the agent value.
And that’s basically what I’m doing in Airtable. Unfortunately Airtable’s FIND() doesn’t have that extra “occurrence” parameter. So it looks to me like I have to nest a series of FINDs, adding +1 to the result of each one, to get to the third slash. In other words,
locate the slash that starts
after the location of the slash that starts
after the location of the first slash.
Which ends up looking like this:
I’m glad I only have three slashes to deal with. And of course, this doesn’t PARSE the string. This just gets me the address of the third slash. Now I have to plug this bit and the bit that gets me the position of the second slash into a MID function.
Please tell me I’m missing something or that there’s a better way to do this! Thanks in advance.
p.s. I can think of an alternative, viz., using different delimiters, say ! instead of the first slash and @ instead of the second slash, and use $ alone in lieu of the third slash. But I’m wondering if there is a better way to do this job without changing the delimiters.