Use the new window command to open a new window.
Find all records.
Now your summary field gives you the grand total without changing the found set of your original layout.
Capture the value in a variable and close the new window.
Define a self join relationship using the cross product symbol (X) instead of equals.
Now newselfjointableinstance::YourSummaryField should give you a grand total independent of your found set.
Thank you very much. It works like a charm.
Another thing I'm going after is count of all fields containing some text. I've tried this calculation Sum(If(table::field="mytext";1;0)) but this gives me question mark as result. Can you help me with this as well?
Kudos to Laretta for teaching me the second trick in my first post the same day I shared it with you. :smileyvery-happy:
A question mark result means that filemaker is unable to display a value. Since this is a sum, is there any chance that the field is too narrow to display the entire number? If that's the case, you should be able to click into the field in browse mode and see the result "pop out" for you to see and you can fix it by resizing the field.
If that's not the case, I'd check your field definition again. Is it set to return a value of type number or another type? (This is controlled in a menu shown in the lower left corner of your specify calculation window of the field in question.)
I think size isn't issue it is long enough to show result even field definition is set to number. If I understand the formula correctly it should return a number, am I right?
I took another look at your posted expression.
Try it this way:
Now define a second field, define it as a summary field that computes "total of" the above calculation. Will that produce the value you want?
The Sum function expects a list of values to add up and the above expression feeds it a single value, either 1 or 0 as the result of the if function.
Yes it is working fine. Thank you. I'm wondering if is always necesary to have one field for converting found text to 1 or 0 and another field for counting them? Isn't simpler solution?
In that case, you don't need the extra calculation field.
"count of" summary fields and the Count() function both count the number of non-blank instances. So if you only want to know how many records have data in a given field and you don't care what kind of data is stored in it, then simply specify the data field in your "counts".