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

Groups > comp.databases.filemaker > #3217

Re: Super Total?

Show all headers | View raw

On 2018-10-19 20:29:07 +0000, Charles H. Sampson said:
> Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:
>> 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).
> 
> That worked, Harry. Thanks. I particularly needed the "Replace Field
> Contents" step, since I was working with around 7700 records.
> 
> Is there any explanation for the steps involved? Is it a well-known
> technique? I thought I understood databases pretty well, but this has me
> stumped, in particular the self-related table bit.

No idea if there's any explanation or how well-known it is. I've never 
bothered with any of the books / websites, etc., and usually just work 
out solutions to things myself. I don't even know what you'd 
"officially" call this kind of total ... it's not a 'grand total', for 
instance.

A quick Google search for 'super total' does bring up a few places like 
these with the same answer:
<https://community.filemaker.com/thread/115094>
<https://forum.filemakertoday.com/topic/29481-sum-all-records/>

It is a relatively straight-forward method though. The relationship 
simply enables you to access all of the records, regardless of the 
current Found Set, by creating a way of 'grouping' them ALL together 
... as long as each record has that "All" data.

I can't remember what I originally used this method for way back in 
FileMaker 3 days, possibly to obtain a count of the number of records 
in the database for the second part of a custom "X found records out of 
Y total records" message.


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