From relations between Views and Tables.

[andyk]

I want to display a one-to-many relationship within a form using a grid for the many end.&nbsp;&nbsp;This is easy if both ends are tables, however the single end of the relationship is a view.&nbsp;&nbsp;Is this possible?&nbsp;&nbsp;Please help.<br><br>The MS Help implies you can create a temporary index on a view and also you can set up a relationship between tables and views(?) at runtime.&nbsp;&nbsp;Is this the way to go about it or should I be using SQL with a filter to populate the grid somehow.<br><br>An example of what I'm trying to do is display the children of a person, as well as linking into other tables to pull in additional data about that person.&nbsp;&nbsp;The children of course, have their own entries in the same table as their parents.<br><br>Many Thanks Andy.

 

[andyk]

Sorry, forgot to mention I'm running VFP 6.0 on both 98 and NT.<br><br>Also can anybody recommend a book, I'm an experienced C++ programmer, but have just seen the light (FoxPro!).&nbsp;&nbsp;Can you believe, not one is sold in the main book shops of my two local university cities!<br><br>Thanks Again,<br><br>Andy.

 

[dgrewe]

1. I would copy the information in the SQL View into another SQL statement only redirect the output to a Temp.dbf or cursor and then&nbsp;&nbsp;print the temp/cursor dbf<br><br>2. For info on Books see: <A HREF="

http://www.Hentzenwerke.com/"

TARGET="_new">

http://www.Hentzenwerke.com/</A>

<p>David W. Grewe<br><a href=mailtoave@internationalbid.net>Dave@internationalbid.net</a><br><a href= > </a><br>

 

[andyk]

Thanks Dave,<br><br>I was hoping to use the view to update the base tables.&nbsp;&nbsp;If I copy into a temp table/cursor, I will loose this ability and I assume will have to update the base tables manually from the cursor (that sounds bad!).<br><br>Am I asking too much of FoxPro, or am I just not approaching the problem from the right direction.&nbsp;&nbsp;I'm sure its the latter!<br><br>Also would you mind indicating which of the books you recommended is best for this level of problem.<br><br>Many Thanks, Andy.<br><br>

 

[JDurbin]

Yes you can do it just as you said.&nbsp;&nbsp;Set your relations as you stated with your temp index on the view (parent) if you like.&nbsp;&nbsp;You may want to place them in the DataEnvironment(DE) of your form and set their relations there.&nbsp;&nbsp;You can then drag fields from the parent table to the form to create the default control for each field's datatype, lavel included and drag the entire child table which will make a grid by default with its properties set to act as a child to the parent specified in the DE.&nbsp;&nbsp;<br>VFP also has builders for controls which are quite helpful.&nbsp;&nbsp;There is a builder in the toolbox and also available in the right click menu of the control.&nbsp;&nbsp;Right click the grid for example and you will find a very helpful tool.<br>Congratulations on finding VFP.&nbsp;&nbsp;You should admire its blazing speed.&nbsp;&nbsp;AFA a book the Developers Guide from MS isn't bad.&nbsp;&nbsp;Search 'FoxPro' at Amazon.com or read descriptions of books at the link Dave gave you above for foundational level info.&nbsp;&nbsp;Good luck and post any questions you may have. <p>John Durbin<br><a href=mailto: john@johndurbin.com> john@johndurbin.com</a><br><a href=

http://www.johndurbin.com>

</a><br>ICQ #9466492<br>
ICQ VFP ActiveList #73897253

 

[andyk]

Thanks John, I am a fan of the builder and drag-n-drop field to form, capabilities on VFP 6.0 you have mentioned.&nbsp;&nbsp;I found them out for myself just by playing with the IDE - very intuitive!<br><br>I'm probably being a bit dim here, but how do you incorporate a temporary index into the DE for the form?&nbsp;&nbsp;I thought only persistent tables/indexes could be represented here.<br><br>

 

[JDurbin]

