cancel
Showing results for 
Search instead for 
Did you mean: 
darogael

Bulk updates for a filtered gallery

Introduction

This blog with the video walks you through how you can update filtered items in bulk. In this blog items bulk updates have been done in an Excel spreadsheet present in OneDrive, however, you can do the same for SharePoint lists or SQL tables.

 

Scenario

Consider an IT asset inventory as shown below. You'd like to filter this inventory by either manufacturer (MFR) or device or name or accessory and update the quantity in bulk. For example, we'd like to update all the Inspiron 17 quantity to 10.

 

Capture.JPG

 

Below is a screenshot of the screen. The top row which is a group of 7 labels to give it a table look and feel. On the right are 4 drop down controls where you can choose any of the options you'd like to filter the gallery with. In This example only one choice selection can be made at a time. Selecting two simultaneously will give an empty response.

 

Capture3.JPG

 

The filtering is done using the FILTER formula in the ITEMS. In addition, the filtering only occurs if something other than the "Select one" option is selected in the dropdown .

 

picture2.png

 

The submit button's OnSelect function with a combination of the 'Update in bulk' checkbox has the formula which will both filter and update the quantity column of the filtered items. Take a look at the formula below.

 

picture3.png

 

Closer look at the formula

 

Filter function in ITEMS

 

The formula is a combination of FILTER with the IF function where the IF runs inside the FILTER. In addition we are using the 'not equal to' or '<>' comparison in combination with the 'or' or ||

 

Here is an overview of the syntax we are following

FILTER(DataSource, If(SelectedValue1<>"Select one"||SelectedValue2<>"Select one"||SelectedValue3<>"Select one"||SelectedValue4<>"Select one")

 

Here is what the formula looks with the columns names

 

Filter(Inventory,If(MFRDropdown1.Selected.Value<>"Select one",MFR=MFRDropdown1.Selected.Value)||

If(DeviceDropdown1.Selected.Value<>"Select one",Device=DeviceDropdown1.Selected.Value)||

If(NameDropdown1.Selected.Value<>"Select one",Name=NameDropdown1.Selected.Value)||

If(AccDropdown1.Selected.Value<>"Select one",Accessory=AccDropdown1.Selected.Value))

 

 

Submit button's on select

 

The formula is combination of UPDATEIF with IF where the UPDATEIF runs inside the IF. We are also using the '||' or double colon to create the OR function.

 

Here is an overview of the syntax we are following

 

If( Condition, UpdateIf( DataSource, Condition1||Condition2||Condition3||Condition4, ChangeRecords ) )

 

Here is what the formula looks with the columns names

 

If(Checkbox1.Value=true,UpdateIf(Inventory,MFR=MFRDropdown1.Selected.Value||Device=DeviceDropdown1.Selected.Value||Name=NameDropdown1.Selected.Value||Accessory=AccDropdown1.Selected.Value,{Quantity:TextInput2.Text}))

 

Video

This video walks you through how the PowerApps works and walks you through the formula

 

 

 

 Conclusion

 This blog is attached with a zipped file that contains both the Excel spreadsheet and the Power's MSAPP file which you can use to re-create this exact scenario and with a little bit of reverse engineering learn on how to do bulk updates with filtered items in PowerApps.

 

Reference links

 

IF formula

UPDATEIF 

Dynamic table sorting

Dynamic filtering of a Data table

Comments
Anonymous

Hi kindly look this link need help in update if function using powerapps

 

https://powerusers.microsoft.com/t5/PowerApps-Ideas/Bulk-update-in-Sql-Server-onPremises-using-Updat...

 

Thanks

Anonymous

Thank you @darogael!

Great guide which really helped me a lot.

 

There's one thing thou I am missing in my workflow:

1. Filtering the gallery works as expected

2. With FirstN I narrow the gallery to a certain number of items

 

Now when I bulk update my SP list, it works like a charm as long as I only work with the filters, my problem is using the FirstN also within the UpdateIf function in order to only update the number of items given to FirstN.

 

Any ideas how I could solve this?

Thanks so much in advance!

Cheers

BJ

Anonymous

Hello,

 

I downloaded the Zip file and tried to import in my system. It's giving me an error. Can you please send me the zip file for this demo?

 

 

PowerappCapture.JPGI have open bulk update power app and also excel file save in my one drive but problem is when I have filter select there is no any item filtered not found deta please find the below screen shot for your ref...