AnsweredAssumed Answered

Calculated Button Bar Segment name including Sub Summary

Question asked by Cécile on Jan 30, 2018
Latest reply on Feb 15, 2018 by mike_wallace_rcc

Like • Show 0 Likes0
Comment • 7

I don't understand quite yet the Summary elements in FM.

I have several button segments that open corresponding layouts with found sets or list of records of found sets. I want to see the total number of items that would be in the found set if the found function was runned. These buttons are repeated on several pages and depending on what is being selected in the relevant field, the count will varie.

E.g.

Button must show :
Pending Initial Review (5)

Here is the calculation that I hoped would create that name (it returns a ?)

"Pending Initial Review (" &

ExecuteSQL ("SELECT COUNT( \"Dossiers::OccurencesPIR\" ) FROM \"Dossiers\" WHERE \"Dossiers::StepRP\" =?"; ""; ""; "Pending Initial Review") &

")"

Dossiers::OccurencesPIR is a summary field (option count selected, running count checked)

I have placed the OccurencesPIR field on the Layout that corresponds to the table Dossiers to monitor the number. It shows the number of values found in the OccurencesPIR field of the active found set rather than the whole DB.

I am not quite sure how to go from here.

No one else has this question

Outcomes

mike_wallace_rcc Jan 30, 2018 4:24 PM

The problem is that you are counting a summary field. Count a field that will always be there i.e. a primary key.

Be careful though because calcualtions like this will run over and over every time you change records. It might be better to run a script on a script trigger when you enter the layout that sets some global variables and then reference those global variables in your button names. That will not cause a slow down in your database like your current method may.

If you are concerned about the data changing frequently then you can always set an timer script to run every 5-10 (or whatever) minutes to update the global variables and refresh the individual objects that are effected by the global variables. This will still be MUCH less than the calculations will run if they are just live on a button.

2 people found this helpful

Actions

beverly

@ mike_wallace_rcc on Jan 30, 2018 4:35 PM

Awesome answer, Mike! Thank you. Do you think you could write a different query showing what you mean for OP?

Beverly

1 person found this helpful

Actions

user19752 Jan 30, 2018 7:01 PM

Missing point on mike_wallace_rcc s answer is, double colon is not used in SQL and table and field are quoted individually like "table"."field"

So how about

"Pending Initial Review (" &
ExecuteSQL ("SELECT COUNT(*) FROM \"Dossiers\" WHERE \"StepRP\" =?"; ""; ""; "Pending Initial Review") &
")"

COUNT(*) is special (standard) syntax for counting found set, not depend on any field value.

In addition, SQL matches case sensitive. So "Pending Initial Review" should be exact text in the field.

3 people found this helpful

Actions

planteg Jan 30, 2018 7:31 PM

If you use FMPA v 16, test your query in the Data Viewer. Go to the Watch tab, select to evaluate automatically. The bonus is if the query is in error, the result will display an error message instead of ?.

2 people found this helpful

Actions

Cécile Jan 31, 2018 4:58 AM

Thank you everyone!

user19752 It works like a charm!

mike, the toll on the db was my concern but so far it does not seem to affect performance. I will monitor this and make the change to variables if it becomes and issue.

Actions

Cécile @ Cécile on Feb 15, 2018 6:50 AM

I wanted to update on this. I had to remove the SQL calculation from the buttons because on the layout where we consult records, the recalculation it was doing everytime a record was loaded was affecting performances for my colleagues while it was making my computer hang endlessly. Why the difference for them and my? No idea. They are on FM Pro and I am on FM Advanced we use FM Server.

Anyhow. Have not had time to devise and implement the variable strategy but it seems it will be the way to go.

Actions

mike_wallace_rcc @ Cécile on Feb 15, 2018 8:52 AM

Let me know if you need any help with the variable method. One reason they may see the difference in production (daily use) that you do not see in development is the caching that happens. FileMaker caches finds and record sets as it pulls them down so while you are testing the first time will be much slower than subsequent times. In development we don't tend to follow real world patterns but test with the same set of records or patterns. Real world patterns would require different sets of data on a regular basis. Each of these new sets will have to be pulled down before it is cached. By that time the person is on to the next task.

Another option is to pre-cache the counts in the parent records on the server periodically.

