Populating most current info into a field from multiple tables

[fmadmin]

Hello Everyone,

 

I have a questions that I am hoping you could help me.

 

I have multiple tables...

 

Original Job Ticket Table

Revision Job Ticket Table

Correction Job Ticket Table

 

Schedule of Job Tickets Table

Shipping Label Table

 

The Original Job Ticket Table has a field for Job Order Number which all of the other tables have a relationship with that field.

 

The layouts for the Job Tickets are identical as far as field names, the only difference could be the data entered into the fields.

 

I want the Schedule and Shipping Tables to pull from either the Original, Revision, or Correction Tickets, which ever has the most updated information. There would only be one Original Job Ticket, but there could be multiple Revision and Correction Tickets.

 

I do have a "creation_date" field on all Job Tickets.

 

For Example: I have an Original Job Ticket 8060020

We create a Revision Job Ticket 8060020 (with lookups from the Original Job Ticket 8060020)

Later we have a correction to do, so we create a Correction Job Ticket for 8060020.

 

I want the Schedule Table to only show only the most updated information and when we go to ship the job, I need the Shipping Label to pull the most updated information.

 

Thanks for your help.

[Maarten Witberg]

wouldn't you be better of just editing the original job ticket and then keeping the changes in a log field? iow why the separate tables? Do you need to create reports of revisions? what's the difference between a revision and a correction from a database standpoint (my hunch is, since the fields are all the same in all three tables, there isn't any except status).

 

for what it's worth if you have say a job parent record and job ticket child records, you can store as many ticket records with revisions as you like in one table. just store the ticket status and a timestamp in the child table; then show only the most recent ticket in a portal in job parent record.