cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cscit
Frequent Visitor

Filter Gallery By Multiple Dropdowns (Month and Year)

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!

3 REPLIES 3
DiegoEscobar2
Helper II
Helper II

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. 

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.

 

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

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,076)