PowerApp CDS – Multi Select Items in Gallery using Checkbox – Bulk Activate and Deactivate records – Use of Patch vs ForAll

In my previous blog, I explained how to filter active and inactive records based on value selected in drop down. In this blog we will see how we can select multiple items in Gallery and activate or deactivate bulk records. We will cover below as part of this blog:

• Add Checkbox control to Gallery list
• Create a collection and add selected item in collection when checkbox is checked
• Remove item from collection when checkbox is unchecked
• Add a button to activate or deactivate selected records
• Difference between Patch and ForAll functions

I have a simple CDS app which filters active or inactive companies based on value selected in drop down and display them in Gallery list. Let’s add checkbox control in Gallery.

Add Checkbox to Gallery:

• To add checkbox inside gallery, select first row in Gallery and click on Check Box option in Controls menu under Insert tab

• Align check box to left hand side so that it looks like below

Now we will set OnCheck and OnUncheck properties of Check box control added above to add or remove selected item from Collection.

Add or Remove Selected Item from Collection:

• When user checks any item, item should be added to Collection “CheckedItems”. We need to take care of few things here:
  • Add item if not already exists in Collection – use of IsBlank function
  • Instead of adding all columns, we will add primary key of item (cr2a5_companyid) and required columns only to activate or deactivate – use of Collect function
  • To Activate or Deactivate there are two required columns: statecode and statuscode. If you have worked with Dynamics 365, you know that these two columns are Global Option Sets and below are their values in case of Active and Inactive
    • Active – statecode: 0, statuscode:1
    • Inactive – statecode:1, statucode:2
  • Based on value selected in dropdown we will decide what values to be stored in collection for statecode and statuscode. If dropdown is selected for “Active Companies” we will store values for Inactive (1,2) else Active (0,1) – use of If function
• Select OnCheck property of checkbox and use below formula to add item and address above mentioned points:

If(IsBlank(LookUp(CheckedItems, cr2a5_companyid=ThisItem.cr2a5_companyid)),Collect(CheckedItems, If(Dropdown2.Selected.Value=”Active Companies”,{cr2a5_companyid:ThisItem.cr2a5_companyid,statecode:1,statuscode:2},{cr2a5_companyid:ThisItem.cr2a5_companyid,statecode:0,statuscode:1})))

Note – Here we used curly braces to include 3 columns in same record. If curly braces are not used, then each column will be treated as separate record/row. Also, we explicitly passed same column names in collection using colon symbol to match source column names. If source and target have same column names, we can use Patch function to update source.

• When user unchecks any item, item should be removed from Collection. Select OnUncheck property of check box and use below formula to remove item if exists in Collection

If(!IsBlank(LookUp(CheckedItems, cr2a5_companyid=ThisItem.cr2a5_companyid)),Remove(CheckedItems,LookUp(CheckedItems, cr2a5_companyid=ThisItem.cr2a5_companyid)))

Now we have collection “CheckedItems” with all selected items in Gallery. Next Step to add one button which will activate or deactivate items in “CheckedItems” collection.

Add button to Activate or Deactivate:

• Add button onto your screen from Insert tab
• Set Visible property of button so that its visible only if “CheckedItems” collection has data using below formula

If(IsEmpty(CheckedItems),false,true)

• Set Text property of button to “Activate” or “Deactivate” based on value selected in drop down using below formula

If(Dropdown2.Selected.Value = “Active Companies”, “Deactivate”, “Activate”)

• Set OnSelect property to below formula to update source “Companies” data from collection data “CheckedItems” using Patch function. Here we don’t need to loop through each records in collection and update source data because we have same column names in both source and collection and we can directly using Patch statement which will automatically update source for columns in collection based on primary key.

Patch(Companies,CheckedItems)

Now run your app, select items in Gallery control and click on button to Activate and Deactivate multiple records. Make sure you clear your collection when drop down value is changed.

Patch vs ForAll:

In case source column names are different from columns in collection, we cannot use Patch statement directly instead we have to use ForAll with Patch where we must loop through each record and compare based on primary key. Refer blog for more details.

Below would be formula if we have to use ForAll with Patch in above case to activate records:

ForAll(CheckedItems,Patch(Companies,LookUp(Companies, cr2a5_companyid=CheckedItems[@cr2a5_companyid]),{statecode:0,statuscode:1}))