Filtering a gallery from SQL datasource by date

Like (0) Share

Report

Posted on 6 Jul 2023 21:24:14 by HectorOlivos

411

Hello community!

I´m running out of ideas, can anyone help me with this.

I have a gallery in powerapps that is connected to a SQL VIEW, currently i have my column filtered by this:

Filter(
 FORD_Characteristics,
 cmb_PartNumber.Selected.Value = NoParte &&
 cmb_Parameter.Selected.Value = Estación 
 )

How can i add the function to filter it also by date using a datepicker and two dropdowns, one is for the hours and the second is for the seconds.

DropdownHour Items: ["00","01","02","03","04","05","06","07","08","09","10","11","12","13","14","15","16","17","18","19","20","21","22","23"]

DropdownMinutes Items:
["00","01","02","03","04","05","06","07","08","09","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31","32","33","34","35","36","37","38","39","40","41","42","43","44","45","46","47","48","49","50","51","52","53","54","55","56","57","58","59"]

Also do I need to add a PM/ AM dropdown?

Thanks for any suggestions.

Hector.

Categories:

General Questions

Connecting to Data

Using Apps Using Formulas

All responses (4)

Answers (1)

HectorOlivos 411 on 07 Jul 2023 at 15:36:42

Like (0)

Report

Re: Filtering a gallery from SQL datasource by date

Hi @timl

I visited the website you shared in the other post, using the Monitor Tool I didn't see the body message of nConvertion failed.

It only says:
"info": "Requested 100 rows. Received 0 rows."

And then it get this

Thanks.
Verified answer

timl 35,109 Super User 2025 Season 1 on 07 Jul 2023 at 10:44:23

Like (1)

Report

Re: Filtering a gallery from SQL datasource by date

Hi @HectorOlivos

If possible, I would try casting FechaDieCastingDate to a value of DateTimeOffset as that may provide better results.

https://powerusers.microsoft.com/t5/Building-Power-Apps/Filter-a-gallery-by-date-for-SQL-column-of-type-datetime2/m-p/954505

Also, this type of Filter is delegable with SQL Server so you don't need to use a collection or Power Automate.
HectorOlivos 411 on 06 Jul 2023 at 23:27:49

Like (0)

Report

Re: Filtering a gallery from SQL datasource by date

Hi @timl thanks for the advice.

I've used your suggestion, but its not bringing data, and its not displaying any error. And yes FechaDieCastingDate is a datetime column.

This is how the column looks from the sql view:

Without the date filter its working and bringing data. I guess its because the SQL view I'm using as datasource is with more than 10k rows.

I know its possible to bring more than 2k rows to powerapps, should I use a collection or power automate?

Thanks.
timl 35,109 Super User 2025 Season 1 on 06 Jul 2023 at 22:27:51

Like (0)

Report
Re: Filtering a gallery from SQL datasource by date

Hi @HectorOlivos

What's the data type of FechaDieCasting? Assuming that it's datetime, something like this may work:

Filter( FORD_Characteristics, cmb_PartNumber.Selected.Value = NoParte && cmb_Parameter.Selected.Value = Estación && FechaDieCasting = DateTimeValue(Text(DatePicker1.SelectedDate, "yyyy-mm-dd") & " " & DropdownHour.Selected.Value & ":" & DropdownMinute.Selected.Value) )

If FechaDieCasting is datetime and the target values include seconds, the above may not give you an exact match as it's matching on date, hours, and minutes only.