Can't get it right. Patching all selected records in Gallery to set values [Help!]

[ManishHiraServi]

So, I have tried everything, watched dozens of YT Videos, Docs, but as a newbie PowerApp Developer, I can't seem to get the code to select the records correctly based on what is returned in the gallery

Use-case: 

Finance has a dedicated app in which they can see all expenses filtered by type/person. They can then choose to approve all those claims en-mass if they want to. They gallery can show up to 1000 items.

What I got right so far

1. The drop downs Type and Emp allow the Gallery to filter on the selection. In this case, based on selection, there are 16 records in the gallery. Note 1806 below is the ID of the record as listed in the SharePoint List.

GalleryName: GalleryMassApprovalsView.AllItems

The Approve All Button shows that we have 16 records as per the selection criteria. These are the 16 that finance now wants to mass approve.

The Approve All's button , onSelect attribute does the following so far.

1. It creates a collections of all the records returned in the gallery based on filter criteria (ColMassApprovalsSelectedList)

2. It then creates another collection from there called ColItemIDstoApprove, using the ColMassApprovalsSelectedList, which returns the ID numbers of the records that we want to Patch/Update

//Get List of all Records in Selected Filter View
ClearCollect(
    ColMassApprovalSelectedList,
    GalleryMassApprovalsView.AllItems
);
//Extract the ID Column so we can use it isolate the records that need updating
ClearCollect(
    colItemIDsToApprove,
    ShowColumns(
        ColMassApprovalSelectedList,
        "ID"
    ),
    ColMassApprovalSelectedList.ClaimID
);

What I am trying to do is extract the Record ID of the claim and then to patch the selected items to have a set values as shown below.

I've tried the patch, forAll ideas found on the web, but nothing seems to work as I can't seem to match the ID coming from the collection to the records I want to update. I've now lost my mind after 1 day of trying to work this out so asking for help 🙂 

I've lost count of the various methods I tried, but this is the last one I have and I just don't know what to write for the record code 

Patch(
        'Expense Claims ',
        WHATDOIWRITEHERE,
    ),
    {
        'Finance Approval Status': {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
            Id: 1,
            Value: "Approved"
        }
    },
    {
        'Finance-Spot-Check-Made': {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
            Id: 2,
            Value: "Mass-Approved"
        }
    }
);

Thanks,

[R3dKap]

Hi @ManishHiraServi,

First of all, thank you very much for all the detailed information you provided. It's extremly helpfull for us to help you efficiently.

Unfortunately 😁, there is just one piece of information that could really help: what's in your gallery's Items property?

And also if you could provide us with the full structure of your Expense Claims datasource (with the data types); maybe a capture of the list's parameters page.

Thanks...

[ManishHiraServi]

Hi @R3dKap ,

Thanks 🙂 

Expense Claims is a SharePoint List with LOADS of columns. Would be impossible to share.

As mentioned earlier, the Gallery is filtering correctly based on the combination of the dropdown lists on the form.

This is the Items configuration for the Gallery if it helps. Gallery is called GalleryMassApprovalsView

If(
    Dropdown_ExpenseType_1.Selected.Result = "All" && Dropdown_EmployeeName_1.Selected.Result = "All",
    FirstN(
        SortByColumns(
            Filter(
                [@'Expense Claims '],
                ('Overall Status'.Value = "Pending Finance Review")
            ),
            "Date_x0020_of_x0020_Expense",
            Ascending
        ),
        1000
    ),
    Dropdown_ExpenseType_1.Selected.Result = "All" && Dropdown_EmployeeName_1.Selected.Result <> "All",
    FirstN(
        SortByColumns(
            Filter(
                [@'Expense Claims '],
                ('Overall Status'.Value = "Pending Finance Review") && ('Employee Name'.DisplayName = Dropdown_EmployeeName_1.Selected.Result)
            ),
            "Date_x0020_of_x0020_Expense",
            Ascending
        ),
        1000
    ),
    Dropdown_ExpenseType_1.Selected.Result <> "All" && Dropdown_EmployeeName_1.Selected.Result = "All",
    FirstN(
        SortByColumns(
            Filter(
                [@'Expense Claims '],
                ('Overall Status'.Value = "Pending Finance Review") && ('Expense Type' = Dropdown_ExpenseType_1.Selected.Result)
            ),
            "Date_x0020_of_x0020_Expense",
            Ascending
        ),
        1000
    ),
    FirstN(
        SortByColumns(
            Filter(
                [@'Expense Claims '],
                ('Overall Status'.Value = "Pending Finance Review") && ('Expense Type' = Dropdown_ExpenseType_1.Selected.Result) && ('Employee Name'.DisplayName = Dropdown_EmployeeName_1.Selected.Result)
            ),
            "Date_x0020_of_x0020_Expense",
            Ascending
        ),
        1000
    )
)

