"I need a way to flag ... the newest contract record based on the most recent start date"
"Most recent" isn't clear but, supposing 3 days:
Contracts::StartDate > Get ( CurrentDate ) - 3
This calculation field ( Contracts::Flag ) must be UNSTORED
Simply establishing a relationship between employess and contracts that is sorted by effective date in descending order so that the effective start date is first, would appear to do the trick here. You might need to add an extra clause to the relationship to filter out contract records that are put into your contracts table before their start dates are effective.
Then, on your employee layout, you'd just place the desired fields from contracts directly on the layout and you'll see the current contract fields for them. If you used a portal, you'd see the current contract record at the top of the portal and any past contracts listed below.
Sorry, let me elaborate. Employee X has 4 contract records with the following start dates:
The record with the most recent start date (01/20/2010) is the 'active contract'
Thanks Phil. I have a portal set up as you described, and currently we are using it to flag the 'active contract' record using a Yes/No checkbox set, setting a text field. This process works ok but I am trying to figure out a way to automate it via a script.
Ok I've had my coffee and now I think I've got it sorted out. Gotta love mondays. Basically what I'm going to do is:
- New contract record is created for employee. Once the start date is entered a script is run.
- Emloyee ID is set as a variable. Contracts table layout is opened, filtered by employee ID, records are sorted descending on start date, all 'ActiveContract' fields for this employee ID are set to null, go to first record (newest based on sorting), set 'ActiveContract' field to Yes.
Seems like this should work ok. Sorry for using a thread as an outlet for my thought process :)
Why do you need to "flag" it?
I am suggesting that you don't need to do any scripting at all if you just need to know which contract is "current" for a given employee, but perhaps there is a need I'm not seeing in your earlier posts...
Yes sorry for not painting the whole picture Phil. I need the flag in order to filter out active contract records in other scripts, and/or for a second table occurrence of this table that only shows the active contracts.
The whole picture is: Employees get scores associated to them (stored in a related Score table) at an irregular interval. Employee X can have score records going back several years. I need to begin tracking these scores on a per-contract basis, at 'quarterly' intervals. These quarters are determined by the employee's start date. e.g. Employee X starts on 1/1/2010, their first quarter score would be an average of all scores in the date range 1/1/2010...3/31/2010.
Flagging the active contract record is the first step to getting the correct start date for each employee's contract, which I can then use to filter the scores table in order to get the average score for these variable date ranges.
I'm sure I can explain this a lot better so if you need me to clarify I can certainly try.
"I need the flag in order to filter out active contract records in other scripts, and/or for a second table occurrence of this table that only shows the active contracts."
Hmm, well that's exactly what the relationship I describe does. In a script, a reference to contracts from employees that uses the sorted relationship I describe will refer to exactly that, the most recent contract for the employee.
If you need to move from a specific contract record to a related record marked with a date that falls in that date range, you might set up this relationship:
Contracts::EmployeeID = RelatedTable::EmployeeID AND
Contracts::startDate < RelatedTable::Date AND
Contracts::EndDate > RelatedTable::Date
That filters the relationship by both employeeID and date range.
Would this work for a variable date range? The date range is essentially StartDate...(StartDate + 90)
I tried creating the sorted relationship you described but I don't understand what you mean by "add an extra clause to the relationship to filter out contract records that are put into your contracts table before their start dates are effective."
The relationship will work for any date range where you can put start and end dates in separate date fields of the same record. The EndDate could certainly be a date calculation such as StartDate + 90 if all contracts will be 90 calendar days in length.
I'm not sure from your post whether you might enter a contract with a start date that is lager than today or not. If that's not a possibility, you can ignore that part.
If it's possible that future contract records might be entered prior to when they become effective, you'd do this:
In employees, define an unstored calculation field, cToday, that uses Get ( CurrentDate ) and select Date as the calculation field's return type.
Now you can use it to filter out contract records that aren't yet effective:
Employees::EmployeeID = Contracts::EmployeeID AND
Employees::cToday > Contracts::StartDate
"Employees::cToday > Contracts::StartDate" would be the "extra clause" that I referred to.
In either case, you specify that the relationship in the contracts side be sorted by StartDate in descending order.