How do you produce the found set of records for this calculation in the first place? It may be possible to incorporate the same criteria in the self join relationship or, if using FileMaker 12, an ExecuteSQL() function call where you use ExecuteSQL in place of List in my earlier example.
we would be getting to the found set just using a manual search request (as opposed to a script) though setting up a "generate report" button might be a nice way to do it. Will also look at the executeSQL function, have never played with that one. Thanks!
My point was that if your user enters/selects criteria into a set of global fields and then a script uses that criteria to perform your find, you might possibly be able to use the data in the global fields in either a relationship or a SQL query to access the same set of records and now you can get your return separated list of values to use with Evaluate ( Substitute ( ...
Here's another approach:
Define a new layout with the same exact name selected in Layout Setup...|Show Records From. You might do that by using Duplicate Layout while in layout mode. Set up this new layout such that Index is the only field on this layout. When you perform a find on your original layout, this layout will have the same number.
Now a script can, after the find is performed (and this can be triggered by the user find), go to the other layout and use Copy All Records to copy the value of index for the entire found set and this will also produce the same return separated list of values that we need. Then it can paste this data into a global field which can then get the Evaluate ( Substitute ( treatment to produce this value. Get ( FoundCount ) can be used to count the number of records for the second part of the calculation.
Another method would be to use a looping script to loop through the records to compute the product so that you can then raise it to the 1/Get( FoundCount) power.
More excellent suggestions Phil. Especially like the "copy all" approach which had never occurred to me. Seems like I'm stuck using a script either before or after the find (which is not such a tragedy in the end) but now I've got quite a few more options for how to do that, in addition to my original thought of using a looping script. Thanks!