Home  >  Blog  >   Microsoft

Powerapps Lookup

Essentially, the PowerApps lookup function returns the first record from a table based on one or more conditions or formulae. Interesting? This blog uncovers the logic behind the PowerApps lookup function, its syntax, and its different types. Mainly, you can understand the application of PowerApps lookup functions with suitable examples by reading this blog. Also, you can learn how to set up lookup fields in Dynamics 365 in detail.

Rating: 4.5
  
 
836

Power Apps is a Microsoft product with which we can build custom business apps quickly. We can easily connect the apps with various data sources such as Dynamics 365, Microsoft 365, etc. Besides, we can run the apps on any platform – desktop, Tablet, or Mobile.

Know that PowerApps lookup is one of the essential aspects of Power Apps. PowerApps lookup function identifies the first record in a table based on conditions. Not only that, this lookup function allows using multiple conditions or formulas based on requirements. The formulae can match with various records in a table, but the PowerApps lookup function returns only the first record.

In this blog, you will walk through what is PowerApps lookup function, its syntax, how to apply the lookup function for different scenarios, and much more.

Power Apps Lookup - Table Of Contents

What is Power Apps Lookup?

As you know, you can find the first record in a table with the help of the Power Apps lookup function based on a condition or formula. The formula is one of the parts of the lookup function. If you need to get a single value instead of a row or record, the lookup function allows you to achieve that. The only thing you must do is write the lookup function in the required way. It is essential to note that if no record matches the formula given in the lookup function, it returns ‘blank’.          

When running the Power Apps lookup function, the formula is evaluated at each record of a table. The records that are true, or in other words, the records that match the criteria, will be added to the result table.
You can retrieve required information from various data sources using the Power Apps lookup function. It helps individuals and organizations to look up values quickly. Also, it allows them to retrieve information based on different conditions.

LookUp Concepts in Power Apps:

There are three types of lookups in Power Apps:

LookUp Concepts

Let’s have a look at them in detail.

Simple LookUp:

This is a simple and easy-to-implement Power Apps lookup function. This function selects a single row from a table based on one or more conditions given in the lookup function.

PartyList-type Lookup:

Using this Power Apps lookup function, we can select multiple records from multiple tables. Every row is added to the result table. Mainly, every time we add a record to the table, it allows searching for a new record in the tables. It is essential to note that we can’t customize the system as well as disable views in this lookup type.

getlsPartyList is the client API reference that returns a boolean value to indicate that it is a party-type lookup. It means that if the lookup function returns true, then it is a party list lookup. If it returns false, then it is not the party-type lookup. Usually, party list lookups allow setting multiple records. The syntax for this lookup type is given as follows:

PartyList

Regarding type Lookup:

We can use this Power Apps lookup to find a single record from multiple tables. If we want to use this lookup type, we must enable the ‘activities’ of a table. Moreover, it is an advanced lookup function that is highly useful to organizations, teams, and even individuals. Its improved capabilities simplify lookup operations significantly. It also supports intelligent authoring, which plays a pivotal role in application development.

Once regarding type lookup function is enabled, we can find the function in the lookup columns so that we can create as well as forms easily. Also, we can change settings and sort rows. Besides, we can change tables within a lookup column.

With the help of client APIs, we can customize this function. For example, we can create a new view for the dialogue box. Also, we can remove functions defined for the PreSearch event. Similar to removal, we can add custom filters using client APIs.

Learn end-to-end PowerApps concepts through the PowerApps Course in Hyderabad to take your career to a whole new level!

Power Apps Lookup Function Syntax:

The syntax of the Power Apps LookUp function is given below:

Function syntax

Let’s look at the arguments of this function in detail below.    
The table is one of the mandatory parts of the Power Apps lookup function. As you know, the table is the location or database where you will search for records. You can find the syntax as ‘source’ above the function box in the user interface.

The formula is another mandatory part of the Power Apps lookup function. The formula helps to identify the records that match one or more conditions or criteria. The formula evaluates every record in tables. You can find the syntax as ‘condition’ above the function box.

The reduction formula is the optional part of the Power Apps lookup syntax. You can use this part if you want a single value resulting from a lookup function instead of a record or records. In other words, the reduction formula converts a single record into a single value. You can find the syntax as ‘result’ above the function box.

 MindMajix YouTube Channel

Power Apps Lookup Function Examples.

As you know, we use the Power Apps lookup function to retrieve the first record from a table. To achieve this, we use one or more formulae.
Let's have a look at some examples of how to use the Power Apps lookup function differently.

