Improving GetFieldStat custom function for calculation on big list of data in ram

[alimrb]

http://www.briandunning.com/cf/493

Evaluate ( Function & " ( " & Substitute ( Field ; "¶";";" ) & " )" )

this custom function has limitation of calculation character count for big lists of data and caused filemaker be crashed.

i must develop some analytic reports on some unknown and user defined tables and create some dynamic reports with some scripts with sophisticated analysis function like benford low and histogram, after user choosing the name of table and field. now because of the custom function limitation, i must do the calculations and compares on data list that be copied to the variable from source field, one by one, and in big list of data running the scripts with my method is very slow.

i can't use the custom function with this definition.

can anyone help me improving the function to work with unlimited list of data ?

or can anybody show me some other functions without this limitation?

[comment]

"Big lists of data" should be in records, not in return-separated lists. Then you can use summary functions or - preferably - summary fields to summarize them.

Users should be able to select which data to summarize by performing a find. Having them choose the table or the field is not only an indication of poor structure; it is also very likely to result in slow performance.

[alimrb]

thanks consultant,

my Filemaker solution is something like IDEA CaseWare and acl auditing software and users can import their different financial data from different sources and the solution make users able to analyze their data with some important auditing functions like benford's law and histogram and ... for fraud detection. and make their auditing worksheet with the solution's dynamic reports.

now, as you said i must transfer the user's selected tables and fields and foundset as the solution functions' parameter to a static table, and then make computations and reports on the static fields. not bad, but in records larger than 500000 based on the number of parameters of analytic function or script, the process of transferring data and making computing fields is become very slow because the solution involved with disk writing and committing data.

but if i can use the partial data lists variable instead of a single big data list variable and GetFieldStat, first parts the data to smaller lists with length of maximum limitation of calculation text, maybe by using a loop and a counter variable, and then apply the functions to any parts and then apply the function to the all parts' result,the calculation of analysis totally be ran in memory so faster, and a little records be wrote to the report's field and the the worksheets be showed.

[comment]

I am not sure Filemaker is the best choice for what you describe. For one thing, a database expects data to be structured, and the structure to be fixed in advance. For another, Filemaker has never won any speed contests. The fastest way it will get statistics is by using summary fields. With 500,000 records, even that won't be very fast - and that's when the summary fields summarize stored data fields. If you intend to point your summaries to unstored calculations (fetching data from fields selected by the user), you must be prepared to wait.

but if i can use the partial data lists variable instead of a single big data list variable and GetFieldStat, first parts the data to smaller lists with length of maximum limitation of calculation text,

I don't think that's going to be any faster, but perhaps you want to look at something similar here:

http://www.briandunning.com/cf/868