Refreshing Calculations

[Silez]

I have a question concerning getting a calculation to refresh. I have a calculation that counts the number of unique records in a database using this method that I found on the forums:

TWO SUMMARY FIELDS, ONE CALCULATION, AND SORT METHOD

If your version of FileMaker Pro does not support the ExecuteSQL function, you can accomplish the same results using a couple of summary fields, one calculation field and sorting your database.

Start by defining a summary field called "TotalCount" which is a Count Of LocationField

Next, create a calculation field called "FractionOf" with the following expression:

1 / GetSummary (TotalCount ; LocationField)

This calculation gets the reciprocal of the TotalCount summary field. The GetSummary function is used to return the value of the "TotalCount" summary field when the database is sorted by the "LocationField" field.

Lastly, create another summary field called "UniqueCount" which is a total of the "FractionOf" calculation field. The "UniqueCount" field is the only field that you need to place on your layouts (if needed) to display the count of unique records.

After you have created the three fields above, sort your database by "LocationField" to provide a count of unique records in the "UniqueCount" field.

 

The problems that i am running in to is getting the calculation to refresh after a new find/sort. In order to get the calculation to refresh, I need to go into manage database, tweek the calculation field and then exit the manage database screen. The causes the calculation to refresh and I get the right count. Is there a way to force the calculation? I have tried commit records and refresh screen with no luck. 

Thanks in advance.

Eric

[Ocean West]

Eric, welcome to the forums. 

Would this help?

 https://www.teamdf.com/blogs/ditch-those-flush-caches-use-cartesian-join-instead/

[Fitch]

I'm not quite sure what the problem is, since summary fields and calcs based on them are unstored.

At any rate, here's an alternative method you might try:

1.     Define a value list that's based on the field you want to count.
2.     Use the ValueListItems function to get a list of the unique values.
3.     Use the ValueCount function to count that list.

HTH.

[comment]

51 minutes ago, Fitch said:

At any rate, here's an alternative method you might try:

1.     Define a value list that's based on the field you want to count.
2.     Use the ValueListItems function to get a list of the unique values.
3.     Use the ValueCount function to count that list.

That will count unique values in the entire table, not just in the found set, as the described method does. You would have to filter a summary field defined as List of by the value list inorder to get the same result: http://fmforums.com/topic/93917-subsummary-count-of-related-parents/#comment-429470

[Fitch]

He did mention wanting to refresh after a find or sort, but I wasn't sure -- that was how I interpreted "counts the number of unique records in a database."