Example: 1

Consider the below table. The Table name is Marks.

Table Name: Marks

Student NameEnglishMathsScience
Mary657598
Neeraj678556
Omar756584
Peter925575
Qumayun758485
Roberts529564

Now, we will discuss the different implementations of the lookup function below:

  • Applying the Power Apps lookup function with a single condition
  • Applying the Power Apps lookup function with multiple conditions
  • Applying the Power Apps lookup function to find the maximum value
  • Applying the Power Apps lookup function to find the last record

Power Apps Lookup function with a Single Condition:

We can apply the lookup function with a single condition in the ‘Marks’ table as follows:

Single condition

We can identify multiple values in the ‘Marks’ table based on the formula. But this Power Apps lookup function returns only the first record.

The result of this function is Mary.

Power Apps Lookup function with Multiple Conditions:

We can apply this lookup function with multiple conditions in the ‘Marks’ table. For this case, the lookup function is written in the following way.

Multiple Condition

The conditions or formulae in the Power Apps lookup function can return two records. However, as shown below, the lookup function will return a single output, the first record in the table.

The result for this lookup function is Neeraj.

Power Apps Lookup function for Maximum value:

If we need to get the maximum value of a column from the ‘Marks’ table, we can use the following lookup function.

Maximum Value

This lookup function will return a value that is the highest in the ‘Maths’ column of the table.

The result for this function is Roberts.

Power Apps Lookup function for Last Record:

As you know, the Power Apps lookup function usually returns the first record of any table. But, if you want to get the last record of the table, then you must apply to sort in the result table to return the last record. The logic behind this operation is very simple. We need to reverse the result table so that the last record of the result table comes as the first.

The Power Apps lookup function for this condition is given as follows:   

Last Record
                

The result of this function is Roberts.

Example: 2

Let’s have a look at one more example to understand how to use the Power Apps lookup function.     

Consider the below table:

Table Name: Ice-cream

FlavorQuantityOnOrder
Chocolate15080
Vanilla25060
Strawberry4000
Teaberry80120

Power Apps Lookup function for a single condition:

Consider the following lookup function    

Lookup function for single condition
                                                   
This lookup function searches the chocolate flavor in the ice cream table. And there is only one record in the table for this condition. The quantity of chocolate ice cream is 150. The lookup function returns this value as output.
The result for this function is 150.

Power Apps Lookup function with multiple results.

Consider the below lookup function                            
This Power Apps lookup function searches for flavors that have a quantity of more than 200. So, the result table will have two records since the ice cream table has two flavors with more than 200. However, the lookup function will return the first record in the table – Vanilla.
The result of this function is 310.

Power Apps lookup function with single condition and blank result

Consider the following lookup function:         

Blank function
                                                          

This lookup function searches for the ‘Butter Pecan ice cream flavor in the table. The table doesn't have this flavor. It means that there are no records for Butter Pecon in the table. As a result, the lookup function will return a ‘blank’ result.

The output of this function is ‘blank’.

Power Apps LookUp function with no reduction formula:

Now, consider the below example.

Reduction formulae

                                                         
This Power Apps lookup function searches for the vanilla flavor in the table. And there is only one record in the table. Note that the lookup function doesn’t include any reduction formula. That’s why this lookup function returns the complete record as a result.

The result of this function will be as follows:                           

Vanilla25060

Example: 3 

Are you Still wondering how to catch up with using Power Apps lookup functions? The following example will help you in a great way.

Consider the below example.

Table name: Employee List

Employee NameRoleDepartment
AliyaStaffAccounts
BenjaminLine ManagerProduction
ChandraAssistant ManagerMarketing
DeepakAssociate ManagerSales
EbyLine ManagerLogistics

 
Power Apps lookup function with a single condition

If you need the records of the associate manager, then you can write a lookup function as follows:        

Employe

                                  
The result of this look function is as follows:

Employee NameRoleDepartment
DeepakAssociate ManagerSales

No reduction formula is included in this lookup function. That’s why the function returned the complete record of the employee.

Power Apps lookup function with person column of the lookup column

In the given table, 'Employee name' is the person column. You can use the following lookup operation if you want only the employee name.

employ name

The result of this function will be Chandra.

Power Apps lookup function with multiple conditions:

As you know, you can apply multiple conditions or formulae in a Power Apps lookup function. The Power Apps lookup function only returns records that satisfy the conditions.
Consider the below lookup function.

employ multiple

