Date Request Validation ?

[Ugo DI LUCA]

Hi,

Some of you might remember of this Article I wrote a few months ago about "Storing Finds and Criterias" in separate files.

I'm currently implementing this idea (which some variations from my original post) in a solution I'm developing.

I'm now 90% done with it. As long as requests are valid, the final result is even way better than what I expected.

Though, in order to comply any need, requests need to be customizable, and I need to validate the Date requests so that the user would be alerted if

- there are mismatched dates

- Any Year is not 4 digits

- This input is not a valid date format (/1/2004)

- The dates are not valid dates (02/31/2004 or 15/31/2004)

- The operator signs ( ! , //, >,>=,<=,<, ...) happen to be uniques and in a correct position.

At the moment, I've made individual calcs for all these conditions but I'm having troubles in joining all of them for a global validation.

I was wondering if by chance one of you had already solved this issue with some magic calc.

Thanks

FileMaker Version: 6

Platform: Mac OS 9

[Ugo DI LUCA]

Ok,

I had this classic epiphany when writting the former post. Sorry for that.

If someone is interrested, just ask.

Now for the Hour validation...

FileMaker Version: 6

Platform: Mac OS 9

[Ugo DI LUCA]

There it is...

Goal

Allow user to enter some date request in a global text field, and have it inserted in the Find Process.

Entries allowed and Conditionals :

1. The " ! " operator in order to find duplicates.

If this sign exist, it MUST be the unique value entered except spaces.

2. The "..." operator in order to find in a range.

There is only one possible occurrence. There need to be valid dates values before and after, and no othe operators used

3. The ">" and "<" operators as well as the "=" sign

The 2 first operators would need to appear as the first values. When one operator is used, the other

cannot and if used in conjunction with the "=" operator, then spaces after the operator would not be allowed.

The "=" sign is not a valid input except if entered as the unique value to find "empty" records or after the ">" or "<" operators.

4. The "//

DateQuerryValidation.fp5.zip

[Ugo DI LUCA]

Any one has a clue for this.

The calc for Date Request Validation is a combination of other boolean calcs.

When I joined them into only one calc, it doesn't work ...

FileMaker Version: 6

Platform: Mac OS 9

[Ender]

It's hard to test without it being part of a find script. But one thing I noticed is that it doesn't seem to like dates in a dashed format like 6-1-2004, which is not an uncommon format in the U.S. Also, FileMaker accepts a range operator ".." as well as "...", your calc only accepts "..."

[Ugo DI LUCA]

Hi,

Well, I'll try to include these parameters as well then, which would just need a few adaptations.

When I'll have finished the Hour calcs, I'll attach a sampler of the "Search Engine".

In fact, the global will pass to the find process within a script, and this calc would be used in the very first step of the script to check for invalid criterias.

Still, I don't get why it wouldn't work when all separate calcs are introduced into a bigger one. Or may be it is too big, that's all ?

Thanks for testing it

FileMaker Version: 6

Platform: Mac OS 9

[Ender]

Have you tried breaking down the cValidDate calc into separate pieces? I know this helps me when I'm debugging a complex calc.

[Ugo DI LUCA]

Modified version.

FileMaker Version: 6

Platform: Mac OS 9

DateQuerryValidation.fp5.zip

[Ender]

One problem--in the first part of the Case(PatternCount(Substitute(gModLogDate;"-";"/") It looks like it's checking for day < 13. If you put 12/12/2003, it works, but 12/13/2003 does not.

I'll keep looking...

[Ender]

Yeah, it looks like the month validation is happening on the second term in the date and the day validation is happening on the first term.

[Ugo DI LUCA]

You're right again Ender.

It seems the Valid Date doesn't work as expected. Need to correct it

FileMaker Version: 6

Platform: Mac OS 9

[Ender]

Do you think it would be any easier to extract the one or two date terms and put them into Global date fields? It would then be a simple matter to compare them to make sure the first date is before the second date. You might also be able to error trap for valid dates.

[Ugo DI LUCA]

My French settings for sure. I'll have to go for the Date() function, then try on this basis

FileMaker Version: 6

Platform: Mac OS 9

[Ugo DI LUCA]

Actually, it is possible to compare the first string to the second and check for Mismatched dates. That's what the cMismatched dates is for, and I'd think it is currently returning correct result if you look at the other layout with all the intermediary calcs.

I really think to rebuil the valid date, which doesn't rely on the Date() function, and therefore isn't acceptable for you on the other side of the pond.

BTW, thanks for your contributions. I'd think you'd better look to the first attachment.

In the second, to account for the "-", I just substituted my original global with a Sunbstitute(myglobal,"-","/"), which is becoming invading here in the calcs.

The ".." and "..." isn't worrying me at all as checking for ".." rather than "..." would just be the difference between the former calc and the new one.

FileMaker Version: 6

Platform: Mac OS 9

[Ugo DI LUCA]

Definitely, this cValidDate is screwing the whole calc.

I need to convert the global step by step with the Date(TextToDate()), then check for Day(Date(TextToDate())) and Month(Date(TextToDate()))

I'll modify this calc.

Thanks

FileMaker Version: 6

Platform: Mac OS 9

[Ender]

As long you educate your users on what you accept as valid find criteria, I think you can skip those suggestions. If they do enter dashes instead of slashes, error messages can easily get users back on track.

[Ugo DI LUCA]

Well,

As a start, I'd like to at least restrict the user from entering some strings that wouldn't be accepted as Find criterias, thus leading the user with a bad FoundSet.

So, apart the 4 digits part of it, I need to limit it to what FM accepts as a valid Date request.

If FM accepts dashes, it should therefore account for them, and the same for the ".."

It's a bit more complex in the final files I'm developing as the user would even be able to enter, from a toolbox, some special characters which would allow some other custom or quick scripted finds (ex: The last 7 days, the next 4 days).

Also, the "..." would be entered using a Range button.

But then, I was trying to put here a standardized calculation which could be used also by some other users here on the Forums.

I'll fix it later this evening.

FileMaker Version: 6

Platform: Mac OS 9

[Ugo DI LUCA]

I hope this one would accomodate the Systems date issues...

Please someone have a try with it with a few dates as for example

02/29/2004

02/29/04

02/29/2005

01/31/2004...02/05/2004

>01/31/2004

//...05/31/2005

...12/32/2004

ABC12/1/2004

Thanks.

I'm not sure wether the TextToDate is sufficient, but at least with my French settings, it now always work.

FileMaker Version: 6

Platform: Mac OS 9

DateQuerryValidationv3.fp5.zip

[Ender]

Two bugs, if dates are:

"2/31/2003" or "2/31/2003...2/12/2004", this is accepted.

"2/20/2003...2/25/2003" this makes cMismatched=0. In fact any time the second date in the range has a day > 12 it fails.

[Ugo DI LUCA]

Now, I'm not sure I get it...

It's all perfect here. Did you tried saving as a clone, so that the system doesn't change anything ?

I'm not sure what to do to have it working on all systems.

Even if I currently only need it here (and it currently works), I'd like to understand how this can be screwed this way.

My thoughts was that Date() would revert the user system settings, so that extracting Day(), Month() and Year() from a Date() would be cross-systems.

I'd be curious to know what result you guys get with these 2 calcs

copy/paste to 2 new calcs...

First Date :

TextToDate(Middle(gModLogDate,Position(gModLogDate,Left(TextToNum(Substitute(Substitute(gModLogDate,"0","1"),"-","/")),1),1,1), POSITION(Substitute(Substitute(gModLogDate,"0","1"),"-","/"),"/",1,2)+4))

Second Date

TextToDate(Middle(Substitute(gModLogDate,"-","/"),Position(Substitute(gModLogDate,"-","/"),".",1,1)+PatternCount(gModLogDate,"."),POSITION(Substitute(gModLogDate,"-","/"),"/",POSITION(Substitute(gModLogDate,"-","/"),".",1,1),2)+5-POSITION(Substitute(gModLogDate,"-","/"),".",1,1)-PatternCount(Substitute(gModLogDate,"-","/"),".")))

Thanks again

FileMaker Version: 6

Platform: Mac OS 9

[Ugo DI LUCA]

And now another calculation for the second date in a range

Date(Month(TextToDate(Middle(Substitute(gModLogDate,"-","/"),Position(Substitute(gModLogDate,"-","/"),".",1,1)+PatternCount(gModLogDate,"."),POSITION(Substitute(gModLogDate,"-","/"),"/",POSITION(Substitute(gModLogDate,"-","/"),".",1,1),2)+5-POSITION(Substitute(gModLogDate,"-","/"),".",1,1)-PatternCount(Substitute(gModLogDate,"-","/"),".")))),Day(TextToDate(Middle(Substitute(gModLogDate,"-","/"),Position(Substitute(gModLogDate,"-","/"),".",1,1)+PatternCount(gModLogDate,"."),POSITION(Substitute(gModLogDate,"-","/"),"/",POSITION(Substitute(gModLogDate,"-","/"),".",1,1),2)+5-POSITION(Substitute(gModLogDate,"-","/"),".",1,1)-PatternCount(Substitute(gModLogDate,"-","/"),".")))),Year(TextToDate(Middle(Substitute(gModLogDate,"-","/"),POSITION(Substitute(gModLogDate,"-","/"),".",1,1)+PatternCount(gModLogDate,"."),POSITION(Substitute(gModLogDate,"-","/"),"/",POSITION(Substitute(gModLogDate,"-","/"),".",1,1),2)+5-POSITION(Substitute(gModLogDate,"-","/"),".",1,1)-PatternCount(Substitute(gModLogDate,"-","/"),".")))))

I just hope I won't need to run these kinds of calcs all along to make sure you can read these same results.

FileMaker Version: 6

Platform: Mac OS 9

[LaRetta]

Hi Ugo,

I admit I didn't read all of your posts or try your demo. But Ray has already done what you need (I think). Have you checked out Date Magic here?

Before vs. 7, that calc (and scripted one for Custom Dialog) was the answer to my prayers. It may not answer all your problems, but it certainly should remove any question about validating those dates - even with dashes and 2-digit years.

LaRetta

[Ender]

Interesting...

Working from a clone fixed the problems with cValid and cMismatched, but created a new problem with cAlpha. When using a date range, cAlpha evaluates to 0. Without a range it works fine.

FileMaker Version: Dev 6

Platform: Mac OS X Panther

Picture.pdf

[Ender]

Oops--I'm playing catch-up here. Re:

>And now another calculation for the second date in a range

This calc evaluates correctly.

FileMaker Version: Dev 6

Platform: Mac OS X Panther

Picture2.pdf

[Ugo DI LUCA]

LaRetta,

I'll took a look at Ray's file. I've got 5.5 here at home, and I specially dislike Custom Dialog anyway.

Thanks for the link.

It's not exactly the same objective but true some parts of this calc may be what I'm looking for.

Ender,

Oops. got it.

The dots would evaluate in the TextToNum while they don't in my French version.

So the cAlpha calc should be changed with both a Substitute(".","") for US version and a Substitute(",","") for European versions.

Her's attached the new file, with the error messages instead of only a boolean result.

Thanks for your help.

FileMaker Version: 6

Platform: Mac OS 9

DateQuerryValidationv3.fp5.zip

[Ender]

Works great!

[Ugo DI LUCA]

Final result here. With the changes made, it still wasn't working if the user was entering a " ! " or a "//" or a " = ".

This one does the job. Thanks for your help Ender.

Save as clone to account for your US settings, even if the opening script forces the Local settings to be used.

FileMaker Version: 6

Platform: Mac OS 9

DateQuerryVal.zip

[Ender]

Looks good.

I'm not sure if this is important, but you account for ">=" and "<=" but not "?" or "?". Also, in your demo screen, in the instructions for "//" FileMaker converts the "//" to today's date in browse mode.