PowerApps LookUp vs Filter vs Search Functions by Examples

PowerApps LookUp vs Filter vs Search Function

PowerApps LookUp function, Filter function and Search function are confusing in the beginning. The three functions do filter records, but there are small differences. This article exaplins when to use which function, how there are used and shows the usage of the functions in simple examples.

We describe each function detail in this article.In case you just want to know, when to use the LookUp function, when to use Filter function and when to use the Search function. Here is th answer in short:

When to use which function?

  • Use the LookUp function, when you are looking for a single record by a certain criterion.
  • Use the Filter function, when you are looking for multiples records by certain criterions.
  • Use the Search function, when you are looking for items where columns contain a search string.

LookUp Function

The LookUp function filters for the first item for a filter condition.

Syntax

LookUp(Table*, Formula [, ReductionFormula ] )

Input parameters

  • Table* (mandatory): The collection where you want to lookup an item.
  • Formula (mandatory): The condition the looked up item should match.
  • ReductionFormula (optional): In case you do not need all data of the looked up item, you can define here what you exactly need.

Return value

  • Without Reduction Formula it will be a single record of the given table
  • With Reduction Formula it can be everything the formula defines (string, number, ..)
  • Returns blank, when no record is found

LookUp Function Examples

To demonstrate the LookUp function we create some test data:

NameAgeCompany
John Doe45Contenso
Maria Smith23Contenso
Mike Miller33Microsoft

This is how the test data can be created via PowerApps.

ClearCollect(
    Contacts,
    {
        Name: "John Doe",
        Age: 45,
        Company: "Contenso"
    },
    {
        Name: "Maria Smith",
        Age: 23,
        Company: "Contenso"
    },
    {
        Name: "Mike Miller",
        Age: 33,
        Company: "Microsoft"
    }
);

Example: LookUp record by column value

To get the single entry of John Doe from our sample records, you can do the following.

LookUp(Contacts,Name = "John Doe");
/*
 Returns  
   {
        Name: "John Doe",
        Age: 45,
        Company: "Contenso"
    }

*/

But what if there would be multiple records matching the lookup condition? LookUp will return the first record, that matches the condition. For instance, there are two entries in our table, that work for the company Contenso (John & Maria). Since we defined John before Maria, the following example will return John.

LookUp(Contacts,Company= "Contenso");
/*
 Returns  
   {
        Name: "John Doe",
        Age: 45,
        Company: "Contenso"
    }

*/

Example: LookUp record by column value with reduction formula

The reduction formula let’s you define, which data you need of the record that matches the lookup.

LookUp(Contacts,Name = "John Doe",Age);
// Returns 45

LookUp(Contacts,Name = "John Doe",Name & " (" & Company & ")");
// Returns John Doe (Contenso)

Example: LookUp with no matching record

What if there is no record matching the condition? A blank value will be returned.

IsBlank(LookUp(Contacts,Name = "Darth Vader"))
// Returns true

Filter Function

The Filter function filters all items of a collection matching the given condition(s).

Syntax

Filter(Table*, Formula1 [, Formula2, ... ] )

Input Parameters

  • Table (mandatory): The table you want to filter.
  • Formula1 (mandatory): The filter condition to filter the records.
  • Formula2 (optional): More filter conditions (can be multiple).

Return Value

  • Returns a collection of items that match the condition(s)
  • -Is empty in case no item matches the conditions

Filter Function Examples

ClearCollect(Numbers, [1,2,3,4]);

Filter(Numbers, Value>1);
// Returns 2,3,4

Filter(Contacts,Age>30);
// Returns a collection with the items of John and Mike 
// (see creation of Contacts at the bginning of ths article)

Filter(Numbers, Value>1,Value<4)
// Returns 2,3

Filter(Numbers, Value>1,Value<4,Value <> 3 )
// REturns 2

IsEmpty(Filter(Numbers, Value>4 ));
// Returns true

Search Function

The Search functions filters a table by checking if a defined search string is contained within one or multiple columns with ignoring case sensivity.

Syntax

Search(Table*, SearchString, Column1 [, Column2, ... ] )

Input Parameters

  • Table (mandatory): The table in which you are search items.
  • SearchString (mandatory): Text that should be contained in the column(s) value. (Empty search string returns all recotds)
  • Column1 (mandatory): The table column where the value of the column gets checkd if it cotains the search string.
  • Column2 (optional): Additonal columns that are being searched (can be multiple).

Return Value

  • Returns a collection of items taht match the search.
  • Returns an empty collection in case the search does not find anything.

Examples Search function

ClearCollect(Names, ["Ada","Aaron","Sandy","Andy"]);
Search(Names,"an","Value")
// Returns Sandy and Andy

Search(Contacts,"n","Name","Company");
// Returns John Doe (Contenso) & Maria Smith (Contenso) 
// (see creation of Contacts at the bginning of ths article)


IsEmpty(Search(Names, "Sally","Value"))
// Returns true

Leave a Comment

Your email address will not be published.