Relationship of Components

[Con]

I am a Landscape designer and use filemaker to a limited degree to create my estimates & invoices. I have been estimating with both material & labor added together as one item for my estimate. I would like to start creating “Components”/ kits to put together groups of material, labor, subcontracting, & equipment.

My question is, I am looking for a simple example of the relationships of Invoice, Line Items, Components, and Items to get me started and build off of. Keep in mind that I might sell Items to a customer only (ie. Sugar Maple delivered) and need to put an item on the invoice without being a component.

Thanks in advance,

Con

[Fenton]

The basic idea is that a "component kit" (kit) is a combination of existing items, as a separate item. In other words, it exists as Item itself in the main table, but all of its parts are other items. So, you need a separate related table for them. This table would have 2 critical IDs: the ID of the parent kit (which is just its ItemID), and the ID of the item (which is also its ItemID) that record is for.

In other words, all of the IDs come from the parent Items table, but are in 2 different fields in the Kits table. The biggest problem is to always know which is which. I recommend totally different names, say "KitID" and "PartID".

If an Item which is a kit has 4 parts, it would have 4 records in the KitParts table. The KitID would occur 4 times, and the PartID would be different in each record. Though that PartID could occur many times in the table, as any particular part could be used in several kits.

The other required field is a "Quantity of this part in this particular kit". This is a factor to multiply by the Price per Item to get the price for this kit-part. This is assuming that the price/item is coming from the regular price per unit of the Items table, which makes it easier to update just by updating the item.

On that subject, I think the Item price per unit should have 2 price fields. One is regular number field, but the real field used for estimates is a calculation, which looks at the KitParts table for kits, or the number field for non-kits. There's other ways to do that, but that way the price of kits is automatically updated by its parts, which makes sense in the landscape/service world (i.e., it's not retail).

My first large database was for landscape estimating. I was in landscaping for 20 years, at all levels. It began in FileMaker 3, long ago, gradually updated to 5. But it never made it to 7. So it would be embarrassing to show anyone, but it had many features, and this kit capability was one of the early ones.

[P.S. The KitParts table is a kind of "self-join" table. It has few fields other than those above, perhaps date created, modified, etc.. All else is back in the parent Items table; unit label, description, etc..]

Edited March 5, 2008 by Guest

[Con]

Fenton:

Thanks for your response.

I am trying to work up a test database to see if I can put it together.

The question I have is, do you create one table & duplicate it to form a parent/ child (kit / Part) or are they two different tables with different fields?

Then do you create a join table to join to the invoice table with the kit / part tables?

Thanks

Con

[Fenton]

There are 2 tables, the main Items table, and the "join" table "KitParts" (or whatever, as long as you can easily tell them apart). There are very few fields in KitParts. The essentials are:

KitID

PartID

Quantity (of item per this kit)

There are 2 important relationships (I should create it so I can show you, but maybe this will do):

1st Relationship:

Items to KitParts:

ItemID =::KitID

Has [x] Allow creation of related records, and [x] Delete related records; on the KitParts' side (important)

This is how parts are added to the "kit" Item, via a portal in Items, but choosing a PartID, which is using a value list of all Items (which are not kits, I think).

2nd Relationship:

KitParts to ANOTHER table occurrence of Items (on the other side of KitParts):

PartID =::ItemID

no options

This relationship is just used so you can access each part's item fields, see the name, look up the price per item.

Edited March 5, 2008 by Guest