9 Replies Latest reply on Jun 19, 2017 9:28 PM by taylorsharpe

How can I get sum of similar values in a field? example, i have one field "boolean" having values "yes" and "no". now I want to get total no. of "yes" and total no. of "no". I wrote " if statement" and return value 1 if the value is "yes". now how can I s

How can I get sum of similar values in a field? example, i have one field "boolean" having values "yes" and "no". now I want to get total no. of "yes" and total no. of "no". I wrote " if statement" and return value 1 if the value is "yes". now how can I sum up those value?

• 1. Re: How can I get sum of similar values in a field? example, i have one field "boolean" having values "yes" and "no". now I want to get total no. of "yes" and total no. of "no". I wrote " if statement" and return value 1 if the value is "yes". now how can

depends on if you want to do it with a script, a sub summary report, or within a calculation

- script prototype -

generate appropriate found set

set var \$no = 0

set var \$yes = 0

go to first record

loop

if field = No then set var \$no = \$no + 1

if field = Yes then set var \$yes = \$yes + 1

go to next record end at last

end loop

// at this point the vars will have the right totals

exit script

• 2. Re: How can I get sum of similar values in a field? example, i have one field "boolean" having values "yes" and "no". now I want to get total no. of "yes" and total no. of "no". I wrote " if statement" and return value 1 if the value is "yes". now how can

This will get the count of "yes" values:

Let ( [

SQL = "SELECT COUNT ( * ) FROM YourTableName WHERE BooleanField = ?" ;

Result = ExecuteSQL ( SQL ; ¶ ; ¶ ; "yes" )

] ;

Result

)

• 3. Re: How can I get sum of similar values in a field? example, i have one field "boolean" having values "yes" and "no". now I want to get total no. of "yes" and total no. of "no". I wrote " if statement" and return value 1 if the value is "yes". now how can

There are many options and it depends on whether you have one field that you are summing across a set of records or multiple fields in the same record.

Assuming one field summed from a group of records:

A "count of" summary field could give you these totals if you set up a summary report with a sub summary layout part "when sorted by" your yes/no field. You put the summary field inside this layout part and sort your records on this field. This total would be calculated from your current found set.

A relationship could be used with this same summary field to get a count from a group of related records. Different relationships could match to yes and no values.

The same relationships could be used with count functions to get the same counts.

A pair of one row filtered portals with the same summary field could be used with portal filters that filter for "yes" or "no".

ExecuteSQL could also be used to get those counts.

1 of 1 people found this helpful
• 4. Re: How can I get sum of similar values in a field? example, i have one field "boolean" having values "yes" and "no". now I want to get total no. of "yes" and total no. of "no". I wrote " if statement" and return value 1 if the value is "yes". now how can

I was trying to do with calculation. I used summary type to get the total value of the filed. But i dont know how to segregate the similar values and add them. I am new to filemaker and doing lots thngs to work out. the script ddnt seems to work

• 5. Re: How can I get sum of similar values in a field? example, i have one field "boolean" having values "yes" and "no". now I want to get total no. of "yes" and total no. of "no". I wrote " if statement" and return value 1 if the value is "yes". now how can

if your trying to do it with a calc then taylorsharpe got you part of the way there with a context independent calc using ExecuteSQL () You will just have to plug in your values and it will work.

If you go that route i would also reccomend setting a single, never empty field to count on. Count(*) can cause performance hit for wide/tall tables.

This will get you count of all values in the boolean field that are not empty.

Let ( [

SQL = "SELECT COUNT ( yourBooleanField ) FROM YourTableName GROUP BY yourBooleanField" ;

Result = ExecuteSQL ( SQL ; "" ; "" )

] ;

Result

)

• 6. Re: How can I get sum of similar values in a field? example, i have one field "boolean" having values "yes" and "no". now I want to get total no. of "yes" and total no. of "no". I wrote " if statement" and return value 1 if the value is "yes". now how can

As others have noticed, there are lots of ways to do this.

If the field is a text field and actually has "Yes" and "No" values, then here's one easy way. Create a "List of" summary field that summarizes that field. Then use a calc with the PatternCount function to get the count of whatever you want. For example,

PatternCount ( MYTABLE::List of YesNo Summary Field ; "NO" )

Note that the summary field has to be a field but it doesn't have to be on the layout. And the calculation doesn't have to be in a field: It can simply be used in a Set Field or Set Variable script step.

2 of 2 people found this helpful
• 7. Re: How can I get sum of similar values in a field? example, i have one field "boolean" having values "yes" and "no". now I want to get total no. of "yes" and total no. of "no". I wrote " if statement" and return value 1 if the value is "yes". now how can

Thank you Everyone. It worked. I appreciate for the help.

• 8. Re: How can I get sum of similar values in a field? example, i have one field "boolean" having values "yes" and "no". now I want to get total no. of "yes" and total no. of "no". I wrote " if statement" and return value 1 if the value is "yes". now how can

Don't forget to mark an answer as correct.

• 9. Re: How can I get sum of similar values in a field? example, i have one field "boolean" having values "yes" and "no". now I want to get total no. of "yes" and total no. of "no". I wrote " if statement" and return value 1 if the value is "yes". now how can

coherentkris wrote:

if your trying to do it with a calc then taylorsharpe got you part of the way there with a context independent calc using ExecuteSQL () You will just have to plug in your values and it will work.

If you go that route i would also reccomend setting a single, never empty field to count on. Count(*) can cause performance hit for wide/tall tables.....

In most SQL databases, your suggestion to avoid Count(*) is valid.  But in performance testing, it is untrue in FileMaker.  When ExecuteSQL came out, we did a bunch of performance testing and this was one of the surprises.  But when you think about it, it makes sense because FileMaker thinks a whole record at a time whereas most SQL databases can think just a particular field at a time.  It is just how Draco works, and gets to the point that re-writing FM to do SQL without converting to FMQL would probably improve a lot of things like this.