If it were me, I would leverage the power of a summary fields (e.g. N_avg) by (as you have done) a self join - with the match field would be what we normally refer to as a multi-key (which is a text field to allow list of unique ids - usually a global field since it is for selecting records).
Preparation - there is a unique id or serial number in every record, and you have built a self join from "multi-key field" (global) to the "unique id field" (which is a text field to allow list of unique ids)
method 1 - find the records you want to average, collect the unique id's in a list and set the global mult-key : all summary fields that have been created are then available for the found set as well as the self-joined relationships (you could have multiple groups of keys and all the various summary fields (sum, avg, min , max etc.) would report accurately.
method 2 - user can toggle records in and out of the multi-key group by inserting or removing the record's unique id from the mult-key field list via button or whatever.
Marx, Thank you for your reply.
I'm doing my best to understand the multi-key concept, so could you let me know if I have it correct? Here goes:
Unique ID is a LabID that is associated with each record (that is, each SiteID can have many different LabIDs)
Global key field: this is where I'm lost. Do I add a field to this table and identify the storage type as a global field?
Establish a link between these variables through a self-join relationship.
Once I've done this, I don't understand how to proceed. I'm leaning toward your 'method 2' and would appreciate if you could add a little more detail to your previous message.
Thanks again for your advice; I appreciate your contribution.
I can send a little sample file - do you have FileMaker advanced - I use custom functions and you would want to use them.
If not I can describe it more fully.
I'm using FM10 Pro. Would the sample work on this version?
Yes - I will make the quick version - and if you want to use the method - I can create a table based version of the custom functions that are used - or alternatively show you how to embed the custom function as a calc field in your database.
From where you are coming from you should consider upgrading to Advanced sometime in the future.
I have to run to the store - it is snowing in TEXAS - I will upload a sample file later today.
Thanks for the message. I'm going to purchase advanced in the near future; working with Pro as a trial version (and like it so far, although I don't like that the sidebar is now anchored to the top of the screen...anyway...).
Send the file when you get the chance. I'll be in the office until 17:00 CST and will check back throughout the day. Thanks for your help!
Have fun in the snow!
Marx, Ok, I think I understand what you mean. I'm going to try and implement the multi-key solution in a moment.
I had one other question for you: can you tell me which part of the lone script you have in this file is specifically for clearing the global field values? I think I found which part is most likely related to the 'clear value' step that I want, but I want to be certain before proceeding with my own script.