Other functions

Expressions may be used in various AppSheet features -- Initial Values, App Formulas, Virtual Columns and Column Constraints (Valid_If, Show_If, Required_If) -- to customize app behavior and provide your users with advanced functionality. Expressions in this article align with the Other Expressions in the Expression Builder of the Editor. The Expression Builder can be found anywhere you are able to enter a formula/expression, noted by a little "flask" symbol next to it. Clicking on the flask will bring up the Expression Builder. The Expression Builder is "context-aware," i.e. it shows you expressions that are relevant to the specific table you are editing. Also included in the builder is an "instant" expression checker, to verify that the expression is valid.

 

Expression components

Use any of the following values as part of an expression:

Constants

  • Words, Dates, Times. Highlight all values with "quotes", except for numeric values, e.g. "Value""01/01/2016", "12:00:00" for time, "012:00:00" for duration.
  • Numeric values are noted just as they are, e.g 10.

Column Names

  • Name any column using square brackets around the exact column name: [ColumnName]. When combining a [ColumnName] with another value, put the expression in (parentheses). May be used in any expression; however, when used in the Initial Value feature, it may only refer to a separate table.

 

Other Functions

Other expressions impact a range of scenarios and don't fit into any of the previous Yes/No, Math, Time, Columns, or Lists expression categories. AppSheet functions are not a 1:1 match with Google Sheets functions; however, in some cases the formatting similarity may help you construct your expressions. Alternatively, if the formatting is not similar, the Google Sheets function page may provide context for use of the function. If available, see the Google Sheets function link in parentheses next to applicable operators.

Current functions

  • LEN(<text-expression>) returns the length of a text value  (LEN)
  • CONCATENATE(<text-expression1>, <text-expression2>, ...) combines two or more text values  (CONCATENATE)
  • LEFT({Text},{Number}) returns a substring from the beginning of a specified string.  (LEFT)
  • RIGHT({Text},{Number}) returns a substring from the end of a specified string.  (RIGHT)
  • FIND({Fragment},{Text}) returns the position at which a string fragment is first found within text, case-sensitive.  (FIND)
  • CONTAINS({Text}, {Keyword}) returns true if the Keyword is found in the Text.
  • INITIALS({Name}) returns the first letter of each component of a Name. For example, INITIALS("Bruce Lee") returns "BL".
  • IF({condition},{then-expression},{else-expression})  (IF) returns either the then-expression or the else-expression, depending on the condition. The last two values (then, else) of the expression must be of the same type, i.e. text, number, etc.
  • UNIQUEID() creates unique Text values for Keys
  • HERE() returns the current LatLong of the current user
  • USEREMAIL() returns the Email of the current user.
  • USERNAME() returns the Name of the current user. Providers like Google only return the user's name if that user has enabled Google+ on their account.
  • TEXT_ICON({Text}) returns a Thumbnail image containing that text. This is particularly useful in constructing images from text data for better presentation in Gallery or Deck views. A common use case is TEXT_ICON(INITIALS([Student Name])).
  • HYPERLINK({Address}, {Text}) returns a hyperlink to the given address with the given clickable text. For example, the formula HYPERLINK("http://en.wikipedia.org", "Click here to visit Wikipedia") will create a link that looks like this: Click here to visit Wikipedia. If you use the HYPERLINK formula in a non-virtual column, make sure to mark "IsHyperLink" to true in the Type Qualifier. This ensures that the value of this column is written as a sheet =HYPERLINK formula.
  • LINKTEXT({Hyperlink}) returns the link text of the given hyperlink. For example, calling LINKTEXT(HYPERLINK("http://google.com", "Google")) will return the string "Google".
  • LINKURL({Hyperlink}) returns the URL part of the given hyperlink. For example, calling LINKURL(HYPERLINK("http://google.com", "Google")) will return the string "http://google.com".
  • LAT({LatLong}) returns the latitude of a LatLong value
  • LONG({LatLong}) returns the longitude of a LatLong value
  • LATLONG({Decimal}, {Decimal}) returns a LatLong type value with the given latitude and longitude, respectively
  • DISTANCE({LatLong}, {LatLong}) returns the approximate distance in kilometers between two LatLong values. DISTANCE does not currently work with Address values.

Legacy operators

For backwards compatibility, we also support the function syntax below for a set of functions that have been supported from the earliest AppSheet release.

  • @(_UNIQUE) to create unique Text values for Keys
  • @(_HERE) for LatLong of the current user
  • @(_USEREMAIL) for the Email of the current user
  • @(_USERNAME) for the Name of the current user

 

Common and complex expressions

Common expressions

  • LEN("AppSheet"): Returns 8.
  • CONCATENATE([First Name]," ",[Last Name]): Returns a Full Name.
  • IF([Status]="Open"."Green","Red"): Returns "Green" when Status equals Open; otherwise, returns "Red".

Example: Column called [AppName] with a value of "Sales-10305"

  • LEFT[AppName, 5]: Returns "Sales".
  • RIGHT[AppName, 5]: Returns "10305"
  • LEFT([AppName], FIND("-",[AppName])) gives you "Sales"

Use the following expressions in the Initial Value feature of the Editor:

  • UNIQUEID(): Use to generate a unique Text value, e.g. a unique Invoice ID.
  • HERE():  Use to identify the user's current LatLong.
  • USEREMAIL(): Use to populate record value based on user login
  • USERNAME(): Avoid USERNAME() because it is unreliable. Providers like Google, only return the user's name if that user has enabled Google+ on their account.

    If you wish to retrieve the user's name, do the following:
  1. Create a table having UserEmail as its key field followed by a UserName field.
  2. Populate the table manually, by adding one entry for each UserEmail address.
  3. To obtain the user name, utilize a reference expression based on USEREMAIL(). See References between tables.

Complex expressions

  • LEN([_THIS])<=10: Use this expression in the Valid_If Constraint to restrict form field input to a maximum of 10 characters.
  • IF([Status]="Open","Green",IF([Status]="Closed","Red",IF([Status]="Not Started","Blue","Purple"))): Returns "Green" when Status equals Open; returns "Red" when Status equals Closed; returns "Blue" when status equals Not Started; otherwise, returns "Purple".

 

Other expression patterns and examples

From the Expression Builder, follow the pattern below for a null, numeric, text, email, name, or LatLong result. See examples for further clarity.

Pattern Result Example
[{ref_column}].[{lookup_column}] null [Ref].[Ref]
LEN({*}) Number LEN([ChangeTimestamp])
CONCATENATE({*},{*}) Text CONCATENATE([ChangeTimestamp],"value_1")
LEFT({Text},{Number}) Text LEFT([Text],1)
RIGHT({Text},{Number}) Text RIGHT([Text],1)
FIND({Text},{Text}) Number FIND([Text],"text value")
IF({Yes/No},{*},{*}) * IF([Yes/No],"value_1","value_2")
USERNAME() Name USERNAME()
USEREMAIL() Email USEREMAIL()
UNIQUEID() Text UNIQUEID()
HERE() LatLong HERE()
Have more questions? Submit a request

0 Comments

Article is closed for comments.