String & Numeric Expressions

String Expressions

Concatenating strings: When you want to make a string value a combination of fixed text and or other string values, use concatenation within the expression:

[StringField1] + '-' + [StringField2] + '-' + [StringField3] + '-' + YEAR([DateField1])

String([NumberField1])

Returns a number value as a string.

Len([TextField])

Returns a numeric value representing the number of characters in a text field.

Let's say we have an Audit App and we want to know which Items have large comments added. We can use the following expression to find the number of characters in the comments for each Record using:

len([Comments])

So 'These are the comments' would return 22.

Substring Functions

stringContains(...stringToFind..., ..stringToSearch...): This function will return a true or false value based on whether a specified piece of text is in a Field. Suppose we have Inspection Item Records and it can be given a [Score] as 'Pass', 'Fail - Not Critical' or 'Fail Critical'.

We use the stringContains() function here to find out if the Item has Failed so the expression in our Field that finds out if it is a failure is:

stringContains('Fail',[Score])

This will return false if the item is a 'Pass' and true if the item is either 'Fail - Not Critical' or 'Fail Critical'.

stringFind(...stringToFind..., ...stringToSearch...): Similar to stringContains(), stringField() will search through text to see if a specific value is there. The difference here is that if the value does exist then instead of returning true, it will return the number of characters into the string that the specific value starts at. If the value is not in the string, then it will return -1.

Let's suppose we have a Contact App and we want to split it into the parts before and after the @ character. First knowing the position of the @ will help us to then split the email address so we can use:

stringFind('@',[Email])

If there is no email address in the Field then it will return -1 but assuming we have a valid email address, it will tell us the position of the @ character from the start. If the email address is support@sotools.net then the expression will return 7. We will now combine this with substring() to see how they can be used together.

substring([TextField], ...startFrom... , ...numCharacters...): Returns part of a string field, where the first parameter is the text Field being looked at, the second is the starting position to take the substring from and the the third parameter is the number of characters after the start position to return.

Now suppose we have email address Field in a Contacts App and we want to pull out the domain into a separate Field. We take the text to the right of the @ character. This would be easy if @ was always at character 6, and we have domains all of length 10, we just use:

substring([Email], 7, 10)

We know from above that we can find the position of the @ character by using stringFind('@',[Email]) so to write the domain we use:

substring([Email], stringFind('@',[Email]) + 1, 20)

This assumes the domain is no longer than 20 characters, if we wanted to be more specific we could also use len() to get:

substring([Email], stringFind('@',[Email]) + 1, len([Email]) - stringFind('@',[Email]) - 1)

This also shows how multiple functions can be used together to build up larger expressions.

stringLeft(...string..., ...numCharacters...): This function works similarly to substring() however it will always be the characters starting at position 0 or in other words the leftmost characters. Suppose we have Projects with a reference that is made up of a 2 character country code, then a 4 digit unique project ID. Now if we need to pull out the country code so we can then build a chart report, stringLeft() is the simplest function to use and would be in the format:

stringLeft([Reference], 2)

Now, a Reference of UK--4010 would return the country code 'UK'.

stringRight(...string..., ...numCharacters...): Very similar to the stringLeft() finction, the stringRight pulls the rightmost characters from a text value. If we wanted to use the same scenario where we have a project reference in the form country code followed by a four digit unique project ID but this time we want the project ID, then we'd use:

stringRight([Reference],4)

For a reference of UK--4010 will return the project ID '4010'.

NOTE: if the string is not long enough, an error is returned, so you may need to use Len() first e.g. If(Len([FieldName]) > 50, Substring([FieldName], 0, 50), [FieldName])

