>>The TASTRADE sample application has an "autonumber" key generator which performs like the Autonumber data type found in Access. If you look at the surrogate key field of TASTRADE tables, you'll notice that the "default value" of the field is NewID(field) which is a stored procedure. This procedure makes use of a table in the database called "keys" or something like that. The keys table stores the next valid key for every table. NewID() gets the key for a table and increments the value in the keys table. This is the system I use.
>
>***
>This sounds like a really good idea. Where do you put the procedure? Is it a regular .prg in the project, or is this something you put in the database itself? What's a surrogate key?

**
Somebody else explained Stored Procedures. A surrogate key is this non-meaningful key that we are talking about. A "natural key" is an existing field, such as customer number, used as a primary key.
**
>***
>
>>A couple of things about this system: I am still stuck in vfp3, which has that bug in combo boxes which makes them store listindexes instead of values when the bound column is a numeric value such as one of these keys. I like to do things in the conventional way, using tables with these surrogate keys to populate combos, having the bound column be the key field, having the key of the selected item be the value passed to the controlsource. To solve this, I made my surrogate keys into strings, by taking key = STR(oldkey) in my version of NewID(). Unfortunately, all my queries run a lot slower as a result. When I get vfp5 or 6, the first thing I will do is change the keys back to numeric. By the way, I don't remember how TASTRADE handles this. One odd thing about TASTRADE is that surrogate keys are used in some tables, and natural keys in other tables. Another thing about the system is that you can add a record with a simple APPEND BLANK and the stored procedure makes the key for
>>you. I often use APPEND BLANK for that reason. If I had many simultaneous users, that would be a bad thing, since APPEND BLANK locks the whole table, I think.
>
>***
>Luckily, I'm running VFP 5.0, so I don't need to worry about that.
>***
>
>>About parameterized views: the main reason I use them is for displaying two tables related many-to-many, one table in the form, the other in the grid. You could join table2 with the common child table3 in one simple view, make that view the child of table1, and display the view in the grid. However, it says somewhere in the vfp books that you get poor performance joining a view as a child.
>
>***
>I don't have any many-to-many tables. I thought you weren't supposed to make those...
**
I mean two tables that are both joined one-to-many with a common table, so that they are effectively related many-many.
**
>***
>
>>
>>Make a parameterized view with the parameter = the keyfield for table1. In your nav button code you'll have to REQUERY the view every time you move to a different record in table1. You might also want to be careful about which fields in this view are going to be updateable.
>
>***
>Ok
>***
>
>>Another choice, if table2 is small (a few dozen records) is to put the common child table3 in the grid, and put a combo in the grid whose controlsource is the table3.table2parentkey, whose rowsource is table2, and whose bound column is table2.surrogatekey. Combos in grids are very slow and should not be considered if the rowsource (table2) is more than just a little lookup table. Even 100 records will be too big.
>
>***
>You've lost me here, I'm afraid...
>***
**
Understanding these examples requires understanding the basic many-many relationship. The example I mention about orders and products is also found in TASTRADE. I don't remember if its forms use the kinds of views we're talking about here, but the relations in their database are typical. The most common many-many relationship I use is for relating "things" to "documents". Since a Document may discuss many Things, and a Thing may appear in many Documents, I always need a table "thingdocs" whose fields are Thingkey and Dockey. I use a parameterized view to make a form displaying all the Documents for each Thing, or all the Things for each Document. The Things table is also related many-many with my "Locations" table using my table "thinglocs". Since there aren't many locations, I put Thinglocs in the grid, rather than a view joining it to Locations. Thinglocs.locationkey is the controlsource for one of the grid columns. This column has a combo whose rowsource is Locations. If they add a whole bunch more locations, I'll have to do this over, because the combo will slow down the navigation.
**
>
>Thanks,
**
I can add my text to the top, bottom, or middle, as you prefer :)
**