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

[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).

1. Create (patch) a new order in the "Order" table based on the data from the current PR

2. Get the newly created order's ID into a variable

3. Collect the selected PR items

4. 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

5. 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

[Drrickryp]

@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. 

[Drrickryp]

@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. 

[Kelligan]

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

[Kelligan]

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?

[Drrickryp]

@Kelligan

That is ridiculous. https://www.google.com/url?sa=t&source=web&rct=j&url=https://learn.microsoft.com/en-us/power-platfor... 

[Kelligan]

@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.

[Drrickryp]

@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. 

[Kelligan]

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!

[Drrickryp]

@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!!