4 Replies Latest reply on Jul 12, 2013 6:50 AM by hferrell

    Running count of the number of referrals

    hferrell

      Title

      Running count of the number of referrals

      Post

           Help is needed to keep a running count of the number of times an attorney has referred new clients.

           Database has two related layouts: Client Data Entry and Contact Info for Referring Attorney.

           Client Data entry layout has a field “Referring Attorney” where the name of referring attorney is entered.  On the Contact Info for Referring Attorney layout there is a field “number of referrals”.  These two layouts have a relationship; Data entry layout field “referring attorney” (pk) is related to contact info layout field “name” (fk).  I would like for the field “number of referrals” on the Contact Info layout to be populated by the number of times an attorney has made a referral. i.e. Atty. Smith has referred three clients so the “number of referrals” field should post the number 3 because there have been three entries for new clients entered into the client data entry layout. 

           Thanks,

           Hal

        • 1. Re: Running count of the number of referrals
          philmodjunk

               PIcky but important detail as the wrong use of terms leads to potential confusion. You do not relate layouts in FileMaker, you use Tutorial: What are Table Occurrences?to relate tables.

               Define a calculation field in Your attorney table: Count ( Client::Referring attorney )

               Place this field on your client layout or your attorney layout to see the number of referrals from that attorney.

               And I strongly recommend that you modify your design so that your pk and fk fields are number fields with the pk defined as an auto-entered serial number.

               Names are not unique. People have more than one form to their names (Phil vs Phillip). People change their names and organizations (such as a law firm) change them also. Names are vulnerable to data entry errors when first entered into the attorney table. For all of those reasons, you are likely to find the need to change a name sometime after it was originally entered into your database. If you make such a name change in the attorney table, all links to records in the client table are lost. You might even be linking your record incorrectly to other client records.

               Using a serial number or Get (UUID) based pk field avoids those issues and keeps any needed name changes to a simple data entry task.

          • 2. Re: Running count of the number of referrals
            hferrell

                 Correct, my mistake.  I know the difference between layouts and tables...my apology.

                 The name field is a pull down list so that will stay the same.  Good recommendations on the pk and fk fields.  I set relations before I knew that and hard to change now. 

                 Thanks for the help.  Glad you are back at work.

                 Hal

                  

            • 3. Re: Running count of the number of referrals
              philmodjunk
                   

                        The name field is a pull down list so that will stay the same.

                   Yet what you describe will not work for linking your records by ID numbers. It's possible to have a drop down list of names that auto-completes and yet links the recorfds by ID number with a bit of behind the scenes creativity:

                   FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                   Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

                    

              • 4. Re: Running count of the number of referrals
                hferrell

                     T hanks again. 

                     Hal