Your view is persistent in that it resides in a DBC.&nbsp;&nbsp;In AfterOpenTables() of the DE you can SELECT your view and create an index or just do it in code or a method/event of the form somewhere like Init().&nbsp;&nbsp;The form is initialized only after all contained objects including the DE have successfully initialized so you can do it there.<br>There are other, perhaps better ways to accomplish what you're doing however.&nbsp;&nbsp;For instance you could requery your view on the primary key of the table it acts as a child of.&nbsp;&nbsp;So say you're skipping thru a customer table (parent), you typically create a view of CustomerOrders (child), etc with a foreign key which matches the primary key of the customer table.&nbsp;&nbsp;On record movement you simply Requery() your view.&nbsp;&nbsp;<br>The help on views is really quite good in VFP.&nbsp;&nbsp;See Primary & Foreign Keys, Parametized Views, NoDataOnLoad.&nbsp;&nbsp;foxdev (Robert Bradley) is very good with this stuff, especially C/S & SQL, maybe you can ask him to create a FAQ if he hasn't already&nbsp;&nbsp; <p>John Durbin<br><a href=mailto: john@johndurbin.com> john@johndurbin.com</a><br><a href=

http://www.johndurbin.com>

</a><br>ICQ #9466492<br>
ICQ VFP ActiveList #73897253

 

[dgrewe]

Thanks John,<br>That is what I get for answering a VisualFoxPro question when I do not know it as well as FPW.&nbsp;&nbsp;&nbsp; <p>David W. Grewe<br><a href=mailtoave@internationalbid.net>Dave@internationalbid.net</a><br><a href= > </a><br>

 

[andyk]

Jon,<br><br>Thanks, I've managed to create the temp index solution in the command window!&nbsp;&nbsp;I'll try doing it in the form shortly.<br><br>I had played with Parameterized Views as per your second solution, however I could not get the child view to update with new parameter values.&nbsp;&nbsp;It stays with the first input value present when the form is initialisd.&nbsp;&nbsp;I assume a requery is in order (if so how?).&nbsp;&nbsp;Also should the view parameter be set to the index of the parent table (=table.index?).&nbsp;&nbsp;This didn't seem to work - I also tried a form property variable but this would need updating each time the parent table record pointer is moved.<br><br>I've noticed I should really be in the Visual FoxPro forum, which I'm now subscribing to now also.&nbsp;&nbsp;I'm well impressed with both though.<br><br>Again Many Thanks for you help, Andy.

 

[JDurbin]

>Thanks, I've managed to create the temp index solution in the command window!  I'll try doing it in the form shortly.<br><br>Same thing there.<br><br>>I had played with Parameterized Views as per your second solution, however I could not get the child view to update with new parameter values.  It stays with the first input value present when the form is initialisd.  I assume a requery is in order (if so how?).<br><br>You have to store the new value to your parametized value before each requery.  Probably the easiest place to issue a requery is in the grid's Refresh() event.<br><br>>Also should the view parameter be set to the index of the parent table (=table.index?).  <br><br>You're kinda mixing up indexes with keys.  Your parent table relates to your child.  A parent doesn't need to indexed at all though it typically is.  You can index on various fields and expressions.  In a 1 to many relation the parent tables key is a unique identifier of each row in the table.  Typically this is a single field such as cust_id.  The best solution though is a 'surrogate' key which is a field such as custkey with its value to have no other meaning in the table than as a unique identifier of each record.  That way if you change cust_id your primary key is left untouched.  So if you are indexing on both the answer is only if the field of the parent table you indexed on has an equivalent in the child's.  If your parent's primary key is cust_id or custkey then your parameter must equal it.<br><br>>This didn't seem to work - I also tried a form property variable but this would need updating each time the parent table record pointer is moved.<br><br>Yes that's right.  No getting around it w/o a set relation whether the view is parametized or not.  Perhaps we should drop the parametized here anyways though.  A parametized value allows you to prompt the user for the value if it is not supplied which you are evidently not doing here.  Just make your <FONT FACE=monospace>WHERE MyChildTable.cust_id = MyParentTable.cust_id</font>. and see what you get.  You still need to requery with each movement of your parent table.  For starters you may want to just experiment with tables.  View are great for viewing more than 1 table in a single entity like a grid control or browse window and for updating remote data sources like SQL Server, etc.  Are you joining more than one table in your view?<br><br>>I've noticed I should really be in the Visual FoxPro forum, which I'm now subscribing to now also.  I'm well impressed with both though.<br><br>Ya both are great and are fun.  I'm still chicken schmidt to answer 2.6 questions cuz well I am not good at it.  At least Dave has guts to try to help with VFP.    More than I can say for me with 2.6     <br>Andy, do you have any books?  I encourage you to get at least one book (though they are on the help files in entirety!)  What do you have?  Studio?  You have the help files right?  I'm just wondering if we have a mutual reference point here as it is much more thorough and accurate than I may be here.<br>