Beta 1 Function Reference
This document describes the operators and functions that are currently supported in Project Siena Beta 1. Please see this link for the Beta 2 function reference
Contents
1.2 ThisItem operator for CustomGallery
Siena supports the following operators:
|
Operator type |
Symbol(s) |
Description |
Usage |
|
Parentheses |
( ) |
Precedence order enforcement, and grouping of subexpressions in a larger expression. |
Filter(T, A < 10) (1 + 2) * 3 |
|
Arithmetic operators |
+ |
Addition |
1 + 2 |
|
|
- |
Subtraction and sign |
2 - 1 |
|
|
* |
Multiplication |
2 * 3 |
|
|
/ |
Division |
2 / 3 |
|
|
^ |
Exponentiation |
2 ^ 3 |
|
|
% |
Percentage (eqv. to "* 1/100") |
20% |
|
Comparison operators |
= |
Equal to |
Price = 100 |
|
|
> |
Greater than |
Price > 100 |
|
|
>= |
Greater than or equal to |
Price >= 100 |
|
|
< |
Less than |
Price < 100 |
|
|
<= |
Less than or equal to |
Price <= 100 |
|
|
<> |
Not equal to |
Price <> 100 |
|
String concatenation operator |
& |
Concatenation |
"hello" & " " & "world" |
|
Logical operators |
&& |
Logical conjunction |
Price < 100 && slider.value = 20 |
|
|
|| |
Logical disjunction |
Price < 100 || slider.value = 20 |
|
|
! |
Logical negation |
!(Price < 100) |
|
Membership operators |
in |
Belonging to a collection or table
|
gallery!Selected in SavedItems |
|
|
in |
Substring test (case sensitive) |
"Andrew" in "Andrew Smith" |
|
Disambiguation operator |
@ |
Field disambiguation |
MyTable[@fieldname] |
|
|
|
Global disambiguation |
[@MyTable] |
|
Expression chaining |
; |
Separate invocations of functions in behavior properties. |
Collect(T, A); Navigate(S1, "") |
The in operator can be used in predicates for checking for membership in tables or collections, or for substring tests.
1. Assuming you have a textbox control instance named Name in your app, the following predicate returns true if Name!Text matches any one of the strings listed within the square brackets. Also note that the square bracket notation used below is a shorthand for creating temporary inline collections for use within predicates.
Name!Text in [“Mike”, “Dave”, “Russ”, “Janine”]
2. Assuming you want to check if the user input in the textbox Name matches any one of the entries in EmployeeName column of Employee table, you can use the following predicate:
Name!Text in Employee!EmployeeName
3. Assuming you want to test whether the employee’s last name is “Smith”, you can use the following predicate:
“ Smith” in EmployeeLastName
A CustomGallery control instance can be bound to a table or collection. The control will create enough instances of its user-defined template (the user decides what get displayed by adding controls to the CustomGallery template) to display all the rows of the bound table or collection. The ThisItem reference operator allows users to refer to rows that individual template instances have been bound to from within the template instance.
For example, assume you have bound a CustomGallery instance to Employee table that contains the columns: FirstName, LastName, Organization, and OfficeLocation. Also assume you have added label1 through label4 to the CustomGallery template to display the column values. You now need to set the Text properties of the labels to the following:
· ThisItem!FirstName
· ThisItem!LastName
· ThisItem!Organization
· ThisItem!OfficeLocation
Please note that for nested galleries, ThisItem refers to the innermost gallery’s items. Assuming the row fields in the inner and outer galleries do not conflict, one can also use the unqualified field (column) names directly, which allows rules in an inner gallery to refer to an outer gallery’s items.
The following functions are supported in the current release of Siena. Many of these functions will be familiar to users who have worked with Excel.
Usage: Abs(number)
Abs(columnExpression)
Returns the absolute value of a number, the number without its sign. The function can be used in the following context:
1. Abs(number)
Returns the absolute value of a number, a number without its sign.
2. Abs(columnExpression)
Given a single-column table of numeric values, this function returns a single-column table consisting of their corresponding absolute values.
Example:
1. Abs(-55) returns 55.
2. Abs([-2, 33]) returns [2, 33].
Usage: AddColumns(table, column1, expression1, [column2, expression2], ...)
Returns a table with one or more new columns added that contain results of the specified expressions evaluated over the rows within the original input table.
Examples:
1. To create a new column named ScrubbedColumn with instances of blanks in column InputColumn in table SampleData replaced with zeros:
AddColumns(SampleData, "ScrubbedColumn", If(IsBlank(InputColumn), 0,
InputColumn))
2. To create a new column, named TotalSales, that computes an expression over the rows of the Sales table:
AddColumns(Sales, "TotalSales", CostPerUnit * UnitsSold)
3. To add a column to Personnel table that concatenates the first (column FirstName) and last names (column LastName) of the employees:
AddColumns(Personnel, "FullName", FirstName & " " & LastName)
Usage: And(logicalExpression1, [logicalExpression2], ...)
Computes the logical conjunction of one or more Boolean values or sub expressions. The same outcome can be achieved by using the inline && operator.
Examples:
1. To check if a slider's value falls between 50 and 100:
And(slider1!Value > 50, slider1!Value < 100)
The above expression can also be written using the inline && operator as:
Slider1!Value > 50 && slider1!Value < 100
2. To identify employees in Marketing Department whose offices are in building 41 (we assume OfficeLocation and Organization are columns in the Employee table):
And(Employee!Organization = "Marketing", Employee!OfficeLocation = 41)
The above expression can also be written using the inline && operator as:
Employee!Organization = "Marketing" && Employee!OfficeLocation = 41
Usage: Average(table, expression)
Average(expression1, [expression2], …)
Returns the average (arithmetic mean) of its arguments. This function can be used in the following contexts:
1. In the context of a table, this function returns the average of the numbers the specified expression evaluates to.
2. When provided scalar numeric inputs (or expressions that evaluate to scalar numeric values), this function returns their average.
Examples:
1. To compute average sales from Sales table:
Average(Sales, CostPerUnit * UnitsSold)
2. To compute the average of values from three sliders:
Average(slider1!Value, slider2!Value, slider3!Value)
Usage: Clear(collection)
Clears all the items from a collection and returns an empty collection.
Important: This function modifies the underlying collection.
Example:
Clear all the items from a shopping cart collection named Cart:
Clear(Cart)
Usage: Collect(collection, item1, [item2], ...)
Collect(collection, table)
Adds one or more individual items to a collection, or adds all rows from a table to a collection. If the specified collection does not already exist, a new one gets created with the item(s) that is being added.
Important: This function modifies the underlying collection.
Examples:
1. Adding user-updated properties of UI elements to a collection:
Collect(Selections, {Name: textBox1!Text, Address: textBox2!Text}
, {Name: textBox3!Text, Address: textBox4!Text})
2. Creating a single-column collection named EmployeeIDs that contains IDs of all employees (stored in the Id column of the Employee table):
Collect(EmployeeIDs, Employee!Id)
Usage: Concatenate(text1, [text2], ...)
Concatenate(columnExpression1, [columnExpression2], ...)
This function can be used to join several strings into one string. It can also concatenate output of several expressions (that return text) over columns within a table and return a single column containing the concatenated text.
Examples:
1. Appending "By " to the text within a textbox:
Concatenate("By ", textbox1!Text)
2. Concatenating first and last names for all the employees in the Employee table.
Concatenate(Employee!FirstName, " ", Employee!LastName)
Usage: CountA(column)
Counts the number of cells that are not empty in a table column. Please note that this function includes error values and empty text (“”) in the count (similar in behavior to the CountA function in Excel).
Example:
To count the number of products in the Product table that have ProductDescription specified:
CountA(Product!ProductDescription)
Usage: Count(column)
Counts the number of cells in a table column that contain numbers. Please note that this function excludes non-numeric types from the count (similar in behavior to the Count function in Excel).
Example:
To count the number of products in the Product table (we assume that the table contains a single row per product, and that each product has a unique numeric ProductID):
Count(Product!ProductID)
Usage: CountIf(table, predicate)
Counts the number of rows, within a table, that satisfy the given predicate.
Example:
To count the number of employees whose salary is greater than 100,000:
CountIf(Employee, Salary > 100000)
Usage: CountRows(table)
Counts the number of rows within a table.
Example:
To count the number of rows in the Employee table:
CountRows(Employee)
Usage: Date(year, month, day)
Date returns the sequential serial number that represents the specified date. The result can be formatted using Text to display in mm/dd/yyyy format. The function mimics Excel's handling of the input parameters as described in the following:
· Year:
o If the year is between 0 and 1899 (inclusive), the function adds that value to 1900 to calculate the year.
o If the year is between 1900 and 9999 (inclusive), the function uses that value as the year.
o If the year is less than 0 or is 10000 or greater, the function returns an error value.
· Month:
o If month is greater than 12, the function adds that number of months to the first month of the year specified.
o If month is less than 1, the function subtracts that those many months, plus 1, from the first month of the year specified.
· Day:
o If day is greater than number of days in the specified month, the function adds those many days to the first day of the month and return the corresponding date from a subsequent month.
o If day is less than 1, the function subtracts those many days, plus 1, from the first day of the month specified.
Example:
Date(1979, 03, 17)
Usage: DateValue(string)
Converts a date stored as text to a serial number that Siena recognizes as a date. While the function mimics Excel’s handling of parameters, the output value is based on the JavaScript reference point/epoch of 1/1/1970 (versus the Excel reference point/epoch of 1/1/1900). The following date formats are supported.
Example:
DateValue(“03/17/1979”)
Usage: Day(datetime)
The Day function returns the day from a datetime value. The returned value can range from 1 to 31.
Example:
Day(DateValue(“03/17/1979”))
Usage: Distinct(table, expression)
Evaluates an expression over one or more columns of a table and returns a one-column table that contains distinct values for the evaluated expression.
Example:
To generate a list of the various departments employees have been assigned to in the Employee table:
Distinct(Employee, Department)
Usage: DropColumns(table, column1, [column2,…])
Returns a table with one or more columns dropped from the original input table.
Example:
To drop FirstName, LastName and Address columns from the Employee table:
DropColumns(Employee, “FirstName”, “LastName”, “Address”)
Usage: Filter(table, predicate1, [predicate2], ...)
Returns the rows within the specified table that satisfy the given predicates. One or more predicates can be specified by the user and they get And-ed implicitly.
Example:
To return the rows for employees whose salary is greater than 100,000:
Filter(Employee, Salary > 100000)
Usage: First(table)
Returns the first row from the specified table.
Example:
To return the first row from the Employee table:
First(Employee)
Usage: FirstN(table, [num_rows])
Returns the specified number of rows from the beginning of the table. The num_rows parameter is optional. If it is not specified, this function simply returns the first row.
Example:
To return the first 10 rows from the Employee table:
FirstN(Employee, 10)
Usage: Hour(datetime)
Returns the hour as a number between 0 (12:00:00 AM) and 23 (11:00:00 PM).
Example:
Hour(TimeValue(“03/17/1979 10:20:30 PM”))
Usage: If(predicate1, expression1,
[predicate2, expression2,
...
predicateN, expression,
default])
Returns the result of evaluating the expression that corresponds to the first matching predicate. If none of the predicates match, default is evaluated and its result gets returned.
Examples:
1. Return a risk category based on employee salary:
If(Employee!Salary < 100000, "High",
Employee!Salary < 200000, "Medium",
Employee!Salary >= 200000, "Low")
2. Return text based on the sum of the values of 2 sliders:
If(slider1!Value + slider2!Value < 100, "Ok!", "Threshold exceeded!")
Usage: IsBlank(expression)
This function takes an expression as input and returns true if the expression evaluates to blank (no value), or false otherwise.
Example:
To check if a required textbox has been filled out:
IsBlank(text!Text)
Usage: IsEmpty(table)
This function takes an expression that evaluates to a table as input and returns true if the table is empty, or false otherwise.
Example:
To check if the Employee table contains any data:
IsEmpty(Employee)
Usage: IsError(expression)
Checks if an expression results in an error and returns true or false accordingly.
Example:
To check if some arithmetic expression produced an error:
IsError(1/0)
Usage: Last(table)
Returns the last row from the specified table.
Example:
To return the last row from the Employee table:
Last(Employee)
Usage: LastN(table, [num_rows])
Returns the specified number of rows from the end of the table. The num_rows parameter is optional. If it is not specified, this function simply returns the last row.
Example:
To return the last 15 rows from the Employee table:
LastN(Employee, 15)
Usage: Launch(hyperlink)
Launches the default app associated with the specified hyperlink.
Example:
Launch(“http://www.bing.com”)
Usage: Left(text, num_chars)
Left(columnExpression, numericExpression)
Returns the specified number of characters from the beginning of the given string.
Examples:
1. To return the first 5 characters from a text box:
Left(text!Text, 5)
2. To return the first 5 digits for each row of the FullZipCode column in the Employee table:
Left(Employee!FullZipCode, 5)
Usage: Len(text)
Len(columnExpression)
Returns the length of a specified string. The function can be used in the following context:
1. Len(text)
Returns the number of characters in a string.
2. Len(columnExpression)
Given a single-column table of string values, this function returns a single-column table consisting of their corresponding string lengths.
Example:
1. Len(“Siena”) returns 5.
2. Len([“Siena”, “Project”]) returns [5, 7].
Usage: LoadData(filename)
Returns the data stored in the specified file as a table value. This file is located within the app’s own protected space. Use this in conjunction with SaveData to save and load application data to and from app local storage
Please note that LoadData is an asynchronous function and cannot be used as part of a predicate. Also note that the result of LoadData should be piped into a collection whose schema is already known, since LoadData itself does not provide a result schema.
Example:
To read data from file Notes into a collection MyNotes:
Collect(MyNotes, LoadData(“Notes”))
To save data from MyNotes collection to a file named Notes:
SaveData(MyNotes, “Notes”)
Usage: LookUp(table, predicate, expression)
LookUp takes 3 arguments: a table, a predicate that evaluates to true or false for each row within the table, and an expression. For the first row for which the predicate evaluates to true, the expression gets evaluated and the result is returned.
Example:
To look up the salary for employee named “John Smith”:
LookUp(Employee, FirstName = “John” && LastName = “Smith”, Salary)
Usage: Lower(text)
Lower (columnExpression)
Converts all the letters in the text string to lowercase. The function can be used in the following context:
1. Lower(text)
Converts all letters in a text string to lowercase.
2. Lower(columnExpression)
Given a single-column table of string values, this function returns a single column table consisting of their corresponding lowercase values.
Example:
1. To return the lower case characters for a text string:
Lower(text!Text)
2. To return the lower case characters for each row of the FirstName column in the Employee table:
Lower(Employee!FirstName)
Usage: Max(table, expression)
Max(expression1, [expression2, …])
Returns the max value of its arguments.
Examples:
1. To compute highest sales from Sales table:
Max(Sales, CostPerUnit * UnitsSold)
2. To compute the max of values from three sliders:
Max(slider1!Value, slider2!Value, slider3!Value)
Usage: Mid(text, start_position, num_chars)
Mid(textColumn, start_positions, num_chars)
Returns the characters from the middle of a string, given the starting position and the number of characters to extract.
Examples:
1. Mid(“ABCDEFG”, 3, 3) returns “CDE”.
2. Mid( [“ABCDEFG”, “Sienna”], [2, 3], [3, 3]) returns [“BCD”, “enn”].
Usage: Min(table, expression)
Min(expression1, [expression2, …])
Returns the min value of its arguments.
Examples:
1. To compute lowest sales from Sales table:
Min(Sales, CostPerUnit * UnitsSold)
2. To compute the min of values from three sliders:
Min(slider1!Value, slider2!Value, slider3!Value)
Usage: Minute(datetime)
Returns the minute as a number between 0 and 59 (inclusive).
Example:
Minute(TimeValue(“03/17/1979 10:20:30 PM”))
Usage: Month(datetime)
Returns the month of a given date as a number between 1 and 12 (inclusive).
Example:
Month(DateValue(“03/17/1979”))
Usage: Navigate(targetScreen, Animation, [Context])
This functions causes the user to be transferred to the specified target screen. The current release supports the following transitions: ScreenTransition!Cover, ScreenTransition!UnCover and ScreenTransition!Fade. The function can also pass a state/context record to the target screen that can then be used to guide computation on the target screen. The fields in that context record can be accessed within the target screen unqualified.
Important: This function may modify the target screen’s context if a context record (third argument) is specified.
Example:
To navigate to the DetailView screen passing state that sets source to the string value "Ravens".
Navigate(DetailView, ScreenTransition!Fade, {source: "Ravens"})
The following example does not pass any context to the DetailView screen:
Navigate(DetailView, ScreenTransition!Fade)
The following example does not cause any animation to trigger when navigating to the DetailView screen:
Navigate(DetailView, “”)
Usage: Not(booleanExpression)
Computes the logical negation of a Boolean expression.
Example:
To negate if a radio button has been selected:
Not(radioButton!Selected)
Usage: Now()
Returns the current timestamp in the device’s locale-specific format. To format it, use the Text function.
Usage: Or(logicalExpression1, [logicalExpression2], ...)
Computes the logical disjunction of one or more Boolean values or sub expressions. The same outcome can be achieved by using the inline || operator.
Examples:
1. To check if a slider's value falls outside the 50 to 100 range:
Or(slider1!Value < 50, slider1!Value > 100)
The above expression can also be written using the inline || operator as:
slider1!Value < 50 || slider1!Value > 100
2. To identify employees whose offices are either in buildings 40 or 41 (we assume OfficeLocation is a column in the Employee table):
Or(Employee!OfficeLocation = 40, Employee!OfficeLocation = 41)
The above expression can also be written using the inline || operator as:
Employee!OfficeLocation = 40 || Employee!OfficeLocation = 41
Usage: Proper(text)
Proper(columnExpression)
Converts words in a string to proper case with the first letter in each word in upper case, and all the other letters in lowercase. The function can be used in the following context:
1. Proper(text)
Converts all letters in a text string to proper case.
2. Proper(expression)
Given a single-column table of string values, this function returns a single-column table consisting of their corresponding proper values.
Example:
1. To return the proper case characters for a text string:
Lower(text!Value)
2. To return the proper case characters for each row of the FirstName column in the Employee table:
Lower(Employee!FirstName)
Usage: Rand()
Returns a pseudo-random number greater than or equal to 0 and less than 1.
Example:
Rand()
Usage: Remove(collection, record1, [record2], ..., [All])
Remove(collection, table, [All])
Removes one or more rows from a collection. Since a collection can have duplicate records, this function also accepts an optional argument All that causes duplicates to be removed.
Important: This function modifies the underlying collection.
Example:
Remove an item from a shopping cart collection named Cart.
Remove(Cart, unselectedItem)
Usage: RemoveIf(collection, condition1, [condition2 ...])
Modifies and returns the collection where all rows that satisfy the specified conditions have been removed.
Example:
Remove items that cost more than $200 from the cart.
RemoveIf(Cart, Price > 200)
Usage: Replace(text, startIndex, count, new_text)
Replace(textColumn, startIndexColumn, countColumn, newTextColumn)
Replace part of a text string with a different text string.
Example:
1. Replace(“ABCDEFG”, 3, 2, “X”) returns “ABXEFG”
2. Replace( [“ABCDEFG”, “123”], [3, 2], [2, 1], [“X”, “Y”]) returns [“ABXEFG, “1Y3”]
Usage: RGBA(red, green, blue, alpha)
Return a color value consisting of the specified red, green, blue and alpha components.
Example:
To set a label’s font color:
RGBA(255, 127, 39, 1)
Usage: Right(text, num_chars)
Right(columnExpression, numericExpression)
Returns the specified number of characters from the end of the given string.
Examples:
Right(“ABCDE”, 4) returns “BCDE”
Usage: Round(number, decimal_places)
Round(numberColumn, decimalColumn)
Rounds off the number to the specified number of decimal places.
Example:
1. Round(23.444, 2) returns 23.44
2. Round( [23.444, 1.57], [2, 1]) returns [23.44, 1.6]
Usage: RoundDown(number, decimal_places)
RoundDown(numberColumn, decimalColumn)
Rounds down the number to the specified number of decimal places.
Example:
Round down 23.44 to 0 decimal places:
RoundDown(23.44, 0) returns 23.
Usage: RoundUp(number, decimal_places)
RoundUp(numberColumn, decimalColumn)
Rounds up the number to the specified number of decimal places.
Example:
Round up the average 23.44 to 1 decimal places:
RoundUp(23.44, 1) returns 23.5.
Usage: SaveData(collection, filename)
Saves the data (after encryption) from the collection to the specified file. This file will be located within the app’s own protected space. Use this in conjunction with LoadData to save and load application data to and from app local storage.
Please note that this function is asynchronous, and as such it cannot be used within predicates (for example predicates of Filter, CountIf, etc).
Example:
To save data from CustomerNotes collection to a file named Notes:
SaveData(CustomerNotes, “Notes”)
To load that data back (within the same app):
Collect(CustomerNotes, LoadData(“Notes”))
Usage: Second(datetime)
Returns the seconds component of the input as a number between 0 and 59 (inclusive).
Example:
Second(TimeValue(“03/17/1979 10:20:30 PM”))
Usage: Shuffle(collection)
Returns a copy of collection where rows within the table are randomy reordered (i.e. shuffled).
Example:
Shuffle the collection deck that stores playing card details (for example) in your app:
Shuffle(deck)
Usage: Sort(table, expression, [order])
Returns a copy of the collection where the rows in a table are sorted based on the result of the specified expression that evaluates to one of the supported expression types like number and its subtypes, string and its subtypes and Booleans. The function does not support sorting on aggregate values such as table and rows. The function also accepts an optional argument that indicates if the table should be sorted in ascending ("SortOrder!Ascending") or descending ("SortOrder!Descending") order. Default behavior is to sort in ascending order.
Example:
Sort the Employee table in descending order on Salary:
Sort(Employee, Salary, SortOrder!Descending)
Usage: Sqrt(number)
Sqrt(columnExpression1)
Returns the square root of a positive number. This function can also be used in the following contexts:
Example:
1. Sqrt(4) returns 2.
2. Sqrt([16, 36]) returns [4, 6].
Usage: StdevP(table, expression)
StdevP(expression1, [expression2], ...)
Returns the standard deviation of its arguments.
Examples:
1. To compute standard deviation of sales by region (assuming the Sales table contains one row per region):
StdevP(Sales, CostPerUnit * UnitsSold)
2. To compute the standard deviation of the values set for sliders 1 through 7:
StdevP(slider1!Value, slider2!Value, slider3!Value, slider4!Value,
slider5!Value, slider6!Value, slider7!Value)
Usage: Substitute(text, old_text, new_text, [instance_num])
Substitute(textColumn, oldTextColumn, newTextColumn, [instanceNumColumn])
Replaces part of a text string with a different text string. If the fourth optional argument is used, it specifies which instance to match and replace, starting with 1.
Example:
Substitute “ & ” with “ and ”:
Substitute(text!Text, “ & ”, “ and ”)
Usage: Sum(table, expression)
Sum(expression1, [expression2], ...)
Returns the sum of its arguments.
Examples:
1. To compute total sales:
Sum(Sales, CostPerUnit * UnitsSold)
2. To compute the sum of the values set for sliders 1,2 and 3:
Sum(slider1!Value, slider2!Value, slider3!Value)
Usage: Text(value, format_text)
Converts a value to a formatted text output. This function is useful for date and time formatting as well. The function supports some of the same formatting options as Excel.
Examples:
Text(23.45, “$#.#”) returns “$23.5”
Text(22.45, “#.00%”) returns “22.45%”
Text(DateValue(“10/15/1976”), “mm/dd/yyyy”) returns “10/15/1976”
Text(TimeValue(“10/15/1976 10:33pm EST”), “hh:mm:ss a/p”) returns “9:33:00 p” (PST. depending on time zone it may return a different time).
Usage: TimeValue(timetext)
Converts a time in text format to a number that represents the time in Microsoft Siena date-time code.
Examples:
Text(TimeValue(“09/06/1979 1:50:24AM”), “hh:mm:ss a/p”)
Usage: Time(hour, minute, second)
Converts the specified hours, minutes and seconds into a decimal, formatted as time.
Examples:
Text(Time(1, 50, 24), "hh:mm:ss a/p")
Usage: Today()
Returns the current date in the device’s locale-specific format.
Example:
Today()
Usage: Trim(text)
Trim(columnExpression)
Removes all spaces from a text string except for single spaces between words.
Example:
1. Trim(“The quick brown fox”) returns “The quick brown fox”
2. Trim([“The quick”, “ brown fox”]) returns [“The quick”, “brown fox”].
Usage: Update(collection, record1, record2, ["All"])
Updates a collection by replacing the matching record with the specified record and returning the resulting collection. To update all matches, specify optional argument "All".
Important: This function modifies the underlying collection.
Example:
Update([1, 2, 3], {Value:2}, {Value:22}) returns [1, 22, 3].
Usage: UpdateContext({name1: expression1, name2: expression2, …})
Updates the context of the current screen and binds the specified variables to the results from evaluating the specified expressions. See Navigate function above for details on alternate mechanism for setting context on a screen.
Important: This function modifies the underlying screen context.
Example:
UpdateContext({page: 5, displayItem: “Tablets”})
Usage: UpdateIf(collection, condition1, {column1: expression1, …}, [condition2, {column1: expression1, …} …])
Updates the specified columns with the results of the corresponding expressions for the rows that satisfy the specified conditions, and returns the modified collection.
Important: This function modifies the underlying collection.
Example:
Update items that cost more than $200 in Cart and set the NumItems to 0.
UpdateIf(Cart, Price > 200, {NumItems: 0})
Usage: Upper(text)
Upper(columnExpression)
Converts all the letters in the text string to uppercase. The function can be used in the following context:
1. Upper(text)
Converts all letters in a text string to uppercase.
2. Upper (columnExpression)
Given a single-column table of string values, this function returns a single column table consisting of their corresponding uppercase values.
Example:
1. To return the upper case characters for a text string:
Upper(text!Text)
2. To return the uppercase characters for each row of the FirstName column in the Employee table:
Upper(Employee!FirstName)
Usage: Value(text)
Converts a text string that represents a number to a number.
Example:
Value(“25”) returns 25.
Usage: VarP(table, expression)
VarP(expression1, [expression2], ...)
Returns the variance of its arguments.
Examples:
1. To compute variance of sales by region (assuming the Sales table contains one row per region):
VarP(Sales, CostPerUnit * UnitsSold)
2. To compute the standard deviation of the values set for sliders 1 through 7:
VarP(slider1!Value, slider2!Value, slider3!Value, slider4!Value,
slider5!Value, slider6!Value, slider7!Value)
Usage: Year(datetime)
Returns the year of a given date as a number between 1900 and 9999 (inclusive).
Example:
Year(DateValue(“03/17/1979”))