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

[jerrycrabb]

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

 

 

 

 

[jerrycrabb]

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]

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]

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

[jerrycrabb]

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

[WarrenBelz]

@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

[jerrycrabb]

@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 · Accepted solution]

@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'
)