Showing results for 
Search instead for 
Did you mean: 
Helper III
Helper III

How to Patch a CDS lookup field using a text field from another table.

Hi everyone, 

Not sure if I can explain this properly, but here it goes.

Background info:

I have created a inventory entry program that uses a Template. The template is generated from all the possible inventory items in the database. The user can create a template using the items, thus creating custom inventory entry for their particular function and location. 


The user (two users....4 template... I don't know why or how) deleted their templates (I guess I have bullet proof a bit more) that they created. Each template has about 800 items in it. The CDS database is set up so it cascades to the all the detail records, thus deleting the template master record, deletes all the template detail record.

My thoughts:

So I thought, this no big deal. Since the inventory report uses the template to create each inventory,  I can just reverse the process and reconstruct the templates from the various inventories these users have completed in the system. Since inventory reports are stored in separate data sets than the template.

Programming Problem:

I use multiple lookups through the system maintain data integrity….EXCEPT on the "Inventory Detail", whish is populated by the template and user just has to fill Qty. to complete the inventory. So my problem is I am trying to patch a record from a text field to lookup field. Now I can know I can find the record I need buy using the "Item No" reverence in another table, but nothing I try seems to work in the patch.

Here as some screen shots explain the problem:

Inventory Detail.png


So as you can see here the 'Inventory Units' field is a text field, however it is a lookup field in the target patch table 'Template Detail', However I do know the 'Item No' in the table associated with the record. I also know the text value of 'Inventory Units', this all the info I need to lookup the record in a table call 'Item Units'


Here is the Target table 'Template Detail':

  • Template Detail.png


Taking all this into account I tried many different renditions of this function:



ForAll('Inventory Details',If(InReportNo.crdf5_inreportno="IN-00000101",Patch('Template Details' ,{TemplateNo:First(Filter('Inventory Templates',Text(crdf5_templateno)="T-1029")),Units:LookUp('Item Units',Text('Item No'.'Item No')='Inventory Details'[@'Item No'])})))


So to be clear, this only demonstrating some of my logic, but this function fails at the "=" on the lookup. Also please ignore some other field being patch. For this purpose if I can get 

{Units:LookUp('Item Units',Text('Item No'.'Item No')='Inventory Details'[@'Item No'])}

Part of the function to work, or get some direction in the error of my logic, that would be great.


I know this long and drawn out, so thanks everyone for taking the time to review.

Community Support
Community Support


Sorry, but could you express the problem more succinctly?


Hi Sik,

Ok, how about this explanation:

I have three tables

Table1  - Destination Table

Table2 - Source Table

Table3 - Lookup Table


I want to patch all the records from Table2 into Table1. Table1 has lookup field 'Field1A' that is a lookup to Table3, however this field does not exist in Table2. But Table2 does have 'Field2A' and 'Field2B' which would allow me to find(lookup) the appropriate record in Table3. Now, for the purposes of the exercise we will ignore all the other fields being patch as I have no problem with these. 

So the question is what a patch command would look like using Field2A and Field2B to lookup a record Table3 that will fill the value for 'Field1A'.




Ok @JasonF 

You need to add new columns to save related Values you want to reference.

I create the ColumnA to save 'Item No'.'Item No' and ColumnB to save InReportNo.crdf5_inreportno in 'Inventory Details' Entity,

also, create ColumnC to save 'Item No'.'Item No' in 'Item Units' entity

Please try this:


ForAll(AddColumns('Inventory Details',"ColumnA",'Item No'.'Item No', "ColumnB", InReportNo.crdf5_inreportno),
If(ColumnB="IN-00000101",Patch('Template Details' ,{TemplateNo:First(Filter('Inventory Templates',Text(crdf5_templateno)="T-1029")),Units:LookUp(AddColumns('Item Units',"ColumnC",'Item No'.'Item No'),ColumnC=ColumnA)})))



I am not explaining myself very clearing, my apologies.

Table3 has the same fields as Table2, So lets call them Field3A, and Field3B, so we do not need to add columns to lookup to Table3('Item Units")


Field1A=Lookup(Table3,'Field3A'=Table2,'Field2A & Table3,'Field3B'=Table2,'Field2B)


My problem is how do put this logic into a patch. For the purposes of this we will forget my first post and all names therein. Lets just look at it as per the example in my second post.

Helpful resources

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (2,332)