Build a function in Project Siena

Symbol

Operator type

Syntax

Description

+

Arithmetic operators

1 + 2

Addition

*

2 * 3

Multiplication

^

2 ^ 3

Exponentiation

=

Comparison operators

Price = 100

Equal to

>=

Price >= 100

Greater than or equal to

<=

Price <= 100

Less than or equal to

&

String concatenation operator

"hello" & " " & "world"

Concatenation

||

Price < 100 || slider!value = 20

Logical disjunction

exactin

Membership operators

gallery!Selected exactin SavedItems

Belonging to a collection or table

in

gallery!Selected in SavedItems

Belonging to a collection or table

@

Disambiguation operator

MyTable[@fieldname]

Field disambiguation

;

Expression chaining

Collect(T, A); Navigate(S1, "")

Separate invocations of functions in behavior properties

Syntax

Abs(Number)

Abs(ColumnExpression)

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.

Syntax

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

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)

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)

Syntax

And(LogicalExpression1[, LogicalExpression2, ...])

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

Syntax

Average(Table, Expression)

Average(Expression1[, Expression2, …])

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)

Syntax

Char(Number)

Example

Char(65)returns:

A

Char(105) returns:

i

Char(35) returns:

#

Syntax

Clear(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.

Syntax

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

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.

Syntax

ColorFade(Color, FadeDelta)

Examples

ColorFade(Color!AliceBlue, -1)

ColorFade(Color!DarkBlue, 1)

ColorFade(RGBA(255, 255, 255, 1), 1)

Syntax

ColorValue(ColorText)

Examples

ColorValue("Blue")

ColorValue("Fuschia")

Syntax

Concat(CollectionName, Expression)

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.

Syntax

Concatenate(Text1[, Text2, ...])

Concatenate(ColumnExpression1[, ColumnExpression2, ...])

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)

Syntax

Count(Column)

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.

Syntax

CountA(Column)

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.

Syntax

CountIf(Table, Expression)

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.

Syntax

CountRows(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.

Syntax

Date(Year, Month, Day)

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.

Syntax

DateAdd(Date, NumberOfUnits[, Units])

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.

Syntax

DateDiff(StartDate, EndDate[, Units])

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.

Syntax

DateTimeValue(DateTimeText)

DateTimeValue (DateTimeText, "LanguageCode")

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.

Syntax

DateValue(DateText)

DateValue (DateText, "LanguageCode")

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.

Syntax

Day(DateTime)

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.

Syntax

Disable(Signal)

Example

Disable(Location)

Syntax

Distinct(Table, 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)

Syntax

DropColumns(Table, Column1[, Column2, …])

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

Syntax

Enable(Signal)

Example

Enable(Location)

Syntax

EncodeUrl(Text)

Example

EncodeUrl("'http://example/page/url.aspx") returns:

%27http%3A%2F%2Fexample%2Fpage%2Furl.aspx

Syntax

Filter(Table, Condition1[, Condition2, ...])

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.

Syntax

Find(FindText, WithinText[, StartNum])

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.

Syntax

First(Table)

Example

If you had an Employees table, this function would return the first row from that table:

First(Employees)

Syntax

FirstN(Table[, NumRows])

Example

If you had an Employees table, this function would return the first 10 rows from that table:

FirstN(Employees, 10)

Syntax

HashTags(Text)

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

Syntax

Hour(DateTime)

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.

Syntax

If(Condition1, Result1[, Condition2, Result2, ConditionN, ResultN, DefaultResult])

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.

Syntax

IsBlank(Expression)

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.

Syntax

IsEmpty(Source)

Example

If a table named Employees contains data, IsEmpty(Employees) returns false; otherwise, the function returns true.

Syntax

Language()

Example

Language() could return en-US based on your configuration.

Syntax

Last(Table)

Example

If you had a table named Employees, this function would return the last row from that table:

Last(Employees)

Syntax

LastN(Table[, NumRows])

Example

If you had a table named Employees, this function would return the last 15 rows from that table:

LastN(Employees, 15)

Syntax

Launch(Hyperlink)

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.

Syntax

Left(Text, NumChars)

Left(ColumnExpression, NumericExpression)

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.

Syntax

Len(Text)

Len(ColumnExpression)

Examples

If you typed "F@V0riteBandName" into a text-input control named Password, Len(Password!Text) would return 16.

Syntax

LoadData(Collection, Filename)

Example

Create your first app for an example.

Syntax

LookUp(Table, Condition, Expression)

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)

