self joins and filtered values?

[skearton]

Hi once again,

I'm stalled at this point of my database and would appreciate any help. I have looked at many examples and walked through J. Starks recursive database info but I still can't seem to get a grip on how to do this for my situtation.

I have an Assembly Table, AssemblyLineItems table and a RawMtrls Table. My layout is based on the Assembly Table and I have a portal for the assembly line items table. Basically, the assembly table is for a sales rep to be able to create a new "Sign Type" which can be made up of many raw materials and come up with a price. This document would also become the basis for a Bill of Materials to create such sign type. J. Starks example worked great if I didn't also have to deal with customizing a raw material. For example, a sign type could have 3 raw materials that come from a supplier that we don't do much too, but the fourth raw material is acrylic that comes from a supplier in 4 ft x 8 ft sheets. We would need to cut a piece of it to 6 x 6" and then paint it a certain colour.

What I wanted the sale rep to do is create a new sign type, assign it a ID (different from the auto serial created ID) and a customer. Then they would select from a drop down list of 1 to 30 Inventory Categories, like 03 Acrylic. Based on the choice of that value a subcategory list would populate that breaks the choice down even further. In the case of 03 Acrylic, they would see Clear, Translucent and Opaque. If they select Clear then they would see what is in that category, like .080" Clear Non Glare Acrylic, 1/4" Clear Acrylic, etc. Then I would like for them to be able to select their choice like .080" Clear Acrylic and have it become a new record in the Assembly Lineitem that they would see in the portal. Now because they don't need a full sheet of acrylic, there would be fields that would let them enter the custom height and width of the piece that they require and this would calculate the cost based on a square footage requirement from the cost of a full sheet.

For clarity, the RawMtrls table has both the Inventory Category and the subcategory in it. So each raw material has been assigned each of these.

Where I keep getting stuck is how to create the self join relationship and calculation fields to create the drill down in the inventory category and subcategory.

If someone could outline for me how to do this based on the info above it would greatly be appreciated!

[efen]

Do you have enough tables for your situation and what you require?

You mention 3 tables - Assembly, LineItems and RawMaterials. (I also assume that you have a customers table.) You also mention that the RawMaterials table includes fields for category and subcategory. Will this lead to partly duplicated records, differing only by subcategory?

Would it be better to have a categories/subcategories table instead of these fields?

Your value lists of sign/product/material attributes could then be created in a simpler way.

The specific information about the material and its attributes for a new sign/product could then be created and stored in the LineItems table when the record for the new sign/product is created. This will also help when a customised product is to be created.

[Søren Dyhr]

Where I keep getting stuck is how to create the self join relationship and calculation fields to create the drill down in the inventory category and subcategory.

It looks like a recursive structure you're after - read up upon it here:

http://jonathanstark.com/recursive_data_structures.php

...and there is even a template you can digest with it:

http://jonathanstark.com/downloads/Inventory.fp7.zip

Another BOM solution is to be investigated by looking at this:

http://www.spf-15.com/fmExamples/BilloM.fp7.zip

--sd

[skearton]

Thank you for your replies!

I did read Jonathan Starks recursive data structure and set it up in my system. It is probably the best example I've seen so far and is fairly much of what I need. I just need to be able to customize some raw materials further to create a price for the estimating / assembly tool. I suppose in technical terms it would be creating another version of the raw material but I was hoping to keep it real simple for the sales people using the tool.

Back to my example of the acrylic sheeting. I need to keep the price of the 4 x 8 ft sheet so that I can use it to auto populate Purchase Orders. But I also want to use the same raw material in the estimating tool. The sales rep would select it as a child product for the assembly they are creating to price, but they would only require a percentage of that full sheet. So, I know I can create fields that let them enter the height x width of what they require; this in turn would calculate a cost based on the percentage of the full sheet.

In a sense I guess when we need to customize some raw material it could be classified as a new raw material. I was thinking that the raw material database could become extremely large with all of the different custom size variations of acrylic that we would make. I suppose that could be managed with further filtered results when someone is looking for acrylic for an estimate, you could show another category base on the widths or something like that.

So, if I approach it this way, I'm assuming I need someway from the Assembly table (the one that the sales rep would see) to be able to create new raw materials on the fly without it being too complicated for them. i.e if they can't find the size of acrylic they need, for example 6 x 6", they would select the full sheet, enter the dimensions they require, and when they commit the record it creates a new raw material in the database, this in turn is what they will see in their assembly portal.

I was hoping to use the assembly details for a product as a work order for production, but I need to further create details for each raw material that is unique to that particular assembly and job run. For example the message on the sign, would bring it down to the nth degree. So, I guess I'm fishing for ideas on how to design this, and still be able to track inventory on selected items.

Now, my brain is starting to hurt...