Exclusions in links

I am creating a database for packing for a trip. I have a table with items I am going to pack. I have Suitcase/Bag into which the item will be going. I would like to document which compartment of the Suitcase/bag the item will be going. For example - Items (sock) will go into the Suitcase in the Compartment (Main).

Another Suitcase/bag is a Backpack; this has a Compartment (Computer Compartment).

Currently I have three tables - Items, Suitcase, Compartment.

If I put the socks in the Suitcase, I would like to also be able to place it in ONLY a compartment in the Suitcase. How do I write this so I can only link with a compartment in the suitcase if the item is linked to the suitcase?

I had considered placing separate entries in the in the Suitcase table, i.e. Suitcase/Bag Table - First Entry (First column Suitcase Second column Main Compartment.) Second entry (First column Backpack Second Column Computer Compartment) etc.

Advice appreciated

Mike

My suggestion: don’t link an item to the suitcase. Link it to a compartment, and link the compartment to the suitcase. All items in that compartment are automatically in the suitcase tied to that compartment. To make it easier to remember which compartment is in which suitcase/bag as you’re linking items, make the primary field on the [Compartments] table a formula that concatenates the compartment and its associated linked suitcase name.

26%20PM

51%20PM

How does one concatenate please?

Looking at the example @Justin_Barrett has shown in his post, the formula is:

{Suitcase} & ': ' & {Compartment name}

JB

1 Like