need help with complicated search/replace

[charlesoutcalt]

Hello All. I am a FileMaker newcomer, so please excuse my ignorance. I am trying to do something in FM that I am sure could be automated, but I don't know how to do so.

To put my problem in the briefest possible terms, I need to replace the contents of Field A in File 1 with the contents of Field B in File 2 IF the value of Field C in File 1 equals the value of Field D in File 2. In other words, I need to compare a value from File 1 to a value in File 2 and then, if they do match, take the value of a field in File 2 and place it in a field in File 1.

I have tried writing a script for this that compares the values of the two fields that need to match, then replaces the value if there is a match, but it seems that the scripts can compare only static texts in quotes, rather than the value of fields.

is there a way to do this? I have tens of thousands of records, and would hate to have to do this type of matching manually.

Any and all assistance is welcome! Thanks!

[IdealData]

Comparison operators work on all field types, of course you must apply the correct logic to the appropriate field types.

Why not post your script and the field types you are comparing, and can you provide some example data?

[charlesoutcalt]

Wow! That was fast

Here is the calculation I am using. I know it's probably ridiculously wrong, but here goes:

[color:blue]If ( (Award Type 05=Unique Award Types::Unique Award Type) ; (Award Cat 05=Unique Award Types::Fin Aid Cat) ; "No Match")

What I am trying to do, in plain English, is this:

If the field called 'Award Type 05' matches the field called 'Unique Award Type' in the related file called 'Unique Award Types,' then make the value of the field 'Award Cat 05' equal to the value of the field 'Fin Aid Cat' in the related file 'Unique Award Types'.

If it helps to know the background, here's what's happening:

I have a file called 'Unique Award Types.' That file lists several hundred types of financial aid awards. I have assigned a category to each of these awards (federal loan, scholarship, etc.). There are 14 categories in total. In other words, categories contain several types (for example, there are many types of federal loans, but they are all assigned to the same category).

In addition, I have several files that contain one record for each financial aid award offered at my university in a given year. There is a file for awards from 1998, 1999, etc., through 2005. Each of these awards has a type that matches a type in the Unique Awards file. What I need to do is assign each of those records a type according to the category.

Hope all this background helps. . .