4 Replies Latest reply on Dec 17, 2013 11:21 AM by bobrace

    A simple DB Setup question, I'm sure.  Please help


      I am new to Filemaker Pro (using 12), have some DB experience and I am trying to setup a DB that is relational so that I may pull (or lookup) in formation in one table of DB into another table or DB.  I want one database or table that will hold last_name, first_name and social_security_number which we'll call NAMES.  In the other table, which will call ACCOUNT_INFO, it will hold account information about a customer such as account numbers, tranaction ammounts, etc. What I need is to set it up so that when a user is in ACCOUNT_INFO, the user will type a person's social security number and it will pull the person's name associated with that social into the account_info screen.  I have tried to this, using a "portal" field in the ACCOUNTS_INFO, but cannot get it to work.  This sounds simple to me, but I just cannot get it to work properly.  Any help would be very much appreciated.  Thank you.

        • 1. Re: A simple DB Setup question, I'm sure.  Please help

          Bob -


          You'll need to set up a relationship between the social_security_number field in the NAMES table and the equivalent field in the ACCOUNT_INFO table. This is done on the Relationships tab in the Manage Database dialog (File > Manage > Database). Just drag and drop the field from one table occurrence to the other.


          Once you've done that, you can define the fields you want as lookups under Options (lower right-hand corner of the Manage Database dialog).


          As an aside, it's usually not necessary to perform lookups of information from one table to another. Except in unusual circumstances, you can use the related fields from the parent table directly on your layouts and in portals without having to copy it over. It's poor database practice to keep data in multiple places, so avoid it if you can.





          • 2. Re: A simple DB Setup question, I'm sure.  Please help





            Thanks you so much for your help.  Do you know if it matters in which table I do the join?  Also, can the Social Security field be made unique so that I do not end up with duplicates?







            • 3. Re: A simple DB Setup question, I'm sure.  Please help

              Bob -


              You don't do the join in a table. You do it on the Relationships Graph, like this:




              When you set up your data entry, you can do it in a number of different ways, but the simplest would be a pull-down list that shows the Social and the person's name. This allows the user to select the appropriate person and then everything lines up appropriately. I've attached a sample.


              (Yes, you can validate any field as unique through the Options tab. See the sample.)




              P. S. I apologize for using the .fp7 format for the sample. My computer is chewing on a data update in 12 at the moment.

              • 4. Re: A simple DB Setup question, I'm sure.  Please help



                Thank you so much for the sample, and the advice.  This has given me some great insight into where I need to go with this  If I may, ask another question??  I would like a portal to list all names and socials (I assume that it should) that belong to the account number(s) listed in the table.  I cannot seem to get the portal to list all associated people.  Examaple:


                I have 5 people's names, 3 accounts numbers (that's IF there are threre account numbers for this transaction) that they belong to.



                Account 1          Account 2     Account 3      Cash in           Cash Out



                Social Security           Names              

                123-56-6789               Joh Doe       

                987-65-4321               Jane Doe

                654-321-9888             Sandy Beach

                000-222-5444             Jim Shoe

                999-88-7777               Santa Claus


                So basically pulling names and socials from the "names_social" table into the "accounts_transactions" table when a user types in the portal (if there is not an existing customer, let the name and social get added through the portal).  I hope this make sense.


                Thank you so much


                1 of 1 people found this helpful