Groups | Search | Server Info | Keyboard shortcuts | Login | Register

Groups > comp.databases.filemaker > #3210

Re: Super Total?

Show all headers | View raw

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.


Helpful Harry  :o)

Back to comp.databases.filemaker | Previous | Next — Previous in thread | Next 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