How To Use Lookup in patch function in Canvas App
Scenario: I have an Item table and a Transfer table, In the Transfer Table I have a lookup column from the item table as “Item Column”. I need to fetch unique values from the Item table and based on it I’m filtering Site and these values I need to patch this lookup value to the transfer table
High level resolution steps
- I have Inserted a Combo box for Item table data and used Distinct to fetch the unique Values from Item Table
- In the Transfer table, I have created a lookup column for item names from item table
- On Submit Button I am using patch and lookup functions
Detailed resolution steps
Distinct Function: The Distinct function in Power Apps returns a one-column table with unique values from a specified column or formula in a data source
Syntax:
Distinct(Datasource, Columnname) |
Step 1: I have Inserted a Combo box and named it Combobox_Itemname and on the item’s property of that combo box I’ve given this formula.
Distinct(‘Item Tables’,’Item Name’) |
Explanation of Formula:
- ‘Item Tables’: The data source containing the data.
- ‘Item Name’: The column from which I want to extract values.
- Distinct: A function that removes duplicates and returns only unique values.

Filter Function: The Filter function in Power Apps is used to return a subset of records from a table that satisfies one or more conditions.
Syntax:
Filter(Datasource, LogicalFormula) |
Combining Distinct and Filter:
We can use Distinct with Filter to find unique values from a filtered dataset. This is useful when we want to extract distinct values based on specific conditions.
Syntax:
Distinct(Filter(Source, LogicalFormula), Expression) |
Step 2: Now I have Inserted another Combo Box and named it as Site, On Items property of that Combo box I have given
Distinct(
Filter(
'Item Tables',
'Item Name' = Combobox_Itemname.Selected.Value
),
InventSite
)
Explanation of the Formula:
This formula retrieves a list of unique Invent Site values from the ‘Item Tables’ data source, filtered based on the selected Item Name in the combo box (Combobox_Itemname).

Step 3: I’ve inserted another Combobox and named it Combobox warehouse, on the item’s property of the Combo box I’ve given
Distinct(
Filter(
'Item Tables',
'Item Name' = Combobox_Itemname.Selected.Value &&
InventSite = Combobox_Site.Selected.Value
),
InventWarehouse
)
Explanation of the formula:
This formula retrieves a list of unique InventWarehouse values from the ‘Item Tables’ data source, based on the selected Item Name and InventSite values in two combo boxes

Explanation of Patch:
The Patch function in Power Apps is used to create or update a record in a data source. It allows you to:
- Create a new record by combining Defaults () with the data source.
- Update an existing record by specifying the record to update.
- Update only specific fields, leaving others unchanged.
Syntax of Patch:
Patch(DataSource, RecordToUpdate, ChangeRecord) |
Explanation of LookUp:
The LookUp function retrieves a single record from a data source that matches a specified condition. It is often used to find related data or reference a specific value.
Syntax of Lookup:
LookUp(Source, LogicalFormula [, Result]) |
Step 4: Inserted a Button and Named it as Submit on the select property I’ve given this formula
Patch(
TransferJournalLines,
Defaults(TransferJournalLines),
{
Item: LookUp(
'Item Tables',
'Item Name' = Combobox_Itemname.Selected.Value
),
'From Site': Combobox_Site.Selected.Value,
'From Warehouse': Combobox_warehouse.Selected.Value
}
)
Explanation of Code:
- Item: LookUp(‘Item Tables’, ‘Item Name’ = Combobox_Itemname.Selected.Value): Finds the record in ‘Item Tables’ where the ‘Item Name’ matches the selected value in Combobox_Itemname. Assigns the corresponding Item value to the new record.
- ‘From Site’: Combobox_Site.Selected.Value: Sets the ‘From Site’ field in the new record to the value selected in Combobox_Site.
- ‘From Warehouse’: Combobox_warehouse.Selected.Value: Sets the ‘From Warehouse’ field in the new record to the value selected in Combobox_warehouse.
Output
Here I have selected the Item name Booster Pumps & based on the Item name it filters its related Invent sites.

Here Based on the Item name Booster pumps and Invent site “2” it is filtering its related Invent warehouse values and after selecting warehouse I submitted the data by clicking on submit button.
The Submitted data is now Added in the Dataverse:

So, in this way we can use the Lookup inside the patch, as the item is the lookup column in the table, we used the Lookup function inside the patch Function.