Hello,

I have another problem, I can’t find solution for that, no solution found on forum too.

I have 5 fields ( text 8 each) that contain a date dd/mm/yy. I need to search records in a form, that match DateFirst to DateEnd in each of the 5 fields.

I know how to search by a procedure, with dataentry DateFirst to data-entry DateEnd , and the final is a list records, but it’s window print, no modification possible on it.

Here, this is another way, the research must be done in a form and the result must be displayed in this form.

I’m in the schwarz, in the dark space … a quick help from you will be TOP ( even a sample i could exploit )

Thanks a lot !

Hi Patrick

Not sure if I understood correctly....

You can try to search in a form as follow:

Select Records 

In the field enter: between date to date

Click Select Record or Next / Previous

Kind regards

Josef

Hello Josef,

Between match in text field like between "12/03/19" to "15/03/19"

as > "12/03/19" or < "16/03/19"

Ok , but if i want to search a between on the 5 text field simultaneously, i don't know how to do ....

Thanks

Patrick

Donwload Sample

I guess this should teach you all a lesson.

Put a boring and "sensible" request on here and you most likely will have to google it or find it out yourself but put on something totally crazy and it peaks an interest ;-)

Obviously there is a rational reason for this request and you can do this directly in the form (if you use Date fields rather than text fields which is a practical requirement anyhow) but the usability will be hopeless. Nobody can type in search criteria between 01/10/15 to 10/12/16 into 5 fields every time they want to search for something.

Ergonomics of a solution is the difference between failure and success, use and no use.

In this "crazy" request we showcase how you in 8.5 (onwards) can manipulate your form to do exactly what you want.

We have used a form that defines table and we have resized the main record so we can use the FORM object as a data-entry form for the search. 

SearchDateFrom and To is form virtual fields that are editable (new feature in 8.5). 

FormVirtual fields cannot be referenced directly in a derivation/action as they are not part of the cursor so we simply pick them out with GetValue() directly in the DQL. GUI functions like GetValue() will not related to the data set at all, only to the GUI object so you can pick any value out of your form.

In the request it was used Text fields 8 for dates. Forget about that!!!

You have to use Date as DataEase sort dates in the right way but if you where to use a text field you just ask for trouble to such an extent that you won't be able to succeed.

DD/MM/YY is not sortable in a normal way as it is jumbled. (this is why ISO dates are YYYYMMDD). 02/01/00 is now bigger than 01/01/19 and 03/01/19 is again bigger than 02/01/00 etc.

You could of course split it up and reform it as a iso date (number) and then make a crazy with statement and I've seen it done and it would work but it would again be - CRAZY. We couldn't help ourselves so we did it the "sane" way and the crazy way (see sample).

define "retval" text .
define "vselectionfilter" text 255 .
define "vselectionfilter2" text 255 .

define "vfromdate" text .

define "vtodate" text .

define "fromdate" number .

define "todate" number .

vfromdate := getvalue("SearchDatefrom") .

vtodate := getvalue("SearchDateTo") .

fromdate := concat(lastc(vFromDate,2),midc(vFromDate,4,2),firstc(vFromDate,2)) .

todate := concat(lastc(vToDate,2),midc(vToDate,4,2),firstc(vToDate,2)) .

vselectionfilter := concat("concat(lastc(Date11,2),midc(Date11,4,2),firstc(Date11,2))+0 between ",fromdate," to ",todate).

vselectionfilter := concat(vselectionfilter," and concat(lastc(Date21,2),midc(Date21,4,2),firstc(Date21,2))+0 between ",fromdate," to ",todate).

--vselectionfilter := concat(vselectionfilter," and concat(lastc(Date31,2),midc(Date31,4,2),firstc(Date31,2))+0 ") .

--vselectionfilter2 := concat(" between ",fromdate," to ",todate," and concat(lastc(Date41,2),midc(Date41,4,2),firstc(Date41,2))+0 between ",fromdate," to ",todate).

--vselectionfilter2 := concat(vselectionfilter2," and concat(lastc(Date51,2),midc(Date51,4,2),firstc(Date51,2))+0 between ",fromdate," to ",todate).

message vselectionfilter window .

--message vselectionfilter2 window .

retval := selectionfilter(vselectionfilter,vselectionfilter2) .

We got it working for two dates but the selection filter is quirky and it is just too long as the filter is 2x255 but how to bridge the gap so we could use both 255 args was beyond us today. 

However the point is - it is mad.

Compare it to the code when you use Dates.

define "retval" text .

define "vselectionfilter" text 255 .

define "fromdate" date .

define "todate" date .

fromdate := getvalue("SearchDatefrom") .

todate := getvalue("SearchDateTo") .

vselectionfilter := concat("Date1 between ",fromdate," to ",todate, " and Date2 between ",fromdate," to ",todate, " and Date3 between ",fromdate," to ",todate, " and Date4 between ",fromdate," to ",todate, " and Date5 between ",fromdate," to ",todate) .

message vselectionfilter window .

retval := selectionfilter(vselectionfilter,"") .

But as it turns out it is not that mad to do this in the form with Date fields.

It worked "surprisingly well" on our test of approximately 30k records but we have not tested it a networked application so beware.

We use LabelExecDQL here as its simpler to do this in a structured DQL then trying to make a derivation in one go to do this ;-)

So the functionality we use here to cheat default handling is:

1. Editable Form Virtual fields (you can do this directly in the form or re-use other fields etc or input methods (setvar/getvar etc.).
2. LabelExecDQL to create the filter.
3. SelectionFilter() to set the complex filter.

PS! DataEase fileters are boolean so this is why it would have worked with Text directly too if the filter had been big enough, but the problem with that approach is that you get a lot of data traffic just to calculate your filter. If you use date between you will get a good hit on the first column that will quickly narrow down you data set as the rest of the searching in your data set is sequential.

DataEase is fantastic when you work with the grain and it will take almost anything you throw at it but things add up so if you keep a "economical" attitude towards resource use you application will be faster and less likely to fall over.

Thanks so much for all guys ! :)

After convert all the 7 text fields  in date, impossible to use the table, lot of error messages. After 16 hours of unsuccessful attempts, no way out, i had to recreate at 100% this table ( ... ) and transplant it into a copy of the app (rock n'roll). Now, for me, my form is a muscle form !

Each underlined text is active and make filters. If you want to be inspired by the methods used, do not hesitate to ask me.

Problem when converting from Text to Date is formatting in derivations.

Dates in DataEase is writtine directly like 03/04/19 while a date in a text field would be "03/04/19".

So when converting from text to date you need to change all the derivations where text dates has been used as you won't be able to save the form before all the derivations are valid.

Other than that converting from text to date is straight forward.