"If" calculation not evaluating all records

July 16, 200421 yr

Newbies

This is my first time writing a database application with FileMaker. I'm wondering if someone can help with my problem...

(1) I have two tables: "Requests" and "Employees" which are related through the 'EmployeeID' field. There may be multiple "Requests" records for each 'EmployeeID.'

(2) When I enter a 'WeekBegin' date in the "Employees" table, I want it to look at each "Requests" 'RequestDate' that matches that date and enter a '1' if a "Request" exists for that date and a '2' if it does not.

(3) The problem: It works fine for the first "Employee" who has a "Request" that matches. But if there are two or more "Employees" with a "Request" with matching dates, it assigns a '1' to one of them and a '2' to the rest.

(3a) Why is it not evaluating all matching "Employees"?

(3b) I am trying to accomplish this via a calculation field "if" statement, so that this data is calculated everytime the record is viewed. Do I need a script with a "find" command first and then a "loop" instead?

Thanks so much for any info!