4 Replies Latest reply on May 20, 2013 3:46 AM by GuyStevens

    Relationship 'leads', 'contacts' and 'accounts'



      Relationship 'leads', 'contacts' and 'accounts'


           Hi, i'm new to FM and despite going through all of lynda.com's courses on the subject, i still have difficulties understanding how to design my database and the relationship between some of the tables i foresee.

           I have a leads-table containing each and every of the prospects i'm calling duriing my cold-calling. It contains just basic info helping me to follow-up on the lead until it's either dead or becomes an account. 

           So i have many leads and occasionaly one becomes an account. 

           The leads table contains very basic stuff like the company name, one contact person's name, a telephone number and a key identifier which is the url of the company's website.

           The accounts table contains all the relavant information about the company (once they became a customer). 

           I also plan to have another -third- table called 'contacts' where i want to store all the contact details of each of the employees i deal with within an 'account'.

           So, many leads with occasionally some becoming accounts.

           And many contacts with each of them always related to at least one existing account.

           The purpose is also to re-use as much as possible existing data when promoting a lead to the accounts table, so i avoid re-typing all the already available info when converting a lead to an account.

           My question is about relationships:  How should i design this database?  What would be primary keys etc?  I'd be very helpful if someone could provide me with a simple diagram showing me how to best address this.

           I'm not sure if this is relevant, but over time, as i find more time and get more experienced with FM, i'd like to add extra tables for products, proposals, invoices & project management....  

        • 1. Re: Relationship 'leads', 'contacts' and 'accounts'

               I see you have found your way over here from Youtube :) Welcome.

               Some questions:

               Will contacts sometimes be related to multiple leads / accounts?

               Do you want to keep your leads and your accounts in a seperate table?
               Or in the same table but just with a field set to "Lead" or "Account"?

               You can do it any way you want. But maybe it's easier to have one table for:

               - Leads
               - Accounts
               - Contacts
               And maybe a join table to connect Leads / Accounts with Contacts.

               This join table might not be neccessary if you are absolutely certain that a contact will always only be related to one lead / account.

               Give every table it's own ID as a number field set to auto enter a serial number.
               Use those ID's as foreign keys in the join table. So your join table would have:

               - It's own ID
               - A foreign key for the Lead
               - A foreign key for the Acount
               - A foreign key for the Contact

               I would suggest creating a lead and then providing that layout with a button "Turn into Account" that imports the lead data into the account table and reassigns the contacts to that newly created account.
               That lead can then be removed.

               A pretty simple structure if you ask me. Just promise me one thing: That you will never ever use something like a URL as a key identifier.

               Met vriendelijke groeten
               Guy Stevens

          • 2. Re: Relationship 'leads', 'contacts' and 'accounts'

                 Dag Guy,

                 Thanks for coming back to me.

                 No, one contact will never be related to multiple leads, nor will they ever be related to multiple accounts.

                 To be honest with you, i'm not sure about the single or multiple tables...  I recently spent 3 weeks on a test account of ZOHO's online CRM app where seemingly all these tables were split, and items just got converted or promoted from one table to the other one, and where you could just indicate which were the fields you wanted to keep after promoting a record (to avoid the double typework)....  utimately i got disgusted at the lack of freedom their solution gave me and turned towards Filemaker as the only affordable solution which will meet all my requiremenrs.

                 But maybe i need to think away from that ZOHO model now and try and understand your approach and do all in one table??

                 On the other hand, please be aware that currently all my leads are in Bento.  While all my Accounts and Contacts are in Podio (online).  So maybe also from that perspective it seemed logical to me that these where going to be separate tables.

                 What i like most from your reply would be the approach where Leads "turn into" Accounts with the click of a button which triggers an import and allows for deletion of the initial Lead record.  Seems perfect to me.

                 So to achieve that, if i understand correctly, you're telling me to:

                 - create 4 tables, one for Leads, one for Contacts, one for Accounts, and then an additional join table.  correct?

                 - in the join table, define it's own ID using auto serial number and 3 foreign keys, one for each of the three other tables. correct?

                 What would then typically be the primary keys in each of the 3 other tables?  (if i'm to keep my promise to you that is ;-)

                 Much appreciated en alvast bedankt!,





            • 3. Re: Relationship 'leads', 'contacts' and 'accounts'

                   Hey Thierry,

                   An ID field in Filemaker wil typically be a field named "Id" of the type "Number" set to auto enter a "Serial Number".

                   If you have seen any of my video's you will see that I start every single table with this kind of field.

                   Whether you should put your leads and your accounts in different tables or not is a question that only time or some testing might tell.

                   Maybe to keep it simple start with two tables. You can always separate leads and accounts later.

                   Just put all your companies in one table and give them a "Type" field where you automatically set the new ones to be leads and make that a dropdown so it can also be turned into an "Account".

                   Then if your contact will always only be related to one Lead / Account you don't need the join table.

                   With this simple system you have two related tables and you are keeping it really simple untill you learn a little more about filemaker.

                   So you'll need:

                   Id (A number field set to auto enter a "Serial Number"
                   Company (Text)
                   Type ( A text field set to auto enter Data "Lead" )

                   Id (A number field set to auto enter a "Serial Number"
                   CompanyIdFk (This is a simple number field) - Here we will store the ID of the related Company
                   c_FullName (A Calculation field set to combine the first name by using the following calculation: FirstName & " " & LastName )
                   Then in the relationships dialogue you create a relationship between the ID Field of the Companies table and the CompanyIdFk field of the Contacts table.

                   Then you create a layout based on the Companies table and you create a portal diwplaying records from "Contacts"

                   If you want to be able to create contacts from this companies layout you need to edit the settings of the relationship and "Allow the creation of records via this relationship" on the contacts side of the relationship.

                   You might also want to check "Delete related records if a record is deleted in this table" So that when you delete a company the related contact is also removed. You don't need that contact then anymore, because like you said they are always only linked to one company.

                   There, that should be a good start. Let us know if you get stuck.


              • 4. Re: Relationship 'leads', 'contacts' and 'accounts'

                     Then as a next step you should create a list view of your companies table that has a subsummary part when sorted by type so you get a list like this:

                        Company 1
                        Company 2
                        Company 3
                        Company 4
                        Company 5
                        Company 6