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.

Charlie
--
Nobody in this country got rich on his own. You built a factory--good.
But you moved your goods on roads we all paid for. You hired workers we
all paid to educate. So keep a big hunk of the money from your factory.
But take a hunk and pay it forward. Elizabeth Warren (paraphrased)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

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?

Charlie
--
Nobody in this country got rich on his own. You built a factory--good.
But you moved your goods on roads we all paid for. You hired workers we
all paid to educate. So keep a big hunk of the money from your factory.
But take a hunk and pay it forward. Elizabeth Warren (paraphrased)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

Charlie
--
Nobody in this country got rich on his own. You built a factory--good.
But you moved your goods on roads we all paid for. You hired workers we
all paid to educate. So keep a big hunk of the money from your factory.
But take a hunk and pay it forward. Elizabeth Warren (paraphrased)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:

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.

Wow! You worked all that out by yourself! I'm impressed! (So impressed
that I used three concecutive exclamation points.) Thanks again, Harry.

Charlie
--
Nobody in this country got rich on his own. You built a factory--good.
But you moved your goods on roads we all paid for. You hired workers we
all paid to educate. So keep a big hunk of the money from your factory.
But take a hunk and pay it forward. Elizabeth Warren (paraphrased)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)