Selectively auto-creating records in a Junction Table

I wanted to figure out the best way to tackle my specific situation. I’m pretty sure it’s a fairly commonplace problem, so I’m sure there must be a solution that already exists for it, only I can’t seem to figure out the exact configuration I need.

I’m setting up a base for a friend who runs an interior design studio. In this base are the following tables:

  • Clients: All her clients and their info
  • Projects: All the projects the studio takes on. This table contains a linked record to ‘Clients’ so that you can see which project is linked to which client.
  • Vendors: All vendors the studio works with
  • Product Catalogue: This table contains records for every product (furniture, wallpaper, soft furnishings, flooring/tiles, lights, sanitaryware, appliances, etc). This table contains a linked record to ‘Vendors’ so that you can see which product comes from which vendor. This table also contains fields for size, unit price, image, etc for each product.

Now I need to be able to assign specific products to specific projects and then input details that are unique to that specific project-product relationship. So I created a junction table called “Project Orders”:

  • First Linked field links to Projects: So I can choose a specific project
  • Second Linked field links to Products: So I can choose what product to assign to this project.

So for example, for project “ABC Residence”, I may assign “XYZ armchair”. Now for me to add details specific to each order, the junction table also has lookup fields (e.g. product image, unit price, product type, etc). And it also has its own fields (e.g. Quantity, Order Status, etc). So for ABC Residence, I may be ordering 2 Qty of XYZ armchair. Therefore total cost will be (unit price x quantity). You get the picture. The primary field of the junction table is a computed field – it concatenates the project name-location-product type (e.g. “ABCResidence-Bedroom-Armchair”)

Now here’s my challenge:
In order to create a new item in this junction table, I have to manually create a new record, select the linked project from the projects table and select the linked product from the product catalogue. But the problem is, the product catalogue is a vast table with several items under several categories. So if I click on this linked field, it’ll open up an endless list of items from the product catalogue. It’ll take forever for me to browse through the list and decide which chair I want, or which sofa, or which light fixture. And I can’t reliably use the search feature either, because I may not always know how a specific product is named (e.g. in the screenshot below, one item is called armchair, but the next one is called wing chair and another one is called lounge chair, so if I search for “armchairs”, I won’t be able to see the wing chair or lounge chair option.)

In an ideal scenario:
I want to be able to go to the product catalogue (where the products are organized neatly into separate views for furniture, lighting, wallpaper, etc). So I can browse through all my products easily, and then when I’ve decided which armchair to assign for ABC project, I can link it to a project directly from within the Product Catalogue table itself. This linking is not the problem – I can create a linked field to link a product to a project (e.g. below I have created a linked field called “Assign to Project”, where I can quickly assign a specific product to a project. I can even assign a product to multiple projects – for e.g. I may want to reuse the same chair in two different projects.)

But WHEN I link it to a project, is there a way for the junction table to automatically create a new record for that specific project-product relationship?

I tried using Airtable’s automatic junction table script, but the problem is that creates junction records for ALL projects x ALL products. But I only want junction records to be created for the specific project-product relationships that I have assigned in the catalogue.

I also tried to go to the Product Catalogue table and use the linked field (“Project Orders”) that was automatically created when I linked to the junction table. But that’s very cumbersome to use because it looks like this:

Here’s what the resulting dialog box looks like:

So as you can see, if I want to create a new junction record (e.g. “AKS-Bathroom-Sink”), I have to always click “add a new record” and then fill in all the details. And even then it’s really confusing, because what if I accidentally assign this product to one of the existing records in the junction table? Ideally I want to just quickly assign a product to a particular project and then have the junction record get created automatically. Because I will be assigning multiple products to a project all at once, I will want to quickly assign a chair and sink and wallpaper and bed and dining table to the AKS project. So I can’t keep using this “add new record” option each time. Later when I’m done with all this assigning, I will go to the junction table and fill in specific details like quantity, colour, location, etc. But in my product catalogue, all I want to do is browse through my products and then quickly assign them to a specific project.

Is there any way to go about this?

Your best bet in the interim is to adjust the Script to chose one Project to create Junction Table records for.

I am in the process of creating a Custom App designed to help make the creation of records in Junction Tables easier. In its current form, my app would let you select a single “order” and multiple “products”, and with one button press create one junction table record for each selected “product” where each is linked to the chosen order. It will also have a sidebar to show any junction records that have already been linked to the order/product to help you avoid creating duplicate junction records.

I may consider an “import selection from cursor” feature to allow people to select multiple records from within a Grid view instead of adding records one-by-one. Its still in development; I’m targeting a release by mid-July.

Hey thanks so much! Looking forward to your custom app, that definitely sounds like it’d work for my situation.

I was also wondering if there’s any way to just do what I need using an automation? Like if I update a product record in the catalogue and assign it to a new project, can I use an automation to then create a new record in the junction table? I tried doing it but I think I got tripped up. Because if I assign a particular product (e.g. a chair) to two projects A & B, then the automation doesn’t know how to create a separate junction record for each instance. I would need two junction records to be created Project A-Chair and ProjectB-Chair. But instead the automation creates one junction record called ProjectAProjectB-Chair. Is there any way to configure the automation differently to do what I want?

I believe so, but you’d need a Run a script action. The Create a record can only create one record at a time.

Since you already have some sort of script, you should be able to modify it to use the array of selected projects instead of the whole table or projects to generate the junction records.

Ah okay. Would you be able to tell me more about how exactly to go about that? Or maybe point me to a resource that’ll tell me how? Because I have zero coding expertise so I’m not very well versed with scripts, so far I’ve just been sticking to Airtable’s basic automations, and I don’t know how to tinker with scripts without accidentally messing something up.

For specific guidance you’d have to provide the script you started using. Also, since I’m already working on a tool to do this, help from me in particular on adjusting the script is going to be limited.

Also I did go ahead and add the feature to select records in a grid view and use that selection within my app. Here’s a video of how it works so far:

1 Like

Of course, I understand!

This is actually exactly what I need, looking forward to using this feature whenever it’s published!