How could I sum all the 1 in a field in the same table? This field is just at checkbox that every record either is 1 or empty. When I use Sum (checkboxField) he would not calculate the right amount of selected records.
You could use a global or constant field in the table set to 1, set a self link relationship up to the check box field and then base the sum using that.
Use a summary field.
You're right Bruce. Sometimes miss the obvious.
Due to the size of data sets here we try to avoid summary fields due to the calculation message when you hit a layout containing one and all you can do is wait for it to complete.
Hey ho, 30 years FileMaker and still making stupid suggestions.
Yes, summary fields can be a perfomance problem on large record sets.
Anyway, here's a simple example.
If I remember correctly, Bruce, it was you who posted this technique quite a while ago:
It's sometimes strange that SUM doesn't work on the same table. I used to be perplexed about that too. Having to set up a self join just to get a SUM seems odd. The SUM, IMHO, should be overloaded to know it should work on the same table as well -- you know, to help US.
In any case, Bruce is correct, just use a summary field or be prepared for more work.
Aggregate functions such as sum, count, list, etc can be used in three ways in FileMaker:
Sum ( Repeating field ) this is the original way that we used this and sums the repetitions of the repeating field
Sum ( Field1 ; field2 ; field3) this is just another way of doing Field1 + field2 + field3
Sum ( RelatedRecors::Field ) this sums Field for all related records and is something that was added in once FileMaker was no longer flat file in structure. (Once did everything with repeating fields because related tables and portals weren't an option in FileMaker. Hard to imagine that now...)
I have to agree with you fmpdude, Excel just uses Sum() to total
a range of figures, but we have to use a non calculation field in Filemaker.
Since Sum (relatedtable::Field) is also an unstored calculation, using a summary field vs Sum can be pretty much of a toss up when it comes to performance issues.
Can't argue with that, just always felt weird we can pop a sum under a portal but can't do the same in a single table!
ExecuteSQL() would allow for a calculated "sum" in the same table. You would need to get the criteria correct to match any "found set", however.
Agreed .... and be prepared for probably slower SQL performance compred to FMP native functions.
I would NOT use a calculation field for this but a scripted Set Field. It's an alternative only to be used as needed.
Yup, that's the route we normally go, keeping as much out of the data structure as we possibly can.
I don't think I suggested using a calculation field, did I?
I was only talking in generalities.
The fact that SUM doesn't work on the same table is totally goofy, counter-intuitive, and counter to how it works in Excel (a product often used by FMI as a bridge to FM).
This SUM behavior, though certainly not the worst omission, is one of the now 25 features missing in FMP that I would want to be more productive using it.
Sorry for the confusion.
I don't believe you mentioned calculation, it's a function and a calculation seems to be how ESQL gets used. I wanted to 2nd your warnings about slowness. Scripted Set Field calcs once, and is my preference.
Sent from miPhone
In these solution the user checks all the resipients of a newletter, so basicly it could be many. But anyways when I make a summery field (recalculate when needed) and in the summery calc choose the field am summering (checkbox), the field just could the first checkbox am checking, not summering al the checks. How did you do this?
(Just more clarification of what I think the behvaior should be)
I realize FMP Isn't OO externally, that is to us, but it would be nice if it were (or more so) internally.
Here's how an OO SUM (and similar) function might work (internally coded in FMP itself) - invisible to us as users, developers, as it should be:
function sum(<pass a table+field name>)
// Sum on this table given field
// this one is missing, the most expected function
function sum(<pass a list of fields>)
function sum(<pass repeating fields>)
function sum(<pass related table>)
With overloaded functions (again, this mechanism would be INTERNAL to FMP, not something you code or have to worry about), you use the function with the SAME NAME and the function. Then, behind the scenes, FMP figures out what to do based on the parameters you used (or how you used the function, or whatever the context).
Function overloading is a tenant of object oriented development. Function overloading means the user of the program, you normally, don't need to do anything different beyond passing the right parameters and types for what you want to do.
My layout is based on the same table as where im calculation the checks. And the summery with a total of the checkboks field seem to work but is only showing some milliseconds before it dissapers again. How whould I make it stay?
Neither can I imagine trying to manage the structure within some current systems using the old multi-file text based links before the relationship graph - it has come a long way that is for sure. I can't remember the last time I used a lookup!
Are you clicking in fields? If you commit it should stay. Is the field in the footer?
Yes am clicking in the with a checkbox in a list view. the sum field is in the header on the layout. should I have a commit script OnObjectChange on the checkbox field then ?
It works. But in this list search the will proparly do multiple searches, and selecting the checkbox. Is it possible to show the "final" summery if the user has made another filter search? if I select some, and do a new filter search the summery get empty again.
it is a summary field. Note spelling.
No, the field is never empty.
There is no such thing as a summary calc field.
Review the example; once again.
Exact same setup, but if there was done searches in the table, the sum field only shows the checks/summery in the current found set, is it possible to show the sum of all the check even if there is not part of the current found set?
Pretty much so, back to my original suggestion have a constant field set to 1 in the table, setup a self link based on the serial ID field matched using the X link (match all records) and a second condition linking the constant to the check field along the lines of:
ID x ID
Constant = CheckField
Put a Count field calculating from TableSelfLink Count ( CheckField )
There is a proviso, if you put the field in the footer and the last record doesn't have a value in CheckField it will display blank. If you put it in the list, then it will always display the total number of records with CheckField set regardless of the found set.
Retrieving data ...