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

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 II
Helper II

@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 II
Helper II

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 II
Helper II

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 II
Helper II

@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 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 (1,417)