Replace Script

[mad_mickey]

I need to create a script to replace information in multiple fields of a current found set.

I am concerned at giving the database users the replace command due to the ease at which data corruption can occur . Conversely, though I would also like the users to be able to make changes to fields in multiple records to save time wasting repetition of data input. The answer to this is a script that will prompt the user to find the CORRECT found set and then initiate the changes to this group of records.

As a result I need a script that will initially ask the user to

1) Input two criteria for the search

2) Make the required changes to the fields in one record

3) Apply these field changes to all the records in the current found set

I am presuming that this should be possible but could someone please point me in the right direction with a sample script. Thanks.

[Steven H. Blackwell]

I would use a looping "Set Field" routine instead of a replace. It's safer and it's faster. Be aware of record locking issues in this routine (or in the replace either for that matter). If doing this over a network, have the first step of each record's update be a go to field so as to lock the record.

HTH

Old Advance Man

[mad_mickey]

Could anyone give me a sample script for this?

[Vaughan]

OAM wrote: "have the first step of each record's update be a go to field so as to lock the record"

But what if it's in use by another user and locked already?

[mad_mickey]

Can anyone give a sample script for this request??

[tlsparker]

I think you could use the replace function safely as long as it is embedded in a script. I would minimize the need for user interaction (and resulting opportunities for data corruption) by having users input all required info (two find requests and the replace data) into global fields on a data input layout. Once the data is entered in global fields, have the user click a button that initiates a script:

Go to Layout[x]

Set Error Capture[on]

Enter Find Mode

Set Field ["SearchField", "FirstGlobal"]

Set Field ["SearchField2", "SecondGlobal"]

Perform Find

If

Status(CurrentError)=401

Show Message "No records, try again..."

Go to Layout [original layout]

End If

Replace["DataField", "ThirdGlobal"]

Go to Layout [original layout]

Of course, you need to substitute your own field names. I assumed you were searching on two different fields. If you are searching for two different criteria in the same field, you would need to insert a new find request before the second set field step.

Good luck!

Tom

[email protected]

http://www.tgparker.com/filemaker

[Steven H. Blackwell]

You must trap for Error 301 (record is locked) and deal with any record so affected. And this is the case whether using Set Field in a loop or using the replace. BTW, if the first record in the set is locked, the entire routine will fail.

If setting more than one field in a record in a loop, seize the record first, then set all the fields, then release the record. This is dramatically faster over a network.

HTH

Old Advance Man

[cinolas]

"If setting more than one field in a record in a loop, seize the record first, then set all the fields, then release the record. This is dramatically faster over a network."

faster than simply setting the fields ? What if I only have to set one field ? Is it still faster to Go to a field first then set it ?

Thanks

[tmanning]

Why don't you have them put the changed information into a global temp field? Make a script like this...

-Prompt user for find criteria

-Perform Find

-Pause for user verification

-Prompt to enter the information into the global temp field

-include any internal error checking here if necessary

-do a replace all and set the field to be replaced with the information in the global temp field

Alternatively, you could skip the temp field and allow users to change the information directly in the field to be changed and then prompt to make the change (Are you sure you want to do this, yadda yadda yadda...). You could even make a custom dialog box that would compare the two fields (Are you sure you would like to replace all instances of 'Jones' with 'Smith'?) The replace would not occur until they clicked continue.

If you are truely worried about data integrity, you could make a change log on each record (although this is a pain in the rear, it really does work!) Set up a field called Log. Make a script called Backup which will copy text from the current field and paste it into the Log field and give it a date, time, and carraige return. Call the Backup as a subscript within other scripts before major changes are initiated in other scritps (delete, replace, cut, etc.). This will leave a nice log of changes made to each record and if you accidentally replace the stuff in the wrong field, at least this way you will know what the information used to be in that field. For extra accountability, you could even have it note Status(Current User). --- "Hey Sally! You screwed up all the 'Jones' Records! You're fired!"

Okay, well be nicer than me. Anyway, I hope this makes you feel more at ease about letting people mess with your data!