Make a formular field check for certain conditions and populate content


I’ve got a database that organises garments and their production prices.

The garments are divided into ‘Sampling’ and ‘Production’

record 1 will be an order for Garment A - Sampling
record 2 will be an order for Garment A - Production
record 3 will be an order for Garment B - Sampling
record 4 will be an order for Garment B - Production

The price of a production garment is based off the price of what the sample cost to make so its quite hand when pricing a garment to have the price shown from sampling record in the production record.

Is there a way I can get a formular field to figure out if record 1 is dealing with the same garment as record 2 and then populate the price to record 2 because it knows that Garment A = Garment A and record 1 is a sample?


Hi Kim,

It seems to me it would be much less of a headache if you create separate tables for Garments and Orders.

In the Garments table, you will have columns Name (A, B etc.), Type (Sampling, Production etc.) and corresponding price for each pair of Name-Type. I recommend to create a column ID with meaningful description for each row which then makes the later lookup less painful.

Then in the Orders table, you can have Garment column which is a link to the above Garments table and Price column which is a look up to Garments table’s Price column. For each record you can select the associated Garment ID and then its Price will be populated automatically for you.

This kind of approach will help you to be more organised about your data thus scaling up would be easier.

Kind regards,



Thank you for your input Alex.
Taken your comment to heart think I need to start getting my data organised