I'm working on building a solution for Job Tickets for a printing company.
I don't have all of my structure built yet, but the trick I'm trying to come up with now is thinking about how I am going to print my job tickets (and maybe still evaluating the overall structure).
First, some background on my structure thought…
I have a parent table called JobTickets. Each JobTicket can have multiple Jobs on it. A single Job could have multiple PressRuns if it is a complex Job (for example, a booklet with cover on one stock and the guts on a different paper). One PressRun could have multiple Jobs ganged on it. It is also possible that some Jobs will have zero PressRuns attached to them (maybe just creating artwork, maybe pulling from inventory, maybe outsourced, etc).
So, I have 2 child tables: Jobs (individual jobs on that JobTicket) and PressRuns. Each JobTicket could have 1-30+ Jobs on it, and same with PressRuns.
So, on my entity relationship diagram, I have something like this:
JobTicket ----< Jobs >---- [JobRun join table] ----< PressRuns
JobTicket to Jobs is 1-to-many. Jobs to PressRuns is many-to-many. People will enter the Jobs onto the JobTicket first, then start entering PressRuns, and will be presented with a picker window to assign the relationships.
Now, on to the printing problem…
Of course, I want to print the job ticket with all of this info on it.
Much like an invoice with varying number of LineItems, I get that to print them all, you will want to print from a layout based on LineItems. That way, you can have a line for each record, and not worry about things not fitting in your portal.
But, in this instance, I have 2 child tables with varying number of records (Jobs and PressRuns) that I want to print on the JobTicket.
Is this possible? Or am I going to have to have 2 forms, one based on Jobs and one based on PressRuns for each ticket?
I would want the Jobs to be listed all together, then lower on the JobTicket, all of the PressRuns. Most JobTickets will only have a handful of Jobs and PressRuns. 1-6 Jobs per JobTicket is very common. For those, all of the info would easily fit on 1 page. For more complex JobTickets with dozens of Jobs, it will require multiple pages.
Perhaps my print script could first get a count of the related records, then either print from a JobTicket layout with enough room for 95% of the JobTickets with 6 or fewer related records, or print from 2 different layouts based on those 2 child tables? Seems overly complicated, but I'm not seeing a more elegant solution.
If you have any other ideas for me, I would appreciate it.