Patch Lookup column to collection

[JR-BejeweledOne]

I have a SharePoint list with lookup columns that I will need to patch from my app.

I am not using forms so will be using patch to first patch the collection and eventually patch the list. 

My lookup source list is BusinessUnits and the column is Title and my collection is colEffort.    This is what I have so far, but am getting an error.

I have tried the suggested solutions and the solution the OP indicated solved his issue at the link below and none of them seem to work.

https://powerusers.microsoft.com/t5/Building-Power-Apps/Collect-and-patch-Lookup-image-Date-and-Name...

[JR-BejeweledOne]

Are you saying I need to change the column in the collection?   I can honestly say I don't know how to do that.

[JR-BejeweledOne]

I am not sure I am understanding this correctly.   I can have a collection within a collection, like this:

ClearCollect(colEffort, {

{BusinessGroup:

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

ID: 1,

Value: ""}

, ProductCategory: "", Effort: "", WeekEnding: "", Manager: "", Developer: "", BGID: "", PCID: "", ShowSave: true}

[v-xida-msft]

Hi @JR-BejeweledOne ,

According to the screenshot that you mentioned, I think there is something wrong with the initialization of your colEffort collection. Within the initialization of the colEffort collection, you set the 'BusinessGroup' column as a Text column rather than Record. But within your Patch formula, you assigned a Record value to the 'BusinessGroup' column, which is not compatible with the pre-defined data type of the 'BusinessGroup' column.

I have made a test on my side, please consider take a try with the following workaround:

Firstly, you need to set the 'BusinessGroup' column as a Record type within your colEffort collection. So you need to initialize your colEffort collection as below:

Collect(
         colEffort,
         {
             BusinessGroup: {
                              Id: "",
                              Value: ""
                            },
             ProductCategory: "",
             Effort: "",
             WeekEnding: "",
             Manager: "",
             Developer: "",
             BGID: "",
             PCID: "",
             ShowSave: true
         }
);
Clear(colEffort)

Please try above formula to initialize your Effort collection. And then modify your Patch formula as below:

Patch(
      colEffort,
      ThisItem,
      {
         BusinessGroup: {    // it is not necessary to specify '@odata.type' column in this record value
                               Id: LookUp(BusinessUnits, Title = DropdownBusinessGroup.Selected.Value).ID,
                               Value: DropdownBusinessGroup.Selected.Value
                        },
         ProductCategory: DropdownProductCategory.Selected.Name,
         Developer: varUser,
         Manager: varManager,
         Effort: TextInputEffort.Text,
         WeekEnding: DPWeekEnding.SelectedDate,
         ShowSave: false
      }
);
....
....

Note: If you just want to store the Lookup record value into the 'BusinessGroup' column, it is not necessary to add the '@odata.type' column inside the Record part mentioned above.

Please consider try above solution, hopes it could help in your scenario.

Best regards,

[JR-BejeweledOne]

I will eventually be patching this collection to a SharePoint list.    Do I need to use the @odata reference in both patch statements or just the one that is patching the SharePoint list?

[JR-BejeweledOne]

I get a number of errors with this, you can see the red squiggly:

1.  An Error telling me that the BusinessGroup is looking for a text value and not a record.  This collection is the only data source with a column of BusinessGroup.   The SharePoint list column is 'Business Group' but I don't have a patch statement yet, so it's not referenced and the gallery is using colEffort as the data source.

2. Multiple errors for the ID

Here is my SharePoint list but I don't have the patch statement for this yet.

Here is the gallery:

Additionally if I add the '@odata' reference then I get a message for the '@odata' segment that says the name isn't recognized

[v-xida-msft]

Hi @JR-BejeweledOne ,

If you want to patch the collection data back to your SP List, and want the 'BusinessGroup' column value from your collection to be saved into the 'Business Group' LookUp field, I think it is not specify the '@odata' reference in your Collect formula.

Based on the formula that you mentioned, I found that there is something syntax error with it. Please modify your formula as below carefully:

Collect(
         colEffort,
         {
             BusinessGroup: {                 // Please do not miss the '{' curly braces here in your formula
                              Id: "",
                              Value: ""
                            },
             ProductCategory: "",
             Effort: "",
             WeekEnding: "",
             Manager: "",
             Developer: "",
             BGID: "",
             PCID: "",
             ShowSave: true
         }
);

Please try above formula in your app carefully, and do not miss the corresponding curly braces '{' or '}' in your formula.

If you want to save above Collect table back to your SP List, please try the following formula:

ForAll(
       colEffort,
       Patch(
             'Your SP List',
             Defaults('Your SP List'),
             {
               Title: "xxxx",
               'Business Group': colEffort[@BusinessGroup],  // reference column value from the colEffort collection
               'Week Ending': colEffort[@WeekEnding], // reference column value from the colEffort collection
               ....
             }
       )
)

Please consider take a try with above solution, check if the issue is solved.

Best regards,

[JR-BejeweledOne]

I found the issue with the curly braces, I was missing one and one was in the wrong place.    Once I corrected that there were no errors.

I have 2 dropdowns in this collection that have to be treated the same way, but when I add the second one, the entire collect statement is now showing an error.

 

[v-xida-msft]

Hi @JR-BejeweledOne ,

Based on the screenshot that you mentioned, I find that there have something syntax issue with your formula. Please check the following screenshot:

So you formula should be modified as below:

Collect(
         colEffort,
         {
             BusinessGroup: {
                              Id: "",
                              Value: ""
                            },
             ProductCategory: {
                                Id: "",
                                Value: ""       // please remove the extra comma here from your formula
                              },
             Effort: "",
             WeekEnding: "",
             Manager: "",
             Developer: "",
             BGID: "",
             PCID: "",
             ShowSave: true
         }
);
Clear(colEffort)

Please try above formula, hopes it could solve your problem.

If the solution I provided above could solve your original problem, please consider go ahead to click "Accept as Solution" to identify my reply as helpful.

Regards,

[JR-BejeweledOne]

That did fix the issue, but now my collection patch doesn't work.   I tried using your suggestion below but value isn't a valid option;

I wonder if it doesn't have something to do with the items property.

DropdownBusinessGroup:   Sort(BusinessUnits, ThisItem.BusinessGroup.Value, SortOrder.Ascending)

DropdownProductCategory:  Sort(Filter(ProductCategories, BusGroup = DropdownBusinessGroup.Selected.Title), ThisItem.ProductCategory.Value, SortOrder.Ascending)

[v-xida-msft]

Hi @JR-BejeweledOne ,

Based on the Patch formula that you mentioned, I think you have something wrong with it. Please consider modify it as below:

Patch(
      colEffort,
      ThisItem,
      {
         BusinessGroup: {
                               Id: LookUp(BusinessUnits, Title = DropdownBusinessGroup.Selected.Title).ID,    // modify formula here
                               Value: DropdownBusinessGroup.Selected.Title  // modify formula here
                        },
         ProductCategory: {
                            Id: LookUp(ProductCategories, Title = DropdownProductCategory.Selected.Title).ID,  // modify formula here
                            Value: DropdownProductCategory.Selected.Title   // modify formula here
                          },
         Developer: varUser,
         Manager: varManager,
         Effort: TextInputEffort.Text,
         WeekEnding: DPWeekEnding.SelectedDate,
         ShowSave: false
      }
);

I assume that your DropdownBusinessGroup and DropdownProductCategory are both Dropdown controls added inside your Gallery. And I assume that the Title column in your BusinessUnits List stores the Business Group Name, and the Title column in your ProductCategories List stores the Product Category name. 

Please try above solution, hopes it could solve your problem. If the solution I provided above solved your original issue, please consider go ahead to click "Accept as Solution" to identify my reply as helpful. If you still have issues in your scenario, please consider open a new thread in our community and describe your issue there, then we could help you there.

Regards,