Need to figure out the best way to create the tables to handle this.

[preston2]

First let me thank all you Alphaholics in advance.

I am creating an application for garment decorators to use when giving quotes and creating invoices. Pricing in the garment decorating industry is very complex as each job may have a multitude of variables that will be different from previous jobs. Besides the garment price based on quantity, sizes, style and color we also have to figure the decorating price and that is usually based on quantity, print locations and amount of print colors. The method normally used is what we call a pricing matrix.

My first attempt at this has resulted in minor success. I am trying not to create a table with 400+ fields in it so my idea was to create a primary table that contained 3 fields. One of those fields would link that table to the child table using a one to many as my thinking is the primary table can have many records from the child table. Then the child table would contain the information for each price break point and print color counts. It works, sort of and before I spend countless hours trying to figure out the best way to do �auto-increases� and allow them to create new pricing matrix�s with empty field data so they can create custom pricing, I need to figure out if I am even going about this correctly.

These are examples of some pricing matrix�s in other programs (they where created using FIlemaker Pro).





Any suggestions oh wise ones? I know I will have a lot more questions later so thanks again to all here.

[Keith Hubert]

Re: Need to figure out the best way to create the tables to handle this.

Hi Preston,

The matrix is a price chart only. The real work is done when creating the Order, the prices are arrived at by the user first selecting from a lookup, the garment and then entering the quantity, the final value changes by the choice of options.

[preston2]

Re: Need to figure out the best way to create the tables to handle this.

Hi Preston,

The matrix is a price chart only. The real work is done when creating the Order, the prices are arrived at by the user first selecting from a lookup, the garment and then entering the quantity, the final value changes by the choice of options.

Thanks for the reply Keith.

Correct to some extent. When creating a quote or invoice the user selects a garment style number with the correct color and size group. This auto populates the line item of the quote form with the base information. The the user keys in the quantity amount for each garment size on that line item. The application then adds them up and applies a default markup to them. The user then keys in the amount of colors to be printed and the locations of the print/s. I have most of it working to this point but it is also at this point that the application needs to take the garment total count and print color count, look at the info in the pricing chart that is correct for the customer type and calculate the final price based on all of that. I understand that I will still need to figure out how to make the program do all of that correctly on its own based on the information from the order entry form.

Garment decorators are a strange brew and want their interaction with a program to be as simple as possible without the need to select a 100 things each time just to get a price. They want to setup the pricing matrix once for each customer type and then have the program come up with the correct pricing based on quantity, sizes, print colors and print locations. I may make a video of some existing programs in action to clarify the flow I will be trying to achieve.

The thing I am trying to figure out right now is would it be better to put all that price break and print color pricing in one table making the table have around 400 fields or would it be better to continue on trying to make a primary table linked to 30 records in a child table? The one big table seems to be the easiest to pull data from and to create new pricing matrix's with but I am afraid a table that large will slow the application down.

I am a complete newb when it comes to Alpha and databases so I am trying to wrap my head around all of this. I have bought all the books and use the demo videos as well. I used to write applications with turbo pascal 20 years ago but database programming and design is a bit different.

[Keith Hubert]

Re: Need to figure out the best way to create the tables to handle this.

Hi Preston,

Each element of a garment would have to be entered ie Garment, quantity, sizes, print colors and print locations. From your matrix, it seems there would be a choice of 12 colours. All of this can can be done but will require a certain amount of xbasic code to automate the end result. I would be pleased to help you as much as possible.

[preston2]

Re: Need to figure out the best way to create the tables to handle this.

Hi Preston,

Each element of a garment would have to be entered ie Garment, quantity, sizes, print colors and print locations. From your matrix, it seems there would be a choice of 12 colours. All of this can can be done but will require a certain amount of xbasic code to automate the end result. I would be pleased to help you as much as possible.

Thank you very much Keith.

I went ahead and just created one table with a ton of fields. The one I am creating has 14 different price break points for the first and second location for a total of 28 break points and then there is a choice of up to 14 colors. I am now working on figuring out how to code some buttons that will allow the user to increase the price per color across each break point by an amount specified in another field. 28 break points so there will be 28 buttons for this. It will need to look and the value in the first color field for that break point, get the increase by amount from another field or variable and then apply that to the remaining 13 colors for that break point. So if the increase amount was $0.50 and the first color in that break point was set at $3.00 then clicking the auto-increase button would need to set color 2 at $3.50, color 3 at $4.00, color 4 at $4.50 and so on.