Matching shifts to requested times off
Goal: Return a Boolean value if an employee is available to work all time points as it relates to a call to work
Personal background: New to FM <30 days, lots of background in MS SQL and Access, I seem to have picked FM up rather well (except this)
Structure Background: I built the DB from the personell management template and added in a system where a tempoary employment agency can match employees to third party contractor's needs. This particular agency deals mostly with single day assigments although some can carry over.
I have a TO that lists all time off request for employees
I have a second series of TOs that deal with the possible assignments:
Call Dates - Individual start and end time - Child
Call Log - Where the assignment is to, a person must be able to make all call dates to be assigned - Parent
Call Sheet - The total request of all calls for a particular instance at a specific location. - Grandparent.
The call assignment page has 2 portals, one for person and one for call (parent.) I need to set a value that when a person is selected (it already sets a Variable and sets a value in an assignment session TO) that all call dates are evaluated against the employee’s time off. If any comes back unavailable, it is flagged and the flag is summed into the parent. I cannot figure out how to get a Cartesian relationship to only evaluate a specific record against the calls. I can restrict the calls down to ones beginning a specific day to reduce load. Any ideas?
Would it be advisable to loop through the Cartesian Product filtered by date and by employee to reduce load and have it calcuate a new value in a field? How do you get FM to display the entire Cartesian Product not just the first record to all sub records?