Recent

Author Topic: [SOLVED]DATASET Doesn't Filter as Expected  (Read 9986 times)

kito

  • Jr. Member
  • **
  • Posts: 78
[SOLVED]DATASET Doesn't Filter as Expected
« on: July 17, 2023, 09:55:03 pm »
Hello
I am working on a program that calculates the resumption date after a certain number of days of suspension, based on the value in the DAYS field.

Code: Pascal  [Select][+][-]
  1. END = START+ DAYS

https://www3.0zz0.com/2023/07/17/19/582840362.png

The resumption date is on the next working day of END.date, excluding weekends
 
Code: Pascal  [Select][+][-]
  1.  
  2. procedure TDataModule1.DatasetCalcFields(DataSet: TDataSet);  
  3. begin
  4.  if  (DayOfWeek(    END )=6 )then    resumption  := IncDay(END,2)   else resumption  := IncDay(END,1)  
  5. end;


So far, everything is working fine. However, when I try to filter the days that match the resumption day, the program does not consider the condition and only gives me the next working day after the suspension end date Despite the values in the DATASET being correct.

Code: Pascal  [Select][+][-]
  1. Dataset.Filter:='resumption  like '+ QuotedStr( DateToStr( DateTimePicker.date)) ;
  2.  Dataset.Filtered:=True;    


https://www3.0zz0.com/2023/07/17/19/892009578.png

Could you please help me with this issue?


LAZARUS 2.2.6  32bit
FPC 3.2.2
OS Win 10 64 bit
« Last Edit: July 21, 2023, 08:03:14 pm by kito »
We can barely feel your presence, because of the depth of your silence

alpine

  • Hero Member
  • *****
  • Posts: 1064
Re: DATASET Doesn't Filter as Expected
« Reply #1 on: July 17, 2023, 10:52:01 pm »
Not quite sure where the problem is, I can see a few potential pitfalls:
  • like is a pattern matching operator, usually used with a char columns, not sure how it will behave on dates
  • You are using a calculated field into the filter expression, probably it won't reach the DB engine (the field is unknown to the DB)
  • You're using a field name (END) which is usually a SQL reserved word, it might be a problem

Why don't you use TDataset.OnFilterRecord to filter programmatically?  Even more so because you have calculated fields.
"I'm sorry Dave, I'm afraid I can't do that."
—HAL 9000

kito

  • Jr. Member
  • **
  • Posts: 78
Re: DATASET Doesn't Filter as Expected
« Reply #2 on: July 17, 2023, 11:31:31 pm »
    thank you alpine for your replay

Quote
  • like is a pattern matching operator, usually used with a char columns, not sure how it will behave on dates
It works pretty fine

Quote
  • You are using a calculated field into the filter expression, probably it won't reach the DB engine (the field is unknown to the DB)

it's a data field  the calculated field is the number of days between START and End, I don't know if I made a mistake when I have incorporated it into this procedure my goal is to have the resumption   date changed automatically once I Change (START, END)

Quote
  • You're using a field name (END) which is usually a SQL reserved word, it might be a problem

 All field names in the topic are aliases to approximate meaning and save time.

Quote
Why don't you use TDataset.OnFilterRecord to filter programmatically?  Even more so because you have calculated fields.
In fact, I use many filtering cases  , and I fear that  using   TDataset.OnFilterRecord  may limit my options.
We can barely feel your presence, because of the depth of your silence

Zvoni

  • Hero Member
  • *****
  • Posts: 2327
Re: DATASET Doesn't Filter as Expected
« Reply #3 on: July 18, 2023, 08:41:52 am »
Going out on a Limb:
DateTimePicker.Date is of Type TDate which is an Alias for TDateTime which is an alias for double.
He's using the LIKE-Operator without wildcards which translates to equality.

Now the question is: What DataType is "resumption"? What Database is underneath it all?
He's converting the Date to a string.

My guess is he's running afoul of equality comparisons of float's
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

kito

  • Jr. Member
  • **
  • Posts: 78