Syntax

Lower(Text)

Lower(ColumnExpression)

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.

Syntax

Max(Table, Expression)

Max(Expression1[, Expression2, ...])

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.

Syntax

Mid(Text, StartPosition, NumChars)

Mid(TextColumn, StartPositions, NumChars)

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.

Syntax

Min(Table, Expression)

Min(Expression1[, Expression2, …])

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.

Syntax

Minute(DateTime)

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.

Syntax

Month(DateTime)

Example

The function Month(DateValue("03/17/1979")) returns 3.

More examples of how to manage dates and times.

Syntax

Navigate(TargetScreen, Animation[, Context])

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.

Syntax

Not(BooleanExpression)

Example

This function makes sure a radio button isn't selected:

Not(RadioButton1!Selected)

Syntax

Now()

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.

Syntax

Or(LogicalExpression1[, LogicalExpression2, ...])

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

Syntax

PlainText(TextWithTags)

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.

Syntax

Proper(Text)

Proper(ColumnExpression)

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

Syntax

Rand()

Example

Rand() could return 0.85116235, 0.76728732, 0.27591115, or any other number greater than or equal to 0 but less than 1.

Syntax

Refresh(ServiceDataSource)

Example

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

Syntax

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

Remove(Collection, Table[, All])

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.

Syntax

RemoveIf(Collection, Condition1[, Condition2 ...])

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)

Syntax

RenameColumns(CollectionName, "OldName", "NewName")

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.

Syntax

Replace(Text, StartIndex, Count, NewText)

Replace(Column, StartIndex, Count, NewText)

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.

Syntax

RGBA(Red, Green, Blue, Alpha)

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)

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.

Syntax

Round(Number, DecimalPlaces)

Round(Column, DecimalPlaces)

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

Syntax

RoundDown(Number, DecimalPlaces)

RoundDown(Column, DecimalPlaces)

Example

RoundDown(23.54, 0) returns 23.

Syntax

RoundUp(Number, DecimalPlaces)

RoundUp(Column, DecimalPlaces)

Example

RoundUp(23.44, 1) returns 23.5.

Syntax

SaveData(Collection, FileName)

Example

Create your first app for an example.

Syntax

Second(DateTime)

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.

Syntax

ShowColumns(CollectionName, "Column1", "Column2"...)

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.

Syntax

Shuffle(Collection)

Example

If you stored details about playing cards in a collection named Deck, this function would shuffle that collection:

Shuffle(Deck)

Syntax

Sort(Table, Expression[, SortOrder!Descending])

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)

Syntax

Sqrt(Number)

Sqrt(ColumnExpression1)

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.

Syntax

StdevP(Table,Expression)

StdevP(Expression1[,Expression2, ...])

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)

Syntax

Substitute(Text, OldText, NewText[,InstanceNum])

Substitute(TextColumn, OldTextColumn, NewTextColumn[, InstanceNumColumn])

Example

This function replaces " & " with " and ":

Substitute(Text1!Text, " & ", " and ")

Syntax

Sum(Table, Expression)

Sum(Expression1[, Expression2, ...])

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)

Syntax

Table({Column1:Row1, Column2:Row1...}, {Column1:Row2, Column2:Row2...})

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.

Syntax

Text(Value, Format)

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.

Syntax

Time(Hour, Minute, Second)

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.

Syntax

TimeValue(TimeText)

TimeValue(TimeText, "LanguageCode")

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.

Syntax

Today()

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.

Syntax

Trim(Text)

Trim(ColumnExpression)

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

Syntax

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

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.

Syntax

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

Example

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

Syntax

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

Example

Create your first app for an example.

Syntax

Upper(Text)

Upper(ColumnExpression)

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.

Syntax

Value(Text)

Example

Store data from the user for an example.

Syntax

VarP(Table, Expression)

VarP(Expression1[, Expression2, ...])

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)

Syntax

Year(DateTime)

Example

Year(DateValue("03/17/1979")) returns 1979.

More examples of how to manage dates and times.