conditional count

[Blake Lucas]

I have done some combing of the forums with no luck so far.

I have a count function now that simply adds up all the open calls in a database by a

Count (table::field.id) easy

Now I want break that down further to giving me a count of all open calls for each employee...

I have attempted to put in a conditional comment in calc, and even an if statement, but get errors that this is not allowed. Is there a simple way to do this?

Am I making any sense? LOL

[Blake Lucas]

The open call is identified by a status of a field as having the word "OPEN" as its contents.

[comment]

I have deleted my response, because on re-read I saw I couldn't understand your description. I still can't. I don't even see how

Count (table::field.id)

adds up all the open calls in a database. Please describe your tables and their relationships.

[Blake Lucas]

ok let me see if I can clarify this...

2 tables.

I have a main page that is using mostly global fields and has portals viewing data from other tables.

2nd table is call records...so a call comes in you pick your customer and fill in the support issues. By defualt, the call is "OPEN" in the "status" field until you close the call by triggering a calculation that then changes that status field to "CLOSED"

So, on the main page the relationship for that portal for the open calls window is show me all calls from that table with the status of OPEN.

On the main page, I have a global field that gives me a count of how many calls are currently OPEN. ( see screenshot)

I want add another set of fields to break that count down to show me all calls that are OPEN for each employee, so:

ALL OPEN CALLS: 50

EmployeeA: 25

EmployeeB: 15

EmployeeC: 5

EmployeeD: 5

So my field that gives the all open calls is:

Count (OpenCallDB::table.id)

What would be the best way to tackle this? Should I make fields that tally the calls in the table and just display that field or use a global field?

Does that help at all?

[Blake Lucas]

forgot screenshot.

[bruceR]

See http://www.briandunning.com/cf/890

[comment]

You could have a calculation field in the Employees table count the open calls of each employee. Then place a portal to Employees on your "Main" layout.

[Blake Lucas]

trying the syntax to get the count for each employee.

I have tried a Count statement of count (If the employee name and the status = "open",1, 0))

But that isnt producing anything...

Is there a better syntax to get that count for each employee?

[comment]

My suggestion was to do the count from the Employees table. Assuming you do have an Employees table, with a relationship to the Calls table matching on EmployeeID, a count of the related call records will automatically include only the employee's calls.

I am still not sure how your:

"Count (table::field.id) easy"

manages to include only open calls - I rather suspect it doesn't. To count only open calls you could do:

ValueCount ( FilterValues ( "Open" ; List ( Calls::Status ) ) )

A simpler way would be to change the Status field into a number field (e.g. 1 for open, 0/empty otherwise), then use Sum() to get the total.

[Blake Lucas]

Here are screenshots of the relationship of the two tables, definition of the calcs i am using for the status field and such, so you can see im not lying in how the count for all open calls work...and it does work.

I just want to add the ability to see how many open calls per employee.

[comment]

I am guessing the g.help.open field contains the text "Open"? Well, you could do the same thing from the Employees table (define another relationship to Calls matching EmployeeID to EmployeeID AND a global field to the Status) - but really, the two ways I have mentioned earlier are simpler.