Filter Gallery by Date from DateTime Value

[Anonymous]

I currently filtering my gallery by "room number". Then, I would like to filter further by only displaying gallery items from today's date. I have a datetime value in the card that I would like to filter it by. However, I think I need to convert the datetime to a date only for the formula to recognize the Today function. The below code is what I have now, and it works, but it is giving me a delegation error. Is there any way to clean this up and get rid of the delegation error?

SortByColumns(Filter(BMObservationList, RoomNumber = Gallery1.Selected.RoomNumber, DateValue(Text(Time,DateTimeFormat.ShortDateTime24)) = Today()),"Time",Ascending)

[WarrenBelz · Accepted solution]

Hi @Anonymous 

Nothing really wrong with the code I can see, but Date Filters, no matter how you structure them are not delegable, Today() is not really the problem.

SortByColumns(
   Filter(
      BMObservationList, 
      RoomNumber = Gallery1.Selected.RoomNumber, 
      DateValue(
         Text(
            Time,
            DateTimeFormat.ShortDateTime24
          )
      ) 
      = Today()
   ),
   "Time",
   Ascending
)

We can do this another way however - firstly a collection (I assume there are never more than 2000 items for a room). Put this in the navigation code to the list.

ClearCollect(
   colBookings,
   Filter(   
      BMObservationList, 
      RoomNumber = Gallery1.Selected.RoomNumber
   )
)

You could also use ShowColumns to just pick the fields you needed.

Now your list

SortByColumns(
   Filter(
      colBookings, 
      DateDiff(
         Time,     //you should rename this - Time is a reserved word
         Now(),
         Days
      ) = 0
   ),
   "Time",
   Ascending
)

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.

[Anonymous]

@WarrenBelz,

Thank you for your response. I will try and unpack your response when I have a chance as I am still very new to PowerApps or anything beyond basic Excel. The clear collect function code would go alongside OnSelect Navigate(Gallery2) in my app? and then the second string of code goes in the Items field for my gallery?

If I change the experimental settings from 500 to 2,000, will my current code work if I'm not able to get what you provided working?

For some reason I was thinking the 2,000 limit was for total records, not total for just the Room, but I guess that makes total sense because that is the second filter argument?

[WarrenBelz]

Hi @Anonymous ,

To simplify - Yes, Yes and Yes

On the last bit - you can use delegable queries to collect a list under the limit. After that on the collection all queries will work, delegable or not. This is actually the structure of every app I do.

[Anonymous]

@WarrenBelz  I changed my column names in Sharepoint so I am not using a reserved word in 'Time' however it does not update in PowerApps even after refreshing my data source. Any advice?

[WarrenBelz]

Hello @Anonymous ,

Your query to date has been about Filters - how are you updating SharePoint?

[Anonymous]

@WarrenBelz  Yes I got my filter query resolved, but I had a Sharepoint column I was filtering by titled Time and you had advised me to rename it. I went into list settings and renamed the column in Sharepoint, but that change is not reflected in PowerApps, even after refreshing my data source.

Thank you for your help

[WarrenBelz]

Thanks @Anonymous ,

Yes SharePoint always retains the original name and I should have been more specific about the term rename. A the only way to do this that I know of is to create a new column ( I will use BookTime), create a datasheet view in SharePoint with the two columns and copy and paste the data. Watch out for Throttling limits if you do these too quickly (you just get locked out for a couple of minutes). Change any SharePoint views to the new field and do the same in Power Apps. I know it is a nuisance, but using a reserved PowerApps Function word has the potential to do the sort of thing that you are experiencing.

However before you do this, can you be a bit more specific about SharePoint not updating. Are you using SubmitForm and is it only this field not updating or all fields?

[Anonymous]

Hi @WarrenBelz ,

When I said not updating, I just meant that although I changed the name of the column in Sharepoint, that was not reflected as a change in the field in the PowerApps browser client...but what you explained makes sense.

The only part I'm confused about is the last second of your first paragraph. I understand that using a reserved function as the title for my column could be problematic so I would like to change it, but I don't think I'm actually currently experiencing and ill effects of that currently.

Thank you.

[WarrenBelz]

Thanks @Anonymous ,

Happy to help