Sort and Filre a Gallery by Dropdowns and Checkboxes

[Rene_Newbee]

Hi

I need help.

I have a gallery built on a Collect (colBossInfo). In my gallery I would like to be able to sort and filter on 2 Dropdowns and 3 Checkboxes.
To start with I would like to remove everyone from my gallery with Status 'true' in Terminated, this works, but I simply can't see where and how to implement the 3 checkboxes. The 2 Dropdowns and the 3 Checkboxes all work independently of each other, so you can only sort/filter on one at a time.

The 2 Dropdowns:
dpd_SelectJobPosition
dpd_SelectAbsence

The 3 Checkboxes:
chb_SortByLeader
chb_WithoutAbsence30Days
chb_FollowUpDateReached

When selecting Dropdown 'dpd_SelectJobPosition', the gallery must be sorted alphabetically (Ascending) by 'EmployeeName', the same applies to Checkbox 'chb_WithoutAbsence30Days'.

chb_FollowUpDateReached must sort on:
chb_FollowUpDateReached.Value = true
Sort(colBossInfo, FollowUpDate, Descending)

chb_SortByLeader must sort on:
chb_SortByLeader.Value <> false,
Sort(colBoosInfo, BossName, Ascending)

This is what I have now.

SortByColumns(
Filter(
colBossInfo,
Terminated <> "true",

If(

IsBlank(dpd_SelectJobPosition.Selected.Result) || IsEmpty(dpd_SelectJobPosition.Selected.Result),
true,
JobTitle = dpd_SelectJobPosition.Selected.Result
),

If(

IsBlank(dpd_SelectAbsence.Selected.Result) || IsEmpty(dpd_SelectAbsence.Selected.Result),
true,
Absence = dpd_SelectAbsence.Selected.Result
)

), "EmployeeName", Ascending
)

Thanks

[WarrenBelz]

HI @Rene_Newbee ,

I am guessing Terminated is a yes/no field - if so, put in the below (if not use your code there)

Sort(
   Filter(
      colBossInfo,
      !Terminated &&
      (
         Len(dpd_SelectJobPosition.Selected.Result) = 0 ||
         JobTitle = dpd_SelectJobPosition.Selected.Result
      ) &&
      (
         Len(dpd_SelectAbsence.Selected.Result) = 0 || 
         Absence = dpd_SelectAbsence.Selected.Result
      )   
   ),
   If(
      chb_SortByLeader.Value || FollowUpDateReached,
      BossName,      
      EmployeeName
   )
)

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

[Rene_Newbee]

Hi WarrenBelz

Thanks for your reply.

Yes, Terminated is a true/false field.

Do I need to put the code somewhere in my code, if not, then your example doesn't sort the gallery alphabetic, when nothing is selected.    

I need the gallery to be sorted alphabetically when none of the filters are selected.
When dpd_SelectJobPosition is selected, it should sort everything in the gallery, alphabetically by JobTitle.
When I choose dpd_SelectAbsence, it must sort by Absence (those with the highest absence at the top).
When I choose chb_SortByLeader, it should sort everything in the gallery by BossName.
When I select chb_WithoutAbsence30Days, it should filter out everyone who has had absent in the past 30 days and sort the rest alphabetically by EmployeeName.
When I choose chb_FollowUpDateReached, it should filter out everyone who does not have today's date in FollowUpdateReached.

dpd_SelectJobPosition = JobTitle (colBossInfo)
dpd_SelectAbsence = Absence (colBossInfo)
chb_SortByLeader = BossName (colBossInfo)
chb_WithoutAbsence30Days = Absence (colBossInfo)
chb_FollowUpDateReached = FollowUpDate(colBossInfo)

I hope it all makes sense, I have struggled with this part of my app for 2 days now and it makes me crazy, that I can't get it right.  

[WarrenBelz]

Hi @Rene_Newbee ,

You will also need rules on what  to sort by if multiple items are selected and consequently this is getting highly complicated (if at all logically possible). Also what field needs to be sorted when no item is selected ?

[Rene_Newbee]

Hi @WarrenBelz 

As I wrote at the beginning, the 2 Dropdowns and the 3 Checkboxes all work independently of each other, so you can only sort/filter on one at a time, so multiple items are not selected at the same time.

[WarrenBelz · Accepted solution]

@Rene_Newbee ,

So how do you stop someone selecting one of the other 4 controls ? The only logical way I can think of is to set a Variable on the OnCheck of the check boxes and OnSelect of the Combo Boxes to the field you want to sort by - examples on dpd_SelectJobPosition 

UpdateContext(
   {
      varSortField: "JobTitle",
      varSortOrder: SortOrder.Ascending
   }
)

dpd_SelectAbsence

UpdateContext(
   {
      varSortField: "Absence",
      varSortOrder: SortOrder.Descending
   }
)

then your Filter

SortByColumns(
   Filter(
      colBossInfo,
      !Terminated &&
      (
         Len(dpd_SelectJobPosition.Selected.Result) = 0 ||
         JobTitle = dpd_SelectJobPosition.Selected.Result
      ) &&
      (
         Len(dpd_SelectAbsence.Selected.Result) = 0 || 
         Absence = dpd_SelectAbsence.Selected.Result
      )   
   ),
   varSortField,
   varSortOrder
)

Also at screen OnVisible, set your Default Sort

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

[Rene_Newbee]

I have simply a check in the DisplayMode of each control.

If one of the controls are used/changed, then the rest change the DisplayMode to Disabled.

[WarrenBelz]

@Rene_Newbee ,

Did you look at the suggestion I posted ?