date validation

[rome33]

I want to have a date that is chosen by the user.

It needs to be validated by being greater than a date in another field. however, every time I try to do some calculation

like

date = date if date is not < otherdate

or something, I get an error that the function wants to return a number or "("

Either that or it lets me choose a date that IS before otherdate and then changes my date to something like 12/10/0001

What is the deal with that 0001?

My Filemaker book is no help.

Can anyone help me with the syntax of this?

Thanks

[LaRetta]

The date you wish to validate against: Date1 (date field)

The date User enters: Date2 (date field)

Validation on Date2 (Define Fields > Options > Validation tab) would be via calculation with:

Date2 > Date1

if Date2 is older than (or equal to) Date1, validation will fail.

[rome33]

yes that is what I want

How Do I write that in the calculation field so that it works?

[LaRetta]

You don't use it in a calculation field. You use it on the date field your Users will be entering. As indicated, go to Options and Validation tab on that Date2 date field and enter the calculation there.

Field validation works on the field the User is exiting. So when they exit that Date2 field, its field validation will be sure it's greater than Date1. Enter a Display Custom Message, directing User to correct their entry and uncheck 'Allow User Override.'

[Søren Dyhr]

Field validation works on the field the User is exiting.

Wrong! I made the same fault the other day in a reply here, it's when the record is committted. Autoenter and validations have swapped places in the processing after going fm7.

This means that a scratchpad'ish interface where the fields having illegal values are high-ligthed before attempted saved via a button is ...the correctest way, if some perhaps too platform centric guidelines for interfaces should be observed. This way is the user alerted immediately after the field is left and not when clicking outside the fields in the entry.

--sd

[LaRetta]

Hi Soren

It may take me a few readings before I really understand what you just said but I am confident it will be very valuable and make total sense once I decipher it. Thank you!!

LaRetta

[smsaw]

Maybe this Tip from The Missing Manual might help clarify things.

"Tip: If you're dying to know what determines when your validation occurs, here's the skinny: If, when validating a field, FileMaker looks at the data only in the field itself, it performs the validation immediately. If it has to look at data in other fields or other records, then it waits until you commit the record before validating."

Taken from FileMaker Pro 8: The Missing Manual pg 406

Edited February 1, 2006 by Guest

[rome33]

I have tried that. It doesn't work.

It gives me something like 12/4/0001

AND the validation itself doesn't work because it still allows me to enter a date2 that is before date1.

I am getting no error.

When you go to the validation tab

and then calculation

It says

field2datethatuserenters =

Then in the box below that

should I enter

field2datethatuserenters > otherdate

because that is not working

i have also tried a case function and an if function there but those didn't work either

I how and where to go and do calculations and validations but I am not getting the syntax right.

[smsaw]

First off, I know that there is a function called Date. FileMaker might be confused about it. Try naming your field differently.

Another thing would be that, your calculation in Validation only return a True or False (False will prompt user with a dialogue box). If you want to have the date auto-entered then you would have to put another calculation in the Auto-Enter tab.

Try this in your Validation calculation:

If ( Date1 ≥ OtherDate; 1; 0)

[rome33]

My fields are called testStartdate and testplaydate

If ( testStartdate >= testplaydate; 1;0)

in the validation field does not give any error and still lets me enter in a date that is before the testplaydate

[LaRetta]

Firstly, you don't need the If() or Case() statement portion. The simple calculation as I've indicated above works and is faster. If you aren't getting validation failure when you enter a date less than or equal to your other date field then remember that you must EXIT the field for validation to fire (or commit as Soren indicated) depending upon what you're doing. And be sure BOTH fields are data-type of DATE.

But validation will NEVER work just by typing the date. I would re-check your field types. It sounds like your fields are NOT date fields. Otherwise, you might want to post your file so we can see if we can spot your problem.

Remember too that this should not be an Auto-Enter calculation; but rather a Validation calculation ...

Your date examples are confusing. It would help to use REAL field names. Remember that validation MUST PASS. Re-read my original description of the two dates. It appears that you might have your two dates backwards.

LaRetta

[Goopie]

Im still confused on exactly how I fire a validation upon exiting a field rather than validating when a record is committed.

Can this be clarified?

[LaRetta]

Soren's comment about validation vs. auto-enter on commit really shouldn't affect your situation. Have you tried putting the validation on the date field as described and then clicking out of the field or clicking into another field? Do you receive the validation error?

Here are the specific steps:

1) Date you are checking against - let's call it OriginalDate. Make sure it is a DATE field (whether a calculation date or a standard date field).

2) Date your Users will type into - let's call it UserDate. Make sure it is a standard DATE field.

3) On UserDate, select Options > Validation and select 'validate by calculation'. In the calc box, you will type:

