# Build a function in Project Siena

In Project Siena, you can use the operators and functions that this topic describes.

All references to data in the following examples are hypothetical. No data samples accompany this reference.

## Operators

Project Siena supports these operators:

 Symbol Operator type Syntax Description ( ) Parentheses Filter(T, A < 10)(1 + 2) * 3 Precedence-order enforcement, and grouping of sub-expressions in a larger expression + Arithmetic operators 1 + 2 Addition - 2 - 1 Subtraction and sign * 2 * 3 Multiplication / 2 / 3 Division ^ 2 ^ 3 Exponentiation % 20% Percentage (equivalent to "* 1/100") = Comparison operators Price = 100 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 & String concatenation operator "hello" & " " & "world" Concatenation && Logical operators Price < 100 && slider!value = 20 Logical conjunction || Price < 100 || slider!value = 20 Logical disjunction ! !(Price < 100) Logical negation exactin Membership operators gallery!Selected exactin SavedItems Belonging to a collection or table exactin "Windows" exactin “To display windows in the Windows operating system...” Substring test (case-sensitive) in gallery!Selected in SavedItems Belonging to a collection or table in "The" in "The keyboard and the monitor..." Substring test (case-insensitive) @ Disambiguation operator MyTable[@fieldname] Field disambiguation [@MyTable] Global disambiguation ; Expression chaining Collect(T, A); Navigate(S1, "") Separate invocations of functions in behavior properties

### in and exactin operators

You can use the in and exactin operators to find a string in a data source, such as a collection or an imported table. The in operator identifies matches regardless of case, and the exactin operator identifies matches only if they're capitalized the same way. Here's an example:

1. Create or import a collection named Inventory, and show it in a gallery, as Create your first app describes.

2. Set the Items property of the gallery to this function:

Filter(Inventory, "E" in ProductName)

The gallery shows all products except Callisto because the name of that product is the only one that doesn't contain the letter you specified.

3. Change the Items property of the gallery to this function:

Filter(Inventory, "E" exactin ProductName)

The gallery shows only Europa because only its name contains the letter you specified in the case that you specified.

### ThisItem operator for galleries

You show data in a gallery by binding it to a table or a collection and then adding one or more types of controls to show different kinds of data. You use the ThisItem operator to specify the column of data that each control shows. For example, that operator in the product gallery for Create your first app specified that the image control showed the product design, the upper label showed the product name, and the lower label showed the number of units in stock.

For nested galleries, ThisItem refers to the innermost gallery's items. Assuming the row fields in the inner and outer galleries don't conflict, you can also use the unqualified field (column) names directly—this approach enables rules in an inner gallery to refer to an outer gallery's items.

## Functions

Project Siena supports the following functions. If you use functions in Excel, you may recognize many of them.

Note

In the syntax sections of this topic, italics indicate generic text that you replace with values that are specific for your app, and square brackets enclose optional arguments. If a comma appears before an optional argument, you must include the comma if you want to use the argument that it precedes.

### Functions by category

Boolean -- And, If, IsBlank, IsEmpty, Not, Or

Collections -- Clear, Collect, LoadData, Remove, RemoveIf, SaveData

Date and Time -- Date, DateAdd, DateDiff, DateTimeValue, DateValue, Day, Hour, Minute, Month, Now, Second, Time, TimeValue, Today, Year

More examples of how to manage dates and times.

Math -- Abs, Average, Max, Min, Rand, Round, RoundDown, RoundUp, Sqrt, StdevP, Sum, VarP

### Abs

 Syntax Abs(Number)Abs(ColumnExpression) Description Returns the absolute value of a number—that is, the number without its sign.Abs(Number) returns the absolute value of a number.Abs(ColumnExpression), given a one-column table of numeric values, returns a one-column table of their corresponding absolute values. Examples Abs(-55) returns 55.If a table contained a column named Trend, you could use Abs(Trend) in a Result column to return the absolute value of each number in the Trend column.

### AddColumns

Syntax

AddColumns(Table, Column1, Expression1[, Column2, Expression2, ...])

Description

Returns a table that has one or more added columns that contain results of the specified expressions evaluated over the rows in the original input table.

Examples

If you had a Sales table that contained a CostPerUnit column and a UnitsSold column, you could create a second table that contained both of those columns plus a third column, named TotalSales, that showed the results of multiplying the values in the first two columns.

AddColumns(Sales, "TotalSales", CostPerUnit * UnitsSold)

Note

This function doesn't modify the original table.

If you had an Employees table that contained a FirstName column and a LastName column, you could create a second table that contained both of those columns plus a third column, named FullName, that showed the results of concatenating the strings in the first two columns.

AddColumns(Employees, "FullName", FirstName & " " & LastName)

### And

 Syntax And(LogicalExpression1[, LogicalExpression2, ...]) Description Determines whether one or more Boolean values or sub-expressions are all true. (Accomplishes the same outcome as the inline && operator.) Examples This function determines whether a slider's value falls between 50 and 100:And(Slider1!Value > 50, Slider1!Value < 100)If a table contained a Dept column and a Salary column, you could use this function in a Result column to show true in all rows in which the value in the Dept column was HR and the value in the Salary column was larger than 200000.And(Dept = "HR", Salary > 200000)These functions use the && operator but return the same results as the previous examples:Slider1!Value > 50 && Slider1!Value < 100Dept = HR && Salary > 200000

