3 Replies Latest reply on Jun 4, 2013 9:07 AM by philmodjunk

    Help with creating a customer database

    nickv

      Title

      Help with creating a customer database

      Post

           Hi guys,

           I am pretty new to using FMP.  I had taken a class on it a while back, but I would in no way call myself anything beyond a novice when it comes to databases.  When it comes to things other than databases, I am a power user.

           What I am trying to accomplish is this, I am trying to create a customer database for my wife so she can more easily keep track of her customers.  What I would like the database to do is not allow duplicate phone number entries (possibly saying something like "there is already an entry with this phone number, would you like to view it?" or something along those lines).  The reasoning for not wanting duplicate phone number entires is that her customers return infrequently.  Because of this there is a concern that multiple entries would occur.  I understand that setting the phone number as the primary key is a bad idea, and this is where I think I am getting stuck about how to approach this.  Obviously if the phone number is set as the primary key, there would be no duplicates allowed.  Is there a way to link a primary key to a number and have this queried each time a new customer is added?

           I understand that a workaround for this would be to search for the phone number first and then enter the data afterwards, but I am trying to remove as many steps as possible from this system for her as she isn't very technically savy.  

           I would also like to setup a cascading combo box (MS Access terminology.  If anyone is unfamiliar with this, a cascading combo box is a drop down box that once data in box 1 is selected, data in box 2 changes.  So if she were to select the state as NY in box 1, only cities in NY would be displayed in box 2)

           From what I could see there is no template that does this already.  Is there any way to do this without writing code?

        • 1. Re: Help with creating a customer database
          philmodjunk

               If you open Manage | database | fields, you can find and double click the field definition for the phone number field and that will open the field options dialog box. Click the validation tab and you'll find a box you can select to specify unique values.

               But this may not be practical. Experience with a contact manager I created here where I work reveals that duplicate phone numbers happen quite easily. Customer one abandons a phone number and does not notify us that they have done so. Then customer two gets a new phone number and it's the number customer 1 abandoned.

               This happens less frequently now that we can take cell phone numbers from old phone to new phone but it still happens.

               Your second question refers to what we call a conditional value list.

               Here are some links on the subject:

               There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in FileMaker 12.

               The last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.

               Forum Tutorial: Custom Value List?

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               Hierarchical Conditional Value lists: Conditional Value List Question

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

          • 2. Re: Help with creating a customer database
            nickv

                 Thanks for the help!!

                 I have some additional questions. 

                   
            •           Would you happen to have any links to tutorials about setting up layouts? 
            •      
            •           Is there a way to create a sort of sub table that links to a main table?  What I am looking to do is create a 'notes' subsection that can be edited each time an interaction occurs.
            •      
            •           If there is a way to create a subtable, is there a way to create a counter on the layout side of things that will count the number of 'notes' (which in effect would give you the number of interactions you have had with that customer)?
            •      
            •           Do these forums have a way for users to reach out to people that develop DB's for possible paid projects?

                 My ultimate goal with this is to create a type of customer database for my wife (she is a hair stylist) that she can use to keep track of her customers.  So far I have found no template that works for me and when I tried to use the tabbed browsing thing it ended up being a disaster.  I like that FMP is cross platform, but it may not be in my best interest to go with it if I cannot get this done within a reasonable time.

            • 3. Re: Help with creating a customer database
              philmodjunk
                   

                        Is there a way to create a sort of sub table that links to a main table?

                   Look up "Portal" in FileMaker help or any training materials you might have. This is a very commonly used layout object for setting up what is usually a kind of "mini table" of related records such as the notes you describe.

                   

                        is there a way to create a counter on the layout side of things that will count the number of 'notes'

                   Yes, it's a function called "count" that you can also look up in help.

                   

                        Do these forums have a way for users to reach out to people that develop DB's for possible paid projects?

                   There's a "consultant" link at the top of this  page. In addition, if you like the advice you are getting from someone, you can click their icon to the left of their message to get a screen where you can send them a private message.