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

Other -- ColorFade, ColorValue, Disable, Enable, Launch, Language, Navigate, Refresh, RGBA, UpdateContext

Strings -- Char, Concat, Concatenate, EncodeUrl, Find, HashTags, Len, Left, Lower, PlainText, Proper, Right, Substitute, Text, Trim, Upper, Value

Table -- AddColumns, Count, CountA, CountIf, CountRows, Distinct, DropColumns, Filter, First, FirstN, Last,LastN, LookUp, RenameColumns, Replace, Sort, Shuffle, ShowColumns, Table, Update, UpdateIf

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 < 100 Dept = 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: A Char(105) returns: i Char(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	Create or import a collection named Inventory, as Create your first app describes. Add a button, and then set its OnSelect property to this function: Clear(Inventory) 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: 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. Press F5, click the button, and then press Esc to return to the design workspace. (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. Year If 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. Month If 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. Day If 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/YYYY DD/MM/YYYY DD Mon YYYY Month DD, YYYY 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 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/2013 15 July 2013 July 15, 2013 More 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 letters numerals underscores (_) 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	Name the default screen DefaultScreen, and add a label to it so that you can verify which screen is showing in Preview. Add a second screen, name it AddlScreen, and add a label to it. Add a button to DefaultScreen, and set its OnSelect property to this function: Navigate(AddlScreen, ScreenTransition!Fade) 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> 100 Dept = "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: <p>We have done an unusually&nbsp;&quot;deep&quot; globalization and localization.<p> 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	Import or create a collection named Inventory, and show it in a gallery as Create your first app describes. Set the OnSelect property of the image in the gallery to this function: Remove(Inventory, ThisItem) 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%. Format dates and times.

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)<TimeValue("5:00:00.000 PM"), "You made it!", "Too late!") If you type 4:59:59.999 PM into the FinishedAt control, the label shows "You made it!" If you type 5:00:00.000 PM into the FinishedAt control, the label shows "Too late!" More examples of how to manage dates and times.

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	Import or create a collection named Inventory, and show it in a gallery as Create your first app describes. Name the gallery ProductGallery. Add a slider named UnitsSold, and set its Max property to this expression: ProductGallery!Selected!UnitsInStock 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}) 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.