Solution: List a Found Set

Description:

to get data list coming from a foundSet, without CF, with a field calculated unstored

the field must be in the table source not on the layout

Working Under: list Found Set

Solution Status: Beta

Author(s): Agnès

Date: 08/01/08

Instructions / Other Info:

Hello,

it is a method much faster than CF especially if the data are sorted.

Little slower than copy all the records, but may be more flexible because you can list the various fields.

It is limited to 60000 records and I do not know the behaviour on a database

It's a test. I tested with 50000 records, and by script for saw the result, and it seems to me really fast.

but it is just a file test without much data

I do not know if it is a solution, and I would like your critiques and comments.

Thanks a lot

Agnès

Disclaimer:

FM Forums does not endorse or warrantee these files are fit for any particular purpose. Do not post or distribute files without written approval from the copyright owner. All files are deemed public domain unless otherwise indictated. Please backup every file that you intend to modify.

FoundSet_test_V1.fp7.zip

It's an interesting idea, though I don't quite see where it would be useful. If anyone needs to collect a found set this large, they'd be better off using Copy All Records, I think. With more reasonable sizes, a simple custom function (up to 10k/50k) or a repeating field (up to 32k) should serve quite well - I don't see a significant speed difference between these three methods.

However, if you really want to collect a huge found set this way, then I would suggest using a custom function for the final stage of "collecting" the intermediate stacks. For one thing, it would raise the theoretical limit from 60,000 to 7.5 million records (I'm not going to test this...). For another, it can be a lot simpler this way - see the attached file.

CalcFoundSetCFHybrid.fp7.zip

Hello Michael,

Thank you for your answer

and thank you very much for this example, very interesting !

There is effectively little difference with the method A.

But if we compare this method with ListFoundSet () and all the records are sorted, there is a great difference

I have just made a test with 3000 records sorted :

ListFoundSet 24 seconds, CollectFoundSet or ChargeFoundSet, 0 second.

And it is for it that interested me to try to make everything in one field

I have not a lot records to be treated, approximately 5000 but they are sorted, and CF which I knew were too slow.

thanks

Agnès

Hummm I like repeating Field, but I have just made a test with the repeating Field....

with 3000 records sort.... I believe that it is not a good idea :

Hmmm... that is very strange. I can see no good explanation for this. After all, they are all just calling GetNthRecord() the same number of times - so it's not a matter of we do, but what Filemaker does with it. It seems that after sorting, calling your neighbor becomes a lot cheaper than calling "long distance".

Agnès, again performance is impressive. Congratulations and thank you for sharing.

Once I've understood your code (it might be in a month or two), I'll try to figure out how your brain is organised. I'm sure this will take much, much longer, but if I go through I'll publish in Science. :

Hmmm... that is very strange..(...)

yes Michael, and that is why I do not want to use CF for my records and "copy all records" always asks a layout in addition, I wanted to try a calculation native.

again performance is impressive. (...)

thank you Fabrice ! This means that it may be exploitable !

in fact, I tried this method because Daniele was right on this post, Here and then, the method works with a ValueList on foundSet - a ValueListItems of all

I like Substitute ^ n :

thank you all both !

Agnès

Hello,

Just 2 precisions :

on the calc of ChargeFoundSet

if you change the Field Name (ChargeFoundSet becomes x_ChargeFoundSet for example), do not forget this name change in the calculation between quote

(...)

Calc = "GetNthRecord ( [color:red]ChargeFoundSet ; let ( $n = $n + 150 ; $n ) ) & $rc &" ;

(...)

and if you want to use ChargeFoundSet on IWP, it's possible if you put 60 instead of 150 (there are three 150 on the calc )

(...)

End = Floor ( Get ( FoundCount ) / [color:red]150 ) ;

Calc = "GetNthRecord ( ChargeFoundSet ; let ( $n = $n + [color:red]150 ; $n ) ) & $rc &" ;

Eval = Case ( End > 0 ; Substitute ( ( 10 ^ end ) - 1 ; 9 ; Calc ) & """" ) ;

CFSResult = Evaluate ( Eval )

];

Case ( Filter ( CFSResult ; ¶ ) = CFSResult ; "" ; CFSResult ) ) )

&

Case (

Get ( RecordNumber ) ≤ 1 or Mod ( Get ( RecordNumber ) ; [color:red]150 ) = 0 ; FieldNameList ;

(...)

I have not button "Edit" and I can change this first file

Thanks,

Agnès

Edited December 29, 200817 yr by Guest

I know this was written in 2008, but it is so cool to see it work. Wish it was broken down with comments and why do certain things in this way.

I love how every developer has his/her way and when you find a certain way to do something, you share it. Thanks Agnés ...!!

-i