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

Trying to filter a ComboBox by date range. No luck.

I have a Training Course app I'm working on that is pulling dates from a SharePoint list and they show in a ComboBox. I need the list in the box to be filtered to a date range of today +2 days and then 30 days out. So they wont be able to select classes over 30 days ahead or 2 days before. I have tried several methods I've found online, but never found anything for dates in a ComboBox and the ones for gallery filtering don't want to work right. 

 

This is the default for Items.

 

Choices([@'Training Requests'].'Front Desk Systems Training Dates')

 

I need to filter these results down for the date range. 

 

I tried modifying this code for a gallery work, but no luck. Same for all the other examples I found searching.

 

With(
    {
        StartDate: Date(
            Year(Today()),
            Month(Today()),
            Day(Today())+2
        ),
        EndDate: Date(
            Year(Today()),
            Month(Today())+1,
            Day(Today())
        )-1
    },
    Filter(
        'Training Requests'.'Front Desk Systems Training Dates',
        CalendarDate >= StartDate,
        CalendarDate <= EndDate
    )
)

 

  So any help is appreciated for this.

1 ACCEPTED SOLUTION

Accepted Solutions
YukonJoe
Frequent Visitor

Happy Monday, because I got it working!! For some reason it was defaulting to another value in the list even though the Items had the filter set to the correct column. So after changing that back to the correct column it displayed!

 

Thanks for all your help in sorting through the process.

 

YukonJoe_1-1656349148221.png

 

View solution in original post

9 REPLIES 9
BCLS776
Super User
Super User

You're almost there. Try:

With(
    {
        StartDate: DateAdd(Today(), 2, Days),
        EndDate: DateAdd(Today(), 1, Months)
    },
    Filter(
        'Training Requests'.'Front Desk Systems Training Dates',
        CalendarDate >= StartDate,
        CalendarDate <= EndDate
    )
)

Hope that helps,

Bryan

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.
YukonJoe
Frequent Visitor

Thanks for the Reply Bryan. That unfortunately did not work. It's giving me an issue with my column name. In my example I forgot to change the column name so it should be whats in the screen shot. But it's giving me an error "Expected operator. We expect an operator such as +,*, or & at this point in the formula."

 

issue1PNG.PNG

BCLS776
Super User
Super User

One syntax error I see is that a comma is needed immediately after StartDate in the Filter() statement.

 

Let the Intellisense feature auto-complete your column names for you -- it saves a bunch of headaches.

 

Bryan

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.
YukonJoe
Frequent Visitor

I think I might have been on the wrong track for this. Because my field is a choice column not date/time. If I try this filter it works as intended showing only the explicit dates. So now I need to figure out how to convert those choices to dates and then maybe I can filter them? Or maybe a Collection?

 

Filter.PNG

 

Thanks

BCLS776
Super User
Super User

A choice column isn't the way to handle this one -- create a new date-only column and populate it with a proper date value.

If the choice column was an attempt to have multiple course dates for a single course, scratch that idea too 🙂 Instead, create multiple rows in your table to reflect multiple course offerings.

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.
YukonJoe
Frequent Visitor

Okay so I have another sheet TrainingDates that was feeding these dates into the Training Classes Sheet that I can use. Still trying to figure out the syntax then for separate control for this, I might end up doing three lists if it's easier one for each training class and have it only have dates in it. 

 

Thanks for your help so far.

YukonJoe
Frequent Visitor

Okay this is just being weird to upset me now I think.  So I made 3 lists to allow me to control and filter the dates better like instructed. When I go to apply a Filter or Sort the items are there like they should be but are not visible. I made a ListBox control and a textbox linked to the selected value to see what was going on. The values are there, they just aren't visible and I don't know why, you can see them change in my linked textbox. I tried it in a dropdown also and got the same results.  This is in Chrome browser btw.


blank1.PNGblank2.PNGblank3.PNG

BCLS776
Super User
Super User

Can you share the Items property of the ListBox controls, and confirm the kinds of columns you're referencing in those lists?

 

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.
YukonJoe
Frequent Visitor

Happy Monday, because I got it working!! For some reason it was defaulting to another value in the list even though the Items had the filter set to the correct column. So after changing that back to the correct column it displayed!

 

Thanks for all your help in sorting through the process.

 

YukonJoe_1-1656349148221.png

 

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (4,830)