stringReplace([TextField], 'valueToReplace','valueToReplaceWith)

Takes a string value from a Field and replaces where the text in parameter two appears with the text in parameter three. If the value in [Title] is 'Mark to assess Risk'

stringReplace([Title], 'Mark', 'John') would give 'John to assess Risk'

Using stringReplace() to identify text contained within other text

The string replace function can also be used to identify if any text is contained within a value in another Field

For example, if we want to know if [TextField] with value 'some text' contains 'some'
Then:
if([TextField] = stringReplace([TextField], 'some', 'match'), false, true)
Or case insensitive:
if(toUpper([TextField]) = toUpper(stringReplace([TextField]), 'some', 'match'), false, true)

Adding a Line Break to an Expression

To add a line break to your text expression you can use @"'\n'" in between where you want the break with a + on either side, this need to be used in a long text box as this can be pulled down to other lines. Example below:

'abc' + @"'\n'" + 'def'

The above expression should come out as:

abc
def

Record Creator and Editor

CreatedBy() : Using this function returns the User ID of the user who created the record. ( Primarily used in Person Fields)

CreatedByUser() : Using this function returns the name of the User who created the record as text.

UpdatedBy() : Using this function returns the user ID for the latest entry in the activity stream. ( Primarily used in Person Fields)

UpdatedByUser() : Using this function returns the name of the User for the latest entry in the activity stream as text.

Reference & Identifiers

id(): returns the current record ID (as a string).

uuid(): Returns a 'Universally Unique ID' that can be used as a unique identifier - we suggest using this over the auto number field option if you want a unique identifier separate to the Record ID. It will be formatted as a 36-character string in hexadecimal with hyphens so in the format:

'550e8400-e29b-41d4-a716-446655440000'

The scenario could be that you would like to integrate to a third party software and you would like to keep the Softools Record ID private to your internal users and so choose to match Records based on a newly generated UUID for each Record.

appIdentifier(): returns the current app identifier (as a string.)

parentAppIdentifier(): returns the app identifier of the parent record (as a string) or the empty string if there is no parent record.

parentId(): returns the parent record ID (as a string) or the empty string if there is no parent record.

fieldLabel('...fieldId...'): returns the Label of the Field from the Field Definition in App Studio

fieldDescription('...fieldId...'): returns the Description of the Field from the Field Definition in App Studio

These two expressions are most likely to be used in combination within an Open AI Prompt. It could be that we have an employee satisfaction survey and we want some insight into improvement initiatives. We would like to send as part of the AI content, the Survey Answers. We can do this by building up a string in the form:

fieldLabel('QU01') + fieldDescription('QU01') + [QU01] + 
fieldLabel('QU02') + fieldDescription('QU01') + [QU02] + 
fieldLabel('QU03') + fieldDescription('QU01') + [QU03]

Over time, the survey questions and description of what they entail may change. but rather than us need to update our workflows, the dynamic referencing here will ensure that the latest survey question context is sent along with the survey answers for AI analysis.

Email Addresses

useremail([PersonField]): Returns the email address of the Person / User in the referenced Person Field

useridfromemail([Email]): Returns the userid of the user with the matching email address in the referenced email Field

Note: This function is not available on Public Sites.

ToUpper() and ToLower()

ToUpper([FieldName]): will convert "Joe Bloggs" to "JOE BLOGGS"

ToLower([FieldName]): will convert "Joe Bloggs" to "joe bloggs"

Numeric Expressions

Round([FieldName]): Rounds a value to the nearest integer or specified number of decimal places.

e.g. Round([FieldName], 2)

If [FieldName] had a value of 130.34567, the resulting value of Round would be 130.35

Double([StringField]): Returns the string as a number

Int([StringField]): Returns the string as an integer (Whole Number)

PoW([NumberField], 2): Returns the value of the Number Field to the Power of 2

Sqrt([NumberField]): Returns the square root of a specified number or numeric field

Log([NumberField],10): Returns the logarithm of a specified number or numeric field

Log10([NumberField]): Returns the base 10 logarithm of a specified number or numeric field

Truncate([NumberField]): Returns the Calculates the integral part of a number or numeric field

Make sure to click the 'Save' button when making any changes in order for them to be added to the next app version. Once you have made all the changes you need to an application you are then ready to publish it to workspace.

Note on Field Dependencies - Read more here

Field dependencies are by definition the other field(s) that are reliant on a given field, usually dependent fields are interlinked to one another by expressions and are indicated by an atom symbol. They are a crucial tool when looking to delete Fields within an App or, when it comes to understanding the structure and data flows of an App.