Hello, Rick. There are at least few ways to accomplish this.
1) Use a script, as you mentioned, to just set the field. Possibly using a Script Trigger.
2) Create another table with a single record and nothing but global fields (like a Connector), or one field per user (sessions model), and use a relationship to fetch the data.
3) Use the Minimum and Maximum summary field types and a GetSummary function to make a calculation field that determines what you need. For example:
GetSummary ( dateMax ; dateMax ) - GetSummary ( dateMin ; dateMin )
where dateMax is a summary field finding the maximum creation date, and dateMin is a summary field finding the minimum creation date. (Warning: this might be slow if you have lots of records.)
Thanks for fast response Mike, I'm afraid your talking a bit over my head so I will have to spend some time figuring out how to apply the solutions you have proposed.
I only have a couple of hundred records a present but that is growing. The third option looks like something I could try fairly quickly. I'll let you know how it turns out.
In addition to what Mike mentioned, try this calculation field:
medication ≠ GetNthRecord ( medication ; Get ( RecordNumber ) - 1 ) ;
Let ( [
startDate = theDate ;
countThisGroup = GetSummary ( sCountAll ; medication ) ;
endDate = GetNthRecord ( theDate ; Get ( RecordNumber ) + countThisGroup - 1 ) ;
totalMedicationThisGroup = GetSummary ( sTotalOfDosage ; medication )
totalMedicationThisGroup / ( endDate - startDate + 1 )
and put it in your sub-summary part.
• a summary field, type TotalOf:dosage, and a summary field CountOf:<anyNonEmptyField>
• the found set is sorted by medication, and by date, ascending
I'm afraid I'm too inept to make this command work. I must be missing something and I suspect I'm too much of a novice to understand some of the basic functions that you have incorporated in your formula.
I tried pasting your formula into a calculation field I created for this purpose but when I tried to save it Filemaker doesn't like a number of the items in the command string. The first thing it questions is "theDate" it says it can't find that field, it also doesn't like the sCountAll or the sTotalOfDosage. Do I need to change some of the text of field names or create additional fields to make it work with my application or are these commands that Filemaker is supposed to recognize and preform some action when they are phrased as you have done in your reply?
I'm afraid I'm a bit lost.
Do I need to change some of the text of field names or create additional fields to make it work with my application or are these commands that Filemaker is supposed to recognize and preform some action when they are phrased as you have done in your reply?
A calculation is usually a mix of (built-in or custom) functions that operate on values that you get by referencing field (and other objects) from your database. In the changed code below, functions are the bits in italics.
So e.g. GetNthRecord ( … ; … ) is a function, but the arguments (in parentheses) are fields or variables.
You need to make sure you have created/performed the components described in “Required bits”.
Then change the references in my calculation to the corresponding fields in you solution – the bits in bold. I now used the field names you listed in your original post, so you only need to make sure you have the two summary fields. If you name them the same, then you should be able to simply paste this calculation.
Medication ≠ GetNthRecord ( Medication ; Get ( RecordNumber ) - 1 ) ;
Let ( [
startDate = Date ;
countThisGroup = GetSummary ( sCountAll ; Medication ) ;
endDate = GetNthRecord ( Date ; Get ( RecordNumber ) + countThisGroup - 1 ) ;
totalMedicationThisGroup = GetSummary ( sTotalOfQuantity ; Medication )
totalMedicationThisGroup / ( endDate - startDate + 1 )
if (“in case”) the record is the first one in a sub-group (the first “new” medication)
declare a variable to hold the earliest date (from this first record per the sort order)
declare a second variable to hold the record count in this sorted group
using that information, declare a third variable to get the latest date (from the last record as per sort order)
declare a fourth variable that hols the total quantity in this sorted group
use variables one, three and four to calculate the result of this entire formula
[implicit else: do nothing if it's not the first record – which isn't really necessary, because you place this field in the sub-summary part, which only reflects values from the first record in the sub-group anyway]
Understand that field names like “sTotalOfQuantity” or “sCountAll” are supposed to be generic names that describe what these field are/doing – summary fields that total or count another field. So you need to make sure you have these fields – it doesn't matter what you call them as long as you reference them in the correct place.
Thanks for all your help erolst, but I'm still not getting a return so I presume I haven't got the correct field settings for the summary fields your calling "sTotalOfQuantity" for which I have a field summing the field I called "Quantity". And the one I'm really struggling with "sCountAll". I'm not sure what field I should be counting. I've tried Medication, Date and Quantity but none of them seem to work. Sorry I'm being such a bother but I think you've got me close to what I want to do but I just can't quite make it work.
I'm really struggling with "sCountAll". I'm not sure what field I should be counting. I've tried Medication, Date and Quantity but none of them seem to work.
Basically, count any field that will never be empty (normally, one would use the table's primary key), because that will tell you how large your group is.
Anyway, just have a look into the attached sample file; that should make things clearer.
RickSR_eos.fmp12.zip 117.6 K
Ah Ha! Now I see what I was doing wrong. I wasn't phasing the GetSummary arguments correctly.
Thank you so much for taking the time to put this together for me, it was a real help. You've opened my eyes to a whole new range of possibilities.
I very much appreciate your help erolst.