Data Types - Power Platform (2023)

Information flows through an application in small, discrete values, like spreadsheet cells. For example, data in aBirthdayfield and aBirthdaywould flow across a fieldDataA value that includes the year, month, and day. The application knows how to format these values, restrict input to what is appropriate for each, and share the values ​​with a database. Birthdays differ from people's birthdays, but the system treats them exactly the same. In this case,Datais an example ofdata type.

This article details the types of data supported by canvas apps. When an app connects to an external data source, each data type in that source is mapped to a canvas app data type.

Many of these data types are similar and have the same underlying representation, such as ahyperlinkfield that is treated asText. Additional data types provide better out-of-the-box experiences in forms and other controls.

Branco

All data types can have a value ofbranco(in other words, worthless). The term "null" is often used in databases for this concept.

Use oBrancowork with theTo placeoCorrectionfunction to establish a variable or field tobranco. For example,set(x, blank())remove any value in global variablex.

test for abrancovalue using theis blankFunction. replace possiblebrancovalues ​​withoutbrancovalues ​​using theget togetherFunction.

As all data types supportbranco, heboleanoyTwo optionsdata types effectively have three possible values.

Text, hyperlink, image and media

These four types of data are based on aUnicodetext string

embedded text

Text strings embedded in a formula are enclosed in double quotes. Use two double quotes together to represent a single double quote in the text string. For example, using the following formula inSelectingowned by aButtonto 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 double quotes repeated around itHello World!are replaced with single double quotes:

Single quotes are used toidentifier namesthat contain special characters and have no special meaning in a text string.

string interpolation

Use string interpolation to embed formulas in a string of text. It is usually easier to work with this and visualize the result than to use theConcatenatefunction theoperator.

Prefix the text string with a dollar signPSand enclose the formula to be incorporated in braces{ }. To include a brace in the text string, use repeating braces:{{o}}. String interpolation can be used anywhere a standard text string can be used.

For example, consider this formula with global variablesapplesset to 3 andbananasdefined as 4:

$"We have {Apples} apples, {Bananas} bananas, making a total of {Apples+Bananas} fruits."

This formula returns the text string.We have 3 apples, 4 bananas, totaling 7 fruits.variablesapplesybananasthey are inserted into the text by replacing the braces, along with the result of the mathematical formulaapples+bananas. Spaces and other characters between braces are preserved as-is.

Built-in formulas can include any function or operator. All that is required is that the result of the formula can be converted to a text string. For example, this formula will insertSurnameif provided, or theFirst nameif not, in a greeting:

$"Welcome {Coalesce( NickName, FirstName )}, nice to meet you!" )

ESurnameis set to "Joe", this formula will produce the text stringWelcome Joe, nice to meet you!. But yesSurnameesbrancoyFirst nameis "Joseph", then this formula producesDear José, nice to meet you!instead of.

String interpolation can include standard text strings in the embedded formula. For example, if noneSurnameemFirst namehave been provided, we can still provide"Amigo"instead:

$"Bienvenido {Coalesce( NickName, FirstName, "Friend" )}!"

String interpolations can even be nested. Consider this example whereFirst,Half, youLastnames are combined into a salutation. Even if one or two of these values ​​arebranco, the correct number of spaces will be between parts of the name. If no part is provided, the inner string interpolation will be reduced to an empty string and will be replaced by the interpolationget togetherfunction by "friend".

$"Welcome {Coalesce( Trim( $"{First} {Second} {Last}"}), "Friend" )}!"

new lines

Embedded text strings can contain line breaks. For example, consider setting theTextowned by aLabelcontrol the following:

"Line 1Line 2Line 3"

The above formula results in three lines being displayed in the label control:

Newlines also support string interpolation, as shown below:

$"Line {1}Line {1+1}Line {1+1+1}"

The above formula gives the same result:

Image and media resources

AcrossFilemenu, you can add image, video and audio files as application resources. The name of the imported file becomes the name of the resource in the application. In this graphic, the Northwind Traders logo, which bears the namenwindlogo, was added to an application:

To use this feature in an application, specify it in theImageowned by aImageto control:

URI for images and other media

You can dig a little deeper into this last example by setting theTextowned by aLabelcontrol thenwindlogo. The label displays a string of text:

Canvas apps reference each image or other media file, whether in the cloud or added as an app resource, using a URI text string.

For example, heImageproperty of an image control accepts not only application resources, but also links to images on the web, such as "https://northwindtraders.com/logo.jpg". The property also accepts online images that use thedata URI scheme, as in this example:

