Need Advice On Database Structure

[Goetch]

Hi, I am trying to develop a solution to sell customized furniture. I have successfully developed a simple invoicing solution using the following tables Customers Invoices InvoiceLines Items Vendors. The graph looks something like this Customer -< Invoices -< InvoiceLines Items >- Vendors. This very simplified invoicing solution however didn't work for our application. What I really need is to be able to customize the items, and this is where I am right now. My items (furniture) are generally priced by the grade of fabric and any options and or finishes that the customer wants. I cam up with the following tables Vendors, Items, Grades, Fabrics, Finishes, Options

Each item only has 1 vendor Vendors -< items Each grade has only 1 vendor Vendors -< Grades Each Fabric has only one vendor Vendors -< Fabrics Each finish has one vendor Vendors -< Finishes Options are based on vendors so each option only has one vendor (for price purposes) Vendors -< Options Now I also have the following TO each fabric has only one grade Grades2 -< Fabrics2 Now I was thinking I need to have a join table to Join items with grades and produce the prices so I create a Table ItemGrades Grades3 -< Itemgrades >- Items2 I am guessing I will have to do the same thing or use the same join table for the Options and Finishes Tables, but this is where I am stuck. Not to mention I am probably over my head in trying to develop the gui for this, but one step at a time here, I need the correct tables and TO's before I start the gui. Now I also made value lists based on the relationship with the vendors so that once a vendor is selected only those fabrics, item, and grades show up and that is working, what I am not sure on how to do is tie the options, and finishes to the itemsgrades for pricing purposes.

My idea is to load all the frames, fabrics, and finishes into the database so basically a sales person can "Build" the piece of furniture and have the correct pricing.

Maybe someone has a better idea, or can steer me in the right direction… I can attach my current file if that would help...

[David McQueen]

You want to build these options right into part of the program where ythe user is building the individual invoice line items.

An example of how I would see that working from the user's prospective is:

In the first field, they pick an item type: Say Fabric, from a drop down list.

Then they pick a Vendor in the next field from a drop down list of vendors. Because they chose Fabric, only fabric vendors would show.

Then they pick an option for that fabric from yet another list.

Then they enter a quantity.

That should trigger a unit price and a final price for that item.

I would do this all in global fields in the invoicing table on the invoice mock up display layout. Then have the salesman confirm that particular item (click a button). This would cause a record to be produced in the line items table and populated with the data in the global fields. The global fields would then be cleared, ready for the next line item to be created.

The user would see the line item show up below on the invoice mock up below, confirming that the item he just entered is indeed in place as he entered it.

It is hard to be too specific from a programming point of view. In general you have to have all of the information available to be called in the drop down lists. So there is a series of related tables to build. The drop down lists are continually filtered by what the user has already chosen in the previous global fields.

HTH

Dave

[Goetch]

I would do this all in global fields in the invoicing table on the invoice mock up display layout. Then have the salesman confirm that particular item (click a button). This would cause a record to be produced in the line items table and populated with the data in the global fields. The global fields would then be cleared, ready for the next line item to be created.

The user would see the line item show up below on the invoice mock up below, confirming that the item he just entered is indeed in place as he entered it.

Not being a filemaker guru, Would the use of globals cause any issues if there are multiple people using the solution at the same time?

Also you are correct I am using relational value lists, however they all are based on the vendor, I have run into a little road block with this, in some cases we may use fabric from a different vendor than the frames vendor. They way I have the value lists set up the sales person would not be able to select a fabric from a different vendor. Is there a way to put in a case or if statement to use a different value list if certain criteria are met?

[David McQueen]

Not being a filemaker guru, Would the use of globals cause any issues if there are multiple people using the solution at the same time?

No they won't. that is why you use them. EAch user will see only his own globals. One thing you do want to do is clear all globals when you go to that layout. The last values entered into those fields when the file was under development in a single user environment will be the default values for the global fields.

Also you are correct I am using relational value lists, however they all are based on the vendor, I have run into a little road block with this, in some cases we may use fabric from a different vendor than the frames vendor. They way I have the value lists set up the sales person would not be able to select a fabric from a different vendor. Is there a way to put in a case or if statement to use a different value list if certain criteria are met?

Clearly differentiate between your invoice and your invoice line items in your mind. As far as the purchaser and the sales person are concerned they are buying an "Acme" couch, your product number 1234. This information should show in the the invoice table.

When it gets to specifying that couch, you are entering the domain of the invoice line items. So, the "Acme" couch may have "Acme" frame number 2, "Gouchi" fabric number 111, "WholeLotOfFeathers" stuffing, their material number 222, etc. These are the items the salesman will enter in the line items section. If on a new item for line items, he selects Fabric, All fabric vendors should show up on the drop down list attached to the next field "Supplier". Once he picks "Gouchi" from that list, all of Gouchi's fabric numbers and/or names should appear on the next field "Specific item". Once he picks that, unit price should auto enter and given how sophisticated you are, also the quantity and final price for that item should auto enter via lookups.

Now, the final invoice printed for the customer does not have to look like the screen the salesman is working on. Purchaser does not have to know that the supplier of the fabric is Gouchi, unit price, quantity etc. He just has to know that he ordered an Acme couch with fabric 111, green velvet.

Again, I do not know how you are set up in the real world. I did a similar project, from a structural point of view for an extrusions manufacturer. We did both the order and shipping end and then did the materials purchase order end. I ended up with quite a few join tables to pull things together.

Take your time and try to see the whole picture in the real world, then program.

HTH

Dave