### Average

 Syntax Average(Table, Expression)Average(Expression1[, Expression2, …]) Description Returns the average (arithmetic mean) of its arguments. You can use this function in these contexts:In a table, this function returns the average of the numbers that the specified expression evaluates to.When provided scalar numeric inputs—or expressions that evaluate to scalar numeric values—this function returns their average. Examples If you had a Sales table that contained a CostPerUnit column and a UnitsSold column, this function would compute the average sales:Average(Sales, CostPerUnit * UnitsSold)If you had three sliders, this function would compute the average of their values:Average(Slider1!Value, Slider2!Value, Slider3!Value)

### Char

 Syntax Char(Number) Description Returns the appropriate ASCII character for your platform, based on the value that you supply. Example Char(65)returns:AChar(105) returns:iChar(35) returns:#

### Clear

Syntax

Clear(Collection)

Description

Clears all of the items from a collection and returns an empty collection.

Important

This function modifies the underlying collection.

Example

1. Create or import a collection named Inventory, as Create your first app describes.

2. Add a button, and then set its OnSelect property to this function:

Clear(Inventory)

3. Press F5, click the Clear button, and then press Esc to return to the design screen.

To confirm that your collection is empty, press Alt-D, and then click Collections in the left navigation bar.

### Collect

Syntax

Collect(CollectionName, Item1[, Item2,...])

Description

Adds a table, a column within a table, or one or more individual items to a collection. If the specified collection doesn't exist, this function creates it and adds the item(s).

Important

This function modifies the underlying collection.

Examples

To create a collection that contains one column of values that you specify:

1. Add a button, and set its OnSelect property to this function:

Collect(Products, "Europa", "Ganymede", "Callisto")

This function creates a collection that's named Products and that contains a row for each of three product names.

2. Press F5, click the button, and then press Esc to return to the design workspace.

3. (optional) To display a preview of the collection that you created, press Alt-D, and then click Collections in the left navigation bar.

Add data from the user for more examples of how to use this function.

### ColorFade

 Syntax ColorFade(Color, FadeDelta) Description Returns a faded version of a color based on a fade delta that ranges from -1 to 1, inclusive. Examples ColorFade(Color!AliceBlue, -1)ColorFade(Color!DarkBlue, 1)ColorFade(RGBA(255, 255, 255, 1), 1)

### ColorValue

 Syntax ColorValue(ColorText) Description Returns the color value that corresponds to a CSS (cascading style sheet) color string. Examples ColorValue("Blue")ColorValue("Fuschia")

### Concat

 Syntax Concat(CollectionName, Expression) Description Concatenates all strings in a column that you specify in a data source that you specify. You can concatenate data in a collection or imported from, for example, Excel or a SharePoint list. Examples 1. Add a button, and set its OnSelect property to this function:Collect(Products, {String:"Violin", Wind:"Trombone", Percussion:"Bongos"}, {String:"Cello", Wind:"Trumpet", Percussion:"Tambourine"})2. Press F5, click the button, and then press Esc to return to the design workspace.3. Add a label, and set its Text property to this function:Concat(Products, String & " ")The label shows Violin Cello.

### Concatenate

 Syntax Concatenate(Text1[, Text2, ...])Concatenate(ColumnExpression1[, ColumnExpression2, ...]) Description Joins several text strings into one string, or concatenates the output of several expressions that return text but appear in different columns in a table, and returns the concatenated text in its own column. Examples If you created an input-text control named AuthorName, the following function would prepend "By" to text that the user typed in that control:Concatenate("By ", AuthorName!Text)If you had an Employees table that contained a FirstName column and a LastName column, the following function would concatenate the data in each row of those columns.Concatenate(Employees!FirstName, " ", Employees!LastName)

### Count

 Syntax Count(Column) Description Counts the cells in a table column that contains only numbers. Example Import or create a collection named Inventory, as Create your first app describes.Add a label, and set its Text property to this function:Count(Inventory!UnitsInStock)The label shows 5, the number of cells in the UnitsInStock column.

### CountA

 Syntax CountA(Column) Description Counts the cells that aren't empty in a table column. This function includes error values and empty text ("") in the count. Example Import or create a collection named Inventory, as Create your first app describes.Add a label, and set its Text property to this function:CountA(Inventory!UnitsInStock)The label shows 5, the number of non-empty cells in the UnitsInStock column.

### CountIf

 Syntax CountIf(Table, Expression) Description Counts the rows in a table that satisfy the given condition. Example Import or create a collection named Inventory, as Create your first app describes.Add a label, and set its Text property to this function:CountIf(Inventory, UnitsInStock<30)The label shows 2 because two products (Ganymede and Callisto) have fewer than 30 units in stock.

### CountRows

 Syntax CountRows(Table) Description Counts the rows in a table. Example Import or create a collection named Inventory, as Create your first app describes.Add a label, and set its Text property to this function:CountRows(Inventory)The label shows 5 because the collection contains five rows.

