Script doesn't work for entire found set

[susan siegel]

I'm trying to copy and paste data from an existing field to another for a specified found set, basically trying to "fix" a database that was not set up correctly in the beginning.

I've set up a script that works - but only for the first record of the found set; not the others. I think I must be missing a basic step somewhere but can't figure it out.

Here's a simplfied version of what I'm trying to do.

Field 1 (an existing field with data, e.g., "x")

Field 2 (an existing field with data, e.g., "y")

Field 3 (new field that will contain either "x" or "y" or be empty if fields 1 and 2 are empty)

One of many different script versions was:

set field (field 1)

select all

copy (field 1)

set field (field 3)

select all

paste (field 3)

I would then repeat the script changing field 1 for field 2.

I've also tried alternate scripts with "insert text" into the new field instead of the copy and paste approach -- but again it works only for the first record in the found set.

Thanks.

[Ender]

It's not clear why you're not trying this with a calculation field. But if you need the destination field to be static data, try a Replace Field Contents[] (be sure to make a backup first, as there's no Undo with a Replace).

[susan siegel]

I tried a calculation field in the new field 3 using an IF statement. I don't remember the exact language or punctuation, but it went something like this:

If (field 1="x"; "1"; "0")

This worked fine for bringing the contents of field 1 into the new field 3. But, if I'm remembering my experience correctly, I encounted two problems.

1)the calc formula only worked when entering new records, not for changing existing ones. (I may not have had the field coded correctly for validation; I have to take another look at that.)

2)because field 3 was a calc field, I couldn't modify the data, e.g., changing the "0" of field 2 to a third option if both field 1 and field 2 were empty (which does happen). I tried changing the calc formula to a new IF statement so I could account for field 1 and field 2 being empty but that didn't work; I don't remember why.

BUT -- even if I can get the calc field to work correctly -- for future use -- I still need to know what I was doing wrong with the script and why I couldn't get it to work for the entire found set.

Susan

[Buggen]

If this is a one time conversion then Ender's suggestion sounds like what you'll want to try. Here's what you'll need to do.

1. Either find all records or the subset you want to work with.

2. From the Records menu select Replace Field Contents.

3. In the next window select Replace With Calculated Result

4. For you calculation you can do something pretty simple such as:

Case ( IsEmpty ( Field_1 ) ; Field_2 ; Field_1 )

This checks to see if the first field is empty. If it is then it inserts the contents of the second field. Otherwise it uses the first field's contents. If both are empty then the result is empty.

You could do the same thing as a calculation if you wanted to constantly update the information.

Hope this helps.

Geoff

[susan siegel]

Thanks to both Enders and Geoff. I think I understand it now.

1) if I use a calculation when I define a field, it forces a result for all records -- BUT

2) if I use a calculation to "replace field contents" , then the calculation only applies for the found set and doesn't change what already exists in the field for all the other "non" found records.

While the above has resolved my immediate problem, I'm still left with one unanswered question for future use:

does a script work for only one record at a time or can it be applied to an entire found set?

Thanks again.

susan

[Buggen]

Susan,

How many and which records a script will act upon depends on how you set up the script. For example, if you create a script using the replace function I mentioned before it will act on the current found set when it hits that script step.

Alternatively, you could create a script with a loop function to start at the first or other specified record, perform the action and then go on to the next record and repeat. The final implemenation really will depend on your long term needs.

Geoff

[comment]

A script works on the entire file. However, a SCRIPT as such does nothing. It's the script STEPS that do the work, and each individual script step has its own scope.

[susan siegel]

Thanks again for pointing me in the direction of the

"replace fields" command.

Now I understand why the script was not working for all the records in the found set.

Susan