Introduction

Hello all, it’s Friday again so it’s time to look at another Power Fx function. In previous weeks we’ve had a look at saving data with Patch, Update Context and Navigate. This week it’s the turn of the Lookup function. Applications are all about working with data, either while we are collecting it from end users or trying to display the information our end users want access to. Often this will mean we want to filter the data in some way so that the most relevant information is displayed at the right time, for the users.

Lookup function

In a nutshell, the ‘Lookup’ function returns the first record from a data source that matches the given formula. We can use the lookup function to search and return a record that matches either single or multiple criteria. It is important to keep in mind that this function only returns the first record in a table which matches the given criteria.

LookUp(Table*, Formula [, *ReductionFormula* ] )

Right let us break down the different parameters of the function;

Table – This is required. It is used to let the function know, what data source it has to look up.

Formula – This is also required. It reduces the data source down to the record we want to return based on the criteria we have defined.

Reduction Formula – This is optional, It will work over the returned record and reduce it to a single result. Using this we can name individual columns in a record. If this is not used the function will return the entire record.

Examples

For the following examples imagine we have a table which stores book titles along with related information such as author, publication date and stock levels.

ID	Title	Author	Publication_Year	Stock
1	The Witches	Roald Dahl	1983	100
2	1984	George Orwell	1949	250
3	Fahrenheit 451	Ray Bradbury	1953	75
4	Do Androids Dream of Electric Sheep?	Philip K Dick	1968	400
5	Down and Out in Paris and London	George Orwell	1933	50

Lookup(
       PublicationTitles,
       Author = "George Orwell"
)

In the above example, the lookup is filtering the records in our fictional book table by the author’s name. It also doesn’t include the reduction formula so will return the entire row. Great if that’s what you want, though what if you want to just return the stock levels of a title?

Lookup(
       PublicationsTitles, 
       Author = "George Orwell",
       Stock
)

Using the reduction formula in the lookup will return just the value in the stock column for the first record it finds which matches the search criteria. Though as you can see from our fictional table we have two records by George Orwell. This example would return the stock level value for 1984. However what if our users want the stock levels for Down and Out in Paris and London? That is when multiple criteria come into play.

Lookup(
       PublicationTitles,
       Author = "George Orwell"
       &&
       Publication_Year = 1933,
       Stock
)

By utilising multiple criteria we can refine our search parameters to return the results that we want to show the end user.

Lookup doesn’t only work with hard-coded criteria either, we can look up our data sources using dynamic data given to the application by the end user. The example below is the same as before, though using dynamic inputs.

Lookup(
       PublicationTitles,
       Author = cmbAuthorName.Selected.Value 
       &&
       Year(
            dtePublicationDate.SelectedDate
           ),
       Stock
)

Conclusion

The ‘Lookup’ function is a great tool for letting us return either single values or whole records from data sets. Giving us an easy way to display the data our end users want to see when they want to see it. Either with static code or even better dynamic search criteria which our end users choose. As mentioned earlier in this article, Lookup is but one way of getting there. Next Friday we’ll have a look at the Filter Function.

More information on the Lookup function can be found here in the Microsoft Documentation.

Finally, if you have any questions about the lookup function or suggestions on how and when it should be used, I’d love to hear them.