cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jerrycrabb
Helper I
Helper I

Filter date selection (in dropdown) based on other column in lookup table.

Hello!  I'm attempting to use a dropdown input in a registration form.  In that dropdown attendees can select a start date, and in theory I can manage the dates available by using a "status" column in the underlying lookup table (Sharepoint). 

 

I'm really close to getting this to work. I can get it to return SOME of the columns based on what I put at the end of the filter statement, just not the one I want.

I think that  in the "Items" property of the dropdown,

 

[code] Filter('Lookup Dates',Status.Value="Registration Open").'Start Date' [/code]

 

should work, but it returns empty options.

 

If I use .Title  or . Dates  instead, that works, but it's not what I'm after. I think there's some formatting functions that I'm missing.

The below images are my lookup table, the formula I'm trying to use in the "Items" property, the result, and the result using .Dates (which is not what I want). 

 

Thanks in advance for any help!

 

-Jerry

 c65dde6b-3c76-4ce0-bcf4-43f3eed843ec.png

e3489c79-ad6a-4cc8-a484-838c64392a3c.png

blank dates.PNG

7a6e2774-5b94-4cf4-b1f5-1f9ce1b58934.png

b49cb8b1-57a1-473d-b4d9-b6310abcfb8a.png

1 ACCEPTED SOLUTION

Accepted Solutions
jerrycrabb
Helper I
Helper I

@WarrenBelz  I figured it out!  After seeing the solution I realized I didn't do a very good job explaining my issue. I'm sorry. I happened across another thread where someone did a much better job explaining the problem, and I was able to adapt that solution to fit my needs.  

 

https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-filter-choices-on-Combobox/td-p/10143...

 

Here is what worked for me:

 

Filter(
     Choices('Attendee Registration'.DatesFirstChoice),
     Value in Filter(
          'Lookup Dates',
          Status.Value = "Registration Open"
     ).'Start Date'
)

 

View solution in original post

7 REPLIES 7
jerrycrabb
Helper I
Helper I

I should clarify this is a combo box.  I did not realize this, and I'm not sure how much of a difference that makes. 

jerrycrabb
Helper I
Helper I

When originally added to the form the "Items" property is set to "Choices([@'Attendee Registration'].DatesFirstChoice)"  

The 'Lookup Dates' list/table is a lookup table for the .DatesFirstChoice column in the 'Attendee Registration' table.

'Attendee Registration' is the table I ultimately need the data written to. I wonder if I'm going about this in the wrong way...

WarrenBelz
Super User
Super User

Hi @jerrycrabb ,

Try this

Filter(
   AddColumns(
      'Lookup Dates',
      "StartDate",
      Text(
         'Start Date',
         "dd/mm/yyyy"
      )
   ),
   Status.Value="Registration Open"
).StartDate

 

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

Hi @WarrenBelz ,

 

Thank you for looking at this.

 

I wasn't able to get that solution to work. The AddColumns function gives an error that "Start Date" already exists

 

frmlaerr.PNGfrmla.PNG

@jerrycrabb ,

Just use another name

Filter(
   AddColumns(
      'Lookup Dates',
      "DateStart",
      Text(
         'Start Date',
         "dd/mm/yyyy"
      )
   ),
   Status.Value = "Registration Open"
).DateStart

 

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

@WarrenBelz ,

Ah, I see.  This creates a whole new column of values that does populate in the combobox and is filtered appropriately.  However, it does not pass data to the primary table 'Attendee Registration', which is the ultimate goal. 

Left unfiltered, any date selection in 'Lookup Dates'.StartDate passes that value to 'Attendee Registration'.DatesFirstChoice.   

jerrycrabb
Helper I
Helper I

@WarrenBelz  I figured it out!  After seeing the solution I realized I didn't do a very good job explaining my issue. I'm sorry. I happened across another thread where someone did a much better job explaining the problem, and I was able to adapt that solution to fit my needs.  

 

https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-filter-choices-on-Combobox/td-p/10143...

 

Here is what worked for me:

 

Filter(
     Choices('Attendee Registration'.DatesFirstChoice),
     Value in Filter(
          'Lookup Dates',
          Status.Value = "Registration Open"
     ).'Start Date'
)

 

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (2,129)