Hello all,

Has anyone ever had a issue with FM6.5 skipping records in a mass replace
statement? The table I'm working with is not designed very well where each
record contains (for instance) invoice #, item1, item2, item3, thru item10
then a grand total which is a calulated field for all 'item' fields, a net
total which is grand total minus (ex) item7 and item8, a commission flag and
finally a commish field which should be populated by my replace statement.
In my commission script I first do a find where comm_flag= (not populated)
and grandtotal>0 which returns all commissionable records. Then I call a
single step script with a replace statement which acts on the commish field.
The calculated value is shown below.

If(IsEmpty(Comm_flag) and NetTotal>0, NetTotal*.6, Commish)

When I ran this on 1,010 records in the found set there were 28 records
skipped for no apparent reason. Inspection of those 28 records show the
commission flag to be (now) populated with the commission date of the last
run (070430) so I have to assume they were in the found set (this field is
populated after the report has been reviewed and posted). The net total is
I have experienced timing issues before where I had to put pauses in a
script to compensate for latency. But that is normally when I'm calling
external processes. I'm befuddled.

In article <d8dc9$4639f833$d1a8fa82$7745 (AT) EDELTACOM (DOT) COM>,
"GSP@DavClaServ" <info (AT) davclaserv (DOT) com> wrote:

The Replace command itself can't possibly skip records, so the problem
is either the way you are initially finding records or the If statement
you're using inside the Replace command.


Since the script has only the single Replace command (ie. can't
possibly change the Comm_Flag field's data), I would guess that the
initial Find is not being performed correctly. The easiest way to check
that this is the problem would be to perform the Find, and then go
through the records BEFORE running the Replace script - you'll probably
discover the "070430" records are already there when they shouldn't be.

If you want records where BOTH Comm_Flag is empty AND GrandTotal is
more than 0, then the Find should be performed as:

Enter Find Mode []
Set Field [Comm_Flag, "="]
Set Field [GrandTotal, ">0"]
Perform Find []

Make sure to turn OFF the Restore options for both the Enter Find Mode
and Perform Find commands, otherwise it will give unexpected records in
the Found Set - this is often the usual cause of problems with scripted
Finds.

The thing that points me to the above conclusion is that the Find is
supposed to be obtaining records where Comm_Flag is empty, but the If
statement is testing to see if the Comm_Flag is empty and when it's not
empty the existing data in the Commish field is used, and so there will
appear to be no change. (It also won't change if NetTotal is empty, 0
or negative, but you say it is >0 in all the found records.) Obviously
Comm_Flag is not empty in those "skipped" records when the Replace
command is performed.


Another possibility is that your Find script is doing something else
before reaching the Replace script, but that would be impossible to
tell from just your description.


As an aside, the "IsEmpty(Comm_Flag)" test inside the Replace command's
If statement is technically redundant (for this process anyway, it may
be needed if other scripts call this one) since the Find is meant to
return just the records where it is empty anyway ... BUT in this case
you were lucky it was there since it has enabled you to spot an error
in the process. Without it you could easily have lost the existing
Commish data in the 28 "skipped" records since it would have replaced
that value with NetTotal*6 (because NetTotal is more than 0).


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Helpful Harry wrote:

Be sure to read everything else Harry has to say, as he is usually
right. But also be aware of possible record locking issues. If other
users are in particular records as the replace rolls through them, it
will skip those records with no error messages if you have Set Error
Capture [On] in your script.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Professional Solutions, Inc. Los Angeles

FileMaker 8 Certified Developer
Associate Member, FileMaker Solutions Alliance

In article <133kul2du28o5ed (AT) corp (DOT) supernews.com>, Howard Schlossberg
<howard (AT) antispahm (DOT) fmprosolutions.com> wrote:

Ahh, the joys of multi-user databases. Unfortunately (or is that
luckily?) I rarely have to play with those, so I don't often think
along those lines. The records being "in use" by another user / users
is definitely another posibility causing the change to be "skipped".

It's usually best to do these "record processing" scripts at times when
nobody else is using the database.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)