cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
westyglf
Regular Visitor

FIltering Choices Dropdown based on another column's date value

Hi,

 

I am trying to update a form / powerapp for one of our SharePoint online sites that uses a Lookup column to retrieve data from another list.

 

Currently the lookup retrieves a list of bookings, the list is filtered so users can only see the items they have created / booked. However the list gets bigger and bigger the more courses a user books onto. All I'd really like to be able to do is filter the list so that only events with a an "Event Date" column value within the last 30 days are displayed.

 

I've come up with the following filter command however the Powerapps GUI is constantly warning me about a syntax error on the second filter command. 

 

The list holding this data is "Site Feedback", the column stating the name of the event is eLookupEVent, and the column brought through containing the event's date displays as "Event Attended: Event Date" in the powerapps GUI.

 

 

Filter(Choices([@'Site Feedback'].eLookupEvent),Value in Filter([@'Site Feedback'], 'Event Attended:Event Date' >= DateAdd(Today(),-30,Days)))

 

 

In a normal company I would probab;y be given the time and resources to go and complete a proper PowerApps course that would help me build this knowledge from the ground up; sadly I work in the education sector which equates to no time, no resource and "why isn't it already fixed?"; so any advise on how I can resolve this or any resources that may prove useful would be much appreciated.

5 REPLIES 5
Ashwin7104
Responsive Resident
Responsive Resident

Hey @westyglf - Try the below code to filter items they have created and within 30days

 

Set(LoggedinUser,User().Email)
Filter('Site Feedback', 'Created By'.Email=LoggedinUser && 'Event Attended:Event Date' >=DateAdd(Today(),-30,Days))

 

Hi,

 

Thanks very much for the response. Unfortunately the syntax is not accepted and I get an error stating "Bahvior function in a non-behavior property. You can't use this property to change values elsewhere in the app".

 

Sorry, I should have probably mentioned that this code is going into the Item property of a Combo Box.

 

Thanks

v-qiaqi-msft
Community Support
Community Support

Hi @Ashwin7104,

Have you solved your problem?

Do you want to filter the choices from the LookUp column based on records in last 30 days?

Could you please share a bit more about the error message?

I think you should modify your formula as below:

ForAll(
    Filter(
        [@'Site Feedback'], 
        'Event Attended:Event Date' >= DateAdd(Today(),-30,Days)
    ).eLookupEvent,
    eLookupEvent
)

 

Best Regards,
Qi
Ashwin7104
Responsive Resident
Responsive Resident

@westyglf - Gotcha ! The below code should go in App on start/ Screen on visible while Filter code can remain in Items property.

Set(LoggedinUser,User().Email)

 

Hi,

Thanks for the suggestions. Haven't had a chance to try these out yet, but hopefully I will by the weekend. I'll let you know how I get on.

Thanks

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,002)