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: 155032 # Views: 1 # Ratings: 0
Version: Visual FoxPro 9 Category: Visual Basic
Date: Saturday, December 22, 2007 2:32:13 AM         
   


> >
> > 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?

Kevin

Use a GridCursor (or as Andy calls it, Safe Select) for your recordsource.

In the form's .load() create your grid cursor using this:

SELECT Options.*, Optionsmodel.model as modelindex, Optionsmodel.option,  .f. AS lSelected ;
   FROM Optionsmodel ;
   RIGHT OUTER JOIN Options ON Options.index == Optionsmodel.Option ;
   WHERE .f. ;
   INTO CURSOR optionscursor READWRITE


This will give you an empty readwrite cursor that you can then bind to your grid. Now create a custom form method and call it ".RepopulateGrid()" or what ever. In that custom method:

*  SELECT the records you need into a temporary cursor
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 Junk NOFILTER
*  Now clear any existing records from your grid cursor
SELECT OptionCursor
ZAP IN SELECT([OptionCursor])
*  and then populate your grid cursor with the records you just selected.
APPEND FROM DBF([Junk])
*  Don't need "Junk" any more so get rid of it
USE IN SELECT([Junk])


The reason your columns get screwed up is when you run your SELECT, VFP closes your OptionCursor and recreates it from scratch. When the OptionCursor dissapears, because it is bound to your grid, your grid columns dissapear. With this method, your OptionCursor never gets closed - it just becomes empty for a bit, so your columns remain intact.

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