Hi,
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.
