Calculation problem

[Colm Tourque]

I have two tables employee and customer

Linked on Emplyee ID.

If I want to have a calculated field of all active customers how do I do this.

I tried an if

IF(CustomerStatus="active";Count(CustomerID);"0") But this gives me either all customers or a 0 but not the active customers. Any suggestions.

[mz123]

If you create a self join, you can use a Count() calculation:

Please take a look at my sample file.

Hope that helps :

Test.fp7.zip

[anagaby]

depending on how you have the database schema setup, if you have all the customer fields local to the customer table, I would try something like

calculation=if(customerstatus="active";1;0)

Summary field=sum(calculation) runing total.

[Colm Tourque]

OK I get both ideas.

Let me ask this then, because I think either idea would work, but the latter might be better in this situation. What if I am trying to on top of that also keep a count of some other things, say for instance I have a field in the cusomter table that keeps track of what date certain things are due for that customer. Say weekly sales call and I want to keep count of all the weekly sales calls for that customer. Could I do a calculation

if(status="active" and weeklysalescall="incomplete"; 1;0) then a count field. how many ands can you run in an if?