4 Replies Latest reply on Nov 14, 2014 7:30 AM by DanielMitchell

    Help please - Customer Referral Counter



      Help please - Customer Referral Counter


           I have just started learning to use filemaker 12 and thus far have been able to follow many guides on certain database developments and have as such learned the basics.

           My intent is to build a "Service Manager" application, which will incorporate the following (to start with) one module at a time, with the intent of combining them into the final product which will serve many business (Computer Sales and Repair)

      •           Contacts manager ( Names, addresses, numbers, email fields, customer type - business, consumer, charity etc)
      •           Systems manager ( type of device, specification, serial numbers, photo's and so forth)
      •           Services and Repairs (repairs that have taken place, parts used, technician who completed work)

           I am currently working on the Contacts manager component, and thus far am happy with the results, where I am stuck though is with regard to referrals.

           At the bottom of the contact record is a drop down from which I can select the name of a current customer to show that he has referred the customer whose contact details are being viewed.  This name is pulled from c_NameFull from the table "contacts".  This part works fine, but I would like to see a count of how many customers have been referred to me by customer  whose card I am working on, displayed in the header of the contact card as "Referrals Made: XX" where XX is the count.

           I hope this is explanation enough?

           I have a ReferralCount field in my Contacts table, I am guessing that it needs to be a calculation field but cannot work out the calculation required.

           Any help would be greatly appreciated.


        • 1. Re: Help please - Customer Referral Counter

               Does the Referred by field enter a name or a contact ID? It should enter a contact ID. Contact names are not unique. People change their names and names can easily be entered incorrectly and later need to be changed. If you are linking records by name instead of an ID, all of those details become problems when working with your database.

               Using an ID field, you can set up a self join relationship:

               Contacts::ContactID = Contacts|ReferredBy::ReferredByID   ----> this should be your field with a value list for selecting a referral contact

               Where Contacts and Contacts|ReferredBy are two Tutorial: What are Table Occurrences? of the same data source table.

               Then Count ( Contacts|ReferredBy::ReferredByID ) will return the number of referrals for a given contact.

          • 2. Re: Help please - Customer Referral Counter

                 Thanks for your help Phil.

                 I have managed the first part (two occurrences with a self join relationship) which makes sense.

                 However I am still struggling with the second part - setting up the count.
                 Each time I set up the calculation i receive an error "The specified table cannot be found" (which highlights "Contacts|ReferredBy")


            • 3. Re: Help please - Customer Referral Counter

                   And what did you name that second occurrence? From the error message, the second occurrence is not named "Contacts|ReferredBy".

              • 4. Re: Help please - Customer Referral Counter

                Very sorry for the lack of reply, I have been somewhat unwell of late and not had a chance to look into this for a very long time.

                Back to full health now, but have lost all my previous work and am starting again from scratch.  Unfortunately I seem to have forgotten a lot of what I had previously learned.