Answered

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

(0) Share

Report

Posted on by 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

blank dates.PNG

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

jerrycrabb on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
jerrycrabb on at

Like (0)

Report
Copy link

Link copied!

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...

Was this reply helpful? Yes No
WarrenBelz 154,797 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
jerrycrabb on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
WarrenBelz 154,797 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
@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

Was this reply helpful? Yes No
jerrycrabb on at

Like (0)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
Verified answer

jerrycrabb on at

Like (0)

Report
Copy link

Link copied!
@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/101436

Here is what worked for me:

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

Was this reply helpful? Yes No