"data:image/png;base64,iVBORw0KGgoAAAAANSUhEUgAAAAkAAAAFAQMAAACtnVQoAAAAABlBMVEUAAAB0J3UMNU6VAAAAAXRSTlMAQObYZgAAABRJREFUCNdjUGJgCGVg6GgAkkA2AA8/AffqCEBsAAAAAElFTkSuQmCC"

This URI shows a zoomed-in version of two purple diamonds:

You can view the most recently captured image on aCameraMake sure you set theImageproperty of an image control for thePhotocamera control property. The application keeps the image in memory and thePhotoThe camera control property returns a URI reference to the image. For example, you can take a picture and the cameraPhotothe property can return"appres://blobmanager/7b12ffa2ea4547e5b3812cb1c7b0a2a0/1".

It uses a URI to reference an image or other 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 to a Microsoft Dataverse table might return"appres://datasources/Contacts/table/..."As in the camera example, you can display this image by setting theImageproperty of an image control to this reference, which retrieves the binary data.

When you save a type of media data, such as an image, to a database, the application sends the actual image or media data, not the URI reference.

size limits

Like strings and URIs, these data types don't have a predefined length limit.

Binary data referenced by these data types also does not have a predefined size limit. For example, an image captured by camera control is now named as"apres://..."it can be as large and high resolution as your device's camera allows. Resolution, frame rate, and other attributes of media files are not limited by data type, but specific controls for media playback and capture can have their own limitations.

However, all data sizes are subject to the amount of memory available in the application. Browsers running on a desktop computer typically support over 100 megabytes of data. However, the amount of memory available on a device such as a phone can be much less, typically between 30 and 70 megabytes. To determine whether your app will run within these limits, test common scenarios across all devices on which your app should run.

As a best practice, keep data in memory only as long as necessary. Upload images to a database as soon as you can; Download images only when requested by the app user.

Number and Currency

NumberyTo dividedata types use theIEEE 754 double precision floating point standard. This pattern gives you a wide range of numbers to work with, from –1.79769 x 10³⁰⁸one 1.79769 x 10³⁰⁸. 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 (–(2⁵³– 1)) e 9.007.199.254.740.991 (2⁵³– 1), inclusive. This range is larger than the 32-bit (or 4-byte) integer data types commonly used by databases. However, canvas apps cannot represent 64-bit (or 8-byte) integer data types. You might want to store the number in a text field or use a computed column to make a copy of the number in a text field so that it maps to aTextdata type in the canvas app. That way, you can keep, display, and enter these values, as well as compare them to determine if they're equal; however, you cannot perform numerical calculations on them this way.

Floating-point arithmetic is approximate, so it can sometimes give unexpected results with many documented examples. You might expect the formula55/100 * 100to return exactly 55 and(55/100 * 100) - 55to return exactly zero. However, the last formula returns 7.1054 x 10^-15, which is very small, but not zero. This small difference is usually not a problem, and the app rounds up when displaying the result. However, small differences can add up in subsequent calculations and give the impression of giving an incorrect answer.

Database systems often store coins and perform calculations using decimal math, which gives you a smaller range but greater control over accuracy. By default, canvas apps map currencies in and out of floating point values; therefore, the result may differ from calculations performed on a native decimal data type. If this type of discrepancy causes problems, you may want to work with these values ​​asText, just as you would for the large integers described earlier in this section.

Date, time and date and time

time zones

Date/time values ​​belong to these categories:

• local user: These values ​​are stored inUTC (Coordinated Universal Time), but the app user's time zone affects how the app displays these values ​​and how the app user specifies them. For example, the same moment appears differently for a user in Canada than it does for a user in Japan.
• independent time zone: The app displays these values ​​in the same way, and the app user specifies them in the same way, regardless of time zone. The same moment appears the same for a user in Canada as it does for a user in Japan. Application authors who don't expect their applications to run in different time zones use these values ​​because they are generally simpler.

This table shows some examples:

Forlocal userdate/time, canvas apps use the device's browser or time zone, but model-driven apps use user settings in the Dataverse. These settings usually match, but the results will be different if these settings are different.

Use oadd dateytime zone informationfunctions to convert local time to UTC and vice versa. See the examples at the end of the documentation for these functions.

numerical equivalents

Canvas apps store and calculate all date/time values, whetherlocal useroindependent time zonein UTC. The app translates the values ​​based on the app user's time zone when it displays them and when the app user specifies them.

When a canvas app reads aindependent time zonevalue from a data source or writes that value to a data source, the application automatically adjusts the value to compensate for the application user's time zone. The app then treats the value as a UTC value, consistent with all other date/time values ​​in the app. Due to this compensation, the originalindependent time zoneThe value appears when the app adjusts the UTC value to the app user's time zone.

