Ok, I think I need a junction table and I can’t quite wrap my head around how to do it. Here’s the scenario: I have an inventory of items that are linked to locations and I’d like to get a list of the items broken out by location. So I go to my Items table and group view by location to get that. But if an item is linked to more than one location, another group is created for that location combo, as seen below:
Kitchen
Dish Soap
Bathroom
Toilet Paper
Kitchen,Bathroom
Paper Towels
What I want to see is something more like this:
Kitchen
Paper Towels
Dish Soap
Bathroom
Paper Towels
Toilet Paper
Like I said at the top of the post, I think I need a junction table, but I don’t need the junction table to create a record for every combination of items and locations (as it would if I use the Airtable Junction table script), I just need it to create a record in the junction table for the combinations that are used. Is there a good way to automate the creation/updating/deletion of these linked record relationships?
Since you don’t want a junction record for every combination, where is the logic that determines which combinations you want? Unless there is some way for the automation to know which combinations you want, you cannot automation creating/updating/deleting the junction records.
Another option is to have a junction record for every combination, but have the inventory set to zero for combinations that don’t actually exist.
If you have a “transactions” table that indicates when inventory is added or removed, you can have an automation that updates inventory in the junction based on the transaction record. For example, a transaction record could state how many of an item were involved, which room they were added to and which room they were removed from. Then the automation could link the transaction record to the proper junction records and rollups could calculate the inventory.
@Kuovonne The logic would be driven by the items linked to a particular location. If an item was added to a location, then a new record should be made in the junction table. If an item is removed from a location, then it’s corresponding junction table record should be deleted, and if an item is moved to a different location, then the junction table record should be updated with the new location. I may be treading into scripting territory. I’m not a strong coder, so I try to use scripts as little as possible.
@RussellBishop That is a quick and dirty solution, but I’m planning to track inventory of items by location, and that data would have to be in the junction table. I took a look at your Automatic Junction Tables and Copying Fields guide and that seems to be similar to what I’m looking for, but I can’t quite wrap my head around how I would set it up for this use case, or if it would really work. Great demo, though.
Are you storing info in the Junction table, such as a quantity? If so, what is the harm in having a record for every combination and a quantity of zero for situations that don’t apply? You can just filter out those records from your views. I think that this would be much easier to maintain.
If you are not storing info in the Junction table other than the two linked record fields, you do not need a junction table at all.
It sounds like a person manually adds new locations. Here is one option. It is slightly different from what you suggest because it sounds like you have two places to maintain your combinations (the linked record field and the junction table). This system uses a field to create new junction records, but then relies on the junction table itself for which combinations actually exist.
Creating New Junction Records
Use a new {Add Location} linked record field in the [Items] table to be used only for adding new locations to an item. Limit it to only one value at a time to make configuring the automation easier.
When this new {Add Location} linked record field in the [Items] table has a value, trigger an automation. The automation does a “Find Records” in the junction table for that combination of item and location. If a record is not found, create the junction record and sets the quantity to 1 or some other non-zero value, possibly form another input field, and clear out the {add location} field. If a record is found, the automation just clears out the {add location} linked record field because the junction record already exists, and the user should not have added it to begin with. The user will know that the automation is done running when the {Add Location} field is empty.
Most of the time the {Add Location} field will be empty. Users should look at the junction table (or a rollup of the junction table) to see what locations already exist.
Deleting Extraneous Junction Records:
When the quantity of a junction record reaches zero, trigger an automation that deletes the record. Note that to delete the record you will need a script, as Airtable does not have a native “delete record” automation action. It is a fairly simple script and there are versions on the internet if you want a free script. I also include a script in my Automation Helpers.
I think that the bigger issue is how are you moving inventory around and how important is it that you enter the move as a single transaction? In an ideal situation, you would be able to move inventory around from one location to another as a single transaction, with a new junction record being created if one does not yet exist and both quantities being modified. However, that would require either a script or a new transactions table. Both are possible but are more complex than having users manually enter two transactions: one to decrement the inventory in one junction record and a second transaction to increment the inventory in the other junction record.
