cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mssss
Helper V
Helper V

Copy dataverse related record ( Multiple Tables)

Hello.

We want to copy the related records of two tables by pressing a button on my gallery.

 

On gallery’s item is “report” .

Table:report

No

date

A1

2021/08/04

B1

2021/08/05

 

I have one more table that has a lookup column.

Table:sub

No(Lookup from report)

products

A1

Tea

B1

Coffee

B1

Tea

 

When I press button on Galley row B1, I would like to copy a record to both tables.

I do not

 

Table:report

No

date

A1

2021/08/04

B1

2021/08/05

C2Auto

2021/08/05

Table:sub

No(Lookup from report)

products

A1

Tea

B1

Coffee

B1

Tea

C2

Coffee

C2

Tea

 

Any and all help is appreciated!

6 REPLIES 6
Nogueira1306
Super User
Super User

Hey!

You can do something like this:

Patch( WhereYouWantToSave; Defaults(WhereYouWantToSave);{No:  ThisItem.No; products: ThisItem.products});;

 

 

Check this link about Patch() function

 

https://docs.microsoft.com/pt-pt/powerapps/maker/canvas-apps/functions/function-patch 

@Nogueira1306 

Thank you for your help.

Is there any way to do without this??

;{No:  ThisItem.No; products: ThisItem.products});;

I have so many columns in my table.

 

And how can I set same “No” to new record?

This is where we have not been able to solve the problem the most.
Multiple people register to the app at the same time.

There is no other way... At least that i know...

If you use my formula, the new No is the same No

@Nogueira1306 

Hi, Thank you for your help😀

OK, I'll use { } to copy record.

 

I can copy record by pressing button on my gallery but how can I patch(copy record) in table sub?

Patch(record; Defaults(record);{No:  ThisItem.No; products: ThisItem.products});;

↓This part.
Patch(sub; Defaults(sub);{No:  ???; products:???});;

 

PaulD1
Community Champion
Community Champion

I believe you will need to do something like the following. Patch your new row to the table 'report' and return the new record (here assigned to variable wRecord via the With statement).

You then need to iterate through all of the lines in table sub related to the report selected in your Gallery (ForAll) and for each one, patch it to table sub using the your reference to the newly created 'report' row, wRecord.

You may need to experiment with the syntax a bit:

 

With({wRecord:

Patch(report, Defaults(report),{date: Now()})

},

ForAll(Filter(sub,No=Gallery.Selected.No),

Patch(sub,Defaults(sub),

{No: wRecord,

products: products})

)

 

I'm not very good with Dataverse, but I believe that is the approach.

With a more full-featured database like SQL you'd create a Stored Procedure to do all the work on the server so it would be faster and more robust, i.e. wrap the data operations in a transaction so if one part fails, it all fails. The problem with doing this from the client is that the step 'create new report record' succeeds and then something fails (say a loss of connectivity) during the creation of the sub records (e.g. tea succeeds but coffee fails) you are left with inconsistent/incomplete data.

san_ravi
Frequent Visitor

Thanks, PaulD1,  I struggled for nearly two weeks searching for this solution, and finally, I got it with a few changes, but it worked perfectly

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (2,361)