Powerapps Having Difficulty with Sharepoint Lookup Field and Patching

[wonderbeef]

Dealing with Sharepoint Lookup Columns has been frustrating me with this problem for the last few days. Essentially, when I try and patch a Collection's values to a Sharepoint List, everything seems to go great until I get to one Lookup field. I have tried three scenarios and they confusingly sway between Expected a Table and got a Record or Expected a Record and got a Table. Here is the scenario

MySharepointList = Sharepoint List trying to be patched/inserted into

zRef_Plants = Source of Sharepoint lookup column in MySharepointList

data_ParsedJSONStream = Collection acting as datasource to patch to Sharepoint List

Problem Field = Sharepoint Column called Plant which is a lookup field for another table

The simplest code I could use to try and retrieve the Plant based on the default Combobox from Forms:

Patch('MySharepointList', {Title:"Test delete",
            Plant: LookUp(zRef_Plants, ID = field_somecombobox.Selected.Id)});

//Yields expected type 'Table' but got type 'Record' error

The large chunk of code... This looks complex because I am essentially making an Excel Uploader using JSON and PowerAutomate. It's important to note the below code works as intended UNLESS i put in the bit about the Plant lookup field. The typing prediction doesn't even recognize Plant as a potential field.

Patch(
    'MySharepointList',
    ForAll(
        data_ParsedJSONStream,
        Defaults('MySharepointList')
    ),
    ForAll(
        data_ParsedJSONStream,
        {
            Title: User().Email & "-" & "MRO " & Now() & ThisRecord.RequestType & ": " & ThisRecord.Description,
            'Request Type': LookUp(
                Choices([@'Requests - MRO Parts'].'Request Type'),
                Text(Value) = RequestType
            ),
            Description: Description,
            'Material #': MaterialNumber
            //The following lines all yield the same error if inserted of "Expecting a Record 
            //and got 'Table' value instead"
            //, Plant:field_Combobox.Selected
            //,Plant:LookUp(zRef_Plants, ID = field_PlantComboBox.Id)

        }
    )
);

So one statement says it needs a record and got a table, the other says it needs a table and got a record, yet it is the same code and same target... I tried clearing my cache and restarting the browser... Any help or workarounds would be much appreciated. Lookup columns feel very frustrating in PowerApps.

[WarrenBelz · Accepted solution]

Hi @wonderbeef ,

I will firstly briefly comment that unless you are using that field directly in SharePoint Edit in Grid View, it is generally not necessary and will cause you unwanted complication - I never use them, but back to your issue.

Firstly, I do not know the Items of field_Combobox, so I have assumed the output is .Value - change if incorrect.

Another issue (not related to your problem) is that ForAll() is not designed to be a Loop, although it can work this way with considerable performance penalty as it does an individual Patch for each record. ForAll() creates a Table, which can be patched in one action provided its content is correct. For new records, this is simply a Table with field names and field types matching the list.

Also, Lookup fields need the ID (shown as Id) of the field item being looked up in the other list.
With all of that in mind, try this

ForAll(
   data_ParsedJSONStream As aData,
   Patch(
      'MySharepointList',
      {
         Title: User().Email & "-" & "MRO " & Now() & aData.RequestType & ": " & aData.Description,
         'Request Type': aData.RequestType,
         Description: aData.Description,
         'Material #': aData.MaterialNumber,
         Plant:
         {
            Value: field_Combobox.Selected.Value,
            Id: 
            LookUp(
               zRef_Plants,
               YourLookedUpField = field_Combobox.Selected.Value
            ).ID
         )
      }
   )
);

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

[wonderbeef]

@WarrenBelz thank you for the quick reply.

1. As you indicated, we are using the Edit Gridview administratively. At this point, if an internal Metadata choice is easier, I'd do it.
2. The ForAll() within a Patch was selected because it was tested as faster somewhere on the forums and I have many rows within a Collection which I want to patch at once. In the screenshot below, the app takes an attachment and creates a grid of editable items stored in a collection and the value from the single combobox applied to all. When Submit is clicked, it writes that collection to Sharepoint. That is the point of tension

3. Unfortunately, the format you provided yielded the error "Invalid argument type (Table). Expecting a Record value instead".

Any other suggestions? I looked at the Sharepoint field and it is a Lookup field with multi-select enabled, though I disabled it in the combobox At the end of the day, we're trying to keep these records linked to a "Plant" table. Is there a way to see what datatype it expects for the Plant argument?  Thank you again for the swift reply

[wonderbeef · Accepted solution]

The problem was, in fact, that the Lookup field itself had Multiselect set to True. In this case, the Plant column expects a Table of records with a datatype for each. In that case, we use yet another ForAll statement to loop through the ComboBox's selected items and generate records. Here is the code that worked below:

Plant: ForAll(field_SomeComboBox.SelectedItems, {
                   '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                    Value:Value,
                    Id: Id
                })

A more generic solution to MultiSelect Sharepoint Lookup based Comboboxes for those in the future:

  Patch('YourSharepointList', {Title:"Test",
            LookupMultiSelectColumn:
                ForAll(YourComboBox.SelectedItems, {
       '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                    Value:Value,
                    Id: Id
                })
            }
        )

[WarrenBelz]

@wonderbeef ,

Not something I could have known - I hope my structure guided you here.

[wonderbeef]

@WarrenBelz you absolutely helped me get to the right train of thought and I appreciate you helping all of us out on the forum so quickly.