count function

[veganboyjosh]

i'm not sure if this is the best way to do this, but here's what i want to do:

i have 2 files.

file 1 is for employees, each employee gets one record. each employee/record gets its own serial number.

file 2 is for references. every reference gets it's own record. employees may have up to 4 references.

in file 2, users enter an employee number, and the name is calculated based on that.

i want there to be a field that tells me how many records in the references file there are, so as to be able to tell from looking at a field in the employee file how many of their references have been contacted.

any ideas? i've tried using the count function, but this just returns the total number of records related to one employee. i need it to store this number, so that when i create a new reference record for that employee, the counter field goes up one.

thanks in advance...

josh

[Jim McKee]

Hey Josh ...

Total number of records for a given employee:

1. Make sure your "empID" field in References allows indexing. You can check this in the Field Definitions window by clicking on the field and clicking the "Options" button, then click on the "Storage" tab.

2. In References, create a calculation field of type Number. Enter a "1" (the number 1, no quotes) into the calculation. For storage, select "Indexing On". We'll call this field "constant"

3. In Employees, create a Relationship: on the left side, select the "empID" field. On the right side, select the "empID" field in References . We'll call the relationship: "empID | References_empID"

4. In Employees, create a calculation field of type Number. Enter the following:

Case(not IsValid(empID | References_empID::constant), 0, Count(empID | References_empID::constant))

The "Case" statement will return the number of Reference records for an employee, or a "0" (zero) if there are no Reference records for an employee.

Put the field on your layout in Employees, and you're good to go.

I also suggest that you create your References records from the Employee record to prevent users from entering an incorrect empID. If you don't know how to do this, just ask

Good luck.

[veganboyjosh]

thanks, jim!

the plan is to script it all from the employee file, right now we have 3 different references for each employee, and the data is kept in the employee records. so for each question i ask a reference, i have 3 of those fields, one for ref 1, one for ref 2, and one for ref 3. sometimes refs don't pan out, so instead of recreating all those fields i figured i'd make it another file. right now there is simply a reference layout, and once i get this new file up and running, i'll substitute the new file for the old layout.

meanwhile, thanks for your help.

josh