Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.filemaker > #3215

Re: Super Total?

Show all headers | View raw

On 2018-10-12 07:57:51 +0000, Charles H. Sampson said:
> Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:
>> On 2018-10-07 21:54:50 +0000, Charles H. Sampson said:
>>> 
>>> Is there a simple way to create a field that contains the total of
>>> another field of all of the records of a database -- independent of the
>>> found set? I've looked at my Pogue book and found nothing.
>>> 
>>> I know that it's pretty easy to write a script that does this and then
>>> set some appropriate script triggers. However, that involves the Show
>>> All Records operation and the whole point is to avoid messing with the
>>> existing found set. So, a related question is: Is there a way -- in a
>>> script -- to remember a found set and restore it later? I'm pretty sure
>>> I can do even this but my approach makes the state of the database
>>> somewhat fragile.
>>> 
>>> TIA, and I'll be appropriately embarrassed if this is obvious.
>> 
>> There's no need for a Script. Probably the easiest (not necessarily the
>> most efficient) way is to use a Relationship that groups *all* the
>> records together.
>> 
>> 1.  Create a new Global Field called g_AllRecords - the field type
>>     doesn't matter, so say a Text Field. Temporarily put it on a
>>     Layout and in Browse Mode enter the data of "All" into this
>>     Field. It can then be deleted from the Layout.
>> 
>> 2.  Create a normal record Field called AllRecords. Set it to
>>     Auto-enter the data value "All". This Field does not need to
>>     be on any Layouts.
>> 
>> 3.  Create a Relationship linking the Global g_AllRecords Field to
>>     the normal AllRecords Field. This links *all* the records in the
>>     database together as a group, whether or not they are in the
>>     current Found Set.
>> 
>> 4.  Now you can create a Calculation Field called c_Total_AllRecords
>>     with the calculation of:
>>          Sum (Relationship::NumField)
>>     where NumField is the Field you want to total. Make sure it uses
>>     the Relationship version of the NumField, not the normal version.
>>     This Field can be set as Global storage as well if you want to
>>     save space in a database with MANY records.
>> 
>> Done. Put the c_Total_AllRecords wherever you want the full dtabase
>> total to be shown. It can of course also be used in other calcualtions
>> and summaries.
>> 
>> As a side note, you can use the same method for any of the "Aggregate"
>> functions to get an Average, Min, Max, Count, etc. of *all* the
>> database records regardless of the current Found Set.
> 
> Thanks, Harry. I've tried this twice now and it hasn't quite worked for
> me. The second time I slavishly followed your instructions to the point
> of using exactly the field names you specified.

You might have missed the extra step I left out and posted yesterday, 
although it's only needed if you have existing records in the database.

Also make sure the Calculation Field c_Total_AllRecords is using 
NumField via the correct Relationship / Table Occurance (use the pop-up 
menu in the Field picker to find the correct one), not the original 
Table. Using the original Table's version will only give you the 
"total" value for that one record, with each record having it's own 
"total". Using the Relationship you get the summation from NuField in 
all the records.



> I have two questions.
> 
> In your step 1, you say to "enter the data of "All"" into the newly
> created field. Do you mean to enter the three letters A-l-l or the
> 5-character sequence "All"?

Just the word All will do. You can use anything you want as the data, 
but make sure you use the same data in both the Global g_AllRecords 
Field and all the normal AllRecords Fields - that's what makes the 
Relationship link to group together the records.



> In step 3, when I create the relationship, FMP says that it must create
> another occurrence of the table to the graph and it does so. Is that 
> o.k., or is that where I'm going wrong?

Since the addition of the silly 'tree diagram' system, creating a 
Relationship does create a new "Table occurance". Really it's just a 
change in terminology, but it pointlesly makes FileMaker more 
complicated than it used to be. The old way was much easier to use and 
understand.  :o(


Helpful Harry  :o)

Back to comp.databases.filemaker | Previous | Next — Previous in thread | Find similar

Thread

Super Total? csampson@inetworld.net (Charles H. Sampson) - 2018-10-07 14:54 -0700
  Re: Super Total? Helpful Harry <HelpfulHarry@BusyWorking.com> - 2018-10-08 13:39 +1300
    Re: Super Total? Helpful Harry <HelpfulHarry@BusyWorking.com> - 2018-10-12 09:11 +1300
      Re: Super Total? csampson@inetworld.net (Charles H. Sampson) - 2018-10-19 13:29 -0700
        Re: Super Total? Helpful Harry <HelpfulHarry@BusyWorking.com> - 2018-10-20 09:55 +1300
          Re: Super Total? csampson@inetworld.net (Charles H. Sampson) - 2018-10-21 14:38 -0700
    Re: Super Total? csampson@inetworld.net (Charles H. Sampson) - 2018-10-12 00:57 -0700
      Re: Super Total? Helpful Harry <HelpfulHarry@BusyWorking.com> - 2018-10-13 10:42 +1300

csiph-web