6 Replies Latest reply on Dec 31, 2013 1:30 PM by cinderjack

    Setting up Data Entry forms using multiple tables

    cinderjack

      Title

      Setting up Data Entry forms using multiple tables

      Post

           (I've tried searching on this, but not sure I'm using the right syntax)

           I'm new to FM Pro and am just setting up my data entry forms.  I have 2 tables (Client_Visit and Client) that I need to use on my main data entry form.  I'll be entering the visit date (easy enough) and then I need to select the Client from the Client table before entering the rest of the details about the particular visit.  I'm just not clear on how to set that up to pull up the Client information.  I have a ClientIDPK (joined to ClientIDFK in Client_Visit) and then I have separate fields for First Name and Last Name.  What's the best way to look up the Client record to relate to the particular Client_Visit record?

        • 1. Re: Setting up Data Entry forms using multiple tables
          philmodjunk

               There are many options and no one "best way" as each offers a different set of advantages and draw backs.

               One very simple way is to add a portal to ClientVisits on your Clients layout. If you enable "Allow creation of records via this relationship" for clientVisits, you can log a new client visit just by entering data into a blank row in the portal.

               From a layout based on ClientVisits, you can format the clientIDfk field with a drop down list or pop up menu of clientID's and Client Names. You'd select the "use values from a field" option and specify the ID field for field 1 and a name field for field 2. The name field may need to combine first and last names and if so, it should be setup as a text field with an auto-entered calculation that combines the first and last names. This field should also have a unique values validation field option specified to avoid problems with your value list.

               This value list is fairly simple to set up but isn't as user friendly as we might like once the number of values in your value list becomes long. In those cases some more sophisticated methods for finding and selecting a client may be used. See these demo files for some examples:

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

               Search portals:  https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

               FileMaker 12 and newer users can use Open from the File menu to open this last file in order to get a copy converted to the .fmp12 file format.

                

          • 2. Re: Setting up Data Entry forms using multiple tables
            cinderjack

                 I'm making progress.  I have the Client field on the Visit screen defined as a Drop-Down List and have the Value List set up (I think - see screen shot).  When I launch the drop-down I can see the last name of the client I select it, but then the display shows the ClientID.  

                 I have two questions -

                 1)  Is there a way to show the client name on this screen?

                 2)  I need to actually see the full name of the client.  Is there a way to use a concatenated field for the display field?

            • 3. Re: Setting up Data Entry forms using multiple tables
              philmodjunk

                   1)  Is there a way to show the client name on this screen?

                   a) use a pop up menu instead of a drop down list

                   b) if you want to use a drop down list, add the related name or names fields from the table of ClientID's and names to your layout to show the name. With the right settings, you can actually hide your drop down list behind a name field.

                   2)  I need to actually see the full name of the client.  Is there a way to use a concatenated field for the display field?

                   Define a text field with an auto-enter calculation that combines the first and last names the way that you want to see them in your value list. Set a "unique values" validation on this field. Select this field in place of the last name field as the second field of your value list.

              • 4. Re: Setting up Data Entry forms using multiple tables
                cinderjack

                     Thanks for the quick reply!

                     The pop-up worked perfectly, thanks.  I'm a little confused about the auto-enter calculation field.  I did define a field called FullName in the Client table, and set it up as a calculation of FirstName + " " + LastName (see attached), but only "0" shows up (including with newly entered records. 

                • 5. Re: Setting up Data Entry forms using multiple tables
                  philmodjunk

                       Plus is the addition operator for adding two numbers. To combine text like this, you need the concatenation operator: & instead of +

                       I'd set it up as:

                       LastName &", " & FirstName

                       given the way the names will appear in alphabetical order in your value list.

                       And a new auto-enter calculation does not automatically update existing records. If you have existing records in your table, see this thread for a way to deal with that problem: Updating values in auto-enter calc fields without using Replace Field Contents

                  • 6. Re: Setting up Data Entry forms using multiple tables
                    cinderjack

                         Perfect!  I'm all set - thanks for the help!