Base design advice

[Andrew_Davies]

Hi

This one is driving me nuts. Am sure I am missing something simple.

3 x tables -

1. Objects
2. Crates
3. Trucks

Every object has a crate - so that is easy. Some crates contain several objects

Crates are loaded onto the truck and I have a view which shows crates that are still to be loaded (If the Truck # field is empty it is not loaded)

BUT!!!

Some crates, which contain an object, are quite small and get packed in a bigger crate. So on my crates table, I have another linked record field, which links to itself so I can show when a small crate is packed into my big crate.

So my “Loaded” view only shows the big crates that are on the truck. The smaller crates packed inside a bigger crate don’t show as being unloaded.

Am sure as soon as I post this I will figure it out - but I have spent a good couple of hours so need someone cleverer than me to help!

Read only link to a temp base to demonstrate this is here : https://airtable.com/shrK947lGGUOBNOvq

Any tips much appreciated!

Andrew

[TheTimeSavingCo]

Hmm, off the top of my head, to retain the workflow you’ve got here, I think a possible solution would be to have an automation that runs every time the Contents - Crate field gets updated, and it would run a script that would help you with linking backwards

You could then add a Lookup field and a Formula field to consolidate it all, resulting in something like this:

I think you’d be consuming two automations every time you updated the Contents - Crate field though

---

Like you said, this seems like it’d be super simple to do, but also baffled me until I settled on just using a script to deal with it. I’m really hoping someone else has a better idea of how to tackle this workflow as well

[Andrew_Davies]

Thanks so much Adam

I keep thinking automation - but keen to avoid it if I can. Only because it is tricky to keep in sync (ie - if something changes after being unloaded)

I am thinking about a junction table maybe - so a table of “things that have been loaded on a truck” - but that feels clunky.

Thanks again,
Andrew

[TheTimeSavingCo]

Andrew_Davies:

(ie - if something changes after being unloaded)

True! We could handle this via the script as well I reckon

Andrew_Davies:

I am thinking about a junction table maybe - so a table of “things that have been loaded on a truck” - but that feels clunky.

Yeah, your current flow is a lot more intuitive

[Andrew_Davies]

Hi all

Thanks again for your tips.

I think I have got this working now - using Make (Integromat) and automation.

Keen to hear peoples thoughts though. This is how I went about it :

1. Added a Checkbox field for “Loaded”
2. Added a ‘flipme’ checkbox field and a button which triggers a script to set ‘flipme’ to true (thanks to someone in this forum for this tip
3. Added an automation which runs when ‘flipme’ is set to true

The automation then:

1. Finds all records set to “Loaded” - then runs a script to set them to false. This just clears it all out so we are starting a fresh.
2. Runs another script which sends a Webhook to Make
3. Sets ‘flipme’ back to false.

The Make scenario then -

1. Searches for records with something in the “Truck” field - and marks them loaded
2. Searches for records with something in the “Truck” filed, AND “Contents - Crate” field, then iterates through those records and marks them loaded. (These are the problematic crates which are loaded into another crate before going on the truck - and caused the problem in the first place as they showed up as unloaded.

I think this should work ok. Doesn’t use too many operations. Only disadvantage is it has to be triggered manually - but that is no big deal. (If it triggered on every change, it would use far too many operations. Same if it triggers at regular intervals).

Keen to hear what people think.

Andrew