### Date

 Syntax Date(Year, Month, Day) Description Returns the sequential serial number that represents the specified date. You can use the DateValue function to customize the date display.YearIf Year is between 0 and 1899 (inclusive), the function adds that value to 1900 to calculate the year.If Year is between 1900 and 9999 (inclusive), the function uses that value as the year.If Year is less than 0 or is 10000 or greater, the function returns an error value.MonthIf Month is greater than 12, the function adds that number of months to the first month of the specified year.If Month is less than 1, the function subtracts that many months, plus 1, from the first month of the specified year.DayIf Day is greater than the number of days in the specified month, the function adds that many days to the first day of the month and returns the corresponding date from a subsequent month.If Day is less than 1, the function subtracts that many days, plus 1, from the first day of the specified month. Example If a user typed 1979 in an input-text control named HireYear, 3 in an input-text control named HireMonth, and 17 in an input-text control named HireDay, this function would return 3/17/1979:Date(Value(HireYear!Text), Value(HireMonth!Text), Value(HireDay!Text))More examples of how to manage dates and times.

### DateAdd

 Syntax DateAdd(Date, NumberOfUnits[, Units]) Description Returns a date that's a specified number of time units (days, quarters, months, or years) after a specified date. The first argument specifies the original date, and the second argument specifies the number of time units to add. By default, this function adds Days, but you can use a third optional argument to add Months, Quarters, or Years to the original date. Examples If today were 7/15/2013:DateAdd(Now(), 3) would return 7/18/2013.DateAdd(Today(), 1, Days) and DateAdd(Today(), 1) would both return 7/16/2013.DateAdd(Today(), 1, Months) would return 8/15/2013.More examples of how to manage dates and times.

### DateDiff

 Syntax DateDiff(StartDate, EndDate[, Units]) Description Returns the difference between two dates. By default, this function returns the result in Days, but you can specify a third optional argument to return the results in Years, Quarters, or Months. Examples If today were 7/15/2013:DateDiff(Now(), 1/1/2014) and DateDiff(Now(), 1/1/2014, Days) would both return 170 days.DateDiff(Now(), 1/1/2014, Months) would return 6 months.More examples of how to manage dates and times.

### DateTimeValue

 Syntax DateTimeValue(DateTimeText)DateTimeValue (DateTimeText, "LanguageCode") Description Converts a text representation of a date and a time to a value on which you can perform a mathematical or Boolean operation, such as comparing two dates. You can also specify a language code to ensure that a date structured with slashes is interpreted appropriately as MM/DD/YYYY or DD/MM/YYYY. Examples If you typed 10/11/2014 1:50:24.765 PM into an input-text control named Start and then set the Text property of a label to this function:Text(DateTimeValue(Start!Text), DateTimeFormat!LongDateTime)The label would show Saturday, October 11, 2014 1:50:24 PM if your computer were set to the "en" locale. You can use several options, other than LongDateTime, with the DateTimeFormat parameter. To display a list of those options, type the parameter, followed immediately by an exclamation point, in the function box.Text(DateTimeValue(Start!Text, "fr"), DateTimeFormat!LongDateTime)The label would show Monday, November 10, 2014 1:50:24 PM.Text(DateTimeValue(Start!Text), "dddd, mmmm dd, yyyy hh:mm:ss.fff AM/PM")The label would show Saturday, October 11, 2014 01:50:24:765 PM if your computer were set to the "en" locale.As an alternative, you can specify hh:mm:ss.f or hh:mm:ss.ff to round the time to the nearest tenth or hundredth of a second.More examples of how to manage dates and times.

### DateValue

 Syntax DateValue(DateText)DateValue (DateText, "LanguageCode") Description Converts a text representation of a date to a value on which you can perform a mathematical or Boolean operation, such as comparing two dates. The source data must follow one of these patterns:MM/DD/YYYYDD/MM/YYYYDD Mon YYYYMonth DD, YYYYYou can also specify a language code to ensure that a date structured with slashes is interpreted appropriately as MM/DD/YYYY or DD/MM/YYYY. Examples If you typed 10/11/2014 into an input-text control named Startdate and then set the Text property of a label to this function:Text(DateValue(Startdate!Text), DateTimeFormat!LongDate)The label would show Saturday, October 11, 2014, if your computer were set to the "en" locale. You can use several options, other than LongDateTime, with the DateTimeFormat parameter. To display a list of those options, type the parameter, followed immediately by an exclamation point, in the function box.Text(DateValue(Startdate!Text, "fr"), DateTimeFormat!LongDate)The label would show Monday, November 10, 2014.If you did the same thing on October 20, 2014:DateDiff(DateValue(Startdate!Text), Today())If your computer were set to the en language code, the label would show 9, indicating the number of days between October 11 and October 20. DateDiff can also show the difference in months, quarters, or years.More examples of how to manage dates and times.

### Day

 Syntax Day(DateTime) Description Returns the day from a DateValue function. The returned value can range from 1 to 31. Example If you added an input-text control named Text1, Day(DateValue(Text1!Text)) would return 15 if a user typed any of these strings into that control:07/15/201315 July 2013July 15, 2013More examples of how to manage dates and times.

### Disable

 Syntax Disable(Signal) Description Disables a signal data source so that an app can't pull signals (data) from it. Location is the only signal data source that this release supports. Example Disable(Location)

### Distinct

 Syntax Distinct(Table, Expression) Description 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 If you had an Employees table that contained a Department column, this function would list each unique department name in that column, no matter how many times each name appeared in that column:Distinct(Employees, Department)

### DropColumns

 Syntax DropColumns(Table, Column1[, Column2, …]) Description Returns a table that is based on the specified table but doesn't contain the specified columns. Example If you had an Employees table that contained a FirstName column, a LastName column, and an Address column, this function would return the same table except without those columns:DropColumns(Employees, "FirstName", "LastName", "Address")

