searching/comparing across 2 files

[neenerbeener]

I've got two related files, each having an "ID" field, and I want to compare the IDs contained in each. I'm running a script in File 1 that opens File 2, and enters a loop. For each record in File 2, search to see if its ID exists in File 1.

I don't seem to be able to compare both files back and forth from within the same script. (I also tried loading up all the IDs from File 2 into a repeating field, but then couldn't access each cell with a variable, so I gave up on that.)

Here's my script executing from File1:

========================================

Open File2

Show all records

Sort them

Count them

Go to first record

LOOP

Enter FIND mode

Insert calculated result (File1 emp_id, File2 emp_id)

Perform FIND

If found, do abc

Else if not found, do xyz

Exit loop if we're on last record

Go to next record

END LOOP

Close File2

========================================

The find always comes back unsuccessful, even when there are definitely matching IDs in File1.

This seems pretty basic, so there must be a way to do it. Any hints?

Edited September 14, 2007 by Guest

[Fitch]

First, the reason your script fails is that once you enter Find mode, you can't refer to a field, local or related, because you're not on a record, so it's meaningless. Global fields are the exception.

You could make your script work by creating the Find script in File 2 that looks like this:

Set error capture [On]

Enter Find mode

Set field [iD ; Get ( ScriptParameter ) ]

Perform Find

Exit Script[ Get ( FoundCount ) ]

So, as you loop through each record in File1, call the script in File2 with the parameter of the File1 record ID.

After the step in File1's script that calls the File2 script, use Get(ScriptResult) and if it's >0 do your abc.

All that being said, here's another approach:

- Create a relationship between the two files by ID. You probably already have this.

- Make a script and use: Go to related (Match all records in the current found set)

Done!