I have been exploring how to get data sets such as record ID lists from a found set. The following technique builds a a list at about 10,000 records/second. A 200,000 row list is built in 16 seconds. Processing 20,000 records is basically instant. Of course you can accumulate data from any field you want.

The general idea is that:

1. The replace operation works on a found set without navigating the record set.

2. A replace operation can be performed on a global field; thus it is multi-user safe and does not change your data.

3. A replace operation can use let functions and variables and does not even have to actually write anything to the global field.

Data is captured into $variable[n] in 5,000 row lists.

This batching seems to speed things up.

Then the list of $variable[n] is accumulated into a global $$variable.

The global field must at least be related. It appears that peformance is greatly increased if the global field is defined in the table that the layout is based on.

Get List Using Replace

#Use replace operation on global field to accumulate list of values into variable

Freeze Window

Beep

Set Variable [ $t; Value:GetAsNumber( Get( CurrentTime )) ]

Replace Field Contents [ thisTable::zTemp;

Replace with calculation:

Let ( [ N1 = Get( RecordNumber) ;

N2 = Div( N1; 5000 ) ;

$z[n2] = $z[n2]& ¶ & thisTable::RecordID ] ;"")]

[ No dialog ]

Beep

Set Variable [ $n; Value:1 + Div( Get( FoundCount) ; 5000 ) ]

Set Variable [ $k; Value:0 ]

Set Variable [ $$z; Value:"" ]

Loop

Exit Loop If [ $k > $n ]

Set Variable [ $$z; Value:$$z & $z[$k] ]

Set Variable [ $k; Value:1 + $k ]

End Loop

Set Variable [ $t; Value:GetAsNumber( Get( CurrentTime )) - $t ]

Beep Show Custom Dialog [ Message:

List( $t ; ValueCount ( $$z ) ; RightValues( $$z; 10));

Buttons: “OK”, “Cancel” ]

Enjoy. Comments welcome.

Demo available here (17MB zipped) :

http://concise-design.com/downloads/fastvariables.zip

--

Bruce Robertson

Concise Design

FileMaker 9 Certified

It's always nice when people take the time to nail down issues, similar as the execution order of triggers vs. autoenters:

http://www.fmpro.org/news/369871967933/official-use-our-drag-drop-technique-with-confidence.html

Excellent work Bruce, and nice to know the overhead involved with custom functions - one thing though, what if the recursion is omitted and replaced by a Evaluate( ... such as was done here:

http://www.tictac.fr/CoinFileMaker/PageChargeFoundSet.html

...by whom I BTW ahead of your template learned this:

1. The replace operation works on a found set without navigating the record set.

2. A replace operation can be performed on a global field; thus it is multi-user safe and does not change your data.

3. A replace operation can use let functions and variables and does not even have to actually write anything to the global field.

But your angle to remedy the missing use of Extend( with repeating variables are indeed welcome!

--sd

Regarding recursive cf, note that the custom function used in the example has very little recursion, and does accumulate a large expression and evaluate it. I received an example from another person, a guy in Australia who had his own cf, non recursive, that got the next 100 records. He also created script statements that did the same thing. That approach seems pretty fast.