Getting multiple ItemID's to appear in a portal of another ItemID

[Scott302]

After lurking around getting excellent info I finally get to post!

I have a Mac database of inventory items, unit cost, vendor, etc. Each "ItemID" is unique. I need to create a Kit listing layout for some ItemID's that are made up of several other ItemID's (components of the kit), a manually entered quantity and also display the component ItemID cost.

I seem to think I need a self join relationship but I cannot figure out how to get the portal to work the way I want. In the portal I want to enter the several ItemID's I need to make the kit and the quantity needed and have the portal display this in addition to the component ItemID's cost, units & vendor from their ItemID record but can't get it to work. Hope I explained it clearly enough.

Do I need a self join or is there a different way? What am I missing here?

Thanks all,

Scott

[T-Square]

I need to make the kit and the quantity needed and have the portal display this in addition to the component ItemID's cost, units & vendor from their ItemID record

If I understand correctly, I think you really need a "join" table that joins two different copies of the Item table. This join table would have ParentID and ComponentID fields, along with the QTY and the various calcs. Without the join table, where would you put that other info?

David

[Scott302]

That's what I was thinking.

I have an inventory database with ItemID (1,2,3,4,5,6,7,8,9)

ItemID 9 is a "kit" whose components are ItemID's 3,4,5 in various quantities. I want a layout where I can call up ItemID 9 and show as part of that layout a portal where I can enter the component ID's (3,4,5) , enter a quantity required for each component and have that portal show the component ID's cost, vendor and certain calculations/summaries regarding the total costs.

What do I need to get there from here?

[Fenton]

As T-Square says, you need a join table for Kits. It would be joined from the main Items table, from the ItemID. The Item in the Items table it would come "from" is a "kit" item; which is much the same as any other item, except it is composed of mulitple other items. The price can be just the sum() of its component items (times their quantities in the kit); or it can be entered manually (you can see the sum of its items also).

However, in the join table it would tie to the Kit_ID (which would therefore not be an auto-enter serial). The join table would also have an Kit_ItemID, which would join "back" to another TO instance of the Items table, from the Kit_ItemID to the ItemID.

It works fine, and is easier than you think. The hardest part is to label the fields and TOs properly, so that at any time you know which you're addressing.

You enter the "quantity" per each item in the kit in the join table.

[Scott302]

Thanks,

I was missing the idea of the "Kit ItemID" and things are looking up.

Working great now.

Your help is APPRECIATED!!

Scott