Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Populating most current info into a field from multiple tables


Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Create New...

Important Information

Terms of Use