Re: DATASET Doesn't Filter as Expected
« Reply #4 on: July 18, 2023, 09:30:07 am »
Hello Zvonic
I am using sqlite database, resumption is vchar, I am not facing any problem  using the  filter expression but it behave strangely when it comes  to the date that muchs the weekend. where I can't filter the dataset matching DateTimePicker.Date, but I must select a specific date(end+1 day)in order to see the results(end+2 days) The strange thing is that the date selected in the DateTimePicker is completely different from the results shown in the filtered dataset.
We can barely feel your presence, because of the depth of your silence

Zvoni

  • Hero Member
  • *****
  • Posts: 2327
Re: DATASET Doesn't Filter as Expected
« Reply #5 on: July 18, 2023, 09:53:12 am »
Hello Zvonic
I am using sqlite database, resumption is vchar, I am not facing any problem  using the  filter expression but it behave strangely when it comes  to the date that muchs the weekend. where I can't filter the dataset matching DateTimePicker.Date, but I must select a specific date(end+1 day)in order to see the results(end+2 days) The strange thing is that the date selected in the DateTimePicker is completely different from the results shown in the filtered dataset.
Wait a sec:
First: Which Format for your dates are you using in the SQLite-DB? I hope it's ISO-Format "YYYY-MM-DD"
Next: Have you checked that DateToStr returns the SAME Format?

I really, really hope you use ISO-Format

.....and there is no vchar DataType in SQLite.
It's TEXT.
Period

EDIT:
Code: Pascal  [Select][+][-]
  1. procedure TDataModule1.DatasetCalcFields(DataSet: TDataSet);  
  2. begin
  3.  if  (DayOfWeek(    END )=6 )then    resumption  := IncDay(END,2)   else resumption  := IncDay(END,1)  
  4. end;
If resumption is of type TEXT (vchar), you do realize that IncDay returns a DateTime? And you're assigning without Formating!
--> https://www.freepascal.org/docs-html/rtl/sysutils/formatdatetime.html
I'm more surprised this compiles.....
« Last Edit: July 18, 2023, 09:57:38 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

kito

  • Jr. Member
  • **
  • Posts: 78
Re: DATASET Doesn't Filter as Expected
« Reply #6 on: July 18, 2023, 12:53:51 pm »

Wait a sec:
First: Which Format for your dates are you using in the SQLite-DB? I hope it's ISO-Format "YYYY-MM-DD"
Next: Have you checked that DateToStr returns the SAME Format?

I really, really hope you use ISO-Format

.....and there is no vchar DataType in SQLite.
It's TEXT.
Period


I'm more surprised this compiles.....
to avoid facing a problem with date encoding especially when I copy my program from one computer to another. Which naturally varies the date encoding, especially in the order of the day and the year  Therefore, I use the following solution/trick:
In the database: the field type is set as text/varchar,
 but in the program, I manually add a field to the dataset and assign it as a date type. Then, I link it to the relevant field, so the program handles it as a DateTime type
So I don't I have to worry about date time Format  issues .
We can barely feel your presence, because of the depth of your silence

alpine

  • Hero Member
  • *****
  • Posts: 1064
Re: DATASET Doesn't Filter as Expected
« Reply #7 on: July 18, 2023, 01:32:43 pm »
to avoid facing a problem with date encoding especially when I copy my program from one computer to another. Which naturally varies the date encoding, especially in the order of the day and the year  Therefore, I use the following solution/trick:
In the database: the field type is set as text/varchar,
 but in the program, I manually add a field to the dataset and assign it as a date type. Then, I link it to the relevant field, so the program handles it as a DateTime type
And who is making the TDateTime<-->Text conversions between the 'linked' fields?

So I don't I have to worry about date time Format  issues .
You should, obviously.

Giving such kind of 'pseudo code' doesn't help. Consider a small project to demonstrate your issue.
"I'm sorry Dave, I'm afraid I can't do that."
—HAL 9000

Zvoni

  • Hero Member
  • *****
  • Posts: 2327
Re: DATASET Doesn't Filter as Expected
« Reply #8 on: July 18, 2023, 01:40:43 pm »

