Patch all items in a gallery to a sharepoint list with lookup columns

[Vish22]

Hello Experts,

Am new to power Apps and building a canvas app similar to a shopping cart. Am stuck in a stage where am not able to add new records to a SharePoint list that has lookup fields.

My App has two lists. device_Collections and user_orders.

Device_collection has ID, device_name, device_type and device Descriptions field. And this list is loaded to a gallery called Available_Items.

When user selects the add icon in this gallery the record is added to a collection called tmp_1 and is fed as a source for another gallery called user_cart. Where user can modify the quantity or delete the quantity. Finally a Finish Button, that patches all contents in user_cart to the list user_orders.

Problem starts here. user_orders has the following structure, ID, Device_Name which is a mandatory lookup column from device_collections list, and device_type which is auto populated from device_collections list based on the Device_Name field and Order_quantity field. How ever i am able to only update the Order quantity field. Once patch executes Share point list shows Device_Name and device_type columns in user_orders list as Required Info.

Below is code,

ForAll(
    user_cart.AllItems,
    Patch(
        'user_orders',
        Defaults('user_orders'),
        {
            Device_Name: {
                Id: LookUp('Device_collection',device_name = ThisRecord.device_name).ID,
                Value: ThisRecord.device_name
            },
            'device_type': {
                Id: LookUp('Device_collection',device_name = ThisRecord.device_name).ID,
                Value: ThisRecord.device_name
            },
            'Order_quantity': ThisRecord.Order_qty,
            'HO_Order Status':LookUp(Choices('user_orders'.'Order Status'),Value="Odered")
        }
    )
);
Clear(tmp_1);
Notify("Data Updated Successfully",NotificationType.Success,3000);

I have also tried the following snippet

 '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", 

but didn't help much.

If i use the label property in lookup to search for ID i receive a delegate warning

'device_type': {
Id: LookUp('Device_collection',device_name = lbl_devicename.text).ID,
Value: ThisRecord.device_name
},

i tried following couple of articles mentioned online like this PATCH A SharePoint LookUp Column In Power Apps - Matthew Devaney but couldn't find a proper solution. Strangely if i do this with a combo box then the content is updated.

[WarrenBelz]

Hi @Vish22 ,

Firstly, you are patching directly from a Gallery, so I assume you have not updated anything from the Gallery to the underlying Items source (you do not have a Collection in there) ? So the first question is the source of device_name in the Gallery (is it a control value the user enters, or a field in the Items that has been updated) ?

[Vish22]

Hi @WarrenBelz ,

When a item is selected from the first gallery (source gallery with products list), its added to a collection called tmp_1, which is then used as the source for user_cart gallery. And then patched to SharePoint using user_cart.AllItems. Not sure if this is the right approach.

source of device_name is suppose to be the text from a label field. but i am not able to write this in that lookup field as its not being populated. And if i directly write is as Id: LookUp('Device_collection',device_name = lbl_devicename.text).ID, i get a delegation warning 😞

[WarrenBelz]

Hi @Vish22 ,

So to confirm my first question, tmp_1 is the Items of user_cart ? If so, are any values altered in the gallery by the user and not Patched to tmp_1 ?

So the next question (which I did not ask clearly before) is what is the Text of  lbl_devicename ? I am trying to establish exactly what value you are using/referring to with device_name from the gallery/collection. Is this also the relevant field name in tmp_1 ?

[Vish22]

Hi @WarrenBelz ,

Yes, tmp_1 is the Items of user_cart Gallery. Regardless the value altered or not from user_cart gallery patching to SharePoint list doesn't work. 

The Text of lbl_Devicename is a property in tmp_1 collection.

This is what the add to cart function from the first gallery. (This has data from SharePoint list device_Collections)

If(
IsBlank(LookUp(tmp_1, ID=ThisItem.ID)),
Collect(tmp_1, {ID:ThisItem.ID, Prd_qty:1, Item: ThisItem}),
UpdateIf(tmp_1,ID=ThisItem.ID, { Prd_qty: Prd_qty + 1 } )
)

And the Item Property of  user_cart gallery is

user_cart.Items=tmp_1

And 

lbl_devicename.Text = ThisItem.Item.device_name

[WarrenBelz · Accepted solution]

Hi @Vish22 ,

This is being complicated by the Lookup columns, which generally are not necessary with Power Apps - but that is another discussion. Try this  - note I may have the case (upper/lower) of some of the fields incorrect.

Patch(
   'user_orders',
   ForAll(
      tmp_1 As _Data,
      {
         Device_Name: 
         {
            Id: 
            LookUp(
               'Device_collection',
               Device_Name = _Data.Device_Name
            ).ID,
            Value: _Data.Device_Name
         },
         'Device_Type': 
         {
            Id: 
            LookUp(
               'Device_collection',
               Device_Type = _Data.Device_Type
            ).ID,
            Value: _Data.Device_Type
         },
         'Order_quantity': _Data.Order_qty,
         'HO_Order Status': "Ordered"
      }
   )
);
Clear(tmp_1);
Notify(
   "Data Updated Successfully",
   NotificationType.Success,
   3000
);

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Vish22]

😀 @WarrenBelz  thank you this solved the problem..   

Regarding your comment on Lookup,  Sorry to ask this, what is the recommended method to use when the SharePoint list has related columns ? eg: products in product table and product order table also Is this the same with dataverse table?

[WarrenBelz]

Hi @Vish22 ,

You can do the lookup in Power Apps - a lookup in SharePoint does not create any Delegable "relationship" between the tables other then the column being looked up and you can simply refer to this directly in Power Apps and write back to a Text column.