Link to another record : restrict by already use

[Valentin_GUENIC]

Hi everyone !
I’m trying to figure out if I can link to another record but with a record’s list without items yet choosen.

Here’s a concrete example : I’m trying to get an inventory of my videogame collection. And I need to create bundles which contain hardware, accessories and games. I create a table for the accessories because I need to have a view of all the accessories. I choose to create a line for each same accessory to get a count (by grouping). Maybe it’s not the right way to achieve that.
But afterall I need to decrease the count of accessories available for bundles, each time it’s used in a bundle. I want to do that to avoid errors because I need to recreate bundles in the physical world (for a tradeshow).

Hope someone understand what I’m trying to achieve.
Thanks for any help !

[Valentin_GUENIC]

A little UP for this question which I can now re-explain in a different way.
I want to be able to view linked records by availibilty number (which is a total number minus the ones already found) : accessories_available_for_bundles = total_accessories - accessories_in_bundles
It would be nice if it can be available when selecting linked records. Maybe a feature, or is there a way to achieve that automaticaly ?

[Valentin_GUENIC]

Is there a way to achieve what I’m looking for with pivot tables ?
I need to get out of the list (link to another record) records that are already linked.
Thanks for any help.

[JonathanBowen]

Hi @Valentin_GUENICHON - I’m not sure if I’ve totally got what you’re trying to do, but does this give you what you want?

You have a table of accessories of different types and quantities. These are assigned to a bundle (link to a bundles table).

The “count of accessories used” is just a count of “assigned to bundle” and the “available accessories” is a formula:

Quantity - {Count of Accessories Used}

The bundles table I have is this:

And when you create a new bundle and link to the accessories you can see the quantity, total used and total available:

JB

[Valentin_GUENIC]

Thanks a lot JB. This is a first step to what I meant.
In my ideal world, I would like to have one record by accessory because there’s unique data per record.
Then I would not like to see the unavailable accessories in the list.
That’s why I think I should use a intermediate table. And the purpose of my last question about pivot tables.
And I think I get the limit of airtable but I hope.

[Alex_Wolfe]

If I am reading this correctly, I think this latest post by @W_Vann_Hall may be helpful, or, using the newer feature of limiting linked records shown in the screenshot below. Basically, you can create a view in your Accessories table which filters out those already used (aka, those ‘linked’) so that only those that are available (aka, ‘unliked’) appear, or vice versa. The recent post I linked above dives deeper into this concept with subcategories, but perhaps just the simple limiting of linked records will solve your problem.

Hopefully this helps, feel free to let me know if not or if you have any questions (I enjoy helping with Airtable issues, but especially when they are video game related) - good luck!

[Valentin_GUENIC]

Whoooo… Thanks a lot @Alex_Wolfe because it solves what I wanted to achieve.
I didn’t see this feature before and it helps a lot.

Now I got another goal : what if I want to filter out this limited list to a common field : for example, I created a bundle with an hardware of a “console family” and want to see only accessories for this family AND available.
I think this is a limit again because I want my list to be driven by a choice made while filling entry.

[Alex_Wolfe]

I think I may need to see/know more about how the base is setup, but the link I posted before may help you walk through setting up a more complex system. I think you would need to either A.) follow the post below and setup a conditional linked record field of some sort, or B.) add a field to one of your tables which indicates the ‘console family’ so that you can further filter out specific records; likely through the use of a different view which only shows available AND for that ‘console family.’

Conditional Logic-ish Linked Records Show and tell

TL;DR version: Airtable’s recently added capability to limit linked-record selection to a specific view allows users to incorporate, to a degree, conditional logic. The specifics: ‘Conditional logic’ can mean a lot of things: Any use of an IF() or SWITCH() statement is a form of conditional logic. Here, I’m using it in what’s become an increasingly common manner: to mean the limiting of available options based on an earlier choice. For example, take a look at the following diagram. [Condition…

There’s also the option of indicating which accessory records are for which ‘console family’ or similar grouping in the primary field so when you go to select them you can see it. This is easier/more of a workaround so that you don’t have to filter by both availability AND console family, etc. Kind of like a post I made here:

Limit Link to another record by a second field Ask the community

Here are my thoughts after testing a couple options: You can have a ‘Project Number’ table with links to three different tables for each department, ‘Sales,’ ‘Accounts,’ and ‘Design.’ This would “filter” or “limit” the linkable records in your ‘Project Number’ table by department (each would have a separate column) - but, it would break the functionality of having a single table ‘Staff’ where you could view all staff together and group them by department. (This is likely the best option I …

[Valentin_GUENIC]

Thanks @Alex_Wolfe, I’ll check later when in better condition to hard-think :winking_face:
Meanwhile, I can share you the base in read-only mode (because I don’t get how to embed it here)

[Alex_Wolfe]

Ok that works. You can try creating a read-only link to the base and sending it (if you are comfortable sharing that):

[Valentin_GUENIC]

Hop ! https://airtable.com/invite/l?inviteId=invepeGUUSlL89Eri&inviteToken=51dcfa327eae2c3eb230f3a7d1c7395...