I have two tables: one table stores employee leave requests (pk_id,fk_emp_id, fk_attendance, start_date,end_date,station,comments

the second table stores daily assignments(pk_id,fk_emp_id,date,station, fk_attendance,comments)


I want a script that will change the employee's attendance in the daily assignment table based on what is enterd in the employee leave table.

NOTE: Employees will already be entered on the daily assignments table as PRESENT(attendance) at their assigned stations. Station is NONE when employee is out.

For example,
An entry on the leave table for Sue Smith on jury duty from 10/26/04 to 10/29/04 should result in her records in the daily assignments table to be updated to
ID,NAME,DATE,STATION,ATTENDANCE
347,Sue Smith,10/26/04,NONE,Jury Duty
348,Sue Smith,10/27/04,NONE,Jury Duty
349,Sue Smith,10/28/04,NONE,Jury Duty
350,Sue Smith,10/29/04,NONE,Jury Duty[/code]

Thanks in advance,
abe nash

Hi. Instead of creating a script (which you then have to make sure always runs when data is changed), why not create a relationship between Leave Requests and Assignments based on the Person and Date.

Then, you could display conflicts between the two; showing the conflicting leave in the assignments file and, perhaps more interestingly, showing any assignments that will be conflicting with a pending leave request.

Forgive me...I was not clear enough...
The assignments table is to track attendance. Who.. was where..when. More specifically this tracks court clerks who may be assigned permanently to one court part (station) but may cover for an absent clerk in another court part on any given day/week. What clerk...was at what station.... on what day?
The application was originally designed in Oracle Portal. Every morning, a dbms_job populated the daily assignments table inserting everyone in as PRESENT, at their assigned station, for the current day, unless they had been previously inserted in the table as absent for whatever reason.
The drawback with this was, for staffing purposes administration could only see who was going to be out... not in.. on a FUTURE date. Those whom would be present on any given day were not showing in the table until the job ran early that morning.
Now that we are converting to FileMaker, I decided not to insert daily but three months in advance so a staffing picture would exist long before that day got here.
In Oracle, I had SQL that looped through the start date to end date inserting the records like in my example.
Hmmmm.....

Code: Select all

Enter Browse[]
Go to  Related Record(Layout [Daily Assignments])
Perform Find (
Daily Assignments::assignment_date >=employee_leave::start_date AND
Daily Assignments::assignment_date <=employee_leave::end_date
)
Set field (Daily Assignments::attendance_status;employee_leave::attendance_status)

Will this script pass enough parameters(Name_id,start_date,end_date,attendance_status) to retrieve the correct set of records to be updated?
What do you think?