Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Ken Murphy
  Where is Ken Murphy?
 Springhill
 Canada
 Ken Murphy
 To: Kevin
  Where is Kevin?
 edwardsville
 Illinois - United States
 Kevin
 Tags
Subject: RE: Table setup
Thread ID: 118706 Message ID: 154897 # Views: 1 # Ratings: 0
Version: Visual FoxPro 9 Category: Visual Basic
Date: Thursday, December 20, 2007 2:01:49 PM         
   


> >
> > It sounds like you need three tables. A Models table with an autoinc record ID as the primary key, an Options, again with an autoinc record ID as the primary key, and a third table that matches model to option. This third table would consist of three fields. An autoinc primary key field, a foreign key to the Models table and another foreign key to the options table. The information about the model is stored once. The information about the option is stored once. In the third table you would have between 20 - 200 records for each of 20 to 200 products. Total table size: 3 four byte fields and a maximum of 200 X 200 = 40,000 records - quite small actually.
> >
> > Ken
> > You shall know the truth - and the truth shall set you free. (John 8:33)
>
> Ken it has been a while since you helped me with this concept.
> If I implemented this 3 table system what I would like to do is have a grid
> with all the options listed in one column then a column for each model with a check mark in that column so the customer can just go through and place a check in the column for the model that would have that option
>
> any ideas how to create the grid

This sounds like a "cross tab" sort of thing. I don't think I would go with that sort of design. The user will probably follow a decision flow. First select the model, then select the options he/she wants. I would use a dropdown for the model and then a multi-select listbox for the options using a SELECT ... FROM ThatThirdTable. This would ensure that I only get the options that actually exist for that model. I would use this approach if we are giving this form to the customer (or sales person) and asking them to select from the list of options for that model.

I suppose you could do it with a cross tab, but you are going to have to limit yourself to 254 models. Start by querying the models table to figure out how many models you have that you wish to work with and then create a cursor with a boolean column for each model. You could probably use the model name or number as the column name. Now scan through the options table and add a record for each. Do beware though. You will get a record for every option. You will have to figure out what to do when one of the models should not be allowed a specific option. This would not work so well for the customer or the salesman, but would be perfect for use by the design staff. This would allow them to "assign" allowable options for each model. You could use something like this to populate that third table that I talked about.

Ken
You shall know the truth - and the truth shall set you free. (John 8:33)

ENTIRE THREAD

Table setup Posted by krbaugh baugh @ 1/16/2007 11:41:24 PM
RE: Table setup Posted by Ken Murphy @ 1/17/2007 4:44:37 AM
RE: Table setup Posted by krbaugh baugh @ 1/17/2007 5:20:06 AM
RE: Table setup Posted by Ken Murphy @ 1/17/2007 1:35:18 PM
RE: Table setup Posted by krbaugh baugh @ 1/17/2007 4:15:33 PM
RE: Table setup Posted by Ken Murphy @ 1/17/2007 5:55:13 PM
RE: Table setup Posted by Kevin @ 12/20/2007 7:56:14 AM
RE: Table setup Posted by Ken Murphy @ 12/20/2007 2:01:49 PM
RE: Table setup Posted by Kevin @ 12/20/2007 3:34:16 PM
RE: Table setup Posted by Ken Murphy @ 12/20/2007 3:53:09 PM
RE: Table setup Posted by Kevin @ 12/20/2007 7:23:00 PM
RE: Table setup Posted by Ken Murphy @ 12/20/2007 8:31:13 PM
RE: Table setup Posted by Kevin @ 12/21/2007 7:18:26 AM
RE: Table setup Posted by Ken Murphy @ 12/21/2007 1:50:29 PM
RE: Table setup Posted by Kevin @ 12/21/2007 11:57:03 PM
RE: Table setup Posted by Ken Murphy @ 12/22/2007 2:32:13 AM
RE: Table setup Posted by Kevin @ 12/22/2007 5:02:58 PM
RE: Table setup Posted by Ken Murphy @ 12/22/2007 10:13:22 PM
RE: Table setup Posted by Andy Kramek @ 1/17/2007 12:38:25 PM