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

Groups > comp.databases.filemaker > #3211

Re: Super Total?

Show all headers | View raw

On 2018-10-08 00:39:06 +0000, Helpful Harry said:

> 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.

Sorry, I realised that I missed out a step there. The above will work 
for an empty database, but for a database which already has data 
entered you will of course need to also set the AllRecords to have the 
data "All" for every existing record.

There are many ways to achieve this:

   - Temporarily put the AllRecords Field onto a Layout, go into
     Browse Mode and Find All records, then go through manually
     entering the "All" data into the Field. This is probably the
     fastest option for only a few records, especially if you use
     a Table View and simply copy the first "All" data and then
     quickly go down pasting it into all the other records.

   - For more than a few records, you can either ...

     A.  go into Browse Mode and Find ALl records, enter the
         "All" data into the first record and leave the text
         cursor in the AllRecords Field, then use the Replace
         Field Contents command in the Records menu to let
         FileMaker automatically enter the data for the other
         records,
     or

     B.  temporarily change the AllRecords Field to be a
         Calculation Field with the formula:
              = "All"
         and come out of the Define Fields window saving changes,
         then go back to the Define Fields windows and change
         AllRecords back to being a normal Field (the "All" data
         will be retained).


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