DB Newbie, getting script to modify all records

[GRM Scott]

I've got completely minimal training with Databases, but I'm trying to broaden my horizons for a particular project at work...specifically we're making a database of clubs we deal with, and I'm trying to get it to print out labels for stuff to send out. My theory was that I could create a field called "Sent", and if the value of Sent was "no" then it would pull all those records, print them in label form, return to the master layout, change all the Sent fields from "no" to "yes", and then return me back home, with all the fields that had previously been at "no" changed to "yes" with labels printed out, and the previously "yes" fields untouched by the process. I've gotten it to print what I want, but it will only change one "no" to a "yes" at a time, so each time I run the script I end up with one less "no", but i need to to change all the "no"s.

Here's what the script looks like so far:

Show All Records

Perform Find [Restore, Replace Found Set]

Go to Layout ["Sendout Labels"]

Print []

Go to Layout [original layout]

Go to Field [select/perform, "Sent"]

Perform Find/Replace ["no", "yes", "Replace & Find"]

Show All Records

and that's what i've got...does this need to be a loop of some kind? Thanks so much to anyone who can help me, I know this is probably childs play to the majority of you, but in the world of databases, I am as a child, with little direction or control.

Whoever helps me get this working can have a free sample issue of the magazine I work for that you'll be helping, Grassroots Motorsports. (http://www.grassrootsmotorsports.com), so it's not a thankless act of kindness

[DanBrill]

Your on track in theory. Check your Find/Replace step. Direction should be set to 'All' and Search Within to 'All Fields'. I would probably have used a loop and a Set Field Command, but your method should work....

Good luck,

Dan

[EddyB]

Hi,

You're nearly there and yu are correct that it needs to be in a loop...

Show All Records

Perform Find [Restore, Replace Found Set]

Go to Layout ["Sendout Labels"]

Print []

Go to Layout [original layout]

Go to record/request/page [First]

Set Field ["sent","yes"]

Loop

Go to record/requst/page [Exit after last, Next]

Set Field ["sent","yes"]

End Loop

Show All Records

Hope this helps

Ed

[Vaughan]

The Show All Records is unnecessary before a Perform Find.

I'd put Allow User Abort [off} at the start to prevent users from cancelling the script part way through.

Then there's the error checking to handle the case where no records are found... it never ends!

[GRM Scott]

I tried DanBrill's approach, but that did the same thing as before, changing only the first record from "no" to "yes" and leaving the rest untouched.

Now I'm trying EddyB's method, but I can't get the Set Field to read ["sent","yes"]

how do you define this? I set the field as "Sent", and that shows up, but the closest I can get to having "yes" in the second field is by putting "yes" in quotes in the specifiy field, and that shows up in double quotes ["sent", ""yes""]

Thanks for the help so far, and in advance for further assistance! If there's a totally different way of doing this, i'm not afriad of that either!

-Scott

[EddyB]

Sorry Scott, my fault. What you are seeing is correct yes will show as ""yes""

Sorry! Ed.

[spb]

No need to mess with loops. Instead of Set Field, use Replace with calculation and make the calc simply "yes" (with quotes, result text). Altering your original script:

Perform Find [Restore, Replace Found Set]

Go to Layout ["Sendout Labels"]

Print []

Go to Layout [original layout]

Replace Contents (No dialog, "Sent", ""yes"")

No need to do another find. At the point you printed the labels, you have a found set of all records that need the sent field set to "yes." The "no dialog" option is key to keep the user from being annoyed by a "do you really want to do this?" error box every time the script is run.

Also, a Find always works on all records, so need need to show all records first.

Steve Brown

[GRM Scott]

EddyB,

Thanks, after posting that I tried with the double quotes and it worked, thanks to everyone who helped out. Eddy, if you want to email me your address at [email protected], I'll see that you get a copy of Grassroots Motorsports or Classic Motorsports, whichever you prefer. Thanks for the help, don't be surprised if you see me in the future asking more silly questions.

Thanks again,

Scott

[DanBrill]

Well now that's a fine precedent! Will we all have to start giving away magazines to get answers? Think of the postage to Australia and Paris. And what will Ugo and Vaughn do with all those magazines?

Ok, seriously, just about any of the methods mentioned here could work: loop with set field, Find and Replace, or Replace. Which is the best? Easiest to set up? Fastest? Best at handling errors?

Thoughts?

Dan

[kenneth2k1]

As many answers as I have gotten, I'd be broke sending magazines to everyone

In my opinion, it is easiest for me to set up a find/print/loop and set field script. Just from reading the post, my first thought is to make a script similar to Eddy's:

Allow user abort [off]

Set error capture [on]

Perform find [restore]

If (status(currentfoundcount)=0)

Show message [No records were found.]

else

Print - whatever print setup you need

Go to record [first]

Loop

Set field [sent , "yes"]

Go to record [next, exit after last]

end loop

end if

show all

That's just my preference.

[EddyB]

Hi Scott,

Don't worry about the magazine - Dan's right, it will cost you a fortune to start sending magazines out everytime you need some help.

If I did that I think I would be bankrupt by now - the amount of help I've needed in the past! I don't think Ugo's postman would be too happy either, a couple of months ago and he would have been delivering sack-fulls to his door!

This is a free forum and people enjoy being here and helping others, I'm always after help, if it wasn't for this forum I would not know half of what I do today about FileMaker!

Thanks for the offer though, it's very kind!

Best regards

Ed.

[bruceR]

Your problem is using the wrong replace function.

You referenced the "word processor style" replace funtion.

Perform Find/Replace ["no", "yes", "Replace & Find"]

You want to use the Replace command, using a calculated result. In FM6 it is now called "Replace Contents". You want to use the "Replace with calculated result" option, where you will enter your "Yes" value. You do not care what the prior value was. Also note that when using the replace command you must be on a layout that contains the field in question.