Unanswered

Patching Parent and Selected Child Records to Another set of Parent-Child Tables

(0) Share

Report

Posted on by Kelligan

Hi Folks,

I have a problem in model driven apps I am trying to solve. I am still pretty new to Power Apps so I am having some trouble. Here is the use case.

I have a parent table called "Purchase Request" and a child table called "Purchase Request Item" where a user can put in a PR (purchase request) that can have many items in it. Since the items in a single PR may come from multiple vendors or may be funded from different funding streams, the purchaser is often required to break the PR into multiple orders. I have a parent table called "Order" and a child table called "Order Item".

I need the purchase to be able to open a PR, select one or more PR items from the sub-grid, then click a custom button, presumably on the sub-grid command bar, and have that OnSelect action do the following (if I am thinking through this correctly).

Create (patch) a new order in the "Order" table based on the data from the current PR
Get the newly created order's ID into a variable
Collect the selected PR items
Patch all the PR items in the collection to the "Order Item" table based on the data in the selected PR items and include the new patent order's ID in related column so that they show up as related
De-select the selected records in the PR item sub-grid.

Then the user should be able to select other PR items and create another new order repeating the process.

Additionally, I will need a strategy to select PR items and add them to an existing order in the event that the purchaser makes a mistake and forgets one, but first things first.

I have a custom button in the sub-grid command bar with the following OnSelect function.

Patch(
 Orders, 
 Defaults(Orders), 
 {
 GPRN:Self.Selected.Item.GPRN.GPRN, 
 ROM:"", 
 'Date Requested':Self.Selected.Item.GPRN.'Date Submitted', 
 'Date Needed':Self.Selected.Item.GPRN.'Date Required', 
 'Priority ':Self.Selected.Item.GPRN.Priority, 
 'Contract and Task Order':"", 
 PoP:"", 
 Project:"", 
 SLIN:""
 }
)

This successfully creates the new order in the "Order" table, but I do not know how to capture the new order's id (OIDN). Can anyone help with this and the subsequent patching of the selected PR items to the "Order Item" table?

Please let me know if my description is unclear or further detail is needed.

Thanks and Kind Regards,

Patrick

Categories:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (0)

Drrickryp Super User 2024 Season 1 on at

Like (0)

Report
@Kelligan

Patch( Orders, Defaults(Orders), { GPRN:Self.Selected.Item.GPRN.GPRN, ROM:"", 'Date Requested':Self.Selected.Item.GPRN.'Date Submitted', 'Date Needed':Self.Selected.Item.GPRN.'Date Required', 'Priority ':Self.Selected.Item.GPRN.Priority, 'Contract and Task Order':"", PoP:"", Project:"", SLIN:"" } ); Set(gvarID,First(Sort(Orders, ID, Descending)).ID)

You can then use gvarID as the foreign key in the child table.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report
@Kelligan

Patch( Orders, Defaults(Orders), { GPRN:Self.Selected.Item.GPRN.GPRN, ROM:"", 'Date Requested':Self.Selected.Item.GPRN.'Date Submitted', 'Date Needed':Self.Selected.Item.GPRN.'Date Required', 'Priority ':Self.Selected.Item.GPRN.Priority, 'Contract and Task Order':"", PoP:"", Project:"", SLIN:"" } ); Set(gvarID,First(Sort(Orders, ID, Descending)).ID)

You can then use gvarID as the foreign key in the child table.

Was this reply helpful? Yes No
Kelligan 49 on at

Like (0)

Report

Hi @Drrickryp

Thanks for the response! I think I understand what is going on in your solution. Is that iron clad? If there are many people working on the system at the same time, will there be a possibility of a collision where the incorrect item in the Order table could be filtered to? I do not know how this works under the hood. Also, assuming that this is a workable solution, how to I get the selected items pulled in to be patched. I am a noob with Power Apps and Power FX.

Thanks again and Regards,
Patrick

Was this reply helpful? Yes No
Kelligan 49 on at

Like (0)

Report
I just tried adding the line

Set(gvarID,First(Sort(Orders, ID, Descending)).ID)

and got the following error...
Name isn't valid. 'gvarID' isn't recognized. View Problem (Alt+F8). clicking the linke renders the following 'Expected operator. We expect an operator such as +, *, or & at this point in the formula.'

I tried to use the "Set" function with more simple terms and got this hover error.
'Set' is an unknown or unsupported function. Here is the line I tried to use.

Set(varUser, "Joe");

Any thoughts?

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

@Kelligan

That is ridiculous. https://www.google.com/url?sa=t&source=web&rct=j&url=https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-set&ved=2ahUKEwiq6MTlg9j8AhWYlmoFHZr2DM8QFnoECBgQAQ&usg=AOvVaw22AXwZGkcqYLbOhl42AFoh

Was this reply helpful? Yes No
Kelligan 49 on at

Like (0)

Report

@Drrickryp

That was an odd reaction. Are you offended or something? Not sure what to tell ya. Here is what I see.

And this is what I see when I click the "View Problem" link.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

@Kelligan

Not at all. Set() is a function that I have used daily. I have no idea why it is not recognized in your formula.

Was this reply helpful? Yes No
Kelligan 49 on at

Like (0)

Report

So, here is another screen shot with the auto-complete not showing the Set function either. To clarify the context, this is a custom command bar button, for a sub-grid, in a model driven app. Would that make any difference? I would have thought that functions like Set() would be ubiquitous throughout the Power Apps platform.

...however, in a canvas app in the same solution, I am able to use the Set() function in with no problem.

Thoughts anyone?
Thanks!

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

@Kelligan

Well that explains my confusion. I haven't tried to set a global variable in a model driven app yet. You had me going!!

Was this reply helpful? Yes No
Kelligan 49 on at

Like (0)

Report

So, I found the answer to the Set() function issue. The fact is, it is listed amongst the Power Fx functions that are currently not supported with commanding in model-driven apps. Here is a link to the documentation.
Functions not supported

Any thoughts for a workaround?

Thanks and Regards,
PK

Was this reply helpful? Yes No