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

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,732)