5 Power Apps Functions You Should Know

By:   |   Updated: 2021-12-16   |   Comments   |   Related: > Power Apps


   Free MSSQLTips webinar - "Efficient Monitoring and Management of SQL Server" (click to register)

Problem

In previous tips we have shown numerous essential Power Apps functions such as Patch, Filter, LookUp, among others. All of them should be very close to the heart of the app maker. What are some other useful Power Apps functions?

Solution

Follow this tip for advice on 5 useful Power Apps functions that you may have not come across yet.

Power Apps Assert Function

With this function, the Power Apps formula language gets just a bit closer to a typical scripting language. Assert, however, is only applicable while working with the Test studio in Power Apps. So, you can’t bind it to a regular control property. In a test case, you can use it to check for a value, data type or if a record has been added to the database. You must go to the Tests screen by clicking on the last icon on the left menu bar:

power apps test studio

Once inside the Test studio, you can configure a check that verifies if a text input contains a certain text:

create a test case in test studio with assert

This example is hardcoded and will return Success every time because I added the correct value to the default property of the text input ("Correct input").

Power Apps Print Function

This one should be self-explanatory. Very helpful and at first Power Apps was not endowed by it, which comes to show that the product does evolve. Print evokes the browsers print dialog allowing you to fit one of your app’s screens to a page and print it out or export it as a PDF. The Print function, however, does not work on mobile devices – it works only in the browser. This is what happens when I press a button that has Print on its OnSelect:

power apps print function

From here I can choose how to print this screen.

Power Apps DataSourceInfo Function

Here is one function that can help the maker pre-validate inputs prior to the user clicking the submit button. The syntax is very straightforward: DataSourceInfo(DataSource, Information, ColumnName). For example, here is how to check if a certain column called "Name" is required. The data source name is called Categories which is a SQL table already connected to the app:

power apps datasourceinfo

The output is boolean, depending on the outcome. In this case the column was previously set to NOT NULL in the database.

The second argument to the DateSourceInfo function can be one of several enum values Power Apps makes available. I will group these logically and in ascending order of subjective usefulness:

  • AllowedValues, CreatePermission, DeletePermission, EditPermission, ReadPermission: all of these are meant to enable the maker to check the current user’s permission against a data source such as an SQL or a Dataverse table. All of them return a boolean. It must be a complex app with a set of data sources that are perhaps used dynamically, and you never know how much privileges the user does have for any of them. However, the output from DataSourceInfo with one of these parameters can also be used as a reference for the users themselves.
  • DisplayName, MaxLength, MaxValue, MinValue, Required: all of these are pretty useful in implementing dynamic checks and validation in your app. Instead of hardcoding the expected length of an input, you can run a check on the go and ensure the value will be accepted. They all return a boolean output too, making it easy to incorporate them in an if-statement. After that, you display a correct feedback message if needed.

Power Apps ShowColumns Function

This one is Power Apps’ own version of the SELECT statement in T-SQL. It allows you to select only certain columns from the underlying table. This can potentially optimize performance (it is also generally considered a good practice in SQL too). For example, if a have a data source called Categories, I can do the following:

showcolumns function

In this case, I am selecting just two of the many columns my table has – Name and Id, and I am passing the resulting table to the Items property of a gallery. It is important to note that ShowColumns does not modify the original table. It produces an in-memory result set based on the original table. Using ShowColumns will ensure you are working only with the relevant columns. If I wanted to populate one of the labels in my gallery based on the example above, I would have a choice between only the selected columns (Name and Id, IsSelected is a built-in property):

thisitem options after using the showcolumns function

It is fair to mention DropColumns in this context too. Conversely to ShowColumns, DropColumns will exclude the stated columns from the table. So, if I did this:

dropcolumns function

then I would have a much broader choice of columns for my label inside the gallery, except for the Name and Id columns (as you can observe by the scrollbar in the dropdown):

thisitem options after using the dropcolumns function

Unfortunately, neither ShowColumns nor DropColumns support delegation. This limits you to at most 2000 records that can be returned.

Power Apps CountRows and CountIf Functions

I am grouping these two together because scenarios for their usage are often interrelated. The CountRows function counts all records in a table and returns a number. CountIf counts all rows that correspond to a certain condition. Both are useful when you want to provide some feedback to the user, e.g., count all rows in a table created by a certain user:

CountIf(Categories, CreatedBy = User().FullName)

Beware that neither CountRows nor CountIf support delegation with SQL. They do support delegation with Dataverse tables only. If you use a nested Filter function inside CountRows, then there is a hard limit of 50,000 records. When you work with CountRows with no filter, then there is no limit, as Dataverse keeps a cached count of the number of rows available in any table.

Bonus: Power Apps IsMatch Function

This is a helpful function to validate the format of the input to a text input control. Let’s say that for a certain field, you want your users to input a serial number only in the format XXXXX-YYYYY (i.e., 5 digits followed by a hyphen, followed by another five digits). What you could do is bind an instance of the IsMatch function to the BorderColor property and turn the border to red in case the user input is in a different format:

using the ismatch function

The advantage here is that as soon as the function validates the input of the user, it will return true. In such a case, the border color will go back to normal instantly. IsMatch can (and should) be used for a submit button or SubmitForm function so that it will be impossible to transmit wrong or wrongly formatted data to the data source.

There you go – five plus one functions you should be aware of in Power Apps. Knowing these should help you create better apps!

Next Steps

Check the official documentation for more information on how to utilize each function:




Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Hristo Hristov Hristo Hristov is a Microsoft certified data professional, specializing in Power Apps and Power BI.

View all my tips


Article Last Updated: 2021-12-16

Comments For This Article





download














get free sql tips
agree to terms