When an app connects to an external data source, each data type in that source is mapped to a data type for canvas apps.
Many of these data types are similar and have the same underlying representation, such as a Hyperlink field being treated as Text. The additional data types provide better default experiences in forms and other controls.
Blank
All data types can have a value of blank (in other words, no value). The term "null" is often used in databases for this concept.
Use the Blank function with the Set or Patch function to set a variable or field to blank. For example, Set( x, Blank() ) removes any value in the global variable x.
Test for a blank value by using the IsBlank function. Replace possible blank values with non-blank values by using the Coalesce function.
Because all data types support blank, the Boolean and Two option data types effectively have three possible values.
Text, Hyperlink, Image, and Media
All four of these data types are based on a Unicode text string.
Embedded text
Embedded text strings in a formula are enclosed in double quotation marks. Use two double quotes together to represent a single double quote in the text string. For example, using the following formula in the OnSelect property of a Button control:
Notify( "Jane said ""Hello, World!""" )
results in a banner when the button is pressed, where the first and last double quotes are omitted (as they delimit the text string) and the repeated double quotes around Hello, World! are replaced with a single double quote:
Single quotation marks are not used for identifier names that contain special characters and have no significance within a text string.
Image and Media resources
Through the File menu, you can add image, video, and audio files as app resources. The name of the imported file becomes the resource name in the app. In this graphic, the Northwind Traders logo, which is named nwindlogo, has been added to an app:
To use this resource in an app, specify it in the Image property of an Image control:
URIs for images and other media
You can dig a little deeper into that last example by setting the Text property of a Label control to nwindlogo. The label shows a text string:
Canvas apps reference each image or other media file, whether it's in the cloud or added as an app resource, by a URI text string.
For example, the Image property of an image control accepts not only app resources but also links to images on the web, such as "https://northwindtraders.com/logo.jpg". The property also accepts inline images that use the data URI scheme, as in this example:
"data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAkAAAAFAQMAAACtnVQoAAAABlBMVEUAAAB0J3UMNU6VAAAAAXRSTlMAQObYZgAAABRJREFUCNdjUGJgCGVg6GgAkkA2AA8/AffqCEBsAAAAAElFTkSuQmCC"
That URI displays a scaled-up version of two purple diamonds:
You can show the most recent image captured in a Camera control if you set the Image property of an image control to the Photo property of the camera control. The app holds the image in memory, and the Photo property of the camera control returns a URI reference to the image. For example, you might take a picture, and the camera's Photo property could return "appres://blobmanager/7b12ffa2ea4547e5b3812cb1c7b0a2a0/1".
You use a URI to reference an image or another media file stored in a database. That way, the app doesn't retrieve the actual data until it's actually needed. For example, an attachment in a Microsoft Dataverse table might return "appres://datasources/Contacts/table/..." As in the camera example, you can display this image by setting the Image property of an image control to this reference, which retrieves the binary data.
When you save a media data type, such as an image, to a database, the app sends the actual image or media data, not the URI reference.
Size limits
As text strings and URIs, these data types have no preset limit on their length.
The binary data that these data types reference also has no preset limit on size. For example, an image captured through the camera control that's now referenced as "appres://..." can be as large and high resolution as the device's camera can muster. The resolution, frame rate, and other attributes of media files aren't limited by the data type, but specific controls for playing and capturing media may have their own limitations.
However, all data sizes are subject to the amount of available memory in the app. Browsers running on a desktop computer typically support more than 100 megabytes of data. However, the amount of available memory on a device such as a phone might be far lower, typically in the range 30-70 megabytes. To determine whether your app will run within these limits, test common scenarios on all devices on which it should run.
As a best practice, hold data in memory only as long as necessary. Upload images to a database as soon as you can; download images only when the app's user requests them.
Number and Currency
Number and Currency data types use the IEEE 754 double-precision floating-point standard. This standard provides a very large range of numbers in which to work, from –1.79769 x 10308 to 1.79769 x 10308. The smallest value that can be represented is 5 x 10–324.
Canvas apps can exactly represent whole numbers (or integers) between –9,007,199,254,740,991 (–(253 – 1)) and 9,007,199,254,740,991 (253 – 1), inclusive. This range is larger than the 32-bit (or 4-byte) integer data types that databases commonly use. However, canvas apps can't represent 64-bit (or 8-byte) integer data types. You might want to store the number in a text field or use a calculated column to make a copy of the number in a text field, so that it's mapped into a Text data type in the canvas app. In this manner, you can hold, display, and enter these values, as well as comparing them to determine whether they're equal; however, you can't perform numerical calculations on them in this form.
Floating-point arithmetic is approximate, so it can sometimes give unexpected results with many documented examples. You might expect the formula 55 / 100 * 100 to return exactly 55 and (55 / 100 * 100) - 55 to return exactly zero. However, the latter formula returns 7.1054 x 10–15, which is very small but not zero. That tiny difference doesn't normally cause a problem, and the app rounds it away when showing the result. However, small differences can compound in subsequent calculations and appear to give the wrong answer.
Database systems often store currencies and perform calculations by using decimal math, which offers a smaller range but greater control over the precision. By default, canvas apps map currencies in and out of floating-point values; therefore, the result might differ from calculations that are done in a native decimal data type. If this type of discrepancy will cause problems, you might want to work with these values as Text, just as you might with large integers described earlier in this section.
Date, Time, and DateTime
Time zones
Date/time values fall in these categories:
- User local: These values are stored in UTC (Coordinated Universal Time), but the app user's time zone affects how the app shows these values and how the app user specifies them. As an example, the same moment appears differently to a user in Canada than it does to a user in Japan.
- Time zone independent: The app shows these values the same way and the app user specifies them the same way, regardless of time zone. The same moment appears the same way to a user in Canada as it does to a user in Japan. App authors who don't expect their apps to run in different time zones use these values because they're simpler overall.
This table shows some examples:
For User local date/times, canvas apps use the time zone of the browser or device, but model-driven apps use the user's setting in Dataverse. These settings typically match, but results will differ if these settings differ.
Use the DateAdd and TimeZoneInformation functions to convert local time to UTC and back again. See the examples at the end of the documentation for these functions.
Numeric equivalents
Canvas apps hold and calculate all date/time values, whether User local or Time zone independent in UTC. The app translates the values based on the app user's time zone when showing them and when the app user specifies them.
When a canvas app reads a Time zone independent value from a data source or writes such a value to a data source, the app automatically adjusts the value to compensate for the time zone of the app's user. The app then treats the value as a UTC value, consistent with all other date/time values in the app. Because of this compensation, the original Time zone independent value appears when the app adjusts the UTC value for the app user's time zone.
You can observe this behavior more closely by using the Value function to access the underlying numerical value for a date/time value. This function returns the date/time value as the number of milliseconds since January 1, 1970 00:00:00.000 UTC.
Because every date/time value is held in UTC, the formula Value( Date( 1970, 1, 1 ) ) won't return zero in most parts of the world because the Date function returns a date in UTC. For example, the formula would return 28,800,000 in a time zone that's offset from UTC by eight hours. That number reflects the number of milliseconds in eight hours.
Returning to our example from above:
Converting Unix times
Unix times reflect the number of seconds since January 1, 1970 00:00:00 UTC. Because canvas apps use milliseconds instead of seconds, you can convert between the two by multiplying or dividing by 1,000.
For example, Unix time shows September 9, 2001, at 01:46:40 UTC as 1,000,000,000. To show that date/time value in a canvas app, multiply that number by 1,000 to convert it to milliseconds, and then use it in a Text function. The formula Text( 1000000000 * 1000, DateTimeFormat.UTC ) returns the string 2001-09-09T01:46:40.000Z.
However, that function returns Saturday, September 8, 2001 18:46:40 if you use the DateTimeFormat.LongDateTime24 format in a time zone that's -7 hours offset from UTC (7 hours west of UTC). This result shows the DateTime value correctly based on the local time zone.
To convert to a Unix time, divide the result from Value by 1,000:
RoundDown( Value( UnixTime ) / 1000, 0 )
If you need the Unix time in a Date value for further calculations or display within Power Apps, use this formula:
DateAdd( Date( 1970,1,1 ), UnixTime, Seconds )
SQL Server
SQL Server has Datetime, Datetime2, and other date/time data types that don't include a time-zone offset and don't indicate which time zone they're in. Canvas apps assume these values are stored in UTC and treat them as User local. If the values are meant to be time-zone independent, correct for the UTC translations by using the TimeZoneOffset function.
Canvas apps use the included time-zone information in Datetimeoffset fields when converting a value to the app's internal UTC representation. The apps always use UTC as the time zone (zero time zone offset) when they write data.
Canvas apps read and write values of the Time data type in SQL Server as text strings in the ISO 8601 duration format. For example, you must parse this string format and use the Time function to convert the text string "PT2H1M39S" to a Time value:
With(
Match( "PT2H1M39S", "PT(?:(?<hours>\d+)H)?(?:(?<minutes>\d+)M)?(?:(?<seconds>\d+)S)?" ),
Time( Value( hours ), Value( minutes ), Value( seconds ) )
)
// Result: 2:01 AM (as shown in a label control, use the Text function to see the seconds)
Mixing date and time information
Date, Time, and DateTime have different names, but they all hold the same information about dates and times.
A Date value can include time information with it, which is usually midnight. A Time value can carry date information, which is usually January 1, 1970. Dataverse also stores time information with a Date Only field but shows only the date information by default. Similarly, canvas apps sometimes distinguish between these data types to determine default formats and controls.
Adding and subtracting date and time values directly isn't recommended because time-zone and other conversions could cause confusing results. Either use the Value function to convert date/time values to milliseconds first and take into account the app user's time zone, or use the DateAdd and DateDiff functions to add or subtract from one of these values.
Choices and Yes/No
Choices and two-option data types provide a two or more choices for an app user to select. For example, an Order Status choice might offer the choices New, Shipped, Invoiced, and Closed. The two-option data type offers only two choices.
Both of these data types show their labels in a text-string context. For example, a label control shows one of the order-status options if the control's Text property is set to a formula that references that choice. Option labels might be localized for app users in different locations.
When an app user selects an option and saves that change, the app transmits the data to the database, which stores that data in a representation that's independent of language. An option in an choice is transmitted and stored as a number, and an option in a two-option data type is transmitted and stored as a boolean value.
The labels are for display purposes only. You can't perform direct comparisons with the labels because they're specific to a language. Instead, each choice has an enumeration that works with the underlying number or boolean value. For example, you can't use this formula:
If( ThisItem.OrderStatus = "Active", ...
But you can use this formula:
If( ThisItem.OrderStatus = OrderStatus.Active, ...
For global choices (which tables share), the name of the option-set enumeration matches the name of the global choice. For local choices (which are scoped to a table), the name might contain the name of the table. This behavior avoids conflicts if multiple tables have choices that have the same name. For example, the Accounts table might have an OrderStatus choice, and its name might be OrderStatus (Accounts). That name contains one or more spaces and parentheses, so you must surround it with single quotation marks if you reference it in a formula.
In addition, two-option values can also behave as boolean values. For example, a two-option value named TaxStatus might have the labels Taxable and Non-Taxable, which correspond to true and false respectively. To demonstrate, you can use this formula:
If( ThisItem.Taxable = TaxStatus.Taxable, ...
You can also use this equivalent formula:
If( ThisItem.Taxable, ...
