Help -- matching data in lookup table, writing to target table 
Author Message
 Help -- matching data in lookup table, writing to target table

I'm a rank novice at FoxPro programming, and I'm trying to set up a
lookup table. I hoped a kind soul on line might be willing to help.
I'm trying to devise a way to compare a string in a field in one table
with a string in a field in another table and, if the strings match,
take the data from twofields in the lookup table and write it to
corresponding fields in the target table.  I'm trying to repeat this
process for each record in the target table.  The SCATTER and GATHER
commands handle the chore of taking data from one table and writing it
to another, but getting the program to move through the target table,
comparing it a record at a time with each record in the lookup
table, has been more than I can figure out.  Any suggestions?


Thu, 29 Jan 1998 03:00:00 GMT  
 Help -- matching data in lookup table, writing to target table
I don't know if this is exactly what you were looking for. I use programs
like this when when I have to make mass edits in my patient database, e.g
when an employer changes from insurance company x to insurance company y
and all patient records for that employer have to reflect that change.

This should(????) work for dbfs with structures like
lookup.dbf   key1,field1,field2
target.dbf   key1,field1,field2,...,fieldn

use lookup in 1 order tag key1 of lookup
use target in 2 order tag key1 of target
select 2
go top
scan
    scatter memvar
    if seek(m.key1,1)
        replace target.field1 with lookup.field1
        replace target.field2 with lookup.field2
    endif
endscan
close all

Hope this helps,

Scott Davis MD




Fri, 30 Jan 1998 03:00:00 GMT  
 Help -- matching data in lookup table, writing to target table

Quote:

>I'm a rank novice at FoxPro programming, and I'm trying to set up a
>lookup table. I hoped a kind soul on line might be willing to help.
>I'm trying to devise a way to compare a string in a field in one table
>with a string in a field in another table and, if the strings match,
>take the data from twofields in the lookup table and write it to
>corresponding fields in the target table.  I'm trying to repeat this
>process for each record in the target table.  The SCATTER and GATHER
>commands handle the chore of taking data from one table and writing it
>to another, but getting the program to move through the target table,
>comparing it a record at a time with each record in the lookup
>table, has been more than I can figure out.  Any suggestions?

This sounds like a SELECT problem, but I don't know how to do it that
way.  Try this:
Open dbfs (table1, table2)
index on field in table2
use table1
SCAN
        scatter memvar
        use b
        SEEK field
        IF FOUND()
                scatter memvar
                select a
                gather memvar
        ENDIF
ENDSCAN

Maybe not the most efficient way to do things, but it will work. Good
luck!
-Josh



Fri, 30 Jan 1998 03:00:00 GMT  
 Help -- matching data in lookup table, writing to target table

Quote:
>>I'm trying to devise a way to compare a string in a field in one table
>>with a string in a field in another table and, if the strings match,
>>take the data from twofields in the lookup table and write it to
>>corresponding fields in the target table.  I'm trying to repeat this

Easy as pie.  If your lookup table is small you can get by without an
index, though it's recommended.

Here's code to create the indexes, do the scan, and do the replace.  
In the interest of clarity, some of these code lines may not be the
most efficient:

USE scanner IN 0        && scanfield, sfield1, sfield2
USE lookup IN 0 && lookfield, lfield1, lfield2
SELECT lookup
INDEX ON lookfield TAG lookfield
SET ORDER TO lookfield

SELECT scanner
GO TOP
SCAN
        cScanField = ALLTRIM(scanfield)
        SELECT lookup
        SEEK cScanField
        IF FOUND()
                clField1 = ALLTRIM(lfield1)
                clField2 = ALLTRIM(lfield2)
                SELECT scanner
                REPLACE sfield1 WITH clField1, sfield2 WITH clField2
        ENDIF
ENDSCAN

***********
Note that when the code hits the ENDSCAN line it will SELECT the
table on which the SCAN is being performed, so if the SEEK is not
successful, you don't have to SELECT the proper work area yourself.

Hope that helps!

Jon



Fri, 30 Jan 1998 03:00:00 GMT  
 Help -- matching data in lookup table, writing to target table
Quote:

> I don't know if this is exactly what you were looking for. I use programs
> like this when when I have to make mass edits in my patient database, e.g
> when an employer changes from insurance company x to insurance company y
> and all patient records for that employer have to reflect that change.

Thanks -- your suggestion accomplished exactly what I needed to do.
One of the techs where I work said he came up with another workable
solution using the UPDATE command. I couldn't quite figure
out the syntax on my own, but as soon as he shows me what he wrote,
I'll post it here, in case it's useful to anyone.


Sat, 31 Jan 1998 03:00:00 GMT  
 Help -- matching data in lookup table, writing to target table

Quote:

>I'm a rank novice at FoxPro programming, and I'm trying to set up a
>lookup table. I hoped a kind soul on line might be willing to help.
>I'm trying to devise a way to compare a string in a field in one table
>with a string in a field in another table and, if the strings match,
>take the data from twofields in the lookup table and write it to
>corresponding fields in the target table.  I'm trying to repeat this
>process for each record in the target table.  The SCATTER and GATHER
>commands handle the chore of taking data from one table and writing it
>to another, but getting the program to move through the target table,
>comparing it a record at a time with each record in the lookup
>table, has been more than I can figure out.  Any suggestions?

How about:

file1: key,name,address
file2: key,address

file2 is indexed on key

use file1 in 0
use file2 in 0 order key
select file1
set relation to key into file2
replace all file1.address with file2.address

You're done.

Jorge.

Jorge Martinez
Programmer/Analyst
San Francisco Unified School District



Sat, 31 Jan 1998 03:00:00 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. Help with joining tables with mis-matched numbers.

2. Writing data into a table?

3. Help Synch'ing writes to >1 Table

4. Need Help Synchronizing Writing to Multiple Tables

5. Transfer data from table of MS Access 97 to table of MS FoxPro

6. LOOKUP( ) Function doesn't do an exact match

7. Matching tables with no common key/finding duplicate records

8. Lookup Tables

9. Indexing technique needed for large table lookup

10. Network error: index does not match table

11. Index dees not match the table, delete the index file and re-create

12. ERROR #114: Index does not match the table.

 

 
Powered by phpBB® Forum Software