Is the data static or subject to change?
In other words, on Jan 30, fields 1, 2 and 3 have values of 5, 6, and 7 respectively. When you pull this up at a later date will you still see 5, 6 and 7 or is it possible they may have changed.
Answering this question tells me whether you need to preserve a copy of the data or can just create a relationship that refers to data from a specific time period.
Thanks for replying, Phil. I need to preserve a copy of the data as it appeared at a point in time.
Job 1 Status can be "Planning", "In process" or "Concluded"
If it's in Planning on Jan 30 and Feb 30, in Process on Mar 30, and Concluded thereafter, I need snapshots of all of those states. (And if the status is "Concluded" and hasn't changed for the past month, we can refrain from making a snapshot of that record for the purposes of this report.)
It would be much simpler to record the dates on which a job has aquired each status.
Thanks, comment. The only problem I can see with that is that there is a Job Status value called "on hold", which could be valid on multiple occasions. Example:
Apr: Proposal submitted
May: On hold - see status notes
July: On hold - see status notes
I can envision a few different approaches:
1. Spawn a new table occurrence each month that transposes each job's status into a static value.
2. Run a month-end report that compares job status values against the values in the last month's archived database backup
3. Somehow record the history of each record's job status in an indefinite, expanding field (not sure if this is possible, or how to parse that data later)
The correct answer would be close to #3: create a new record in a related table whenever a job status changes, e.g.:
Status: On Hold
Notes: Blah, blah.
I agree with Comment.
You can display your "Journal" table in a portal sorted by date to see both current and past status. List type reports will also work well.
Thanks, comment and Phil... sorry, but I'm pretty much a raw beginner with FM, so I can't really envision how to implement that - would you be kind enough to spell it out a bit more?
Okay... let me take a stab at this.
To detect a change in the JobStatus dropdown of a record, I attach an OnObjectSave script trigger to the dropdown.
The script will create a new record in the "JobStatusChanges" table. That record will need:
-the related Job record #
-the new value of JobStatus
How do I pass these values into the new record?
Better yet log the status in a portal of related "Status Change" records. Record each status change on a new row in the portal.
No script needed.
I don't understand how to do this with a portal. Can you walk me through it?
Let's assume that your job table has an auto-entered serial number field: JobID
Create a second table, StatusLog, with at least three fields: JobID, Status, Date
Define a relationship to link the two tables:
JobTable::JobID = StatusLog::JobID
Enable the "Allow creation of records via this relationship option.
On a layout that refers to your Job table, add a portal that specifies StatusLog as its table occurrence.
Place the fields Date and Status in this portal.
If you want the most recent JobStatus record to be at the top of your portal, specify a sort order that sorts by date in descending order.
Now to log a status change, simply enter a date and status in those two fields in the bottom empty row of the portal.
Note: the date field can be defined to auto-enter the creation date and you can format Status with a value list to streamline the data entry.
I'm not sure we're talking about the same thing. I don't want users to have to enter anything in the portal - I want any changes to the record to be automatically reflected in the portal.
When I follow your directions, I just get an empty portal. Am I missing something?
Somehow you will need to record the change in status. My assumption was that an authorized user would "log" the change in status in the portal.
The portal will remain empty until a related record is created in JobStatus to show the status change. The simplest way to do this is to enter data in one of the two fields in the portal.
To show current status as it is currently recorded in your Job table, you'll need to move this data into the new related table. This can be done with the Import records tool in a one time operation.