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


> >
> >
> >
> > ok
> >
> > Questions
> >
> > So the this did work was the model table had a one to amy relation to the options table indexed on the model field in both tables. I then had a drop down with models
> > and a grid with options. With your 3 table idea how do I set the relations so that when I select a model only the options for that model show in the grid
>
> For the sake of simplicity, let's call that third table the "OptionsAssign" table.
>
> The relationship between that third table and the Options table is a many to one relationship, as is the relationship between the third table and the Models table, so in the model dropdown .Click()
>
> SELECT OptionsAssign.RecordID, OptionsAssign.OptionID, Options.Name, .f. AS lSelected ;
>    FROM OptionsAssign ;
>    RIGHT OUTER JOIN Options ON Options.RecordID == OptionsAssign.OptionID ;
>    WHERE OptionsAssign.ModelID = Model.RecordID AND ;
>          llAnyOtherConditionThatYouMightNeed ;
>    INTO CURSOR csrOptionsAssign
> 

>
> This will give you a "clean slate" cursor containing a record for each possible option for that model. Existing options will have a "RecordID" > 0 and options that have not been previously assigned to this model will have a "recordID" of NULL. (If you wanted, you could pre-set the "lSelected" field with .t. for any record where RecordID > 0 and this would give you your existing options assignment.)
>
> Now, base your grid on this cursor. The user will be able to select any option, check (or un-check) the box as required and then click a save button. When you save the options assignments, you only need to save the records where the lSelected field is .t. If you have a RecordID > 0 AND NOT lSelected, then you need to delete that existing record from your OptionsAssign table. If you have a NULL RecordID and lSelected, then you need to INSERT that record into your OptionsAssign table. If you have a record where RecordID > 0 and lSelected, you need do nothing. That record is already correct in your OptionsAssign table. If the csrOptionsAssign record has a NULL RecordID AND NOT lSelected, again, you need do nothing. It does not currently exist in your OptionsAssign table nor do you wish to add it.
>
> Give it a bit of a play
>
> Ken
> You shall know the truth - and the truth shall set you free. (John 8:33)

Ok that is working! Thanks
Now my problem is the grid has somme moved columns

When I do

thisform.grid1.RecordSource =space(0)

SELECT Options.*, Optionsmodel.model as modelindex, Optionsmodel.option,  .f. AS lSelected ;
   FROM Optionsmodel ;
   RIGHT OUTER JOIN Options ON Options.index == Optionsmodel.Option ;
   WHERE Optionsmodel.Model = Model.index;
   INTO CURSOR optionscursor READWRITE
thisform.grid1.RecordSource =space(0)
thisform.grid1.RecordSource = "optionscursor"  


My data is in the wrong columns do I need to redo the select so the columns are in the order I need them on the grid then redo the grid so the columns are not rearranged?

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