### Enable

 Syntax Enable(Signal) Description Enables a signal data source so that an app can pull data (signals) from it. Location is the only signal data source that this release supports. Example Enable(Location)

### EncodeUrl

 Syntax EncodeUrl(Text) Description Encodes all instances of non-alphanumeric characters in a string that represents a URL. For example, you can use this function to help create a mail link by merging strings that contain From:, To:, Subject: and Body: fields. Example EncodeUrl("'http://example/page/url.aspx") returns:%27http%3A%2F%2Fexample%2Fpage%2Furl.aspx

### Filter

 Syntax Filter(Table, Condition1[, Condition2, ...]) Description Returns the rows in the specified table that satisfy the given conditions. By default, if you specify more than one condition, And (that is, &&) joins are used. Example If you had an Employees table that contained a Salary column, this function would identify the employees whose salaries were greater than 100,000:Filter(Employees, Salary > 100000)For more information about how to use this function, see Show, sort and filter a data set in Project Siena.

### Find

 Syntax Find(FindText, WithinText[, StartNum]) Description Indicates where one string appears in another string for the first time. The first argument specifies the string that you want to search for in another string. The second argument specifies the string in which you want to search. You can also include a third optional argument to ignore any instances before a certain location in the string in which you're searching. This function is case-sensitive and returns nothing if the string that you're looking for doesn't appear in the string that you're searching. Example Find("me", "time") returns 3 because "me" appears in "time" starting with the third letter.Find("my", "time") returns nothing because "my" doesn't appear in "time" anywhere.Find("in", "Honorificabilitudinitatibus", 4) returns 18.Find("in", "Honorificabilitudinitatibus", 19) returns nothing because no match was found after the 18th letter.

### First

 Syntax First(Table) Description Returns the first row from the specified table. Example If you had an Employees table, this function would return the first row from that table:First(Employees)

### FirstN

 Syntax FirstN(Table[, NumRows]) Description Returns the specified number of rows from the beginning of the specified table. The NumRows argument is optional; if it isn't specified, only the first row is returned. Example If you had an Employees table, this function would return the first 10 rows from that table:FirstN(Employees, 10)

