>>>I am beginning to experiment with setting up persistent relationships for the first time and I am running into a couple of problems.
>>>
>>>I have a database that has an inventory table and a category table and a sub-category table. Example categories might be 1-Band 2-Fretted Instruments. Example of subCategories under Category 1 (Band)might be 1-Piccolo, 2-Clarinet, 3-Saxaphone, etc and under Category 2 might be 1-Acoustic, 2-Electric, 3-Banjo, etc.
>>>
>>>Anyway I have a form established with 2 combo boxes cboCat has the category table set as the rowsource and cboSub has the rowsource set to the subcategory table.
>>>
>>>I was thinking that if I pick a category 2 from cboCat that only the subcategories related to category 2 would show in cboSub. This isn't the case .. all subcategories show in cboSub not just the ones related to the category chosen in cboCat.
>>>
>>>I do have a persistent relationship set in the database between the foreign key in the subcategory file and the primary key in the category file.
>>>
>>>Thanks,
>>>Paul
>>Paul,
>>I really not to mean it cannot be done that way but I suggest not to use it with relations. For comboboxes managing SQL is a lot easier IMHO.
>>For second combo you could leave both Rowsource and Rowsourcetype at their default (none) :
>>

* subCombo.init
>>lcSQL = "select subCat "+;
>>          "from myChild "+;
>>          "where subCatParentKey = thisform.catCombo.value "+;
>>        "into cursor myCursor"
>>RowSourceType = 3
>>RowSource = lcSQL
>>
>>* subCombo.when or gotfocus
>>this.requery()

Advantage this is free of relations and once you build correct SQL you get what you want.
>>If subCatParentKey (category link key) is not char, you should take that into account and do conversion (combos return value as char).
>>lcSQL variable usage is not necessary (think as #define). Not written directly to combo PEM sheet because long SQL cause headeache there.
>>Cetin
>
>Hi Cetin,
>
>Thanks....I am also having a problem with the data entry form for the subcategories. I am beginning to wonder whether or not I should even set up any persistent relationships in the DBC. Is there any recommendations or guidelines out there on whether or not persistent relations are a good idea or not?
>
>Paul
Paul,
DBC persistent relations and form DE relations are different entities. Persistent relations are basically for data integrity. Form level relations change the way the data is displayed (of course edit-save-delete are tighted to persistent relations and you wouldn't do things that corrupts overall integrity).
In a DBC you could relate a parent.pk with a child.key and in referential integrity might say cascade deletes, edits. This would provide an automatic integrity and when you change a parent.pk all child.keys with the former pk value would also change their value to new.pk or when you delete a parent record, childs would be deleted automatically. OTOH at form level you could set the relations upside down (child to parent) or no relation at all. This wouldn't mean you to gain right to break data integrity. ie: RI add restriction would prevent you adding a child record with a key value that doesn't exist in parent even parent is in DE or not.
A long and complex subject really. But think this way : DBC and persistent relations are an enhancement to language and was first introduced in VFP3. I think all developers accept it as real enhancement. Nobody could easily play with my tables using ie: browse when they have VFP installed (of course there are n ways of harming any system at will but at least now I know there is a bad guy around when that happens). And persistent relations are a step towards views. Since VFP3 tendency is to use (p)views instead of relations. You might still use relations though (I do honestly).
If you can read some books (ie: Hacker's Guide to VFP) or VFP online documentation these are all much better explained there.
Shortly (not short?) IMHO don't drop persistent relations.
Cetin