Identifying date changes
I am using Filemaker 12 to track progress of jobs. Every week, the job manager uploads an excel file containing a task list and the latest start dates for each task.
There is a table for job uploads which has the following fields :-
- Job Upload id
- Job id
- Job No
- Upload date
- Current Period
- Start Date
- Revised start date
- Date Change Flag
The script automatically imports the data from the excel spreadsheet into this table.
The field "Current Period" is used to denote which period the record relates to. So :-
1) Any records in the current upload have the value 2 for the field "Current Period"
2) Any records in the previous upload have the value 1 for the field "Previous Period"
3) Records from uploads before the previous period have the value 0 for the "Previous Period"
Date Change Flag is always set to 0.
This is automatically done by the script.
So I have set up a second instance of "Job Uploads" called "Job Uploads where start date has changed" and the relationships are :-
1) Job Upload::Job id = Job Upload where start date has changed::Job id - connecting records from the same job
2) Job Upload:Task = Job Upload where the start date has changed :: Task - connecting tasks on the same job
3) Job Upload:Current Period <> Job Upload where the start date has changed:Current Period - comparing between the current and the previous period for the current task on this job.
4) Job Upload:Current Period >=Job Upload where the start date has changed:Date Change Flag - eliminating any thing from jobs prior to the previous period.
I have then created a field called revised start date which is a look up field:-
Job Upload from previous period:revised start date = Job Upload : start date
However, I always get blanks for values in the field "revised start date" - when i am expecting dates. Thanks