cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SteveGard
Regular Visitor

Filtering Columns with Dates as Titles

Ugh, I've tried a million different things, and I'm sure the answer is right in front of my face. I am building an app to help teachers find available substitutes based on an Excel Spreadsheet. I currently have a table titled "SubAvailTime" where the first column is titled "SubName" and then each column after is titled with a date ("8/14/2020","8/15/2020", etc.). The names of the subs are under the "SubName" column and then under each date I have tried different things ("True/False"; their name again, if they are available; etc.) 

 

End Goal: Teachers choose a date from a dropdown (we'll call it DateNeeded), PowerApps finds the column titled with that date and returns the names of the available subs in a gallery with checkboxes, so the teacher can select which subs to contact.

 

Any help?

2 ACCEPTED SOLUTIONS

Accepted Solutions

I attached a screen to show the method - slightly different data source. You just need a date and a name - I used initials for speed. The first dropdown uses Distinct to get the list of dates. Then you apply that as a filter in the second dropdown and get the names available on that date. You could add a third column with the email to facilitate contacting thePwrApp_Substitue_01.jpg sub. 

View solution in original post

v-yutliu-msft
Community Support
Community Support

Hi @SteveGard ,

Do you want to get  names of the available subs based on selected dates?

I agree with @BrianS .

Please do not use dates as column names.

Distinct function's syntax is:

Distinct(tablename, fieldname)

Then you will get no repeated data in that field.

So could you change your table format?

1)If yes, please change to this:

fieldname:       date                 subname

                       8/14/2020         Tony,Peter

                       8/15/2020          Steve,Ben,...

drop down's Items for date:

Distinct(SubAvailTime,date)

gallery's Items to display subname:

Distinct(
          Filter(
                 Split(
                         Concat(
                               Filter(SubAvailTime,date=dropdown1.Selected.Result),
                               subname&","),
                          ","),
                  !IsBlank(Result)
                  ),
           Result)

 

2)If no, you still do not want to change your table format, then the formula will be a little complex.

drop down's Items for date:

["8/14/2020","8/15/2020","8/16/2020","8/17/2020","8/18/2020","8/19/2020","8/20/2020"]

 gallery's Items to display subname:

Filter(SubAvailTime,
If(dropdown1.Selected.Value="8/14/2020",!IsBlank('8/14/2020'),
dropdown1.Selected.Value="8/15/2020",!IsBlank('8/15/2020'),
dropdown1.Selected.Value="8/16/2020",!IsBlank('8/16/2020'),
dropdown1.Selected.Value="8/17/2020",!IsBlank('8/17/2020'),
dropdown1.Selected.Value="8/18/2020",!IsBlank('8/18/2020'),
dropdown1.Selected.Value="8/19/2020",!IsBlank('8/19/2020'),
dropdown1.Selected.Value="8/20/2020",!IsBlank('8/20/2020')
)



display column name based on the selection of the drop down.

 

 

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.

View solution in original post

5 REPLIES 5
BrianS
Super User
Super User

I would think that if the Excel sheet had the persons name repeated for the various dates then you could just capture a collection using the Distinct function. If you need additional info about the substitute you could use an ID number in the date column and have that point to a list with their name, phone, email, etc to make it easier to contact them.

I love this idea. Wasn't aware of the Distinct function, very helpful. What do I put in the expression portion of the distinct function to get it to pull up the column names? Here are some screenshots of where I'm at...

 

I attached a screen to show the method - slightly different data source. You just need a date and a name - I used initials for speed. The first dropdown uses Distinct to get the list of dates. Then you apply that as a filter in the second dropdown and get the names available on that date. You could add a third column with the email to facilitate contacting thePwrApp_Substitue_01.jpg sub. 

View solution in original post

v-yutliu-msft
Community Support
Community Support

Hi @SteveGard ,

Do you want to get  names of the available subs based on selected dates?

I agree with @BrianS .

Please do not use dates as column names.

Distinct function's syntax is:

Distinct(tablename, fieldname)

Then you will get no repeated data in that field.

So could you change your table format?

1)If yes, please change to this:

fieldname:       date                 subname

                       8/14/2020         Tony,Peter

                       8/15/2020          Steve,Ben,...

drop down's Items for date:

Distinct(SubAvailTime,date)

gallery's Items to display subname:

Distinct(
          Filter(
                 Split(
                         Concat(
                               Filter(SubAvailTime,date=dropdown1.Selected.Result),
                               subname&","),
                          ","),
                  !IsBlank(Result)
                  ),
           Result)

 

2)If no, you still do not want to change your table format, then the formula will be a little complex.

drop down's Items for date:

["8/14/2020","8/15/2020","8/16/2020","8/17/2020","8/18/2020","8/19/2020","8/20/2020"]

 gallery's Items to display subname:

Filter(SubAvailTime,
If(dropdown1.Selected.Value="8/14/2020",!IsBlank('8/14/2020'),
dropdown1.Selected.Value="8/15/2020",!IsBlank('8/15/2020'),
dropdown1.Selected.Value="8/16/2020",!IsBlank('8/16/2020'),
dropdown1.Selected.Value="8/17/2020",!IsBlank('8/17/2020'),
dropdown1.Selected.Value="8/18/2020",!IsBlank('8/18/2020'),
dropdown1.Selected.Value="8/19/2020",!IsBlank('8/19/2020'),
dropdown1.Selected.Value="8/20/2020",!IsBlank('8/20/2020')
)



display column name based on the selection of the drop down.

 

 

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.

View solution in original post

Thank you both very much! So helpful! It's working great. I really appreciate it!

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 (89,615)