I have 3 sheets: Quote, Groups, and Items.
There are multiple Groups in a Quote and the same group can show up in different Quotes, and 1 item can show up in multiple Groups or multiple times in the same group.
So let’s say I have an Item named: “Camera”. This Item Shows up in Group 1 and Group 2 and these two groups are in Quote 1.
Now let’s say I want a single inline list of all items associated with Quote 1 (through Group 1 and Group 2). Since both Group 1 & Group 2 have the item named “Camera” then this list should show the “Camera” item twice.
How do I accomplish this?
Assuming that you have a multiple relation from Quotes to Groups, and then another multiple relation from Groups to Items, then you could do the following:
- In your Groups table, create a Joined List column that targets the Item Name through the multiple relation to Items
- In your Quotes table, create a Joined List column that targets the above column through the multiple relation to Groups
- This should give you a list of all Items associated with each Quote.
- To transform that list into something that could be used as the source of an Inline List, you can use the Miracle Method
Thank you @Darren_Murphy for your reply. Unfortunately the Miracle Method does not work in this case, because the kinds and number of items are not predetermined.
It will work, all you need to do is make sure you have enough rows in your helper table to cover the maximum possible number of items.