cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JasonF
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. 

Problem:

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.

4 REPLIES 4
v-siky-msft
Community Support
Community Support

@JasonF 

Sorry, but could you express the problem more succinctly?

Sik

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'.

 

Jason

 

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)})))

 


Sik

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")

So

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

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,015)