I know I have to configure the Patch command to reference the gallery's selected items (now in a collection) where ID is the important bit to match the records selected to update but I am obviously just too dense to figure out how. I even added an label in the Gallery called ClaimID whose text value is ThisItem.ID as one of my attempts but it did not work.

Thanks again.

[R3dKap]

Hi @ManishHiraServi,

Perfect!

Ok, first, here's a simplified version of your Items property which will do just the same as your previous code:

FirstN(
    SortByColumns(
        Filter(
            [@'Expense Claims '],
            'Overall Status'.Value = "Pending Finance Review" && ((Dropdown_ExpenseType_1.Selected.Result = "All" || 'Expense Type' = Dropdown_ExpenseType_1.Selected.Result) && (Dropdown_EmployeeName_1.Selected.Result = "All" || 'Employee Name'.DisplayName = Dropdown_EmployeeName_1.Selected.Result)
        )),
        "Date_x0020_of_x0020_Expense",
        Ascending
    ),
    1000
)

Now, I needed to look at your Items property to be aware of the exact structure of the datatype used inside the gallery: in this case, it's just the structure of your datasource Expense Claims (since you didn't alter it with AddColumns/DropColumns).

So, if you want to approve all items in the gallery, the following code should do it:

ForAll(
    GalleryMassApprovalsView.AllItems as GalleryItem,
    Patch(
        [@'Expense Claims'],
        GalleryItem.ID,
        {
            'Finance Approval Status': {
                Id: 1,
                Value: "Approved"
            },
            'Finance-Spot-Check-Made': {
                Id: 2,
                Value: "Mass-Approved"
            }
        }
    )
)

Note that you don't need the ugly '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference" part of Choices or Lookup columns anymore when creating or updating SharePoint columns of that type... 😉

Well I guess that should do it.

Tell us if this works for your...

[ManishHiraServi]

Thanks @R3dKap for putting up with me. I have so much to learn. That streamlined Items code shows me how far I still have to go. Definitely learning 🙂

Unfortunately, the forall/patch code doesn't seem to work.

This is what the OnSelect Button code now looks like

//Get List of all Records in Selected Filter View
ClearCollect(
    ColMassApprovalSelectedList,
    GalleryMassApprovalsView.AllItems
);
//Extract the ID Column so we can use it isolate the records that need updating
ClearCollect(
    colItemIDsToApprove,
    ShowColumns(
        ColMassApprovalSelectedList,
        "ID"
    ),
    ColMassApprovalSelectedList.ClaimID
);
ForAll(
    GalleryMassApprovalsView.AllItems As GalleryItem,
    Patch(
        [@'Expense Claims'],
        GalleryItem.ID,
        {
            'Finance Approval Status': {
                Id: 1,
                Value: "Approved"
            },
            'Finance-Spot-Check-Made': {
                Id: 2,
                Value: "Mass-Approved"
            }
        }
    )
);

However, the code checker (is that what it's called :D) is looking like this 

Says both Patch and Forall has invalid arguments 😞

[R3dKap · Accepted solution]

Ok. I think I made a mistake on the second parameter for the Patch() function. Try this:

ForAll(
    GalleryMassApprovalsView.AllItems As GalleryItem,
    Patch(
        [@'Expense Claims'],
        LookUp([@'Expense Claims'], ID = GalleryItem.ID),
        {
            'Finance Approval Status': {
                Id: 1,
                Value: "Approved"
            },
            'Finance-Spot-Check-Made': {
                Id: 2,
                Value: "Mass-Approved"
            }
        }
    )
);

Tell me how this goes...

[ManishHiraServi]

Dear @R3dKap ,

Thank you for sticking with me. It's working 🙂 You are a genius and taught me some new things.

Also I think I worked out what the issue was with your solutions (and it was not your fault), so it could be that your first provided solution also works but I never tested it again 😉

I pasted your second solution and was getting the same errors. After a while, I worked out that data source is called 'Expense Claims ' and not 'Expense Claims'  = there is a space after Claims in the data source name. Now I didn't create it, but some 'professional development company' - Well as you can see, they were not so professional that I had to start working on on the app and solution (as a newbie!!) 

Thank you so much again.

[R3dKap]

I'm glad everything works fine now... 😊

OMG: the space after the name of the datasource! 😅

See you around...