Show and Tell
135655e5-74b0-44a6-99f1-cc122cba8fbe
135655e5-74b0-44a6-99f1-cc122cba8fbe
 · Posted in Show and Tell

Parsing words out of a string: Possible in SmartSuite?

TL;DR: SmartSuite needs a function that will tell me where a particular character is positioned in a string, like the FIND() function in Airtable or the POSITION() function in FileMaker.

(If you have time to kill, read on...)

I miss exactly two things about Airtable: Undo, and the function library. Actually, it's funny for me to hear myself say that, because I think Airtable's formula library is pretty weak, compared to what I've worked with in the past. For example, in FileMaker, to get the last name from a multi-word 'Name Entered' field, the formula is simply

RightWords ( 'Name Entered'; 1 )

In Airtable, it's considerably harder, because Airtable's functions have no idea what a "word" is. But at least it's possible in Airtable. I've got a base that can reliably extract the elements of my name ("William Malin Porter, Jr"). Here's the formula in Airtable for the field xNameLast:

IF(NameLastOverride, NameLastOverride,
  IF(zxNameBaseWordCount >1,
  TRIM(
    MID(
      SUBSTITUTE({zxNameBase}," ",REPT(" ",LEN({zxNameBase}))), 
      (zxNameBaseWordCount-1)*LEN({zxNameBase})+1, 
      LEN({zxNameBase})
     )
    )
  )
)

If you're familiar with Airtable at all, you'll see right away that the formula refers to at least four other fields. I suspect I could have done it all in a single formula but it would have been a nightmare to write and debug, so my approach involves multiple sequenced utility fields: a first field that trims the source string and removes otiose/superfluous spaces (like two spaces between middle name and last name), a second that counts the words in the cleaned-up string (= count of remaining spaces + 1), another that figures out where the spaces are, etc.

As far as I can tell, this simply ain't possible in SmartSuite. The only thing I can do (somewhat reliably) is count the number of words in a string, and even this is a bit of a chore. In FileMaker, it's

WordCount ( 'Name Entered' )

That function isn't thrown if somebody enters a name with unnecessary spaces, e.g. "Winston Churchill" (with 4 spaces between first name and last). In SmartSuite, here's what I have to do — or what I do (having not yet found a better way).

First, I have a field called 'y Name Entered Trimmed' that makes an attempt to remove superfluous spaces:

REPLACE( 
    REPLACE ( 
        TRIM( [Name Unvalidated] ), 
            "   ", " " ), 
    "  ", " "
)

Not very good, of course. If a name has eleven unnecessary spaces in it in a row, this isn't going to clean it up. But this handles 99% of my data. (NOTE: I name this field 'y Name Entered Trimmed'. A lowercase "y" is my personal convention for a mere utility field, one that never needs to be viewed by anybody.)

Then, now that I am pretty confident about the spaces, I can compare the length of the original string (with spaces) to the length of the string after the spaces are removed, add 1, and that is (should be) the number of words in the string. Formula for my field xNameWordCount =

( 
LENGTH ( [y Name Entered Trimmed] ) - 
LENGTH ( REPLACE([y Name Entered Trimmed], " ","") ) 
) + 1

But at this point, I'm stymied. To extract the last word from a string, I have to know not just how many words are in the string (got that sussed) but where the spaces in that string are located — each of them. Say the name is "Franklin Delano Roosevelt". If I know that a string contains 2 spaces (because it's three words), and I know that the second space occurs at position 16, then getting the last word is easy:

RIGHT ( 'Name Entered', Length ('Name Entered') - 16)

RETURNS: "Roosevelt". Yay! But in SmartSuite, I don't see a way to figure out where those spaces are located.

.

Concluding note: I said Airtable's function library is anemic compared to FileMaker's, and it is. Now Airtable does something that compensates, at least in theory, for the limitations of its native functions: It gives you access to regular expressions. The access is in fact fairly easy — but of course, regex itself is anything but. It would be great if SmartSuite eventually added support for regular expressions. But it would be much, much better if it could expand its function library to include a few simple, basic functions that would be tremendously useful, like

  • Function that returns position of a character or characters in a string, like Airtable's FIND() or FileMaker's POSITION().

  • Function that can replace a particular instance of a character or characters in a string, like Airtable's SUBSTITUTE().

  • Function that counts occurrences of a substring within a string, like FileMaker's PATTERNCOUNT().

  • And while I'm being greedy, one other I'd dearly love to have is a function that filters a string to show only certain characters, like FileMaker's FILTER(). This allows me to take "Dame Hilary Mantel 7/6/1952" and easily extract "7/6/1952" from it, by filtering for numerals and the "/" character.

functions
6
7 replies