Validating Field Script Based on Another FMP File

[snackcakes101]

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!!!

[Fitch]

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.

[-Queue-]

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.

[snackcakes101]

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?

[-Queue-]

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.

[snackcakes101]

Ok, that's what I thought...

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

[Fitch]

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.

[-Queue-]

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.

[Fitch]

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

[-Queue-]

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.