### HashTags

 Syntax HashTags(Text) Description Creates a list of hashtags that start with a pound sign (#) and contain any combination of these kinds of characters:upper and lowercase lettersnumeralsunderscores (_)currency symbols (such as \$)Listed hashtags can't contain any other special characters. Example Add an input-text control, name it Tweet, and type this sentence into it:This #app is #AMAZING and can #coUnt123 or #123abc but not #1-23 or #\$*(#@")Add a vertical custom gallery, and set its Items property to this function:HashTags(Tweet!Text)Add a label to the gallery template. The label shows these hashtags.#app#AMAZING#coUnt123#123abc#1

### Hour

 Syntax Hour(DateTime) Description Returns the hour of a given TimeValue as a number between 0 (12:00:00 A.M.) and 23 (11:00:00 P.M.), inclusive. Example If you typed 10:20:30 PM into an input-control named EventTime, a label would show 22 if its text property were set to Hour(TimeValue(EventTime!Text)).More examples of how to manage dates and times.

### If

 Syntax If(Condition1, Result1[, Condition2, Result2, ConditionN, ResultN, DefaultResult]) Description Returns the result that corresponds to the first condition matched. If none of the conditions match, the Default result is returned. If you specify a string as a result, you must surround it with quotation marks. Examples Import or create a collection named Inventory, and show it in a gallery as Create your first app describes.Set the Text property of the lower label in the gallery to this function:If(UnitsInStock<30, "Order more!", UnitsInStock)If the number of units in stock is fewer than 30, the label shows the message between the quotation marks. Otherwise, the label shows the number of units in stock.

### IsBlank

 Syntax IsBlank(Expression) Description Returns true if an expression evaluates to blank (no value); otherwise, returns false. For example, you could combine this function with the If function to notify users when they leave a required field blank. Example If you created an input-text control named Quantity on an order form, a label with a Text property of If(IsBlank(Quantity!Text), "Please add a quantity.") would remind you if you forgot to specify a quantity in that form.

### IsEmpty

 Syntax IsEmpty(Source) Description Identifies whether a table or a collection contains any data. Example If a table named Employees contains data, IsEmpty(Employees) returns false; otherwise, the function returns true.

### Language

 Syntax Language() Description Returns the currently active language from the language preferences for an app that hasn't yet been published. If you change the language preference while Project Siena is open, you must restart Project Siena for the function to reflect the change.For a published app, this function returns the language with which the app was branded just before it was published. Example Language() could return en-US based on your configuration.

### Last

 Syntax Last(Table) Description Returns the last row from the specified table. Example If you had a table named Employees, this function would return the last row from that table:Last(Employees)

### LastN

 Syntax LastN(Table[, NumRows]) Description Returns the specified number of rows from the end of the table. The NumRows argument is optional; if it isn't specified, this function returns only the last row. Example If you had a table named Employees, this function would return the last 15 rows from that table:LastN(Employees, 15)

### Launch

 Syntax Launch(Hyperlink) Description Runs the app that's associated with the specified hyperlink and opens the hyperlink itself. Example Set the OnSelect property of a button to this function:Launch("http://www.bing.com")Press F5, and then click the button.The Bing homepage opens.

### Left

 Syntax Left(Text, NumChars)Left(ColumnExpression, NumericExpression) Description Returns the specified number of characters from the beginning of the given string. Examples Import or create a collection named Inventory, and show it in a gallery as Create your first app describes.Set the Text property of the lower label in the gallery to this function:Left(ThisItem!ProductName, 3)The label shows the first three characters in each product name.

### Len

 Syntax Len(Text)Len(ColumnExpression) Description Len(Text) Returns the number of characters in a string.Len(ColumnExpression) Given a one-column table of strings, returns a one-column table that contains the corresponding string lengths. Examples If you typed "F@V0riteBandName" into a text-input control named Password, Len(Password!Text) would return 16.

### LoadData

 Syntax LoadData(Collection, Filename) Description Decrypts the data in the specified file and inserts it into the specified collection. Use this function together with the SaveData function to save and load application data to and from app local storage.LoadData is an asynchronous function and can't be used in predicates. We recommend that the result of LoadData be piped into a collection whose schema is known, because LoadData itself doesn't provide a schema. Example Create your first app for an example.

### LookUp

 Syntax LookUp(Table, Condition, Expression) Description This function takes three arguments: a table, a condition that evaluates to true or false for each row in the table, and an expression. For the first row for which the condition evaluates to true, the expression is evaluated, and the result is returned. Example If you had an Employees table that contained a FirstName column and a LastName column, this function would return the salary for the employee named "John Smith":LookUp(Employees, FirstName = "John" && LastName = "Smith", Salary)

### Lower

 Syntax Lower(Text)Lower(ColumnExpression) Description Lower(Text) Converts the letters in the specified text string to lowercase.Lower(ColumnExpression) Given a one-column table of string values, returns a one-column table of the corresponding lowercase values. Examples Import or create a collection named Inventory, and show it in a gallery as Create your first app describes.Set the Text property of the lower label in the gallery to this function:Lower(ThisItem!ProductName)The label shows the name of each product in all lowercase letters.

### Max

 Syntax Max(Table, Expression)Max(Expression1[, Expression2, ...]) Description Returns the largest value among its arguments. Examples Import or create a collection named Inventory as Create your first app describes.Add a label, and set its Text property to this function:Max(Inventory, UnitsInStock)The label shows the highest value in the UnitsInStock column of the Inventory collection.

### Mid

 Syntax Mid(Text, StartPosition, NumChars)Mid(TextColumn, StartPositions, NumChars) Description Returns the characters from a string, given the position of the starting character and the number of characters to extract. You can run this function on a table of strings. Examples Import or create a collection named Inventory, and show it in a gallery as Create your first app describes.Set the Text property of the lower label in the gallery to this function:Mid(ThisItem!ProductName, 2, 3)The label shows second, third, and fourth letters in the name of each product.

### Min

 Syntax Min(Table, Expression)Min(Expression1[, Expression2, …]) Description Returns the smallest value among its arguments. Examples Import or create a collection named Inventory as Create your first app describes.Add a label, and set its Text property to this function:Min(Inventory, UnitsInStock)The label shows the lowest value in the UnitsInStock column of the Inventory collection.

### Minute

 Syntax Minute(DateTime) Description Returns the minute from a given TimeValue as a number between 0 and 59 (inclusive). Example If you typed 10:20:30 PM into an input-control named EventTime, a label would show 20 if its text property were set to Minute(TimeValue(EventTime!Text)).More examples of how to manage dates and times.

### Month

 Syntax Month(DateTime) Description Returns the month from a given DateValue as a number between 1 and 12 (inclusive). Example The function Month(DateValue("03/17/1979")) returns 3.More examples of how to manage dates and times.

### Navigate

Syntax

Navigate(TargetScreen, Animation[, Context])

Description

Changes the view to the specified target screen. These transition animations are supported: ScreenTransition!Cover, ScreenTransition!UnCover, and ScreenTransition!Fade. By specifying a Context argument, you can pass a state/context record to the target screen that it can then use to guide its computations. The target screen has unqualified access to the fields in the context record.

Important

If you pass a Context record to a target screen, its own context may be modified.

Examples

1. Name the default screen DefaultScreen, and add a label to it so that you can verify which screen is showing in Preview.

2. Add a second screen, name it AddlScreen, and add a label to it.

3. Add a button to DefaultScreen, and set its OnSelect property to this function:

Navigate(AddlScreen, ScreenTransition!Fade)

4. From the DefaultScreen, press F5, and then click the button.

AddlScreen appears.

### Not

 Syntax Not(BooleanExpression) Description Computes the logical negation of a Boolean expression. Example This function makes sure a radio button isn't selected:Not(RadioButton1!Selected)

### Now

 Syntax Now() Description Returns the current date and time in the device's locale-specific format. To format it, use the Text function. Example If today were October 11, 2014, at exactly 3:25 PM and you set the text property of a label to Text(Now(), "mm/dd/yyyy hh:mm:ss.fff"), the label would show 10/11/2014 3:25:00:000 PM if your computer were set to the en language code. The label would show 11/10/2014 3:25:00:000 PM if your computer were set to the fr language code.As an alternative, you can specify hh:mm:ss.f or hh:mm:ss.ff to round the time to the nearest tenth or hundredth of a second.If fractions of seconds don't matter in your app, you can use the DateTimeFormat parameter to specify the date, time, or both in any of several built-in formats. For example, you can replace the function in this example with Text(Now(), DateTimeFormat!ShortDateTime) to get the same results but without the milliseconds. To display a list of options for this parameter, type it, immediately followed by an exclamation mark, in the function box.More examples of how to manage dates and times.

### Or

 Syntax Or(LogicalExpression1[, LogicalExpression2, ...]) Description Returns true if any specified expression is true; otherwise, returns false. In contrast, the And function returns true only if all specified expressions are true.The Or function accomplishes the same outcome as using the inline || operator. Examples You can use this function to determine whether a slider's value falls outside the 50 to 100 range:Or(Slider1!Value < 50, Slider1!Value> 100)If a table contained a Dept column and a Salary column, you could use this function in a Result column to show true in all rows in which the value in the Dept column was HR or the value in the Salary column was larger than 200000:Or(Dept = HR, Salary >= 200000) As an alternative, you can use the || operator to get the same results as what the previous functions return:Slider1!Value < 50 || Slider1!Value> 100Dept = "HR" || Salary > 200000

### PlainText

 Syntax PlainText(TextWithTags) Description Strips HTML and XML tags from text or converts the tags to an appropriate symbol. Examples If you bind a text gallery to an RSS feed and then set the Text property (in the Data category) of a label in that gallery to ThisItem!description, the label might show raw HTML or XML code as in this example:

We have done an unusually "deep" globalization and localization.

If you set the Text property of the label to PlainText(ThisItem!description), the text appears as in this example:We have done an unusually "deep" globalization and localization.

### Proper

 Syntax Proper(Text)Proper(ColumnExpression) Description Converts words in a text string to proper case; that is, the first letter in every word is uppercase, and the other letters are lowercase.Proper(Text) Converts a string to proper case.Proper(Expression) Given a one-column table of string values, returns a one-column table that contains the corresponding proper-case values. Examples Add an input-text control, and name it Slogan. Add a label, and set its Text property to this function: Proper(Slogan!Text)If you type "WE ARE THE BEST!" into the text-input control, the label shows "We Are The Best!"

### Rand

 Syntax Rand() Description Returns a pseudo-random number that's greater than or equal to 0 but less than 1. Example Rand() could return 0.85116235, 0.76728732, 0.27591115, or any other number greater than or equal to 0 but less than 1.

### Refresh

Syntax

Refresh(ServiceDataSource)

Description

Refreshes the data from the specified data source so that the app has the most recent state.

Important

You can't use this function to refresh data from Excel tables. For an example of how to work with Excel data, see Show Excel data in a Project Siena app

Example

If you added an RSS feed named rss_1, Refresh(rss_1) would refresh that feed.

### Remove

Syntax

Remove(Collection, Record1[, Record2, ..., All])

Remove(Collection, Table[, All])

Description

Removes one or more rows from a collection. Because a collection can have duplicate records, this function also accepts an optional argument All that removes duplicates.

Important

This function modifies the underlying collection.

Example

1. Import or create a collection named Inventory, and show it in a gallery as Create your first app describes.

2. Set the OnSelect property of the image in the gallery to this function:

Remove(Inventory, ThisItem)

3. Press F5, and then click an image in the gallery.

The item is removed from the gallery and the collection.

### RemoveIf

 Syntax RemoveIf(Collection, Condition1[, Condition2 ...]) Description Removes from a collection all rows that satisfy the specified conditions and returns the modified collection. Example If you had a collection named ShoppingCart that contained a field named Price, this function would remove from the collection any item for which the price was more than 200.RemoveIf(Cart, Price > 200)

### RenameColumns

 Syntax RenameColumns(CollectionName, "OldName", "NewName") Description Creates a temporary table that contains the same data as a data source except that one column has a different name. You can rename columns in a collection or in data imported from, for example, Excel or a SharePoint list. Examples Import or create a collection named Inventory as Create your first app describes.Add an image gallery with text, name it TableHolder, and set its Items property to this function:RenameColumns(Inventory, "ProductName", "JacketID")Add a button, and set its OnSelect property to this function:Collect(Inventory2, TableHolder!AllItems)Press F5, click the button you just created, and then press Esc to return to the design workspace.Press Alt-D, and then click Collections in the left navigation bar.Confirm that you've duplicated the Inventory collection, except that the new collection, named Inventory2, contains the same information in a column named JacketID as the original collection did in a column named ProductName.

### Replace

 Syntax Replace(Text, StartIndex, Count, NewText)Replace(Column, StartIndex, Count, NewText) Description Replaces part of a text string with a different text string, given the position of the starting character and the number of characters to replace. You can run this function on a table of strings. Examples Import or create a collection named Inventory as Create your first app describes.Add an image gallery with text, and set its Items property to this function:Replace(Inventory!ProductName, 3, 2, "zz")In each product name, "zz" replaces the third and fourth letters.

### RGBA

 Syntax RGBA(Red, Green, Blue, Alpha) Description Returns a color value that has the specified red, green, blue, and alpha components. Example Add a label, and set its Color property to this function:RGBA(112, 48, 160, 1)The text of the label becomes purple.

### Right

 Syntax Right(Text, NumChars)Right(ColumnExpression, NumericExpression) Description Returns the specified number of characters from the end of the given string. Example Import or create a collection named Inventory, and show it in a gallery as Create your first app describes.Set the Text property of the lower label in the gallery to this function:Right(ThisItem!ProductName, 3)The label shows the last three characters in each product name.

### Round

 Syntax Round(Number, DecimalPlaces)Round(Column, DecimalPlaces) Description Rounds the given number to the specified number of decimal places. You can run this function on a table of numbers. Examples To use this function with data that you provide manually: Add an input-text control, and name it Decimal. Add a label, and set its Text property to this function:Round(Value(Decimal!Text), 2)In the text-input control, type a decimal value such as .634 or .635, and confirm that the label shows the appropriate value (for example, .63 or .64).

### RoundDown

 Syntax RoundDown(Number, DecimalPlaces)RoundDown(Column, DecimalPlaces) Description Rounds down the given number to the specified number of decimal places. Example RoundDown(23.54, 0) returns 23.

### RoundUp

 Syntax RoundUp(Number, DecimalPlaces)RoundUp(Column, DecimalPlaces) Description Rounds up the given number to the specified number of decimal places. Example RoundUp(23.44, 1) returns 23.5.

### SaveData

 Syntax SaveData(Collection, FileName) Description Encrypts the data in the specified collection and saves it to the specified file. This file is located in the app's own protected space. Use this function together with the LoadData function to save and load application data to and from app local storage.SaveData is an asynchronous function and can't be used in predicates such as predicates of Filter and CountIf. Example Create your first app for an example.

### Second

 Syntax Second(DateTime) Description Returns the second from a given TimeValue as a number between 0 and 59 (inclusive). Example If you typed 10:20:30 PM into an input-control named EventTime, a label would show 30 if its text property were set to Second(TimeValue(EventTime!Text)).More examples of how to manage dates and times.

### ShowColumns

 Syntax ShowColumns(CollectionName, "Column1", "Column2"...) Description Creates a temporary table that contains only the columns that you specify from a data source that you specify. You can specify columns from a collection or data imported from, for example, Excel or a SharePoint list. Examples Import or create a collection named Inventory as Create your first app describes.Add a text gallery, name it TableHolder, and set its Items property to this function:ShowColumns(Inventory, "ProductName", "UnitsInStock")Add a button, and set its OnSelect property to this function:Collect(Inventory2, TableHolder!AllItems)Press F5, click the button you just created, and then press Esc to return to the design workspace.Press Alt-D, and then click Collections in the left navigation bar.Confirm that you've created a collection, named Inventory2, that contains the ProductName and UnitsInStock columns (but not the ProductDesign column) from the Inventory collection.

### Shuffle

 Syntax Shuffle(Collection) Description Returns a copy of the given collection, in which rows in the table are randomly reordered. Example If you stored details about playing cards in a collection named Deck, this function would shuffle that collection:Shuffle(Deck)

### Sort

 Syntax Sort(Table, Expression[, SortOrder!Descending]) Description Returns a copy of a collection, in which the rows in the given table are sorted based on the result of the specified expression evaluated to one of the supported expression types—number and its subtypes, string and its subtypes, and Boolean types. The function doesn't support sorting on aggregate values such as table and rows. The function also accepts an optional argument that indicates that the table should be sorted in descending order. Example If you had an Employees table that contained a Salary column, this function would list the employees with higher salaries above those with lower ones:Sort(Employees, Salary, SortOrder!Descending)

### Sqrt

 Syntax Sqrt(Number)Sqrt(ColumnExpression1) Description Returns the square root of a positive number. You can run this function on a table of numbers. Examples Add an input-text control, and name it Source. Add a label, and set its Text property to this function:Sqrt(Value(Source!Text))Type a number into the input-text control, and confirm that the label shows the square root of the number that you typed.

### StdevP

 Syntax StdevP(Table,Expression)StdevP(Expression1[,Expression2, ...]) Description Returns the standard deviation of its arguments. Examples If you had a Sales table that not only contained a CostPerUnit column and a UnitsSold column but also listed each region on a different row, this function would compute standard deviation of sales by region:StdevP(Sales, CostPerUnit * UnitsSold)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)

