Comments/Tips on New Text Functions

With the release of Tap Forms 5.2.2, there are new Text functions available for Calculation Fields. I’ve played around with them a little bit, and thought it might be helpful to share some of the things I’ve found out about them and to provide an example that might be useful.

FUNCTION DESCRIPTIONS AND COMMENTS

Here are the new text functions. In the formula arguments, note that A and B represent text strings, while X represents a numerical value. Arguments can be constants, references to other fields, or expressions that evaluate to a text string or number, as needed. Initial descriptions are quoted from the Tap Forms “Edit Formula” sheet:

POS(A,B) — “Returns the position of B within the text A.”
Details: The returned value is an integer, so make sure to set the Result Type to Number, otherwise it won’t work. Note that it is the first occurrence of the search string that is reported.
Example: POS(“Hello123Hello456”,””o”) returns the numerical value 5, meaning that “o” was found at the fifth position of the input string.
Comment 1: An enhancement to this function would be to include a third argument that would be a start value for the search. So, for example, POS(“Hello123Hello456”,”Hello”,3) would return the value 9, since that is the first position where the full string “Hello” is found, beginning the search from the third character onward.
Comment 2: If Tap Forms is localized for any right-to-left languages, will this function scan the string and count characters from right to left?
Error Behaviours: If the search string is not found, the function returns NULL, not the number zero. I’m not suggesting it should be one way or the other, just reporting how it works.

LEFT(A,X) — “Returns the left-most X characters from the text A.” (I cleaned up the description a little bit).
Details: The returned value is a text string, so make sure to set the Result Type to Text, otherwise it won’t work.
Example: LEFT(“Hello123Hello456”,6) returns the text string “Hello1”.
Error Behaviours: The argument X would normally be an integer between the values of 1 and the full length_of_string. The function returns the following results for abnormal arguments: If X=<0, it returns the NULL value. If X has a decimal part, the decimal part is ignored. So X=5.9 will result in the left-most 5 characters being returned, same as X=5. If X > length_of_string, then the entire string is returned.

RIGHT(A,X) — “Returns the right-most X characters from the text A.” (I cleaned up the description a little bit).
Details: The returned value is a text string, so make sure to set the Result Type to Text, otherwise it won’t work.
Example: RIGHT(“Hello123Hello456”,6) returns the text string “llo456”.
Error Behaviours: (same behaviours as for LEFT(A,X)

WORKING EXAMPLE

This example assumes that you are importing name data from another source, and the values come in the format of LastName+FirstName. I know, the most likely delimiter would be a comma, not a plus sign, but bear with me. Your goal is to process the LastName+FirstName field to product two calculation fields: First Name and Last Name. So if your text field contains the value “O’Connor+Sinead”, then First Name would show “Sinead” and the Last Name would show “O’Connor”.

Last Name: This one is easier. The approach is to find the position of the “+” sign, call it N. Then the Last Name is simply the first N-1 characters of LastName+FirstName. The formula is LEFT(LastName+FirstName,POS(LastName+FirstName,”+”)-1). Result Type is Text.

First Name: This one is slightly more involved. If the “+” sign is at position N, then the First Name occupies the right-most K characters, where K equals the full length of the LastName+FirstName string minus N. The formula is RIGHT(LastName+FirstName,LENGTH(LastName+FirstName)-POS(LastName+FirstName,”+”)). Result Type is Text.

Comment: Okay, so why did I use the “+” sign in this example instead of a comma? Because it doesn’t work with a comma. Commas are used as delimiters within the Tap Forms functions, so if you try to insert a text argument of “,” then it fools the parser into thinking that there are too many arguments and the whole function fails. Believe me, I tried to make it work. I tried adding a separate text field named “Comma” with the value “,” and referencing that field in the name formulas. No dice. I also hoped that Tap Forms might recognize the CHAR(X) function, which returns the character associated with the ASCII value X (the ASCII code for a comma is 44). So I tried replacing “,” with CHAR(44), but also no joy. So until such time when we can ESCAPE a comma value in a function, the approach to this problem would be first to massage the source data in a word processor or spreadsheet to replace the comma character with a plus sign, then import the data into Tap Forms.

I hope that the above information and example will be useful in your applying these functions to your own forms.

— Mike