AnsweredAssumed Answered

Help with referencing the same table several times on the same layout

Question asked by StevieP on Oct 2, 2017
Latest reply on Oct 2, 2017 by philmodjunk

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.

 

For example:

Ink name

Batchnumber

Description
Cyan100154NOVAVIT BIO CYAN
Magenta100178NOVAVIT BIO MAGENTA
Yellow100152NOVAVIT BIO YELLOW
Black100245NOVAVIT BIO BLACK
Pantone 281100165NOVABOARD 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?

Outcomes