8 Replies Latest reply on Jul 26, 2012 10:39 AM by beverly

    Alternatives to storing Contact data

    Gingernut

      I am revisiting an old file discussed and have the following question which will probably move me in the right direction.

       

      I have a Contact table and the contact type can be Customer, Supplier or Prospect

      Whilst a lot of the basic information is the same for all of them if they are a supplier (for example) they will have a Supplier account number which would not apply to any other contact type and there is prob also other info ONLY needed for a supplier

      The same applies to Customers and Prospects

       

      As I see it I have three obvious options

       

      1. If contact is a Prospect I want to store information about their existing Copy Volume and copy costs plus any proposed copy costs and maybe also lease details.
        I could store all these “attributes” (have been looking at Training Series) – in separate tables there would be quite a lot of them. Eg Lease details, Proposal details, etc etc.
      2. I could have Supplier Attributes – Prospect Attributes etc each in a different table as the content would be different. Currently they are all in the contacts table but there is a different layout for each contact type so only see the relevant information but it means that I have a lot of empty/redundant fields with no data.
      3. Another possibility is to have a Supplier table with all fields relevant to suppliers
        A Prospect Table with all fields relevant to prospects and a Customer Table with all relevant details to Customers.

       

      Which would you recommend and why please.

       

      Many thanks

      Cathy

        • 1. Re: Alternatives to storing Contact data
          beverly

          Cathy, it may depend upon:

                

          • how you do data entry

                

          • how you report

           

          I typically don't worry about "extra fields" in the table, but have layouts and/or tabpanels with the specific fields needed for each type (entry). That makes it easier to enter data. For reporting, different layouts, of course.

           

          Don't make it more difficult than it needs to be! If you want a "related" (one-to-one) table with all the attributes, I can agree on that. But separate tables for each set of attributes can get to be a night mare.

           

          Beverly

          • 2. Re: Alternatives to storing Contact data
            Gingernut

            Thanks Beverly - so would you stick with what I have - Contact table with lots of fields some of which only apply if the contact is a Supplier etc.

            Data is entered on the relevant layout which is switched to as soon as the Contact type is set. Ie If a Supplier goes to that Layout where there are addional fields.

            Might even change that so on entry the user selects contact type before anything else. If they are in Supplier layout they have option Create new Supplier etc and they will generally go to releveant layout BEFORE creating new one anyway.

             

            Reporting again would be only be based on one Contact Type never across all of them.

             

            I also have a lot of calculations which I think are superflous (inherited from Filemaker 5.5 when variables did not exist and were not better ways of doing things)

             

            Seems most people are recommending scripting for calculations - would you agree with that view?

            • 3. Re: Alternatives to storing Contact data
              beverly

              I use scripting for most of previous calculations, yes. There are a lot of "+'s" for that.

               

              Beverly

              • 4. Re: Alternatives to storing Contact data
                comment

                Gingernut wrote:

                 

                 

                As I see it I have three obvious options

                 

                If you ever need to find a contact (or a group of contacts), regardless of their type, then your options come down to two only:

                 

                1. Let your Contacts table have fields to accommodate both types (leaving some of them empty as appropriate);;

                 

                2. Place the dedicated fields in separate subtype tables (your option 2 above).

                 

                While the latter is the more "proper" solution, the former requires considerably less effort, and in most cases works very well.

                • 5. Re: Alternatives to storing Contact data
                  comment

                  Gingernut wrote:

                   

                  Seems most people are recommending scripting for calculations - would you agree with that view?

                   

                  I most certainly would NOT. But it may depend on what the calculation is doing.

                  • 6. Re: Alternatives to storing Contact data
                    chaser48

                    Plus if the user is using FileMaker application for data entry, you can use the conditional formatting to obscure fields that are not needed. Also add a script trigger so if user clicks into field not used for supplier the script send cursor to next field. That way you have one layout for the three types of contacts. Now if you have many types of contacts then multiple tables and layout make more sense.

                     

                    Setup the script properly and you can reuse it based upon "if/else if" statements to move cursor to next field and have only one script handle all the contact types.

                     

                    Chance Fry

                    • 7. Re: Alternatives to storing Contact data
                      comment

                      Much easier to use a dedicated layout (or tab panel) for each type, IMHO.

                      • 8. Re: Alternatives to storing Contact data
                        beverly

                        I agree with Michael. Conditional Formatting does NOT prevent a field from being entered accidently (you'd need privileges for that). So much easier to use the tab panel and/or multiple layouts.

                         

                        Beverly