calculation with filter

[ron G]

I have an application called "MEMBERSHIP":

 

It has these fields:

PKMEMID

MemberLast

MemberFirst

Mem_Can   (Holds "Member" or "Candidate" as appropriate)

ActiveInactive  (Holds "Active" or "Inactive" depending on the member status.

 

I want to create Membership::gcMemberActiveSummary that is Global , caluclated field that holds the COUNT of  (Mem_Can="Member" and ActiveInactive="Active")

 

I have tried:

 

gcMEMBERActiveSummary=count(Evaluate(ActiveInactive="Active" and Mem_Can="Member"))

 

I suspect I have to create a layout, add the necessary fields.... filter the necessary fields and and then script a 'count' of the displayed fields then set the global field to that value??  Or, does FM have an easier way?

 

 

[LaRetta]

I think I would skip the relationships and use ExecuteSQL() as:

 

ExecuteSQL (
"SELECT COUNT (*)
FROM membership
WHERE MemCan = 'Member' and ActiveInactive = 'Active'" ;
"" ; "" 
)

[ron G]

Thanks for the idea. 

 

I found that SeedCode has a great ExecuteSQL app that is ideal for learning.  It is at:  http://www.seedcode.com/pmwiki/pmwiki.php?n=SQLexplorer.HowToMovies .  Since I do not know SQL I should have a lot of fun. 

 

Thanks again.

 

Ron

[LaRetta]

BTW, if you expect a global calculation to update then you will be in for a surprise.