Making sure that all pieces of a package are on a floor

[snipe]

Hey guys,

So I am trying to build a catalog of what is on our floors. This is easy to do for individual items. I am trying to do it for packages. I work in furniture, and a package is something like a table and 4 chairs, a 2 piece sectional, etc. There are 3 main tables I am using: 'INV_ITM' which is a list of every item and package in my system (including discontinued ones) and various details on those items/packages; 'INV_ITM_FIFL' which is a current snapshot in time of the inventory in my locations (I am limiting my locations to just the stores, not the warehouse); and 'INV_PACKAGE' which is a list of every package and their components. See the snippets below.

What I am really trying to is make a list (catalog) of every item and package that is on at least 7 of my 8 showrooms. The packages are the tough one for me. If 1 item is missing, I don't want that package to show up on the list.

What I have tried:
-[INV_ITM_FIFL]![ITM_CD] is not null. if 1 item out of a 3 piece package was not on the floor, only that item wouldn't show up, but the package still would.
-I tried doing a count of items in the package table, vs a count of the items in the packages that are on a floor. I just couldn't get it to work though.



Notes on the snippets: [DROP_DT] is null, makes sure that I am not pulling discontinued items/packages. [INV_ITM_1] is a copy of a [INV_ITM], and is purely used for the previous sentence. The second snippet shows what it looks like when a package has all of its items (not LOC_CD isn't null). The third snippet shows what it looks like when a package is missing some components. I think everything else makes sense.

[orange]

snipe,

Here's a link on hierarchical structures - it may give some ideas.

[snipe]

Helped a bit. I went back to square one on how I wanted to solve this dilemma. I think I am on the right track. Appreciate the link!