filter SQL Date: filter on no date / date zero

(0) Share

Report

Posted on by HansHeintz

711

Not working:

Filter('[dbo].[asiel_verblijf]';vertrek_datum=0)
Filter('[dbo].[asiel_verblijf]';IsEmpty(vertrek_datum))

Filter('[dbo].[asiel_verblijf]';Day(vertrek_datum)=0)

Filter('[dbo].[asiel_verblijf]';IsBlank(vertrek_datum))

Filter('[dbo].[asiel_verblijf]';Text(vertrek_datum)="")

If a gallery shows a record/item with a blank vetrek_datum:

If I put a label in a gallery Day(Thisitem.vertrek_datum) it does say "0"'

If I put a label in a gallery Text(Thisitem.vertrek_datum) it does say ""' (nothing)

If I put a label in a gallery IsBlank(Thisitem.vertrek_datum) it does say True

What will?

I read messages about date filtering SQL tables and I have to say upfront: changing the SQL database is *not* an option for me.

Filtering on vertrek_datum < 01-01-2000 would also work for me but then the server connection throws errors and I read averywhere that sql date comparing with < and > will not work.

The only filter that does work is if I filter on a specific date and do

Text(vertrek_datum;"dd-mm-yyyy") = "23-02-2017"

vertrek_datum=DateValue("23-02-2017") does not work either

Additional info:

If I do ClearCollect(collectSQLDates;ShowColumns('[dbo].[asiel_verblijf]';"vertrek_datum")) it appears dates are in format "d-m-yyyy" when inspecting collectSQLDates

Filter('[dbo].[asiel_verblijf]';IsBlank(Text(vertrek_datum;"[$-nl-NL]d-m-yyyy"))) does not work either

I I do

Filter('[dbo].[asiel_verblijf]';DateDiff(vertrek_datum;Today())>800)
I get a number of hits where vertek_datum is in the 2017s

If I do

Filter('[dbo].[asiel_verblijf]';DateDiff(vertrek_datum;Today())>1000)

I get no hits, while there are records with vetrek_datum=0

Categories:

Building Power Apps

All responses (3)

Answers (0)

Sort by

HansHeintz 711 on at

Like
a
(0)

Report
Copy link

Link copied!

The problem is too silly to bother with probably.
I worked around it like this

First I made a subset
;;UpdateContext({KennelRecords:
LastN(Filter('[dbo].[asiel_verblijf]'
;kennel_asiel_id>=_kennel_asiel_id
&& kennel_asiel_id<= _last_kennel_asiel_id
);500)
})
Since records represent stays in an animal shelter and i want to find animals that did not leave yet (vertrek_datum=0) I may hopefully assume that this filter doesn't exclude animals that have been there extremely long.
Now I have a subset of 500 on which some of the filters that failed earlier do work. That absolutely none worked was because when I was using a filter with warning only the first 500 records were considered and none fell in the right category I guess because of the delegation thingie.
Now I can filter the subset in the items of the gallery like this:
Filter(KennelRecords;Day(vertrek_datum)=0) and I get the results I expected
So if I would add this filter to the previous filter I get a delegation warning and nothing shows up, the animals that have no vertrek_datum (date of leaving) are in the bottom part of the table of course.

mmm I cannot rember so much trouble looking for records with no date from my dbase4,clipper,delphi days; strange idea of progress
v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @HansHeintz ,
Is your problem caused mainly by delegation?
When using large data set, you need to use delegable funtions. Otherwise, the app will not perform well.
Using non-delegable functions, the app can only deal with at most 2000 records.
Here's doc about delegation and delegable funtions in SQL Server:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview
https://docs.microsoft.com/en-us/connectors/sql/

Best regards,
Community Support Team _ Phoebe Liu
HansHeintz 711 on at

Like
a
(0)

Report
Copy link

Link copied!

Are yout trying to explain to me that it is normal that filtering a table on a date field being empty is a problem?