Running A Script on All found records

[nightdesigns]

I do a search that returns X records. I want to run the same script on everyone of those records. How can i do this without going through each one at a time.

[Fitch]

Loop

. Perform Script( )

. Go to Next Record (Exit after last)

End Loop

[Rigsby]

There are 1 or 2 points I would keep in mind with regards to scripts on whole databases or large found counts.

You have two main ways for changing lots of records. One is the loop command, as stated by Fitch, and one is the replace command. So you need to think about what you want to change. In many cases a replace will do the job just as well, especially as you can replace using a calculation, and is about 5 times faster than a loop. One great speed advantage of a replace is that you have access to the Case statement, whereas a normal script offers only the If statement. I can’t give you definite figures on the speed differences, as it depends on the calculation involved, but keep this in mind: FMP (or any program for that matter) works through the whole set of embedded If-statements, until every If has been tested even if a result is found. In contrast to this, a Case statement stops as soon as the criteria is met. For this reason Case is much faster.

The loop method is of advantage if you want to change multiple field values with one step; this is especially important if one result is dependant on another newly changed field. Replace can only change one field at a time, so you would have to go through the whole found count several times to change more than one field.

Tips are:

With replace commands, the field you want to edit must be on the current layout. If you run into a record that another user is editing, the record will not be changed, but you won’t know about it – which can be a problem.

With loops, always use a blank layout or freeze the window whilst looping. I have a 10 second benchmark for testing this, and with a simple script, FMP managed about 200 changes within a normal layout, about 2000 with the window frozen, and about 12000 with a blank, frozen layout.

Hope this all helps,

Rigsby

[BobWeaver]

There are situations where looping is faster than using a Replace, especially if you are changing lots of fields in each record.

[Steven H. Blackwell]

>In contrast to this, a Case statement stops as soon as the criteria is met. For this reason Case is much faster.

This is not correct. Case statements evaluate all arguments and all results and then return the result that mtches the first true argument.

In a networked situation, when using Set Field or Insert Calculated Result in a loop where there are multiple fields per record to be acted upon, as a first step on each record use a Go To Field and select a field. THat loks ther ecord, allows all changes on it to be ade at onece, and transmits the changes back to the server in batch. This will have a dramatic impact on the speed of the action.

Finally be aware that some records in the found set could be locked in a multiuser situation. You will need to trap for that (Error 301) and decide how to handle it. Be aware that if the first record in the found set is locked, that the entire process can fial.

HTH

Old Advance Man

[Rigsby]

What I meant was:

A case statement doesn’t need to evaluate negative returns if the first case returns a positive result, and/or, doesn’t need a default negative result response stated, because if the case isn’t met, FMP will automatically return a default of 0, if no default is stated. For this reason, Case often brings much less overhead with it, and is thus usually much faster than the If statement.

I’m currently working on a project for a company with a seriously over-worked DB. One script they use on a weekly basis was taking around 15 – 18 hours to complete. By switching most of the Ifs to Cases, I got it down to about 10 – 11 hours. After that, I got rid of just 6 calculating fields, and now use the replace step to set 6 number fields to the correct values. The script is now down to 3 – 4 hours.

Rigsby

[Steven H. Blackwell]

Case statements evaluate all arguments and all results and then return the result that matches the

first true argument.

Thus, a formula:

Case (a,1,

b,2,

c,3,

d,4)

will evaluate all the "tests" a,b,c,and d and all the results 1,2,3,and 4. Then it will return the result for the first true "test".

Old Advance Man

[Fitch]

I always thought Case() evaluated as Rigsby said, i.e. only until the first True expression. The FileMaker online help is open to interpretaion:

"The case function evaluates each expression in order, and when a TRUE expression is found, returns the result supplied for that expression."

I suppose one could test this by creating some gigantic Case() functions and timing them. OAM, I'll take your word for it, but what's your source?

[Steven H. Blackwell]

Hhe understanding you have is a commonly held view; and I am not really sure how it originally started or how it took view.

At the FileMaker DEVCON in 2000 in Palm Desert, California, this issue came up in a Q&A on the Experts Panel. Both the FMI Engineer and several Partner developers on the panel had to correct this misconception. I suppose I originally understood it to work as I have described it here. It is most evident when using some plug-in that gives a visible or audible response.

I do not think there is a TIL that specifically describes this. I will suggest that one be written.

HTH

Old Advance Man

[danjacoby]

Q. How many software developers does it take to change a light bulb?

A: 106. One to change it, and 105 to write the Help file.

[Rigsby]

It seems we are talking in circles here. Maybe I just haven’t presented myself clearly enough. The point is, that an If statement requires at least 2 values, True & False. E.g.:

If(FLOWER= "Rose", "Red","")

In this situation, FMP checks the first statement, “Rose” and if it finds “Rose” it returns the value “Red”. If it doesn’t find “Rose” then you have to tell it what value to return, in this case “”. I.e. empty.

Case, on the other hand, needs no default value. If you do not define a default value, FMP returns 0 or empty. E.g.:

Case(FLOWER = "Rose"; "Red")

So, in some cases, Case will be much, much faster than If. In addition to this, case will almost always offer the developer a much clearer picture of what he is trying to do. To sum this up, lets just put the above calculations into plain English:

IF Statement =

If you find the value “Rose” in the field FLOWER, please set this field to the value “Red”, otherwise set this field to the value “”

Case statement =

If you find the value “Rose” in the field FLOWER, please set this field to the value “Red”

There are embedding techniques, where If still finds its uses, but I would advise any would be developer, to get to grips with the case statement as soon as possible.

I understand what you are saying OAM, but some of my tests on this have been run over DBs containing 500,000 files, where the difference between If and Case is a matter of hours, not minutes.

Rigsby

[Gogargirl]

Ok, into the fray here.....

Filemaker has contributed to the commonly held view that Case tests stop at the first true result by having this in their paper manual (their italics):

"Evaluates test(s) to produce a result. When test1 is true (a non-zero value), returns result1; when test1 is false, evaluates test2. When test2 is true, returns result2 , and so on. When no test is true, returns an empty (null) value, or the default result you supply."

This clearly implies that a test is only evaluated if a preceding test is false.

[elvis_impersonating_penguin]

quote:

---

Originally posted by danjacoby:

Q. How many software developers does it take to change a light bulb?

A: 106. One to change it, and 105 to write the Help file.

---

but, how many Microsoft software developers does it take to change a light bulb??

none, they just declare darkness to be the new standard.