### Substitute

 Syntax Substitute(Text, OldText, NewText[,InstanceNum])Substitute(TextColumn, OldTextColumn, NewTextColumn[, InstanceNumColumn]) Description Replaces part of a text string with a different text string. If the optional fourth argument is used, it specifies the instance to match and replace, starting with 1, which means the first instance. Example This function replaces " & " with " and ":Substitute(Text1!Text, " & ", " and ")

### Sum

 Syntax Sum(Table, Expression)Sum(Expression1[, Expression2, ...]) Description Returns the sum of the arguments, for the specified range. Examples If you had a table named Sales that contained a CostPerUnit column and a UnitsSold column, this function would compute total sales:Sum(Sales, CostPerUnit * UnitsSold)To compute the sum of the values set for sliders 1, 2, and 3:Sum(Slider1!Value, Slider2!Value, Slider3!Value)

### Table

 Syntax Table({Column1:Row1, Column2:Row1...}, {Column1:Row2, Column2:Row2...}) Description Creates a temporary table that contains the data that you specify. You don't have to specify data in every column for every row. Examples Add a listbox, and then set its Items property to this function:Table({Color:"red"}, {Color:"green"}, {Color:"blue"})Add a text gallery, and set its Items property to this function:Table({Item:"Violin123", Location:"France", Owner:"Fabrikam"}, {Item:"Violin456", Location:"Chile"})(optional) Set the Text property of the Heading1 label to ThisItem!Item, set the Text property of the Subtitle1 label to ThisItem!Owner, and set the Text property of the Body1 label to ThisItem!Location.

