Multiple Quantities in the same Field

[Tyler_Nelson]

This is a tough one to explain so I am going to do my best. If you need more information on what I am trying to do, just ask.

So, I have two tables. One is titled “W/O’s Closed” and the second table is titled “SHS Products.” The meaning of SHS is irrelevant, so I will not go into detail on what it stands for. Anyways, in the “W/O’s Closed” table, there is a field titled “Items Sold:” in which I have linked to the “SHS Products” table. However, what I am trying to do, is when a customer purchases a product in multiple quantities, I can not link the same record from “SHS Products” in the “Items Sold:” field on the “W/O’s Closed” table to indicated the customer purchased two of the same products. I’ll try to attach pictures of what I am wanting to do, so that you can get a better idea. Thanks for any help in advance.

[Tyler_Nelson]

[W_Vann_Hall]

As best I know, you cannot link multiple times to a single record. (I’m not exactly sure why, as a linked record field is at heart an array of record IDs, and other Airtable arrays can have multiple items with identical values. It would be interesting to know if this was a limitation in the UI or if it is enforced in the core, as the API documentation does not explicitly state each entry in a linked record field must be unique.)

The expectation is one would use a quantity multiplier to indicate how many of the item was purchased. And, yes, in that case you would not typically be able to get by with only two tables; instead, you would use a junction table between your ‘W/Os Closed’ and ‘SHS Products’ tables. On one hand, using such a table increases slightly the amount of keystrokes/clicks to add an item to the order; on the other, it makes such things as quantity discounts and changing prices without invalidating historical data much easier…

[W_Vann_Hall]

Along the same lines, I modified the Product Catalog & Orders base from Airtable templates to make use of pricing schedules. (The templated base already made use of a junction table to link orders and products with a quantity modifier.) You can find my announcement in the Show and Tell forum.

[Luke_Chesney]

Thank you very much for your continued contributions to this community, they are invaluable.

Do you know if this issue was ever resolved without the use of a junction table?

It appears that it is still impossible to link multiple times to a single record, which is causing me huge grief with the base that I am working on.

[W_Vann_Hall]

I’m thinking this is a feature, not a bug, and not likely to change — for one thing, the UI is set up to exclude already-linked records from the list of potential targets. It appears this and the inability to deduplicate lookups are a couple of design decisions we’ll have to continue to find ways to work around, when possible, and do without, when not.