Hi! I tried to do a search on this in the forum before posting, but I couldn't find a solution. At the moment, my manager has requested I write a "dumping" database for importing Excel timesheets for each employee. From there I will "clean up" the blank and invalid records with a script. SO FAR SO GOOD.

Now I need to validate the following:

Job Number Field: check against an existing Job Number Log database and make sure the number exists (FYI it is 2alpha 3numeric format, ex: PR562, TI744, etc.)

Job Code: make sure it's a 3-digit number between 101 and 324.

Finally, I need the database to show me all records that do not meet this criteria so I can fix them or punish the employees!

Eventually, I will write a system that allows employees to actually enter the info into a database that can validate on the spot and prevent them from making these errors; however, this is what the owner wants at the moment. Please advise if you can help.

THANKS SO MUCH!!!

The calcs you need are:

If (IsEmpty(Job Number::Existing Job Number), 1, 0)

and then...

If (Job Code > 100 and Job Code < 324, 1, 0)

Whether you implement these as calculated fields and then search or GTRR, or use them in a script is up to you.

Booleanizing (as usual), if you're going to create calculation fields, shrink them to:

IsEmpty(Job Number::Existing Job Number)

and

Job Code > 100 and Job Code < 324.

Cheers.

Forgive me for missing something that seems obvious. The (Job Number::Existing Job Number) is not recognized in my calc...you don't mean plugging in a specific job number, do you? Each record will have a different job number to assign the time to.

Example: Record 1 says Job Number AR665 Job Code 111

Record 2 says Job Number OA527 Job Code 123

ETC

The job numbers have to exist in the Job Number Log database. Does your : sign indicate the relationship between the two databases?

Job Number:: is the relationship from your job number field to the related job number field in your Job Log db. Looking back, I think Fitch meant to put IsValid() instead of IsEmpty(), by the way.

Ok, that's what I thought...

IT WORKED LIKE A CHARM. Many thanks to the both of you.

1. No, I meant IsEmpty(). FileMaker reps have said in the past that IsValid is not intended to be used in this way -- it's one of those things that works, but is at risk of breaking in future version of FileMaker.

2. I generally use the abbreviated style for boolean calcs as well. I chose to do it the long way above just for clarity.

Hmm. Then maybe I'm missing the logic here. It wouldn't be empty if the relationship were invalid. It just wouldn't exist. So IsEmpty() can also be used as a test for the existence of a relationship, even if the field referenced doesn't exist? How very odd.

Well, if something doesn't exist, you can't get much more empty that that!

True, but it's not really empty; it's NULL. The field doesn't even exist to be tested. I think I prefer using rel::constant to test validity and not rel::constant = 1 to test invalidity. It's a bit more rational.