cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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 

Anonymous
Not applicable

@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

Anonymous
Not applicable

@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
Super User
Super User

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.

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
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

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