You can examine this behavior more closely using theValentinato access the underlying numeric value for a date/time value. This function returns the datetime value as the number of milliseconds since January 1, 1970 00:00:00000 UTC.

Since each date/time value is maintained in UTC, the formulaValue( Date( 1970, 1, 1 ) )will not return zero in most parts of the world because theDataThe function returns a date in UTC. For example, the formula would return 28,800,000 in a time zone eight hours off UTC. This number reflects the number of milliseconds in eight hours.

Going back to our previous example:

Converting Unix times

Unix times reflect the number of seconds since January 1, 1970 00:00:00 UTC. Since canvas apps use milliseconds instead of seconds, you can convert between the two by multiplying or dividing by 1000.

For example, Unix time displays September 9, 2001 01:46:40 UTC as 1,000,000,000. To display this date/time value in a canvas app, multiply this number by 1000 to make it milliseconds, then use it in aTextFunction. FormulaText (1000000000 * 1000, data and time format.UTC)returns the string2001-09-09T01:46:40.000Z.

However, this function returnsSaturday, September 8, 2001 6:46:40 PMif you use theDateTimeFormat.LongDateTime24format in a time zone within -7 hours of UTC (7 hours west of UTC). This result shows theDate and timevalue correctly based on the local time zone.

To convert to Unix time, divide the result ofValentinaper 1,000:
Round Down (Value(UnixTime) / 1000.0)

If you need Unix time in aDatavalue for further calculation or display in Power Apps, use this formula:
DateAdd( Date( 1970,1,1 ), UnixTime, Seconds )

servidor SQL

SQL Server temDatetime, Datetime2, and other date/time data typesthey do not include a time zone offset and do not indicate which time zone they are in. Canvas apps assume these values ​​are stored in UTC and treat them aslocal user. If the values ​​must be time zone independent, correct the UTC translations using theTimeZoneOffsetFunction.

Canvas apps use the time zone information included in thedate and time offsetfields when converting a value to the application's internal UTC representation. Applications always use UTC as the time zone (zero time zone offset) when writing data.

Canvas apps read and write values ​​from theTempodata type in SQL Server as strings inISO 8601 duration format. For example you would parse this format to string and use theTempofunction to convert string to text"PT2H1M39S"yetTempovaleria:

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 displayed in a label control, use the Text function to display the seconds)

Combine date and time information

Data,Tempo, youDate and timeThey have different names, but they all contain the same date and time information.

ADataThe value can include time information, which is typically midnight. FORTempoThe value can contain date information, which is typically January 1, 1970. Dataverse also stores time information with ajust datebut it only shows the date information by default. Likewise, canvas apps sometimes distinguish between these data types to determine default formats and controls.

Adding and subtracting date and time values ​​directly is not recommended because time zone and other conversions can lead to confusing results. Or use theValentinafunction to convert date/time values ​​to milliseconds first and take into account the application user's time zone, or use the functionadd dateyCloseDifffunctions to add or subtract from one of these values.

Options and Yes/No

Options and two-option data types provide two or more options for an application user to select. for example, oneorder statusthe choice could offer the optionsNovo,Sent,Invoiced, youCerrado. The two-option data type provides only two options.

Both data types display their labels in a string context. For example, a label control displays one of the order status options if the controlTextThe property is set to a formula that references that choice. Option labels can be localized for app users in different locations.

When an app user selects an option and saves that change, the app passes the data to the database, which stores it in a language-independent representation. An option in a choice is passed and stored as a number, and an option in a two-option data type is passed and stored as a boolean value.

Labels are for display purposes only. You cannot do direct comparisons with tags because they are language specific. Instead, each option has an enumeration that works with the underlying number or boolean value. For example, you cannot use this formula:

If( ThisItem.OrderStatus = "Active", ...

But you can use this formula:

If( ThisItem.OrderStatus = OrderStatus.Active, ...

For global options (which share tables), the option set enumeration name matches the global option name. For local options (that are scoped to a table), the name can contain the name of the table. This behavior prevents conflicts if multiple tables have options with the same name. For example, heaccountsthe table can have aorder statuschoose, and your name can beOrderStatus (accounts). This name contains one or more spaces and parentheses, so you must enclose it in single quotation marks if you reference it in a formula.

Furthermore, the values ​​of two options can also behave like boolean values. For example, a two-option value calledtax situationcould have labelsTaxableyno taxation, which belong toTRUEyFALSErespectively. To demonstrate it, you can use this formula:

If( ThisArticle.Taxable = TaxStatus.Taxable, ...

You can also use this equivalent formula:

If(ThisItem.Taxable,...