Share via


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.

Abs function to calculate absolute values

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)

AddColumns puts calculated results in new column

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)

Add returns true if all conditions are true

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

  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

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

  2. 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

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

  2. 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

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

  2. 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

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

  2. 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

  1. 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 #$*(#@")

  2. Add a vertical custom gallery, and set its Items property to this function:

    HashTags(Tweet!Text)

  3. 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

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

  2. 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

  1. Set the OnSelect property of a button to this function:

    Launch("http://www.bing.com")

  2. 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

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

  2. 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

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

  2. 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

  1. Import or create a collection named Inventory as Create your first app describes.

  2. 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

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

  2. 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

  1. Import or create a collection named Inventory as Create your first app describes.

  2. 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.

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> 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

  1. Add an input-text control, and name it Slogan.

  2. 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

  1. Import or create a collection named Inventory as Create your first app describes.

  2. Add an image gallery with text, name it TableHolder, and set its Items property to this function:

    RenameColumns(Inventory, "ProductName", "JacketID")

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

    Collect(Inventory2, TableHolder!AllItems)

  4. Press F5, click the button you just created, and then press Esc to return to the design workspace.

  5. Press Alt-D, and then click Collections in the left navigation bar.

  6. 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

  1. Import or create a collection named Inventory as Create your first app describes.

  2. 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.

Syntax

Right(Text, NumChars)

Right(ColumnExpression, NumericExpression)

Description

Returns the specified number of characters from the end of the given string.

Example

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

  2. 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:

  1. Add an input-text control, and name it Decimal.

  2. Add a label, and set its Text property to this function:

  3. Round(Value(Decimal!Text), 2)

  4. 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

  1. Import or create a collection named Inventory as Create your first app describes.

  2. Add a text gallery, name it TableHolder, and set its Items property to this function:

    ShowColumns(Inventory, "ProductName", "UnitsInStock")

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

    Collect(Inventory2, TableHolder!AllItems)

  4. Press F5, click the button you just created, and then press Esc to return to the design workspace.

  5. Press Alt-D, and then click Collections in the left navigation bar.

  6. 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

  1. Add an input-text control, and name it Source.

  2. Add a label, and set its Text property to this function:

    Sqrt(Value(Source!Text))

  3. 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

  1. Add a text-input control, name it Source, and then type 56.75 in it.

  2. Add a label, and set its Text property to this expression:

    Text(Value(Source!Text), "$#.##")

    The label shows $56.75.

  3. 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

  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

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

  2. 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.