I've never needed to use the SET SKIP TO command until this weekend.
The bottom line, I'm guessing that my understanding is incorrect or I'm not following the examples I've found closely enough.
From reading various sites, VFP help, etc. I've come to believe that if you properly set the relation and properly
issue the SET SKIP command, as VFP scans through the parent table it sort of creates phantom/virtual extra rows
to correspond with those child table rows that create a one-to-many situation.
In other words, the parent table pointer stays on the parent record until the multiple child records
have been cycled through.
I've spent hours trying to figure out how I've screwed this up but have a hit a brick wall. I can't get SET SKIP to work in my effort to add data from the fields of a parent table to fields of a child table on those records that represent a one-to-many relationship. I'm sure it's something simple and obvious but I'm surely overlooking whatever is staring me in the face.
Following the code are things I've tried and other pertinent information.
Your help will be greatly appreciated.
The environment:
SET CONFIRM ON
SET DELETED ON
SET EXACT ON
SET LOGERROR ON
SET STATUS OFF
SET TALK OFF
The code:
Some other potentially pertinent facts:
I'm using VFP 6
These test tables have several hundred thousand records. The actual tables will have millions.
The example above does not work with even 1 child table. The real example has 6 child tables and they were named in the one SET SKIP TO command with commas separating them.
The parent table always has unique records.
Not every record in the parent table has a corresponding record in the child tables, depending upon the particular child table, perhaps 5% to 85% of the parent table records have a corresponding child table. The single child table example I've been working with trying to solve this problem has about 85%.
The field used to set the relation (in the example above, called field1) has been double checked in both parent and child tables using the MODIFY STRUCTURE command and they are identical character fields with a length of 14 characters.
The indeces on the field used to set the relation created in the parent and child tables have the identical form of ALLTRIM(field1)
As a cross check, when what is the child table in the example above is viewed without having related it to the parent; i.e., not a child in this particular cross check case, just as an independent check of the index, those "children" with multiple records do appear in the correct sort order based on the index, one below the other
It has worked flawlessly with SET RELATION; i.e., prior to having to add an occasional second record for an account in the child table.
It still works flawlessly for the first record of an account that has 2 records, only the second record fails to be updated
I've used the expressions "parent_table", "child_table", and "fieldn" for simplicity of the example. The actual table names and field names are valid Foxpro names.
The tables are not and have never been part of a DBC.
The index for each table is part of a structural compound index.
I've packed (and, therefore, re-indexed) both tables used in the example above.
The one-to-many relationship do not appear when a BROWSE command is issued.
The contents of field1 is automatically generated by a program at two separate times, once for the parent table and once for the child table but that code appears to be solid as evidenced by the flawless performance of the SET RELATION COMMAND and my visual inspection of the contents of the fields causing the problem in the child tables.
In the example above, the parent table happens to be ordered on the relational expression of the SET RELATION command. My understanding is that the child table must be ordered on that but the parent table may be ordered on that, on some other field or expression or on nothing.
The bottom line, I'm guessing that my understanding is incorrect or I'm not following the examples I've found closely enough.
From reading various sites, VFP help, etc. I've come to believe that if you properly set the relation and properly
issue the SET SKIP command, as VFP scans through the parent table it sort of creates phantom/virtual extra rows
to correspond with those child table rows that create a one-to-many situation.
In other words, the parent table pointer stays on the parent record until the multiple child records
have been cycled through.
I've spent hours trying to figure out how I've screwed this up but have a hit a brick wall. I can't get SET SKIP to work in my effort to add data from the fields of a parent table to fields of a child table on those records that represent a one-to-many relationship. I'm sure it's something simple and obvious but I'm surely overlooking whatever is staring me in the face.
Following the code are things I've tried and other pertinent information.
Your help will be greatly appreciated.
The environment:
SET CONFIRM ON
SET DELETED ON
SET EXACT ON
SET LOGERROR ON
SET STATUS OFF
SET TALK OFF
The code:
Code:
PROCEDURE procedure_name
USE child_table in 1 ALIAS child_table
USE parent_table IN 2 ALIAS parent_table
SELE parent_table
SET ORDER TO
* extract the date of the last records appended to the parent table in text format from field 1
GO BOTT
date_text_ = SUBSTR(field1,1,6)
SET ORDER TO field1
SET RELATION TO parent_table.field1 INTO child_table ADDITIVE
SELECT parent_table && probably superflous here
SET SKIP TO child_table
SET EXACT OFF
SEEK date_text_
SET EXACT ON
SCAN WHILE date_text_ $ field1
* I've tried to replace field contents two ways (they each appear to work for one-to-one relationship but not one-to-many)
* this was the first
REPLACE child_table.horse_id with parent_table.horse_id, ;
child_table.country with parent_table.country, ;
child_table.comb_alpha with parent_table.comb_alpha, ;
child_table.alpha_only with parent_table.alpha_only, ;
child_table.trainer_r with parent_table.trainer_r, ;
child_table.train_id with parent_table.train_id
* this was the second
REPLACE horse_id with parent_table.horse_id, ;
country with parent_table.country, ;
comb_alpha with parent_table.comb_alpha, ;
alpha_only with parent_table.alpha_only, ;
trainer_r with parent_table.trainer_r, ;
train_id with parent_table.train_id IN child_table
ENDSCAN
RETURNSome other potentially pertinent facts:
I'm using VFP 6
These test tables have several hundred thousand records. The actual tables will have millions.
The example above does not work with even 1 child table. The real example has 6 child tables and they were named in the one SET SKIP TO command with commas separating them.
The parent table always has unique records.
Not every record in the parent table has a corresponding record in the child tables, depending upon the particular child table, perhaps 5% to 85% of the parent table records have a corresponding child table. The single child table example I've been working with trying to solve this problem has about 85%.
The field used to set the relation (in the example above, called field1) has been double checked in both parent and child tables using the MODIFY STRUCTURE command and they are identical character fields with a length of 14 characters.
The indeces on the field used to set the relation created in the parent and child tables have the identical form of ALLTRIM(field1)
As a cross check, when what is the child table in the example above is viewed without having related it to the parent; i.e., not a child in this particular cross check case, just as an independent check of the index, those "children" with multiple records do appear in the correct sort order based on the index, one below the other
It has worked flawlessly with SET RELATION; i.e., prior to having to add an occasional second record for an account in the child table.
It still works flawlessly for the first record of an account that has 2 records, only the second record fails to be updated
I've used the expressions "parent_table", "child_table", and "fieldn" for simplicity of the example. The actual table names and field names are valid Foxpro names.
The tables are not and have never been part of a DBC.
The index for each table is part of a structural compound index.
I've packed (and, therefore, re-indexed) both tables used in the example above.
The one-to-many relationship do not appear when a BROWSE command is issued.
The contents of field1 is automatically generated by a program at two separate times, once for the parent table and once for the child table but that code appears to be solid as evidenced by the flawless performance of the SET RELATION COMMAND and my visual inspection of the contents of the fields causing the problem in the child tables.
In the example above, the parent table happens to be ordered on the relational expression of the SET RELATION command. My understanding is that the child table must be ordered on that but the parent table may be ordered on that, on some other field or expression or on nothing.
![[cheers]](/data/assets/smilies/cheers.gif "[cheers] [cheers]")
