cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JasonF
Level: Powered On

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
Microsoft v-siky-msft
Microsoft

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

@JasonF 

Sorry, but could you express the problem more succinctly?

Sik

JasonF
Level: Powered On

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

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

 

Microsoft v-siky-msft
Microsoft

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

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

JasonF
Level: Powered On

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

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
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (9,388)