Power Apps LookUp is one of the most popular features of the Microsoft low-code development environment. In fact, this functionality allows you to find a single record based on personalized search criteria. In this article, we will learn more about what the Power Apps LookUp function is, what features distinguish it from the Search and Filter functions, and how to use it in our business apps with a use case to be recreated in 3 simple steps.
The LookUp function of Microsoft Power Apps is one of the fundamental functions for manipulating and managing data in an application developed within the low-code development environment offered by Microsoft and plays a crucial role in helping users search for specific entries within large volumes of data.
LookUp allows users to retrieve specific information from a table or data source, retrieving a single record corresponding to certain search criteria and is useful when you want to find a specific record that can meet certain conditions within a larger data set, greatly speeding up and simplifying the process of searching for the information we need.
When a user enters a certain search term to find matching rows, the LookUp function displays any rows that will match or whose text bears partial resemblance to the entered query.
Using this function for our Power Apps applications guarantees users more dynamic and user-friendly searches and can greatly simplify browsing through their data, allowing users to immediately find what they need with minimum effort and maximum precision.
Basically, Power Apps' LookUp function returns the first record from a table based on one or more conditions or formulas, helping users and organizations to quickly search for the matching values they want. It is generally used to sort directories of important data stored within the user's organization or to recognize important parts within related tables.
It's very similar to Excel's VLOOKUP and HLOOKUP, but more flexible and covers more areas such as INDEX MATCH, perfect if you're working with multiple data sources in a single app.
Essentially, the records in a table are filtered based on one or more criteria specified by a formula. When you run the Power Apps LookUp function, the formula is evaluated on each record in the table. The records that match the given search criteria will be added to the results table.
It's important to note that if no record matches the formula provided in the search function, the search function will not provide any results.
The general syntax of the function should look something like this:
LookUp (Table*, Formula [, *ReductionFormula*])
Or how it will appear in the user interface in Power Apps:
LookUp (Source, Condition, Result)
This function evaluates each record in the specified table and keeps only the records that meet the criteria specified in the formula (s). Let's now see what the parameters of the formulas given above correspond to:
When using the LookUp function, it is vitally important to understand the interaction between the various parameters that contribute to producing the final result of our research. The more formulas you specify, the more precise the result will be.
In Microsoft Power Apps, the LookUp function can fall into three main types that are different in some aspects and can be used in different contexts depending on your needs. Let's take a closer look at them and see what differentiates them and in what contexts we can use them.
This is the most common type of LookUp function. Find the first record in a table that matches a specified formula. It can be used to find a single record that matches one or more criteria.
For example, it could be used to find a specific customer in a table of customer data based on their ID or name. The basic syntax of the LookUp function as we have already seen is:
LookUp (Source, Condition, Result)
This type of LookUp will return the first record that meets the specified formula. If no record matches the defined criteria, an empty value will be returned.
The LookUp PartyList type in Power Apps allows you to select multiple records from multiple tables. Each row is added to the results table. Basically, every time a record is added to the table, you can search for a new record in the tables. It is important to note that it is not possible to customize the system or disable views in this type of LookUp.
This type of LookUp is useful when you want to select multiple records from different tables and add them to a results table. However, because it is not particularly customizable, it may not be suitable for use in more complex scenarios that require greater control over the user interface.
The syntax for using the PartyList type is as follows:
FormContext.getAttribute (arg) .getLSPartyList ()
GetLSpartyList is the reference to the client API that returns a Boolean value to indicate that it is a PartyList lookup function. It means that if the search function returns true, then it's a LookUp PartyList function.
The LookUp Regarding type is particularly useful when you want to search for data from multiple tables within a Power Apps app. This functionality allows you to find a single record that matches certain criteria specified by the user.
It is an advanced option that requires the 'activities' of a table to be enabled. It is especially useful for organizations, teams, and individuals as it significantly simplifies research operations.
Once enabled, it offers a series of advanced features that allow users to further customize the search process and to manage the results obtained effectively. For example, using client APIs, developers can extend and customize the functionality of this type of search.
This type of LookUp also supports intelligent authoring, allowing for quick creation of apps and forms.
The LookUp Regarding function has a specific syntax that is similar to the standard one, but includes the ability to specify the type of relative search. The general syntax is the same as the standard LookUp function, but includes the option to specify the type of relative search using an additional parameter.
LookUp (Source, Condition, Result, RegardingType)
Dev4Side Software specializes in creating business apps with Power Apps, drastically reducing internal application development and maintenance processes.
Operating vertically across the entire Microsoft Power Platform ecosystem, we have developed extensive expertise in Power Apps, which allows us to offer custom-designed and fully integrated solutions within your Microsoft 365 tenant.
Contact us to transform business information into concrete actions.
Now that we have given a general overview of the Lookup function, let's see how and under what conditions it is possible to apply it to search for the data that interests us.
In this regard, we will take the data table below (which we will indicate as “EmployeeStable”) and we will use it as a reference for all the application modes that we are going to analyze.
To exemplify the search function with a single condition, suppose we want to find the employee named “Giorgio Neri” and view the department to which he belongs. Using the LookUp function the syntax would be:
LookUp (EmployeeStable, Employee Name = “George Blackwell”, Department)
This formula searches the table for the first record where the 'EmployeeName' field matches 'George Blackwell' and returns the value of the 'Department' field, in this case 'Accounting'.
If, on the other hand, we wanted to use more conditions for our search to display the name of an employee that complies with the parameters we chose, the syntax of the LookUp function could be similar to this:
LookUp (EmployeeStable, Department = Marketing, Salary > 4000, Employee Name)
The result of this function will return the name of Anna Brown, the only entry in the table to respect both of the set parameters.
Instead, suppose we want to search for the maximum value present in a column in our table, in this case the salary, and to know who it is associated with. The syntax that we will use for our function will be this:
LookUp (EmployeeStable, Salary = Max (Salary), Employee Name)
The function will return as a result the name of the employee to whom the highest value in the 'Salary' column is associated, in this case Mark Redwall.
As we know, the Power Apps search function usually returns the first record in any table. However, if you want to get the last record in the table, you must apply a sort in the results table to return the last record.
The logic behind this operation is very simple, we must invert the results table so that the last record in the results table becomes the first.
The syntax of the function we are going to use will be this:
LookUp (Sort (EmployeeStable, ID, ascending), Employee Name)
The result of the function will be the last entry in the table, namely 'George Blackwell'.
LookUp is very often a rather confusing function for those who are just getting closer to the world of Power Apps development and is often exchanged by novice users with some of the other search functions made available by the Microsoft development environment, such as Search or Filter.
So let's try to clear up the confusion on the subject once and for all and understand what differentiates LookUp from the other two functions mentioned above.
The Filter function is used to find all the records in a table that meet the criteria of the formula. This is equivalent to how you would use the SharePoint or Excel filter to return multiple records from the list that match one or more criteria and discard those that don't.
The Search function, on the other hand, is designed to search for records that contain a string and not that satisfy a formula.
Instead, the LookUp function, as we have already seen, finds the first record in a table that meets the formulas inserted in the function. You use LookUp to find a single record that matches one or more criteria.
Take, for example, a list of Service Tickets. The list has a search field for a list of employees to whom a Ticket can be assigned.
If we want to see only the currently open Service Tickets, Filter is the right solution, while if we want to be shown the tickets that have been assigned to someone with 'Marco' in their name, the Search function may be more useful to us.
If, on the other hand, you want to return as a result the telephone number of the employee to whom the Service Ticket selected in your Power Apps table is assigned, this would be a good use case for the LookUp function because for our research it is necessary to return a single record.
Now that we know how the LookUp function works in more detail, let's see together an example of its application in a possible real scenario.
Let's say we have an app that allows internal users of a company to request the purchase and shipment of office equipment and have it delivered to their specific office.
In this case, we will use a SharePoint list as a data source, but in any case, remember the possibility of using numerous other data sources, such as Microsoft Excel.
We log in to the Power Apps portal with our Microsoft account. If you don't have one, don't worry. It will only take a few clicks to create an account and in addition it is completely free.
Once we have obtained access to the portal, select the option”New app” from the menu and choose the type of app we want to create.
In the design interface of Power Apps Studio, we drag and drop elements from the toolbar such as forms, tables, buttons, and controls onto the design area of our app and organize the user interface elements like this:
The office location and shipping method are disabled because it is in these specific fields that we will use LookUp and we do not want to allow manual data entry within these two specific fields.
Now let's deal with our data sources. In this particular case, we will use SharePoint and organize our lists as follows:
The first will be an order table where any orders placed by users will be saved and we will call it 'OrderStable'.
Secondly, we will have a list of the available offices and their geographical locations that we will call 'OfficeStable'.
Finally, for the preferred shipping companies for the different locations, we will create a list that we will call 'ShipperStable'.
When our end user is filling out the form and selects the office where they want to deliver the items, we want to automatically populate the Office Location and Shipping Method inputs, to do this we can use the LookUp function.
We want to insert our expression in the 'Default' property of the input we're working on, this means that the default text value of the input will be the result of the LookUp function.
First, to automatically populate the Office Location, we can perform a search in OfficeStable, to retrieve the region, based on the name of the office that the user selects in the form.
When we transpose our values into the model, we obtain:
LookUp (OfficeStable, OfficeName = DeliveryOffice.selected.OfficeName, Region)
This is asking the app to:
Second, we can do another search in the carrier table to populate the Shipping Method input, based on the office location populated by the previous LookUp. Following the model again and entering our values, we obtain:
LookUp (ShipperStable, Region = OfficeLocation.text, PreferredShipper)
This is instead asking the app to:
Now, when the user changes the desired delivery office, the two inputs for Location and Shipping Method will change automatically based on the items selected by the user.
We then conclude by inserting the Patch function on our 'Save' button to save the order in our order table using the following syntax:
Patch (OrderStable, Defaults (OrderStable), {Title: CountRows (OrderStable) + 1, first name: FirstNameInput.text, last name: LastNameInput.text, equipment: EquipmentInput.selected.Value, DeliveryOffice: DeliveryOfficeInput.selected.OfficeName, ShippingMethod: ShippingMethodInput.text})
Once the function has been inserted, the 'Save' button will be fully functional and we can dedicate ourselves to customizing the aesthetics of our app at will, testing it for any errors to be corrected or malfunctions and then distributing it to our users for immediate use.
In summary, using the LookUp function in Microsoft Power Apps allows us to immediately obtain the desired result in our searches, personalize user experiences by showing users only relevant data and automate processes within their apps to automatically search for and use the information that is necessary for us.
By applying formulas and criteria specific to our needs, we can always show only the information or records necessary and relevant to the needs of our users, without running into the common problems of regular manual searching.
The LookUp function in Power Apps is used to search for a single record within a data source that meets a specified condition. It returns the first record that matches the criteria.
You can use the LookUp function in Power Apps by providing the data source, the condition to match, and optionally, the column to retrieve. The syntax is LookUp(DataSource, Condition, [Column]).
No, the LookUp function in Power Apps is designed to return only a single record. If multiple records match the condition, it will return the first record found.
If no records match the condition in LookUp Power Apps, the function returns a blank value.
While both LookUp and Filter functions in Power Apps are used to search within data sources, LookUp returns a single record, whereas Filter returns a table of all records that match the criteria.
Yes, you can use multiple conditions in the LookUp Power Apps function by combining them with logical operators like && (AND) and || (OR).
LookUp Power Apps function can be used with a variety of data sources, including SharePoint lists, SQL databases, Excel files, and more.
To handle errors in LookUp Power Apps, you can use the IsBlank() function to check if the result is blank, indicating that no matching record was found.
Yes, LookUp Power Apps can be used to search within collections, just like any other data source, to find a specific record.
Delegation in LookUp Power Apps refers to the ability of the function to delegate the query to the data source. However, not all data sources support delegation, which can lead to incomplete data being returned if the data set is large.
The Modern Apps team specializes in development and integration across the entire Microsoft 365 ecosystem. We design native applications for Microsoft and Azure platforms, and implement business processes that integrate with and maximize the investment in Microsoft 365.
