filters a dataverse gallery with buttons selecting different date options

[Terry-B]

Hey gang,

I am trying to figure out how I can filter and or display only the gallery records that meet the different date buttons that I have created on the bottom of the page.  I have attempted several things with no luck so far.  See the buttons at the bottom of the page here.  The data field I am trying to filter on is the one that is displayed in the Gallery on the left.  It is a shipping date field in my dataverse.

thank you in advance,

T

[v-qiaqi-msft]

Hi @Terry-B,

Based on the issue that you mentioned, do you want to filter the Gallery based on the button click?

Could you please share a bit more about the scenario?

I have a test on my side, please take a try as below.

Add 4 buttons for different filter conditions.

Set OnSelect property to filter records only today:

ClearCollect(SortForDate,Filter(TableName,ShippingDate=Today()))

Set OnSelect property to filter records only tomorrow:

ClearCollect(SortForDate,Filter(TableName,ShippingDate=Today()+1))

Set OnSelect property to filter records only between a date range:

ClearCollect(SortForDate,Filter(TableName,ShippingDate>=DatePicker1.SelectedDate && ShippingDate<=DatePicker2.SelectedDate))

Set OnSelect property to filter records in max future:

ClearCollect(SortForDate,First(Sort(TableName,ShippingDate,Descending)))

And then set the Items property of the Gallery as below:

SortForDate

Hope it could help you.

Regards,

Qi

[Terry-B]

@v-qiaqi-msft 

Thank you for the response.  Some progress there with your suggestions.  

running into several issues here.

So, here is the Today code as I modified it for my program :

ClearCollect(SortForDate,Filter(Schedules,'ship-date'=Today()))

                                                 -Schedules being the table name, and 'ship-date' being the field for the shipping                                                        date

now my display output only showing the 1 record for today, but some things are missing in the gallery now :

This is what it normally looks like : 

My normal Items property for the gallery already had this in it, which sorts all my records descending by the ship-date : 

SortByColumns(Filter(Schedules, FilterBox.Value in 'Job-Number'),"cr174_shipdate",Descending)

So how do I incorporate

SortForDate

I am using powerapps in Teams and CDS (Dateverse for Teams).  

I tried one example with the only today code, and it does work and shows only the one record.

I need it to default to shownig all records initially, as well as after selecting on of the filter buttons, be able to reset it back to showing all records.

Date Range - Self explainatory.  from and to dates selected in the pickers, based on the ship-date field

Only Today - ship-date field for today

Tomorrow - ship-date field for today + 1

Max future - ship-date of today to the last ship-date record in the future

This app is basically a shipping tracking app that tracks our truck loads as we import and export our products.  The idea is for people to be able to see what is shipping on any given date so that if space allows and they have something going to the same place they can add to it.

[v-qiaqi-msft · Accepted solution]

Hi @Terry-B,

“I am trying to figure out how I can filter and or display only the gallery records that meet the different date buttons that I have created on the bottom of the page.”

These italic and colored words comes from your description, I thought that you just want to filter the Gallery to display records only based on the date buttons, isn't that what you need?

Or, do you want to filter the records clicking the button and then reset to display all the default records?

If so, please try the following solution.

Set OnSelect property to filter records only today:

UpdateContext({VarToday:!VarToday})

Set OnSelect property to filter records only tomorrow:

UpdateContext({VarTomorrow:!VarTomorrow})

Set OnSelect property to filter records only between a date range:

UpdateContext({VarDateRange:!VarDateRange})

Set OnSelect property to filter records in max future:

UpdateContext({VarMax:!VarMax})

Set Items property of your Gallery as below

If(
    VarToday,
    Filter(
        Schedules,
        'ship-date'= Today()
    ),
    If(
        VarTomorrow,
        Filter(
            Schedules,
            'ship-date'= Today() + 1
        ),
        If(
            VarDateRange,
            Filter(
                Schedules,
                'ship-date'>= DatePicker1.SelectedDate && 'ship-date'<= DatePicker2.SelectedDate
            ),
            If(
                VarMax,
                Filter(
                    Schedules,
                    'ship-date'= First(
                        Sort(
                            Distinct(
                                Schedules,
                                'ship-date'
                            ),
                            Result,
                            Descending
                        )
                    ).Result
                ),
                SortByColumns(
                    Filter(Schedules, FilterBox.Value in 'Job-Number'),
                    "cr174_shipdate",
                    Descending
                ) // Here is your default formula！
            )
        )
    )
)

Hope it could help you.

Regards,

Qi

[Terry-B]

@v-qiaqi-msft 

Thank you for the responses.  Based on the code you sent me and I pasted in with your instruction, I don't see any of the records now in the gallery or on the main detail screen.  I understand what the code is doing, but I am not sure why I am not seeing any records.

[v-qiaqi-msft]

Hi @Terry-B,

I want to explain to you that why I set a variable like that:

UpdateContext({Var1:!Var1})

Once you click the button, the variable will be triggered. And if you click the button second time, the variable will be cleared so that the Gallery will display the default items. Notice that you should click the button continuously to trigger and clear the variable. It is not allowed to click the Today button once and then click the Tomorrow button. You should click the Today button once to filter the records related to Today, then click the Today button twice to clear the records related to Today and display the default records.

The first time clicking the button is to trigger the variable, the second time clicking is to clear the variable and display the default items.

Here is a GIF, please check my test result.

Regards,

Qi

[Terry-B]

@v-qiaqi-msft 

Thank you for the video.  That was a great learning tool.  I did a video as well.  I created a video of what I am seeing, but this forum doesn't seem to allow me to upload it, so I created a youtube link for it : 

https://youtu.be/LzWxBp-XFt8

This is what I am seeing.

T

[Terry-B]

@v-qiaqi-msft 

I have been troubleshooting while I am not able to get the same result as you.  I broke apart the code and pasted in each button individually and slowing built up the code until it failed.  All buttons work, except when I put in the date range code.  

As an example below I just put in the date range code along with my default code.  In this fashion every other button works as expected, but for some reason, the date range code breaks things with this error and I have not been able to resolve this : 

Specifically it seems to show that DatePicker1 and DatePicker2 are not valid names : 

but they are valid : 

[v-qiaqi-msft]

Hi @Terry-B,

From the screenshot, what you happened confused me like a shot.

Yes, I could understand you that the control name is valid clearly, but the error message certainly could not recognize it. 

Currently, I recommend that you could delete these Date Pickers. Re-add two new Date Pickers and refer to the Date Picker name again to check if it works.

Hope it could help you this time.

Regards,

Qi

[Terry-B · Accepted solution]

@v-qiaqi-msft I finally found the issue : 

A simple syntax issue that I guess pops up with inconsistencies between Powerapps Teams Vs Powerapps.

Your code and my discovered fix: 

 'ship-date'>= DatePicker1.SelectedDate && 'ship-date'<= DatePicker2.SelectedDate



My Fix : 
'ship-date'>= DatePicker1.Value && 'ship-date'<= DatePicker2.Value

I don't know why it is different or why it works, but so be it.

thanks for all our help.

T