Patch Dataverse Table with Lookup Columns from Collection

[ashokpershad]

I have one Table in Dataverse (Manhours) with following columns:

Now I used one FORM with this table as Datasource. When I press one submit button after filling details in this form I am creating one collection (displayed in Gallery5) where I am storing all  FORM submit entries. Please note this Submit button actually not for SUBMITFORM function. It is only for submitting new entries in collection.

Gallery5:

Now I want to Patch this Gallery back to my Dataverse Table using this ForAll and Patch functions. 

I used Following Formula:

ForAll(Gallery5.AllItems, Patch(Manhours, Defaults(Manhours), {WOrder:glry5WO.Text, 'Lab Supervisor':LookUp(Manhours, 'Lab Supervisor'.Supervisor = glry5Sup.Text), 'Service Engineer': LookUp(Manhours, 'Service Engineer'.'Engineer Name' = glry5eng.Text), 'Date Started':glry5DS, 'Date Completed': glry5DE, 'Actual Work Hours': glry5AH.Text, Remarks: glry5Remarks.Text}))

But this formula is showing following error:

What formula shall I use? 

I hope its clear.

[dpoggemann]

Hi @ashokpershad ,

Looking at your patch function should your lookups be actually going against the User table?

ForAll(Gallery5.AllItems, Patch(Manhours, Defaults(Manhours), {WOrder:glry5WO.Text, 'Lab Supervisor':LookUp(Users, 'Full Name' = glry5Sup.Text), 'Service Engineer': LookUp(Users, 'Full Name' = glry5eng.Text), 'Date Started':glry5DS, 'Date Completed': glry5DE, 'Actual Work Hours': glry5AH.Text, Remarks: glry5Remarks.Text}))

Not sure if the above is the right adjustment but overall you have two lookup columns for the Lab Supervisor and Engineer and I would expect you are trying to find the Users table value for these to populate in the field?  In this case I think your lookup needs to go against that table vs. your Manhours table.

Hope this helps.  Please accept if answers your question or Like if helps in any way.

Thanks,

Drew

[ashokpershad]

Dear @dpoggemann 

Someone suggested me this way to deal with Lookup when patching but it seems this is not the right way. Ignore my Formula... Could you please suggest what should be the best solution to patch this collection back to ManHours table in dataverse ?

[dpoggemann]

Hi @ashokpershad ,

Are the lookup columns for the engineer and the supervisor to the Users table?  If they are you might want to try the code I put in there above...  

A good article showing how to patch some of the more complex types is here:  http://powerappsguide.com/blog/post/dataverse-how-to-patch-the-5-most-complex-data-types 

Hope this helps.  Please accept if answers your question or Like if helps in any way.

Thanks,

Drew

[rampprakash · Accepted solution]

Hello @ashokpershad,

Can you please try the same below

ForAll(Gallery5.AllItems, Patch(Manhours, Defaults(Manhours), {WOrder:glry5WO.Text, 'Lab Supervisor':LookUp(Manhours, Name = glry5Sup.Text), 'Service Engineer': LookUp(Manhours, Name = glry5eng.Text), 'Date Started':glry5DS, 'Date Completed': glry5DE, 'Actual Work Hours': glry5AH.Text, Remarks: glry5Remarks.Text}))

Name --> It should be Primary Name Field in the Lookup Entity

Please mark as Answer if it is helpful and provide Kudos

Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Blog : https://microsoftcrmtechie.blogspot.com

[dpoggemann]

Hi @rampprakash,

The lookup for the users wouldn't be to the Manhours table I wouldn't think...  I assume they would be looking at the Users table for the Supervisor and Engineer so that is why I changed the code to do lookup like the following for those two that are like this one...  Does this make sense as the right change to you?  

'Service Engineer': LookUp(Users, 'Full Name' = glry5eng.Text)

Thanks,

Drew

[rampprakash]

Hello @dpoggemann,

If @ashokpershad need to fetch User then your code is perfectly correct. If he is expecting from custom entity then he can look at my code

[ashokpershad]

Dear @rampprakash  and @dpoggemann 

Thank you for your replies. 

Let me clarifies few things. Both my Supervisor and Engineer tables are custom tables with data like name, emails company etc. Have a look at below tables screenshot:

SUPERVISOR TABLE:

ENGINEER TABLE:

@rampprakash  I have tried adding same formula as you advised:

ForAll(Gallery5.AllItems, Patch(Manhours, Defaults(Manhours), {WOrder:glry5WO.Text, 'Lab Supervisor':LookUp(' Abc Supervisors', Supervisor = glry5Sup.Text), 'Service Engineer': LookUp('Service Engineers', 'Engineer Name' = glry5eng.Text), 'Date Started':glry5DS, 'Date Completed': glry5DE, 'Actual Work Hours': glry5AH.Text, Remarks: glry5Remarks.Text}))

There is NO errors now except one delegation warning. How can we remove this warning.

Only thing now, the data is not patching in the Manhours Table. Table is still not updating any data after pressing this Patch button.

In Manhours I have total 7 columns which I am already using in Patching. 

Thank you for your patience but this issue is taking too long to resolve.

[dpoggemann]

Hi @ashokpershad ,

I would try the following to break this down:

Create this outside of the ForAll:

1.  Add fields to your Canvas App to store Lookup values of your Engineer and Supervisor

2.  Manually pass in the values for one of the rows to these Lookups and verify they return what you are expecting

3.  Set all the fields in the patch manually from values of one of the records and verify you can get this patch to work. 

4.  Once you have patch working for 1 record then we can analyze the ForAll.

Hope this helps to identify the source of the issue.

Thanks,

Drew

[ashokpershad]

@dpoggemann 

I tried but there is some error coming:

This error coming after i press the Patch submit button. Before pressing button this error is not showing in formula section.

Everything seems fine but still not resolved.

Sorry now its becoming very frustrating.

Thanks