1. Loop through the parent records in a scheduled server script and calculate your summary records.

2. Compare them to the previously cached data.

3. If the are different then update the cached data in a field in the parent record.

This will have zero lag time in real life where as the variable method I listed above will have some as you change records but still MUCH less than something that is recalculated constantly like you originally did.

1 person found this helpful

Actions

7 Replies

mike_wallace_rcc Jan 30, 2018 4:24 PM
Mark Correct
Correct Answer
The problem is that you are counting a summary field. Count a field that will always be there i.e. a primary key.

Be careful though because calcualtions like this will run over and over every time you change records. It might be better to run a script on a script trigger when you enter the layout that sets some global variables and then reference those global variables in your button names. That will not cause a slow down in your database like your current method may.

If you are concerned about the data changing frequently then you can always set an timer script to run every 5-10 (or whatever) minutes to update the global variables and refresh the individual objects that are effected by the global variables. This will still be MUCH less than the calculations will run if they are just live on a button.
2 people found this helpful
Like • Show 1 Like1
Actions
beverly @ mike_wallace_rcc on Jan 30, 2018 4:35 PM
Mark Correct
Correct Answer
Awesome answer, Mike! Thank you. Do you think you could write a different query showing what you mean for OP?
Beverly
1 person found this helpful
Like • Show 0 Likes0
Actions
user19752 Jan 30, 2018 7:01 PM
Mark Correct
Correct Answer
Missing point on mike_wallace_rcc s answer is, double colon is not used in SQL and table and field are quoted individually like "table"."field"
So how about
"Pending Initial Review (" &
ExecuteSQL ("SELECT COUNT(*) FROM \"Dossiers\" WHERE \"StepRP\" =?"; ""; ""; "Pending Initial Review") &
")"

COUNT(*) is special (standard) syntax for counting found set, not depend on any field value.

In addition, SQL matches case sensitive. So "Pending Initial Review" should be exact text in the field.
3 people found this helpful
Like • Show 2 Likes2
Actions
planteg Jan 30, 2018 7:31 PM
Mark Correct
Correct Answer
If you use FMPA v 16, test your query in the Data Viewer. Go to the Watch tab, select to evaluate automatically. The bonus is if the query is in error, the result will display an error message instead of ?.
2 people found this helpful
Like • Show 2 Likes2
Actions
Cécile Jan 31, 2018 4:58 AM
Mark Correct
Correct Answer
Thank you everyone!

user19752 It works like a charm!
mike, the toll on the db was my concern but so far it does not seem to affect performance. I will monitor this and make the change to variables if it becomes and issue.
Like • Show 1 Like1
Actions
Cécile @ Cécile on Feb 15, 2018 6:50 AM
Mark Correct
Correct Answer
I wanted to update on this. I had to remove the SQL calculation from the buttons because on the layout where we consult records, the recalculation it was doing everytime a record was loaded was affecting performances for my colleagues while it was making my computer hang endlessly. Why the difference for them and my? No idea. They are on FM Pro and I am on FM Advanced we use FM Server.
Anyhow. Have not had time to devise and implement the variable strategy but it seems it will be the way to go.
Like • Show 1 Like1
Actions
mike_wallace_rcc @ Cécile on Feb 15, 2018 8:52 AM
Mark Correct
Correct Answer
Let me know if you need any help with the variable method. One reason they may see the difference in production (daily use) that you do not see in development is the caching that happens. FileMaker caches finds and record sets as it pulls them down so while you are testing the first time will be much slower than subsequent times. In development we don't tend to follow real world patterns but test with the same set of records or patterns. Real world patterns would require different sets of data on a regular basis. Each of these new sets will have to be pulled down before it is cached. By that time the person is on to the next task.

Another option is to pre-cache the counts in the parent records on the server periodically.
1. Loop through the parent records in a scheduled server script and calculate your summary records.
2. Compare them to the previously cached data.
3. If the are different then update the cached data in a field in the parent record.

This will have zero lag time in real life where as the variable method I listed above will have some as you change records but still MUCH less than something that is recalculated constantly like you originally did.
1 person found this helpful
Like • Show 1 Like1
Actions

Calculated Button Bar Segment name including Sub Summary

Outcomes

Related Content

Recommended Content