UserDate > OriginalDate

... select your REAL fields to replace these example field names ...

4) Enter a validation message and uncheck 'Allow User Override.'

5) If the UserDate is greater than OriginalDate, your validation will PASS and you will receive no error when you click out of the field. Otherwise, your validation message (something like, "The UserDate must be greater than the OriginalDate. Please correct your entry") will pop up and require your Users to correct the UserDate.

If, after stepping through each point, you still have problems, I will post a demo file if you wish (or you can attach your file). Validation IS a boolean test. Your test will either pass or fail. Hang in there! You'll get it! But it helps to know SPECIFICALLY what you did to try our examples; otherwise, we have no idea how to help you through it, okay?

LaRetta :wink2:

[Goopie]

Thanks Laretta

Btw I'm not the person who posted about the date problem.

I'm trying to validate a SalesID field so that a user cant get past that field without entering a valid Sales Id. I just tried a validation calculation IsEmpty ( SalesId ); that doesnt work either. The Sales Id must be a member of a value list derived from the Staff table

I want the validation to fire after the user leaves the field.

Any suggestions appreciated.

[Søren Dyhr]

Laretta read the text snipped from "The missing manual" when more fields values are involved is it where the problem arises.

What I crypticly suggested is to use this principle that Comment shows in his template in this thread http://www.fmforums.com/forum/showtopic.php?tid/173247/tp/0/all/1/, where you bolster it by making autoenters change the colours of illegal values in the globalfields and prevents the creation before they are corrected accordingly.

--sd

[rome33]

LaRetta,

I have been doing it the exact way you say to do it and what I have found is that it doesn't work because the ORIGINALDATE is in a different table than the USERDATE.

If you could give an example of a two table structure where the ORIGINALDATE is validating against the USERDATE, I would really appreciate it. I have been banging my head up against a wall.

SOREN!

If you could give an example of that color change upon illegal entry, I would love that.

I saw a filemaker pro scripting book somewhere on the web but I can't find it anymore and the missing manual is very little help.

thanks!

[LaRetta]

Yes, it makes a difference if your related table is the 'many' side of a one-to-many (to your main table). FileMaker will only validate UserDate against the FIRST related OriginalDate.

Example: Your UserDate should be greater than the most RECENT OriginalDate. FileMaker currently will only see the FIRST related date so the validation will break. If you wish to validate against the most recent related date then you need to sort the relationship descending by the OriginalDate so the most recent becomes the FIRST related record (see attached bare demo). Is this similar to your situation? You also haven't specified if the UserDate could be left empty. If so, you would want to modify the validation to allow Users to continue working without a UserDate. I've modified my validation calc accordingly.

I feel like I'm working a bit blind and I have no sense of your structure or process. I've never had to validate against a related table. It would help to know what these dates represent. But if your related table is your 'many' side then this should fix you up. If anyone sees problems with this, please speak up.

LaRetta

test.zip

[Søren Dyhr]

I've never had to validate against a related table.

I'm confusing you, the original question were in part this:

It needs to be validated by being greater than a date in another field.

And it's all it takes to demand the record to be committed before the validation works, but if the user tabs from field to field in a larger set of fields will it prospone it into being quite out of context.

As long as we let the validations deal with the single field alone will it speak up accordingly ...but when referencing another field is going to be WAY Way too late, if the user tabs from field to field. The ideal is like unique or range!!! They reacts immediately!

--sd

[LaRetta]

Well I am at a loss here. I rarely use field-level validation because it isn't User friendly AND I've had it fail to respond when User activates a script (it validates sometimes on another layout), probably because of the reasons Soren mentioned. But, without providing tight lockdowns (restricting User movement), other methods can also be problematic. A button to fire a validation script can be ignored by a User and they could just close FM. There are so many escape routes.

Field-level validation will stop a User from exiting FM until it is corrected. But User could just shut down their box. Globals are great but, unless you remove all exits, User can still get away without firing a validation script. I usually cheat ... if I need to provide strict validation, I use SecureFM to remove all windows widgets, lock the window in place and refuse to let them leave (or even exit FM) except via my button (which does the validating) and then I walk the User through their corrections.

I DO have a few places where I use standard FM field validation and I'm unsure if it should be eliminated entirely from our bag of tools. I can't see an efficient way to handle this! How could we use unique or range with something like this? I tried using a global UserDate instead but the same issues remain. Bottom line ... I'm lost on the best way to ensure validation unless you lock it down fully (either with window running pause script or via plugin).

I will add this issue to my list of 'must figure out.' I gave the standard FM answer for field validation but I now seriously question it. But I have no better answers at this time. I hope others will ...

LaRetta