13 Replies Latest reply on Jan 6, 2013 6:45 PM by philmodjunk

    Tracking referrals

    EP

      Title

      Tracking referrals

      Post

           Let's say you have a solution that allows you to generate invoices.  On these invoices, users enter the referral source into a field (the person who referred the client to the business).  These referral sources are part of the contacts table that the marketing team uses (users have the ability to search for the contact and create a new contact if not found).  How could I develop a way to track referrals? I was thinking of a separate table, but since it is one field on the onvoice, I am considering portal filtering by contact name in each contacts record.  Not the best way to go, as I rather link it by serial, but can't figure out the relationships and scripting for this one.

           Thanks

        • 1. Re: Tracking referrals
          philmodjunk

               You shouldn't need an additional table.

               You can set up a layout based on the invoices table for reporting referrals.

               You can also set up a new occurrence of the invoices table and link it to contacts by this referral field if you want to put a portal listing all referrals on your contacts layout.

          • 2. Re: Tracking referrals
            EP

                 Thanks Phil.  If I link an occurrence by the referral field, would I be safe from name changes, etc? I always try to link by serial.

            • 3. Re: Tracking referrals
              philmodjunk

                   The referral field should be an ID number matching to the PK ID field in Contacts.

              • 4. Re: Tracking referrals
                EP

                     Ok, do the following relationships make sense?  

                     I would have Customers::_pkID-<Invoices::_fkID, as well as Contacts::_pkID-<Invoices::_fkReferralID?  ( 2  _foreign key fields on the invoices table)

                • 5. Re: Tracking referrals
                  philmodjunk

                       Yes but you need two occurrences of invoices--each with different names such as:

                       Customers::_pkID-<Invoices::_fkID

                       Contacts::_pkID-<Invoices 2::_fkReferralID

                  • 6. Re: Tracking referrals
                    EP

                         Ok, I have the relationships in place.  I added a field on my invoices layout that allows the user to select the creferrer from my contacts table via a name search portal and Set Field function (when user clicks the name of the contact, it enters it into the invoices::referrer field).  

                         Is this all that is needed? I think I am missing something (Is that what you meant by "another layout" in your initial post? Do I need to have a layout based on the second table occurrence for thsi to work? If so, is there any way to put the field from the second table occurrence on the invoice layout?) Thanks

                    • 7. Re: Tracking referrals
                      philmodjunk

                           I mentioned two options:

                           Set up a layout based on Invoices 2 that allows you to list referrals with sub summary parts to serve as sub headers. This could be used to list contacts in the sub headers with the referring invoices that listed that contact in the referrals field listed under each sub heading.

                           Or

                           Put a portal to invoices 2 on a contacts layout. This will list all invoice records that listed that specific contact in the referral field.

                      • 8. Re: Tracking referrals
                        EP

                             Phil, I got that part. I am going to go with the portal on the contacts layout.  I am having trouble undertstanding the relationship however.

                             When a New Invoice is created, the fk is filled in by the Customer pk via Customers::_pkID-<Invoices::_fkID

                             How does the fk for the referral field get filled in by the Contacts pk (Contacts::_pkID-<Invoices 2::_fkReferralID).  Aside from the relationship, I think I need to add a set field for the _fkReferralID  field containing the Contacts::_pkID from the name search portal.  Is this correct? If so, Do I set field on Invoices or Invoices 2?

                              

                              

                              

                              

                        • 9. Re: Tracking referrals
                          philmodjunk

                               Set field is an option and since your layout would be based on invoices, you would reference the field in Invoices, not invoices 2. Keep in mind that you still have one table here, just two "boxes" in manage | Database | relationships with different names that both refer to the same data source table.

                               You can also set up _fkReferralID with a drop down list where the first (primary) field enters the contact ID and the secondary field displays the contact name.

                          and if you use the method in this demo file, you can even set up a text field with an auto-complete enabled drop down list of names yet a combination of script and data lookup enters the ID number to establish the needed ID number based link:

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

                                

                          • 10. Re: Tracking referrals
                            EP

                                 Thanks Phil.  I am using the search portals because it recognizes all letters in a name and will help cut back on users "not being able to find the contact" and creating a duplicate. A popup or drop down would be cleaner, but I fear users making a mess of the contacts table creating duplicates.  Any way to have a drop down behave similar to my search portal?

                            • 11. Re: Tracking referrals
                              philmodjunk

                                   Take a look at the demo file. It uses scripts to improve on the user experience, detecting and preventing duplicate can be part of the process. In fact, with customer names, I'd expect you to get duplicates. My strategy for that is to use a scripted method where selecting a name that exists in more than one contact record pops up a list of all such matches with additional data--such as an address to enable the user to identify the correct name to select.

                                   Preventing duplicate contact  records with 100% success may be impossible to achieve no matter how carefully you design your system to prevent it. If you get two contacts with the same address and or phone number, they might be the same contact after a change in name or with a misspelled name. Or it could be two people that are located at the same address and thus have identical contact info. (And they might be named John Smith Jr. and John Smith Sr. and neglected to tell you they were jr or sr.) Identical names with different contact info could be the same contact after a change in address or phone number or it could be two different contacts with the same name.

                                   Messy, but such is the nature of managing customer contacts.

                              • 12. Re: Tracking referrals
                                EP

                                     Phil, I reveiwed theDemo file.  I still prefer the name search portal.  For example, in theDemo file, typing "George" yields zero results.  Therefore the user has to know the correct first letter of the name.  I prefer to give the user options to search first name, middle name, last name, letters of the name, etc.  Can this be done with the dropdown?

                                • 13. Re: Tracking referrals
                                  philmodjunk

                                       Nope. That's why I also have a demo file with a filtered portal that uses pattern match--which sounds like the method you are using here.