Is it possible to use the SCATTER function to scatter fields from two tables and reference the fields from those tables even if some fields from each table have the same name? A more thorough explanation of the SCATTER/GATHER functions than what is provided in the FoxPro help would be great. Thanks!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SCATTER confusion
- Thread starter kiglid
- Start date
SCATTER MEMVAR will scatter table field values into memory variable with the same names as the table fields.
If you SCATTER from two tables which the same field names - and thereby generate the same memory variables, when you issue a SCATTER MEMVAR the second table values will over-write the values from the first.
An alternative is to SCATTER TO <array name>
As long as your array names are different for the two tables which you scattered, the values will remain separate. However the arrays will not contain the field names.
If you use AFIELDS() you can get the associated field names.
You can then write code to pick up the appropriate array subscript and then by going to the value array you can get the associated value.
However this does seem like a long way around to get what you need. Perhaps with a little more understanding of what you need to accomplish, we can provide you with a better solution.
Good Luck,
JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com
If you SCATTER from two tables which the same field names - and thereby generate the same memory variables, when you issue a SCATTER MEMVAR the second table values will over-write the values from the first.
An alternative is to SCATTER TO <array name>
As long as your array names are different for the two tables which you scattered, the values will remain separate. However the arrays will not contain the field names.
If you use AFIELDS() you can get the associated field names.
You can then write code to pick up the appropriate array subscript and then by going to the value array you can get the associated value.
However this does seem like a long way around to get what you need. Perhaps with a little more understanding of what you need to accomplish, we can provide you with a better solution.
Good Luck,
JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com
- Thread starter
- #3
Thanks for the reply, I would like to avoid using arrays if possible.
What I'm doing is looping through a client table to update the ethnic origin field based on the original value and the country of birth field. I'm also searching another 'child' table to see if there is a corresponding record. If there is I base my update on some fields in that table as well, using a diagnosis date field and the ethnic origin value in that child table. There is one situation where the child table is updated rather than the parent table. The conversion program I'm writing is basically a whole bunch of case statements, nested 3 deep at one point. The parent table has about 190,000 records and the child table has about 4,300 records. I want to obviously make sure I'm updating the tables at the right time and with the right values, I'm also writing to a log file to help validate the conversion.
What I'm doing is looping through a client table to update the ethnic origin field based on the original value and the country of birth field. I'm also searching another 'child' table to see if there is a corresponding record. If there is I base my update on some fields in that table as well, using a diagnosis date field and the ethnic origin value in that child table. There is one situation where the child table is updated rather than the parent table. The conversion program I'm writing is basically a whole bunch of case statements, nested 3 deep at one point. The parent table has about 190,000 records and the child table has about 4,300 records. I want to obviously make sure I'm updating the tables at the right time and with the right values, I'm also writing to a log file to help validate the conversion.
From what you describe, it seems a whole lot easier to set up a Relation between the Parent table and the Child.
Then, when you scan through the Parent table, if a Child record exists, you would be able to "see" it and its table-specific values as well.
From that you could "see" the values from both tables (if the rrelation existed) concurrently and, from that, make your determination as to update or not.
Just make certain that you will not be changing the value on which the relation has been established.
Good Luck,
JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com
Then, when you scan through the Parent table, if a Child record exists, you would be able to "see" it and its table-specific values as well.
From that you could "see" the values from both tables (if the rrelation existed) concurrently and, from that, make your determination as to update or not.
Just make certain that you will not be changing the value on which the relation has been established.
Good Luck,
JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com
- Thread starter
- #5
Setting a relation sounds great, I didn't know it existed before. Once the relation is set how do I reference the fields on each of the tables? I'm getting a compile error trying to reference the child table as tablename.fieldname. Also what would the check be to see if there is a child record?
Thanks for all the help.
Thanks for all the help.
- Thread starter
- #6
As to "Also what would the check be to see if there is a child record?"...
For the sake of discussion we'll call your Parent table (PARENT.DBF) and the Child table (CHILD.DBF). You can substitute your real table names later.
* --- some time before ---
USE child IN 0 EXCLUSIVE
SELECT child
INDEX ON <field expression> TAG key
USE
* --- some time later (not sequential with above code) ---
USE parent IN 0
USE child IN 0 ORDER key && KEY is the INDEX TAG
SELECT parent
SET RELATION TO <your index field expression> INTO child
SCAN
IF RECNO("CHILD"
> RECCOUNT("CHILD"
* --- Child's Record Pointer is beyond last record ---
* --- No Related Table Record Exists ---
<do whatever>
ELSE
* --- Child's Record Pointer is valid record ---
* --- Related Table Record Exists ---
<do whatever>
ENDIF
ENDSCAN
Note that if you change your SELECT pointer anywhere during the SCAN, you will need to reset it (SELECT parent) before the ENDSCAN. If you do not change it, then no additional SELECT is needed.
Good Luck,
JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com
For the sake of discussion we'll call your Parent table (PARENT.DBF) and the Child table (CHILD.DBF). You can substitute your real table names later.
* --- some time before ---
USE child IN 0 EXCLUSIVE
SELECT child
INDEX ON <field expression> TAG key
USE
* --- some time later (not sequential with above code) ---
USE parent IN 0
USE child IN 0 ORDER key && KEY is the INDEX TAG
SELECT parent
SET RELATION TO <your index field expression> INTO child
SCAN
IF RECNO("CHILD"
> RECCOUNT("CHILD"* --- Child's Record Pointer is beyond last record ---
* --- No Related Table Record Exists ---
<do whatever>
ELSE
* --- Child's Record Pointer is valid record ---
* --- Related Table Record Exists ---
<do whatever>
ENDIF
ENDSCAN
Note that if you change your SELECT pointer anywhere during the SCAN, you will need to reset it (SELECT parent) before the ENDSCAN. If you do not change it, then no additional SELECT is needed.
Good Luck,
JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com
- Status