### Text

 Syntax Text(Value, Format) Description Converts a value to a formatted text output. Examples Add a text-input control, name it Source, and then type 56.75 in it.Add a label, and set its Text property to this expression:Text(Value(Source!Text), "\$#.##")The label shows \$56.75.Change the Text property of the label to this expression:Text(Value(Source!Text), "#.##%")The label shows 56.75%.

### Time

 Syntax Time(Hour, Minute, Second) Description Converts the specified hours, minutes, and seconds into a decimal. Example If a user typed 14 in an input-text control named BirthHour, 50 in an input-text control named BirthMinute, and 24 in an input-text control named BirthSecond, this function would return 02:50:24 p.Text(Time(Value(BirthHour!Text), Value(BirthMinute!Text), Value(BirthSecond!Text)), "hh:mm:ss a/p")More examples of how to manage dates and times.

### TimeValue

 Syntax TimeValue(TimeText)TimeValue(TimeText, "LanguageCode") Description Converts a time value stored as text to a value on which you can perform a mathematical or Boolean operation, such as comparing two times. For consistency, all times are converted from the local time zone to UTC (Coordinated Universal Time). As a result, a user in the Pacific time zone might specify 1:50:24 PM, a user in the Eastern time zone might specify 4:50:24 PM, and both values would appear as 5:50:24 AM.You can also specify a language code to ensure that a time value is interpreted and formatted appropriately. Example Name an input-text control FinishedAt, and set the Text property of a label to this function:If(TimeValue(FinishedAt!Text)

### Today

 Syntax Today() Description Returns the current date, in the device's locale-specific format. Example If today were October 11, 2014, and you set the Text property of a label tothis function:Today()The label would show 10/11/2014 12:00 AM if your computer were set to the en language code or 11/10/2014 12:00 AM if your computer were set to the fr language code.Text(Today(), "mm/dd/yyyy")The label would show 10/11/2014 regardless of the language code to which your computer was set.Text(Today(), DateTimeFormat!ShortDate)The label would show 10/11/2014 regardless of the language code to which your computer was set.You can use several options, other than ShortDate, with the DateTimeFormat parameter. To display a list of those options, type the parameter, followed immediately by an exclamation point, in the function box.More examples of how to manage dates and times.

### Trim

 Syntax Trim(Text)Trim(ColumnExpression) Description Removes all spaces from a text string except for single spaces between words. Examples If you typed "We are    the best!" into an input-text control named Slogan, a label with a text property of Trim(Slogan!Text) would return "We are the best!"

### Update

Syntax

Update(Collection, Record1, Record2[, "All"])

Description

In a specified collection, replaces the matching record with the specified record and returns the resulting collection. To update all matches, specify the optional argument "All".

Important

This function modifies the underlying collection.

Example

1. Import or create a collection named Inventory, and show it in a gallery as Create your first app describes.

2. Name the gallery ProductGallery.

3. Add a slider named UnitsSold, and set its Max property to this expression:

ProductGallery!Selected!UnitsInStock

4. Add a button, and set its OnSelect property to this function:

Update(Inventory, {ProductDesign:ProductGallery!Selected!ProductDesign, ProductName:ProductGallery!Selected!ProductName, UnitsInStock:ProductGallery!Selected!UnitsInStock}, {ProductDesign:ProductGallery!Selected!ProductDesign, ProductName:ProductGallery!Selected!ProductName, UnitsInStock:ProductGallery!Selected!UnitsInStock-UnitsSold!Value})

5. Press F5, click a product in the gallery, specify a value with the slider, and then click the button.

The number of units in stock decreases by the amount that you specified.

### UpdateContext

Syntax

UpdateContext({Name1: Expression1, Name2: Expression2, …})

Description

Updates the context of the current screen and binds the specified variables to the results from the evaluation of the specified expressions.

Important

This function modifies the underlying screen context.

Example

UpdateContext({page: 5, displayItem: "Tablets"})

### UpdateIf

Syntax

UpdateIf(Collection, Condition1, {Column1: Expression1, …}[, Condition2, {Column1: Expression2, …} …])

Description

Updates the specified columns by using 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

Create your first app for an example.

### Upper

 Syntax Upper(Text)Upper(ColumnExpression) Description Upper(Text) Converts the letters in the specified text string to uppercase.Upper (ColumnExpression) Given a one-column table of string values, returns a one-column table of the corresponding uppercase values. Examples Import or create a collection named Inventory, and show it in a gallery as Create your first app describes.Set the Text property of the lower label in the gallery to this function:Upper(ThisItem!ProductName)The label shows the name of each product in all capital letters.

### Value

 Syntax Value(Text) Description Converts a text string that represents a number to a number. Example Store data from the user for an example.

### VarP

 Syntax VarP(Table, Expression)VarP(Expression1[, Expression2, ...]) Description Returns the variance of its arguments. Examples If you had a Sales table that not only contained a CostPerUnit column and a UnitsSold column but also listed each region in a different row, this function would compute variance of sales by region:VarP(Sales, CostPerUnit * UnitsSold)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)

### Year

 Syntax Year(DateTime) Description Returns the year of a given date as a number between 1900 and 9999 (inclusive). Example Year(DateValue("03/17/1979")) returns 1979.More examples of how to manage dates and times.
Show: