Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Active records count


Recommended Posts

Hi everyone,

Newbie here. I am very new to Filemaker and I have a DB that I have been working on for a while now. I'm sorry if this is a dumb question, but ...here it goes.


How can I get running total of only active records for a client?


Let me try to explain, I have a petsitting DB. Each client has pets listed. With a (foundcount) I can get the total of pets for that client. When I make a pet inactive (ran away, died or whatever) it still totals all records for that client. I'm sorry if I'm not explaining this correctly (insert head banging here). If it helps I can email a clone of my mess.


Thanks in advance

Link to comment
Share on other sites

Let's assume you have two tables - CLIENT and PET. They are currently related as follows:


CLIENT::clientID = PET::clientID


So that gives you all related pets - active and inactive.


You have not said how you make a pet record 'inactive' but let's assume you have a field in the PET table called Status that can be either active or inactive.


You need another field in your CLIENT table called say Active_constant which is a calculation = "Active".


Then you need a new relationship from CLIENT to PET. Create a new table occurrence for the PET table and call it client_PET__active. Then the relationship is:


CLIENT::clientID = client_PET__active::clientID


CLIENT:: Active_constant = client_PET__active::Status


Using this relationship, create a portal on the client layout. It will show only active pets.


If all you need is a count of active pets for each client, create a calculation field in the CLIENT table called pets_activeCount:


= Count (client_PET__active::clientID)


That's a lot to take in for a newbie but the answer you need is there. smiley-wink

Link to comment
Share on other sites

Thanks for the help. You are right about the Pet and Client tables, as well as the active status (I used a value list). I will have to read this a few times, but i will figure this out smiley-laughing !

Link to comment
Share on other sites

Just a quick update. After a few temper tantrums and a lot of mistakes, It works great!


Thank you very much for the help.

Link to comment
Share on other sites

  • Create New...

Important Information

Terms of Use