1 Reply Latest reply on Aug 6, 2009 9:45 AM by TSGal

    Unduplicated Number



      Unduplicated Number


      This is probably really simple but I'm unable to figure this out! I need to create a formula that will give me an unduplicated total of client records in my database. If I put in a date range and come up with a list of clients that received services during that date range, I want it to then give me the total number of clients served within that date range unduplicated. Some clients may have been seen multiple times during the time period but I only want the individual client counted once.


      Any help would be greatly appreciated!

        • 1. Re: Unduplicated Number



          Thank you for your post.


          Suppose you have five records with the same name.  When you add this up, you only want it to display one.  Therefore, for each record, assign the value 1/<number of occurences>.  In this case, 1/5.  When you add up these five records, the value 1 will be displayed.


          Create a Summary field, NAMES, that results in a count of your Client Name field.


          Next, create a calculation field, CALC, with the formula:

          1 / GetSummary ( NAMES ; <Name field> ]


          Finally, create a Summary field, UNDUPLICATE, that is a Total of CALC.


          You can now search for a group of records, sort by your name field, and you will have an "unduplicated number"


          Please let me know if you need clarification for any of the above steps.



          FileMaker, Inc.