The result of this function will be Eby.

Would you like to ace Power Apps job interviews? Top PowerApps Interview Questions from MindMajix are exclusively for you!

How to read and set up lookup fields of Dynamics 365 in Power Apps:

We can perform multiple operations with Power Apps. Let’s look at how to read as well as set up lookup fields in Dynamics 365.

However, before reading and setting up the lookup field in Power Apps in Dynamics 365, you must perform the below steps.

  • First, select the phone layout in the new Power Apps.  

Set up 1

  • Next, you need to select the database. To achieve this, navigate to the connections tab on the new page and select ‘opportunities’.

set up  2

  • Now, you can click ‘connect’.
  • Now, you can view three screens on the left side of the page: browse, edit, and detail.
  • If you click the browse screen, then you will see the list of opportunities on the page.

setup 3

  • Also, you can see the GUID of the parent account.
  • If you need to display the name of the parent account, then you need to add a ‘Data source’ account. To do this, you must open ‘view’ and then ‘Data sources’. And then you must add a ‘new data source’.

setup 4

  • Then enter the following lookup function in the Text body to display the name of the parent account.

Once you complete all the above steps one by one, you can view the parent account under the opportunities section.

If you want to assign lookup values to power apps, you need to perform the following steps.

  • In the DataCard tab, Click on the ‘edit’ screen. Then, insert the ‘Search’ icon.
  • You can insert another list screen. Also, you can add ‘data source’ as ‘accounts’.
  • Now, you can see the list of all accounts that exist in the CRM.
  • Set the formula on the ‘OnSelect’ search box.
  • You can enter the formula as follows:
  • This function will update the ‘searchAccountName’ by value in DataCardValue10. 
  • Working on Lookup Field of Dynamics 365 in Power Apps | Microsoft Dynamics 365 CRM Tips and Tricks (inogic.com)
  • Now, enter the following formula in the ‘OnSelect’ property of the icon on the account screen. 
  • This formula creates a new collection name, "accountToBeAdded"
  • Add the selected account to the collection and then return to the edit screen.
  • Then navigate to the edit form and then enter the following formula on the text box of the account.
  • This formula helps confirm whether the ‘accountToBeAdded’ collection has an account ID. If the ID is found, it will set the name. Otherwise, it will return ‘blank’.
  • Finally, set the following formula on the updated property of Datacard as follows:
  • This formula helps to update the Datacard value with the selected account.

FAQs:

1. How does lookup work in Power Apps?

The lookup function in PowerApps returns the first record from a table. It returns the record that satisfies the condition or formula given in the lookup function. Using this lookup function, you can get a single record or a single value. If you need to get a single value, use the reduction formula in the lookup function.

2. How do I use the lookup field in Power Apps?

We can use lookup fields in Power Apps in the following way.

  • You can browse the lookup column to select a list of items from a list
  • You can identify the most recently used rows with the help of row images.

3. How do I create a lookup in Power Apps?

You can create a lookup using the Power Apps lookup function. The lookup has three essential elements: table, formula, and reduction formula. You need to create a lookup function based on your desired results.

4. How do I filter a lookup field in Power Apps?

We can filter lookup fields in Power Apps in the following way.

  • Navigate to the tree view and then click the screen tab
  • Select the field that you want to filter
  • Apply the filter function for the selected field
  • Finally, click create.

5. What is the lookup column in Power Apps?

The columns in tables represent the lookup columns. For example, the table has an employee list, which refers to a lookup column.

Conclusion:

In a nutshell, the PowerApps lookup function returns the first row or record of a table. It evaluates the records of the table based on a single condition or number of conditions. If we want to get a single value instead of a record, the lookup function allows doing so. So, this blog might have helped to understand PowerApps lookup concepts, its applications, and many others. If you want to learn more about the PowerApps lookup function, you can visit the MindMajix PowerApps Course and achieve certification. This way, you can sharpen your knowledge better.

Join our newsletter
inbox

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Course Schedule
NameDates
PowerApps TrainingMar 30 to Apr 14View Details
PowerApps TrainingApr 02 to Apr 17View Details
PowerApps TrainingApr 06 to Apr 21View Details
PowerApps TrainingApr 09 to Apr 24View Details
Last updated: 08 Nov 2023
About Author

Keerthi Sai is a Senior Writer at Mindmajix with tons of content creation experience in the areas of cloud computing, BI, Perl Scripting. She also creates content on Salesforce, Microstrategy, and Cobit.

read more
Recommended Courses

1 / 15