cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gines
Advocate II
Advocate II

Forms: filter dropdown based on 2 dates and choice

I have 2 lists: 

  • List A: Masterdata list containing all the assets someone can borrow (like asset category and the name of the asset)
  • List B: The request list where someone can request an asset for a certain period. This is also the list where I have a custom Power Apps form. In this list I have a lookup to the asset category and the name of the asset from List Masterdata.

What I want to achieve is when someone requests an asset in List B and they select an asset category + the period, the lookup column with the Asset Name should only show the assets which are available in that period and only of that category.

 

For example:

List A:

  • Item 1: Category "Laptop" - Name "HP 1"
  • Item 2: Category "Laptop" - Name "HP 2"
  • Item 3: Category "Screen" - Name "Monitor 1"
  • Item 4: Category "Screen" - Name "Monitor 2"

 

List B:

  • Person A: requested HP 1 from 01/11/2019 - 05/11/2019 (2 seperate columns from and till)
  • Person B: requested HP 2 from 03/11/2019 - 10/11/2019

If Person C wants to request a new laptop from 06/11/2019 - 08/11/2019, he only needs to select asset category Laptop and the period and the dropdown should automatically filter all the Laptops (from List A) that are available in the chosen period (only available in List B), in this case only HP 1.

 

Is this possible to filter on the date and show all the available assets from another list? If so, how?

2 REPLIES 2
v-yutliu-msft
Community Support
Community Support

Hi @Gines ,

Do you want to filter name in listA based on the selection of Category(listA) and time in listB?

I've made a similar test for your reference:

Here are my two lists: (starttime and endtime is date type)

1184.PNG

 

1185.PNG

 

1)insert a Textinput to fill in person name

2)insert drop down1 to choose Category

set the drop down1's Items:

Distinct(listA,Category)

3)insert two date pickers to choose start time and end time

4)insert drop down2 to display the filtered Name

set the drop down2's Items:

Filter(
  Filter(listA,Category=Dropdown1.Selected.Result),
  Not(Name in Filter(
                        Filter(listB,requestedHP in 
                               Filter(listA,Category=Dropdown1.Selected.Result).Name
                               ),   //filter items in listB request for the same Category
                        (DatePicker1>=starttime&&DatePicker1<=endtime)|| 
                        (DatePicker2>=starttime&&DatePicker2<=endtime)
                      ).requestedHP   //filter items with time problem
        )  //filter items without time problem
      )  //filter names belong to this Category based on time problem

1186.PNG

 

Then drop down2 will only display the items filtered based on other controls.

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 @v-yutliu-msft 

 

Thank you for the formula, I think it will work but I get an error when I want to use the first "Not" function.

When I use Not(NameOfTheColumn in Filter..., I get "Powerapps cannot convert this Text to a Record. "NameOfTheColumn" is a single line of text in a SharePoint list, and RequestedHP in your example is in my list a lookup to "Name". 

 

Any idea why I can't use this single line of text column?

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (52,218)