Brent Thrasher

One of the common tasks when developing power apps that interact with data sources is to check if a record exists in a database. This can be useful for various purposes, such as validating user input, preventing duplicate entries, or displaying conditional content. In this blog post, I will show you my method of checking if a record exists in a database. I will also provide some code examples and screenshots of how to implement this in power apps

I have found the most efficient method is using the First and Filter functions. These are built-in functions in power apps that allow you to return the first record or an empty table based on a filter condition. For example, if you have a data source called Customers, and you want to check if there is a customer with the name “John Smith”, you can use the following formula:

First(Filter(Customers, Name = “John Smith”))

This formula will return the first record from the Customers data source that matches the filter condition, or an empty table if no record matches. You can then use an If statement to check if the result is blank or not, and return a true or false value accordingly. For example:

If(IsBlank(First(Filter(Customers, Name = “John Smith”))), false, true)

This formula will return false if there is no customer with the name “John Smith”, or true if there is one.

One of the advantages of using this method is that it is convenient and intuitive for power apps. You can easily use the First and Filter functions with any data source that supports delegation, such as SharePoint lists, Excel tables, or SQL databases. You can also use complex filter conditions with multiple criteria, such as:

First(Filter(Customers, Name = “John Smith” && Age > 30 && City = “New York”))

In my own app, I have utilized these concepts to display conditional content based on the existence of a record. A formula similar to the one above will show or hide a checkmark beside items in a list of inventory items, indicating whether or not the item has been checked off this year.

I used the following formula to show or hide the checkmark icon based on the existence of a record in another data source called Inventory Check:

)

This formula checks if there is a record in the Inventory Check data source that matches the current item ID and a creation date greater than the one selected in the on screen date picker. If there is no record, it returns false and hides the checkmark icon. If there is a record, it returns true and shows the checkmark icon.

This simple indicator can show the user if the item has been checked or not, which is much more efficient than requiring the user to look up each item one by one.

One of the downsides of using this method is that it can be costly and inefficient as it requires calling out to the data source for each item. This can affect the performance and responsiveness of your app, especially if you have a large number of items or a complex filter condition. To avoid this issue, you should try to limit the number of items you display in your app or use other methods that are more efficient. In my implementation, the list is filtered by the custodian of the inventory items. This filter shortens lists to no more than 100 items in most situations.

I have found that checking for records is very useful in providing dynamic UI. I hope this either helps you develop your own app or shows how dynamic Power Apps can be in creating custom apps.