Add a feature to select single entry multiple times from a linked record

[Dhaval_Gorasia]

I wonder why Airtable doesn’t have the basic feature to select the same option multiple times from the linked records.

[Alessio_Monino]

hi @Dhaval_Gorasia,
why would you need that feature? What are you trying to accomplish?

[Dhaval_Gorasia]

Hi @Alessio_Monino,

I’m managing the inventory table that is linked to sales orders. Now, if I want to record sales of two units of Product A, I have to create two separate entries for each unit sold.

I know creating one more column for recording the number of quantities would solve this issue, but it comes with problems. For instance, if I want to record the sale of two units of Product A and one unit of Product B under the same invoice number (meaning the same customer has ordered these units), I won’t be able to record it in the quantity column as it will look like - 2,1 and Airtable see this as 21.

[ScottWorld]

Airtable does not see an array of 2,1 as the number 21. It sees it as an array.

However, it sounds like you need to create a many-to-many relationship by adding a 3rd table called “Invoice Line Items”.

Here is Airtable’s guide on many-to-many relationships:

Airtable Support

Airtable's guide to many-to-many relationships

What sets Airtable apart from ordinary spreadsheet applications is its ability to link related concepts together. Knowing how to represent the relationships between different groups of objects can ...

[Alessio_Monino]

hi @Dhaval_Gorasia,
@ScottWorld beat me to the answer. I completely agree with him, the most appropriate approach is to create a many-to-many relationship table called “Invoice Line items” which contains a record for each item in each sales order. You could then group this table by sales order and get very intuitive, views of the content of each of your sales orders. From there you could flag if an item type in a specific order was not delivered, was under-delivered, was over-delivered, was delivered in bad condition, etc.
Hope this helps!

Alessio
Website: alessiomonino.com
Email: alessio.monino@gmail.com

[Dhaval_Gorasia]

@Alessio_Monino @ScottWorld I think you are missing the point here. I have created many to many relationships using the junction table. I record daily sales orders in the “Sales Order” table which is linked to the Junction table. Now, for instance, I’m recording order no.1 that includes the sale of two units of Product A and one unit of product B. So, to record that I’ll select Product A from the list of all the products in the product column which is linked to the junction table that fetches product name and other details. Like I said before I have to record the sale of Product A twice but once I select Product A in that particular cell/record, I won’t be able to select Product A again and to do that I have to create one more entry of Order no. 1 to record the sale of 2nd unit of Product A. Refer screenshot. For eg. Order no. starts with 403-479… includes sales of two units of SKU - SPL050. SKU column is linked with Junction table that includes all products. Now, I won’t be able to select SPL050 twice in the same cell.

[ScottWorld]

Your screenshot doesn’t reveal whether you are actually using a junction table or not, but once you create a junction table, each record (row) represents one particular line item on an invoice. In other words, an invoice will have multiple records within your junction table, so stop trying to add multiple items onto the same row.

[timur_sisimbaev]

I think this will help to explain. As you can see in my example there’s a table which creates the total cost for MRI exam base on approved tarrifs and protocols. So as you can see the FOURTH position must include: 1x procedure coded C03.001.007 and 2x same procedures coded C03.007.007

UPD: As far as I can see the only option is to create a table which is going to calculate the price for each exam using QUANTITY field, right?

[Karlstens]

Thread necro! :skull_and_crossbones:

I too was wondering this today. The only method I know of is to create a 3rd table, which acts as an array for each of the linked records you’re needing to duplicate from the secondary table, that need to be accounted for in the master table via a lookup/rollup.

Below is a screenshot that shows how you need to create a new table whenever you’re considering a Lookup that needs to return the same record multiple times (in my case it’s the Weapons Array) - in my example below, I like to test out workflows with my spaceship builder ( :nerd_face: ) where you’ll note the multiple “Heavy Lasers” stacked. :rocket:

From this, my Master table returns a stringified JSON of each spaceship via REST API calls. Note the array of lasers in the objects data;

{
    "name": "Basic Frigate",
    "technology": {
        "chassis": "Frigate",
        "engine": "E-10",
        "shield": "Bomber Shield",
        "armour": "Armour T2",
        "weapons": [{
                "name": "Heavy Lasers",
                "type": "Heavy Gamma"
            }, {
                "name": "Heavy Lasers",
                "type": "Heavy Gamma"
            }, {
                "name": "Light Lasers",
                "type": "Light Photon"
            }, {
                "name": "Light Lasers",
                "type": "Light Photon"
            }
        ]
    }
}

Excitingly, I have built a Script Extension that implements filtered lookups when I’m building new ships - it’s so cool - and boggles my mind why it too isn’t default functionality in Airtable fields.

Anywho, for same records in an array, by using a 3rd table the end result is near-enough useful… but is a pain to set up. For example, if I decide that my “Shields” or “Armour” need to be modules that are filled, possibly with the same tech, then I need to create a table per array.

This sort of lacking feature reminds me of the recent video where Airtable was demo’ing some features, and it was spotted that a Lookup field was editable(!!!) - I would LOVE to tinker with such functionality - such a feature is up there with filtered/dependent Linked Fields in endless possibilities and ease-of-use.

Anyway, I’m interested to hear thoughts or reasons as to why or why not Link Record fields don’t allow to insert the same record more than once.