Filter Gallery By Multiple Dropdowns (Month and Year)

[cscit]

Hi Folks,

Built some Apps earlier in the year that are logs. They filter by month in a drop down and it defaults to the current month. With a new year coming up, I am trying to implement a year drop down filter as well, but I can't seem to get it to work. 

I am learning so I know this code is probably not as efficient as it could be (also these are relatively small datasets so delegation doesn't matter).

In the screens OnVisible I create 2 collections, one for months and one for years.

ClearCollect(collMonths,
{ID:0,Name:""},
{ID:1,Name:"Jan"},
{ID:2,Name:"Feb"},
{ID:3,Name:"Mar"},
{ID:4,Name:"Apr"},
{ID:5,Name:"May"},
{ID:6,Name:"Jun"},
{ID:7,Name:"Jul"},
{ID:8,Name:"Aug"},
{ID:9,Name:"Sep"},
{ID:10,Name:"Oct"},
{ID:11,Name:"Nov"},
{ID:12,Name:"Dec"});
ClearCollect(collYears,
{ID:1,Name:"2022"},
{ID:2,Name:"2023"}); 

In the gallery, I have this code set:

If(
    chkShowResolved.Value = false,
    SortByColumns(
        Search(
            Filter(
                'Work Orders',
                And(
                    Or(
                        IsEmpty(cboLogPriority.SelectedItems),
                        IsBlank(cboLogPriority.SelectedItems),
                        Priority in cboLogPriority.SelectedItems
                    ),
                    Month('Log Date') = ddMonth.Selected.ID,
                    //Year('Log Date') = ddYear.Selected.ID,
                    'WO Status' <> "Resolved"
                )
            ),
            inpSearch.Text,
            "cscit_description"
        ),
        varSortColumn,
        varSortDirection
    ),
    SortByColumns(
        Search(
            Filter(
                'Work Orders',
                And(
                    Or(
                        IsEmpty(cboLogPriority.SelectedItems),
                        IsBlank(cboLogPriority.SelectedItems),
                        Priority in cboLogPriority.SelectedItems
                    ),
                    Month('Log Date') = ddMonth.Selected.ID
                )
            ),
            inpSearch.Text,
            "cscit_description"
        ),
        varSortColumn,
        varSortDirection
    )
)

If I uncomment out that year dropdown filter, the gallery is blank. Using labels to see return values I am getting the correct IDs and years back from the drop down but the gallery is still blank. Help please!

[DiegoEscobar2]

Hey @cscit. Try changing from:

Month('Log Date') = ddMonth.Selected.ID,
Year('Log Date') = ddYear.Selected.ID,

 to 

Month('Log Date') = ddMonth.Selected.Name,
Year('Log Date') = ddYear.Selected.Name,

Anyway, be careful cos you got a delegation warning, and it may be inefficient on large datasets. 

[cscit]

I tried that! The Month filter is working fine with ID. But when I try putting in the year, even without the Month line, the gallery returns blank.

[WarrenBelz · Accepted solution]

Hi @cscit ,

Your month logic is correct (the ID is the month number), but you have so many non-delegable elements in there (almost the entire filter), you might as well try the newest records (note also the xxxx needs replacing with the output value selected in the combo box) to see if this gets results

With(
   {
      wList:
      Sort(
         'Work Orders',
         ID,
         Descending
      )
   },
   SortByColumns(
      Filter(
         AddColumns(
            wList,
            "MonthNo",
            Month('Log Date'),
            "YearNo",
            Year('Log Date')
         ),
         (
            Len(cboLogPriority.Selected.xxxx) = 0 ||
            Priority in cboLogPriority.SelectedItems
         ) &&
         MonthNo = ddMonth.Selected.ID &&
         YearNo = ddYear.Selected.ID &&
         (
            chkShowResolved.Value ||
            'WO Status' <> "Resolved"
         ) &&
         (
            Len(inpSearch.Text) = 0 ||
            inpSearch.Text in cscit_description
         )
      ),
      varSortColumn,
      varSortDirection
   )
)

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.

Visit my blog Practical Power Apps