You can have another calc that uses a formula of: Status="Open"
Then you can use a self join and use a Count () or Sum () of this field.
Or you can create an unstored calc field that returns the string "Open" and create a self join that keys from that calc field to the Status field and then use Count (), Sum (), or even a summary field to count.
Thanks Mr_Vodka, but if I can't get determine what is the correct calculation to use in my existing calculation, how does it help to create another field that requires a calculation with "status=Open". If it won't work in one it is not going to work in another calc field. Guess I must be missing something here, so feel free to correct me.
if the value of the Status field is "Open", then Status = "Open" returns 1. If it is not, it evaluates to 0.
A summary field, count or sum function that adds up the contents of this field will then count the number of open records.
Your formula won't work because the way it is set right now, it is only performing itself based on the current record.
You need to make a relationship so that you can count the number of records in the related table.
Maybe I haven't explained the problem sufficiently.
I have a calculation field on a table which I want to use to calculate the number of records with Status="Open". These records are currently being viewed in a portal which all works fine, except for this missing value.
Using Count() always returns 1.
"Total Nº of Claims : " & Get ( TotalRecordCount ) // This works fine to get the total number in the table.
Can I not simply replace the Get(TotalRecordCOunt) with a function that allows me to specify a condition that Status="Open"?
Yes you can but that means that either your calculation is wrong or your relationship is wrong.
First you have to create a relationship, which in this case sounds like if would be a cartesian (the x symbol) which will connect any record from the main table to the related table.
What the other person was saying is that in the related table, you need to make a calculation (we'll call it StatusOpen) that goes like this:
If(Status = "open";1)
THEN, in the main table where you want the count you need to create a calculation that is:
This will count how many fields have 1 in the related table.
Thanks deltatango finally managed to get a look at this and your suggestion works a treat.
The one thing I am finding difficult to adjust to with Filemaker is that to do a calculation one nees to create extra fileds all over the place. In other products, these do not need to be stored or calculated in the table but can be done on one's form and makes the whole issue easier to get one's head around.
Anyway, another minor issue resolved - thanks.
Welcome to the wonderful world of Filemaker PRO.
In everything there are trade-offs. The one advantage I've encountered in filemaker's table based calculation fields is that I can use the same field on multiple layouts. If I later determine that I need to change the calculation, I change it once in the table definition and don't have to track down multiple layouts or SQL queries and make identical changes to each like I used to do with MS Access...
Buts that's MS Access. In full blown programming languages you can have calculations stored globally so you only need to change in one place - simpler, and better structured