NOTE: to learn more about the Value Picker and its many function, please read the documentation.

Introduction

Ultimate Forms is a complete platform for no-code business solution development and automation for the Microsoft SharePoint environment. It comes in two versions, for Microsoft 365 and on premises. It contains all the necessary components to enter information, automate processes and visualize the output to facilitate business decisions.

Many components of Ultimate Forms allow you to configure conditions to select items or define new values for SharePoint list columns. In these cases you use the Value Picker to enter the values. These values can contain text, existing column values, arithmetic operations and special functions.

For example, when you use an Update list item action, you both select an item[s] to update and set the new column values. You will find Value Pickers in Form Designer, Actions, Alerts and many other components.

Value Pickers look just like ordinary data entry controls. But once you click on one of them, it will expand to show the full picker. You can then type in your values and also select from the list of columns on the left and from the list of functions on the right.

The picker automatically validates the entered value. The validation ensures that there are no grammatical errors and that the returned value is of the correct type. For instance, when entering a condition for a date/time column, the value must return a valid date/time. A relevant error message will display if what you've entered is somehow incorrect.

As mentioned, your value consists of simple text, column value (on the left side) and functions (on the right). Several types of functions are color-coded for convenience.

Value Picker offers a selection of powerful functions. For example, [Me] returns the current user, whereas $Year([date column]) returns the year part of the date value. Moreover, you can even use simple arithmetic, such as [Amount]+100.

You can combine text, columns and functions. You can also nest columns within functions and functions within functions.

Functions in the Spotlight

In this article I would like to focus on some of the most widely used functions. Our customers from around the globe successfully use this functions on a daily basis. You can find them in their forms, actions, alerts or import profiles.

$Year(DateTime)

Returns the year (number) from a date column value. For example, $Year([Created]) - returns the year an item was created. Value Picker will show an error if you try to pass a column that is not a date.

$Month(DateTime)

Similarly, returns the month (number) from a date column value.

$Day(DateTime)

Returns the day (number) from a date column value.

$Weekday(DateTime)

Returns the weekday (text) from a date column value. For example, it will return Saturday, if the language of your SharePoint site is English.

$Hour(DateTime)

Returns the hour (number) from a date column value. The value will be in 24-hour format.

$Minute(DateTime)

Returns the minute (number) from a date column value.

$WeekNumber(DateTime)

Returns the number of the week in the year (1-52). The numbering starts according to the regional settings of the site.

[Today]

Returns today's date

[Current User] or [Me]

Returns current user for assignment or conditions, e.g. Where Created by Equals [Me].

$Lookup(LookupField|DisplayField)

Returns the value of a column from a list used in a lookup column in the current list.

Consider the following: Leave Requests list uses a column Department, which is a lookup to Departments list and shows the values from its Title column. The Departments list also contains an another column, Abbr, which is used to store abbreviated names of the departments (such as SL for Sales or MK for Marketing). $Lookup([Department]|[Abbr]) will return the abbreviated name of the currently selected department. For instance, you can use it for generating IDs or referencing manager names.

$Extract(Text|Pattern)

Extracts a portion of text according to a pattern.

Your pattern can provide the required prefix and suffix surrounding the text you are looking for. For example, your Body column contains the contact name after the word "Contact:". $Extract([Body]|?Contact:^\r) will return the text from the word Contact: until the end of the line from anywhere within the column.

• ? - indicate that the prefix can be found anywhere within the text. When ? is not included, the prefix must be at the beginning of the text
• Prefix - optional, any text immediately preceding the extracted value
• ^ - value to extract
• Suffix - optional, any text immediately following the extracted value. When omitted, spaces, commas, periods and line breaks are considered the suffix. \r is the special character indicating that the suffix must be the line break (spaces, commas and periods are included in the extracted value).

For example, if your text is "First name: John", the pattern First name: ^ will return the word John. A possibly better pattern to use would be ?name: ^. The latter will work equally well in case of "Last name: Smith".

[List Title]

Returns the name of the current list

[Site Title]

Returns the name of the current site

[List URL]

Returns URL of the current list

[Site URL]

Returns URL of the current site

$Left(String|Length)

Returns the specified number of character from the beginning of a literal or column value. You can include column names for both arguments or even use functions within functions.

$Right(String|Length)

Returns the specified number of character from the end of a literal or column value. You can include column names for both arguments or even use functions within functions.

$Substring(String|Start|Length)

Returns the specified number of character starting from the a specific position within a literal or column value. You can include column names for all three arguments or even use functions within functions.

$Contains(String|String)

Returns whether or not (yes/no) the second argument value is found anywhere within the first argument value. You can include column names for both arguments or even use functions within functions.

$Find(String|String)

Returns the position (number) of the second value within the first value. If the value is not found, -1 is returned. You can include column names for all three arguments or even use functions within functions.

$GetValue(URL|ListName|ItemID|FieldName)

Returns a field value from an arbitrary list

• URL - optional, URL of the site. When omitted, current site is assumed, you must still include the pipe | character. In principal, you can provide URL of any site, assuming the user will have the necessary permissions to access it. Column values and functions are also permitted.
• ListName - name of the list to query. Column values and functions are permitted.
• ItemID - ID of the item to retrieve. Column values and functions returning a number are permitted.
• FieldName - name of the column to get the value from. Column values and functions are permitted.

$Text(DateTime/Number|Format)

Convert datetime or number value to string according to the specified format string. The format strings are according to Microsoft .NET specifications, e.g. given a date column value, dd-MM-yyyy could return 01-01-2013

$AddDate(DateTime|Amount|Unit)

Add/subtract value to a datetime value, e.g. $AddDate([Created]|10|day) will return the date 10 days after the Created date of the item.

$If(Boolean|Expression|Expression)

Returns either expression A or expression B result depending on the value of the Boolean argument. All 3 parameters can be literal, column values or functions. For the Boolean parameter you can even use comparison, such as [Amount]>10 or [Approval]!=Approved.

Conclusion

Value Picker is a powerful tool heavily used in all components of Ultimate Forms. It provides flexibility to your forms and automation, while ensure correct syntax and output type. By leveraging its functions you can greatly enhance the business solutions you build in your SharePoint.