Wait a sec:
First: Which Format for your dates are you using in the SQLite-DB? I hope it's ISO-Format "YYYY-MM-DD"
Next: Have you checked that DateToStr returns the SAME Format?

I really, really hope you use ISO-Format

.....and there is no vchar DataType in SQLite.
It's TEXT.
Period


I'm more surprised this compiles.....
to avoid facing a problem with date encoding especially when I copy my program from one computer to another. Which naturally varies the date encoding, especially in the order of the day and the year  Therefore, I use the following solution/trick:
In the database: the field type is set as text/varchar,
 but in the program, I manually add a field to the dataset and assign it as a date type. Then, I link it to the relevant field, so the program handles it as a DateTime type
So I don't I have to worry about date time Format  issues .

What utter non-sense.
Use ISO-Format "YYYY-MM-DD" in the Database, and any Date-Representation in your Frontend "converted" to that same ISO-Format in the background before writing/reading to/from the Database.
The ISO-Format WAS "invented" to address exactly the problem of "localized" Date-Representations
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

kito

  • Jr. Member
  • **
  • Posts: 78
Re: DATASET Doesn't Filter as Expected
« Reply #9 on: July 18, 2023, 07:15:09 pm »

Quote
And who is making the TDateTime<-->Text conversions between the 'linked' fields?

 I don't know how but as long as  I respect the date format every thing is fine.


Quote
You should, obviously.
:(

Quote
Giving such kind of 'pseudo code' doesn't help. Consider a small project to demonstrate your issue.

good idea! thanks  here you are :

We can barely feel your presence, because of the depth of your silence

kito

  • Jr. Member
  • **
  • Posts: 78
Re: DATASET Doesn't Filter as Expected
« Reply #10 on: July 18, 2023, 07:30:15 pm »

Quote
What utter non-sense.
Use ISO-Format "YYYY-MM-DD" in the Database, and any Date-Representation in your Frontend "converted" to that same ISO-Format in the background before writing/reading to/from the Database.
The ISO-Format WAS "invented" to address exactly the problem of "localized" Date-Representations

In bilingual countries, especially, you could find an individual using two different encodings if he own two devices. Some may use a different date format (yyyy-mm-dd" / "dd-mm-yyyy"), while others might utilize a different date separator ("/"or "-" or ".") on their device. Often, the program stops execution when it encounters such discrepancies, displaying error messages like "Unknown date" or "Invalid encoding." Therefore, I resort to storing date-related data as text to avoid such issues.

I think you give me a hint because I am using a different date separator on my pc (-) and the registered dates on the database (/)
besides the attached project works fine 
We can barely feel your presence, because of the depth of your silence

alpine

  • Hero Member
  • *****
  • Posts: 1064
Re: DATASET Doesn't Filter as Expected
« Reply #11 on: July 18, 2023, 07:33:52 pm »
The attached Zip is empty.
"I'm sorry Dave, I'm afraid I can't do that."
—HAL 9000

kito

  • Jr. Member
  • **
  • Posts: 78
We can barely feel your presence, because of the depth of your silence

alpine

  • Hero Member
  • *****
  • Posts: 1064
Re: DATASET Doesn't Filter as Expected
« Reply #13 on: July 18, 2023, 11:13:09 pm »
Can't download the shared file.
I'd recommend to clean the project before zipping.
"I'm sorry Dave, I'm afraid I can't do that."
—HAL 9000

kito

  • Jr. Member
  • **
  • Posts: 78
Re: DATASET Doesn't Filter as Expected
« Reply #14 on: July 19, 2023, 10:13:00 pm »
Can't download the shared file.
I'd recommend to clean the project before zipping.
Thankful for your time .I am very sorry for the delay.
google said it may contain a harmful script or something and needs to be reviewed    :o

please try this :

https://drive.google.com/file/d/1cfl77-GR2lHuYZQuqodiujkE9wMzvcrJ/view?usp=sharing
« Last Edit: July 19, 2023, 10:26:18 pm by kito »
We can barely feel your presence, because of the depth of your silence

 

TinyPortal © 2005-2018