My database has an "ink" table which holds data on every pot of ink we have in stock. This data includes the description, batch number, order date, order number, etc.
We also have the "print job" table which collects data for every print run on the printing press. This includes batch numbers including that of the ink. One print job usually has 4-6 different types of ink being used at a time so I have 6 fields for them to enter the batch numbers into. The "job ID" is the unique field here.
Here's my problem: I want every batch number to immediately show the name of the ink they scanned next to it as a way of double checking if the batch number matches up with the right ink.
|Cyan||100154||NOVAVIT BIO CYAN|
|Magenta||100178||NOVAVIT BIO MAGENTA|
|Yellow||100152||NOVAVIT BIO YELLOW|
|Black||100245||NOVAVIT BIO BLACK|
|Pantone 281||100165||NOVABOARD BCS 281 C-BO|
My question is: what is the most efficient way of doing this?
Right now I have a dirty way: I made 6 iterations of the "Ink" table and then linked the batch number of each of the iterations to ink fields (cyan, magenta, etc.) and this way I can display the description for each. But this feels sloppy - I'm sure there's a cleaner way that doesn't require that many 6 iterations.
One idea I had, was to use a Portal: make a separate table for the ink values and for each ink used, make a record with the ink name and the job ID number. So for example, job ID 151500 would have 5 records, one for each ink, and then the portal would list them all besides each-other. For the description, I would have to link the batch number in this new table with that of the "ink" table. The advantage here, is that I don't need a field for every ink used - you just use create records in the new table according to what inks are used.
Or are there even more efficient ways?