Comments/Tips on New Text Functions

[Mike Schwartz]

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

[Brendan]

Hi Mike,

Wow, thanks for posting this!

It would be great to have this type of documentation for all of the functions! :)

[Mike Schwartz]

You’re quite welcome; I was afraid it might be TLDR for a lot of people. Feel free to incorporate any of it you want in your documentation updates. Just to quickly reiterate points/suggestions addressed at you:

1. Expansion of the POS(A,X) function to POS(A,X,Y), where Y represents the starting number for the search. Not high priority, but potentially useful.
2. Operation of the POS(A,X) function in the context of right-to-left language localizations (if any).
3. Formula sheet description for LEFT(A,X) function: It would be more natural to say either “the left-most X characters” or “the X left-most characters” rather than “the left X most characters”. Try saying all three out loud, substituting the spoken word “seven” for “X”. :)
4. Same nit for the description of RIGHT(A,X).
5. Tough one: finding a way to allow a comma to appear within a function argument, even though commas are used to delimit the arguments. I could imagine maybe using a backslash as an escape character, so if \, appears in a function, the parser knows that the comma is not a delimiter. Or maybe it could interpret a backslash followed by an ASCII code: \44 means a comma as an argument, not a delimiter.

Thanks,
Mike

[Brendan]

I’m actually contemplating writing a routine to convert ALL formulas to use the semi-colon instead of the comma for the delimiter. It was stupid of me to not think properly about how that would work with different regions.

I was thinking of an update that once a document was opened, I would loop through all of the fields and change all the formulas to have semi-colon delimiters. It does present a bit of a problem though because if someone were to have a comma in their text within a formula, I wouldn’t want to accidentally convert that to a semi-colon.

Thanks for the wording suggestions. I’ve made those corrections.

[Mike Schwartz]

Yeah, it’s a tricky problem with the delimiters. You’re probably safe in assuming that right now people do not have a comma in their text within a formula, because the formula wouldn’t be working properly. So maybe it would work to run the conversion routine once on each document, and then store a flag to tell Tap Forms that the conversion has already been done and not to do it again, to guard against future inclusion of a comma.

Presumably, the software version that would include this routine will also have a modified formula parser that looks for semicolon delimiters. But then you might run into another problem: what if the formula originally contained a semicolon in text within a formula? Then the formula might break due to the extra semicolon delimiter.

So it still might make sense to incorporate an escape code to allow the semicolon. Then the conversion routine could first replace any semicolons with the escape code, then convert the commas to semicolons and set the conversion flag.

I’m sure if you do this it will require a lot of beta testing!

— Mike

[Karl Reed]

Converting numbers to characters and reverse. Hi Mike, I can’t see how to do that easily..

[Brendan]

Hi Karl,

What is your reason for wanting to convert numbers to characters? You can append numbers and characters together in the same formula. Look for the FORMAT() function.

Also, I’m sad to say that Mike died over a year ago :( It’s very sad because he was very active on my forum and he helped a lot of people.

Thanks,

Brendan

[obisbis]

That’s really sad!. I came across Mike’s posts many times in the past and always thought this guy is awesome, he was very detailed, efficient and had excellent writing skills. I didn’t really know him and for some reason feel very sad. All the best for his family and friends.

[Author]

Posts