Patching selected records and fields to SharePoint from a collection

[David283]

I am trying to build an update button with a patch command to take particular fields from particular records in a collection and patch them out to a SharePoint list.

This is part of my ordering app, it presents the user with a gallery of items they can order.  The gallery is itemized from a collection.

OnVisible = ClearCollect(ColTempOrder, AddColumns(OAItemList,"OrderQuantity", 0))

This code is on the screen and itemizes the list.  Then the user will increase the quantity for the items they want ordered, then press the submit button.

Submit should then patch out to a first SharePoint list creating a general order record and bring back the Order ID; and then patch out a second time to a different list with each item ordered and the quantity requested, this time only for records with a quantity greater than 0.

Set(NewOrderID, Patch(OAOrdersList, Defaults(OAOrdersList), {OrderSubmittedBy: User().FullName}));
ForAll(ColTempOrder,                    
    If(ColTempOrder.OrderQuantity > 0,      //Test to make sure the user is ordering one or more of this item
        Patch(OAOrderItemisedList, Defaults(OAOrderItemisedList), 
            {ItemID: ColTempOrder.ItemID,
            OrderQuantity: ColTempOrder.OrderQuantity, 
            OrderedBy:User().FullName,      //A text verson of the user name placing the order, not a 365 user account link.
            OrderID:NewOrderID.OrderID}))); //The Order ID saved from the return of the patch command above.
Back()

 My first patch command (the one with Set() ) above works fine, but the second one is giving me a couple of errors.  The 'If()' doesn't like OrderQuantity > 0;  and the Patch command itself is giving me errors about invalid arguments.  I am still not good enough with the syntax to figure out what I am doing wrong.

[WarrenBelz]

Hi @David283 ,

You are comparing a table value (which does not have a single Numeric value)

Set(
   NewOrderID, 
   Patch(
      OAOrdersList, 
      Defaults(OAOrdersList), 
      {OrderSubmittedBy: User().FullName}
   )
);
ForAll(
   ColTempOrder,                    
   If(
      Sum(
         ColTempOrder,
         OrderQuantity
      )> 0,
      Patch(
         OAOrderItemisedList, 
         Defaults(OAOrderItemisedList), 
         {
            ItemID: ColTempOrder.ItemID,
            OrderQuantity: ColTempOrder.OrderQuantity, 
            OrderedBy:User().FullName,      
            OrderID:NewOrderID.OrderID
         }
      )
   )
);
Back()

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[David283]

That fixed the 'IF' part of my code.  But there is still an error in the patch command, now it says ' The type of this argument 'ItemID' does not match the expected type 'Number'. Found type 'Table. ' 

ItemID is a renamed standard SharePoint field 'ID'.  As far as I know its numeric, it just counts up by one with each new record.  Is it actually a record field?  If so 'OrderID' will have the same issues later in the patch command.

OK, I am very new to Power Apps, so forgive me for the basic questions.

As I showed above, the OrderQuantity field was created in the app using AddColumn =  AddColumns(OAItemList,"OrderQuantity", 0)

So from what your saying, it looks like the default for a field created this way is record.  Is there a way to change that to text or number?

And I take it the 'Sum' function somehow converts it to numeric temporarily?  Will I need to do something similar when, later in the command, I patch that value out to the SharePoint list?

[David283]

OK, managed to fix part of this myself, 

ForAll(
   ColTempOrder,                    
   If(
      Sum(
         ColTempOrder,
         OrderQuantity
      )> 0,
      Patch(
         OAOrderItemisedList, 
         Defaults(OAOrderItemisedList), 
         {
            ItemID: ItemID,
            OrderQuantity: OrderQuantity, 
            OrderedBy:User().FullName,      
            OrderID:NewOrderID.OrderID
         }
      )
   )
);
Back()

The patch's for both ItemID and OrderQuantity had references to the collection itself, which the patch command doesn't like for some reason, so taking that out and just doing "ItemID: ItemID," and "OrderQuantity: OrderQuantity" fixed the errors.

And the patch command works now, it writes the first patch to the Orders list and gets back the ID, then patches everything to the second list including the ID from the first list, brilliant!

But unfortunately it writes all records to the second list, including those with quantity 0...  So the logic of the "IF" statement isn't working.

Could someone assist?

[David283 · Accepted solution]

OK, managed to fix that part myself as well!

The "Sum" statement wasn't working for me, so I went back to my original idea of =  "If(ColTempOrder.OrderQuantity > 0,".

And I worked more with it, it turns out once again the issue was mentioning the collection I wanted the variable pulled from, "ColTempOrder".  Taking that out and just using " If( OrderQuantity > 0," worked.

I am still too new at this to really understand the syntax I am employing.  I just google around until I find an example posted somewhere and then substitute my variables and list names and try to make it work for me.

In this case I have to assume that because this code starts with "ForAll( ColTempOrder,"; That this collection is now kind of a home point and it assumes everything is coming from here unless you specify something outside, and referencing the collection from within the collection breaks it.

That seems to be the case.

Closing this thread, thank you everyone for your continued assistance.

[WarrenBelz]

Hi @David283 ,

I can only respond to the information you provide - I am also (I assume) in a different time zone to you (UTC + 10).

A bit of advice - when you solve something, take the time to understand it and it will save you a lot of grief when you have to re-produce something similar. It also does not make a lot of sense to me (from your description) how 

If(colTempOrder.OrderQuantity)>0

would now be valid as it is a Table and was actually the error in the question you posted (and I responded with the solution).

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[David283]

Hello Warren, thank you so much for your assistance last Friday in getting this working.

I know there are many ways to do the same thing in coding, and your idea of using 'Sum' was excellent and got me thinking about this in different ways.

In the end, the solution I went with was " If( OrderQuantity > 0,".  It made the most since to me and worked well in my code.

I am sorry if I didn't explain well what was going on in my application and didn't give you good information to work from.  I will try to give clearer information in the future.

And your 100% correct, Its very important to take time to understand how a solution to a problem works, so that I can learn how the underlying system works, and can avoid the same problem in the future.  I feel I am getting a little better at this each day, slowly improving.  This little ordering app project has been very beneficial to me as a learning project.  And I am very grateful to the community on this forum for their support.

Thank you.