Database structure for objects / crates on a touring exhibition

Airtable Questions General Airtable Questions
1

Hello all

I am a Project Manager for a large touring exhibition, and have been using Airtable for approx 2 years to track the exhibitions objects (insurance value, care guidelines, condition reports etc).

I feel like I am (or should be) fairly competent in Airtable, but am stuck on something I think should be or is fairly basic. I suspect I may be over complicating things - so am keen to hear your thoughts. I am also hoping that writing it down in this forum, may help clarify things in my mind - I have been tying myself in knots over this!

At the core of it I have two tables - Objects and Crates

Objects are the items on display in the exhibition. Fields for values, owners, photos etc
Crates are as you would imagine, the things the objects travel in.

  • Some objects are very small. They are wrapped in bubble wrap, labelled with their code then put in a box. There are, or can be, several small objects in one box (many to one). In Airtable I classify a box as the same as a crate (it is still storing something)
  • Some objects are very large and travel in 2 pieces. In this case, each piece is stored in a different crate (one to many)
  • 60% of the objects travel in their own dedicated crate (one to one)
  • Going back to the small objects, with several stored in one box - that box is then in turn stored in another crate. So one crate can have an object, then another box with several objects inside.

I am having difficulty structuring it (for some reason). I really feel it should be quite simple and a perfect application for a database.

Issues I have -

*In the crates table, I have a linked field back to itself called “Stored in”. This is so that a small box full of lots of objects, for example, can show which larger crate it is in. But if I check the record for that larger crate, the small box does not show. I have to look at the Small box record.

  • If I do it the other way, and instead of having a field “Stored in” linked field, I have a “Also contains crate” field, I have the same issue in reverse. I can see what is in each crate, but I can’t see which crate that Small box is stored in

The next complication is - the whole exhibition travels in 5 sea containers, and I need to log clearly and concisely which sea container which crate is travelling in.

I am sure this should be quite straightforward and I am likely over complicating it or missing something obvious. Just hoping one of you clever people can point it out to me before my head explodes!

Any help or advice much appreciated

Andrew

2

It sounds like you’re missing the Boxes table.

Each box in the boxes table would be linked to one (or more) objects.

Then, each crate in the crates table would be linked to one (or more) boxes.

3

Hey Scott

Thanks so much for this, and sorry for the slow reply. With the shenanigans in the Red Sea we are having to Ship from the UK to New Zealand this week instead of mid February (opening in Wellington 1st June), so it has been CRAZY getting it all ready!

Thanks for the suggestion. I am reluctant to add another table, as a box, for example may not always travel within an another crate. It may go to one venue inside another crate, but then may get couriered separately to the next venue if that makes sense.

I have got it close, and maybe this is as close as I am going to get. I’ve set up a dummy AT base to demonstrate it here -

The only issue I have with this is that I would like to show the total insurance value per container with the correct number of pieces per container on one view - but I don’t think that’s possible.

Issue is this -

Say 3 boxes (Box 1, Box 2, Box 3) - with a combined value of £5,000 travel in Crate 1. I have a linked record from Box 1, Box 2, Box 3 called “Travels in” - which links to Crate 1. In order to get the number of pieces correct, I set a filter to filter out any record with the linked field “Travels in” empty. That way, as Box 1, Box 2 and Box 3 are already in a crate - it doesn’t skew the figures.

However, as the values of the contents of Box 1 etc are being filtered out, the total value in the view is incorrect. So I have to have a second view with all the records showing so that the insurance figures are correct - but the number of pieces is wrong.

On the other hand - if instead of a “Travels in” linked field, I have a “Contains crate” field instead, I can get the same issue - I can’t have just one view with the correct count AND the correct values.

Maybe I am being pedantic and just need to set the views and forget it?

Have a good day buddy!

4

Sorry buddy, I don’t think I understand your situation or your database enough to help guide you on this.

Perhaps others will chime in below with their opinions!

5

No worries Scott. My use case is quite specific and I am probably not explaining it well!

I have just manually (urrrggghhh) updated some insurance values and so thought if I explain what I did, it may help clarify what I’d like to automate.

For simplicity say I have

2 tables - Objects and crates
5 Crates numbered crate 1 to 5
6 objects numbered A to F. Object A is worth £1,000, B is worth £2,000, C £3,000 and D £4,000 etc

Object A&B travel in crate 1 -
Object C splits into two so travels in crate 2 and 3
Objects D,E and F are very small so travel in crate 4 (which is very small) - which then travels in crate 5. I have a field in the crates table called “Stored in” which shows that crate 4 is stored in crate 5.

The total insurance value of the objects is £21,000 and crate count is 5. That’s the info I need for my shipping manifest.

I created a view of crates, which has a roll up of insurance values in each crate from the objects table. I then filter it so it only shows records where then”stored in” field is empty. That way I get the correct count of items for the manifest.

But of course the insurance values are then incorrect., as objects C, D and E are not rolled up anywhere. If I don’t filter it, the insurance value is correct, but not the count of items.

I guess I could move the items that travel in a crate, which then travels inside a crate into a different table, but they don’t always travel that way. They may travel on their own.

I am sure I’m missing a trick.

To update it manually at the moment, I have added a manual (yuk) insurance value column and enter the missing insurance values in there - then a formula field which adds that manually entered value to the rollups.

My head hurts

Andrew

6

The best chance to get response is to break down the individual issue and then ask for response to that particular step you are facing.

Just skimming through your multi-relational database, one thing came to my mind is junction tables. Maybe that would help somehow? I am not expert in this area, but I read they do help in some instances with creating relationships.

I guess you may also need a container table too. Then you can have your insurance per container with whatever rules you need, maybe roll up, and there set formula.

You probably can same roll up formulas in crate table to get insurance value you need.

I think this needs just take a big sheet of paper and doodling first. That’s what I do. That also clears the mind :wink: