7 Replies Latest reply on May 20, 2009 3:45 AM by Jade

    Setting up my database - Please help



      Setting up my database - Please help


      I really need some quick help on this one because I am new to Filemaker. Say i want to create and invoices.fp7  and a clients.fp7. 


      On invoices i have:







      On clients i have:





      I tried many ways of creating table relationships between the 2 files but they dont sync?  If the fields in Name, Phone, Company on both files have a = relationship shouldnt it ne when i enter a new invoice a new client is saved automatically into my clients file or do i have to create a script?


      My main question, and i have read all the manuals, is how to work with realtionships without running a script?

      Im sorry if this is a silly question but i cant find an asnwer on this simple yet important question.


      Thanks in advance!





        • 1. Re: Setting up my database - Please help

          Hi Loudegg:


          I have 4 quick suggestions:


          • Although you can use 2 files (invoices.fp7 & clients.fp7), you will probably find it easier to use 2 tables in the same file: 'yourfile.fp7 '


          • Using Name & Phone & Company for your primary and foreign keys is not such a good idea because, sooner or later, someone is going to change their phone number, company name, or even name and this change will break your links.  It is better to use another key (e.g. Client_ID) that is auto incrementing and unique.  There is a field type for this purpose.  So your Invoice table should have: Invoice_ID, Client_ID, price, etc.  The Client table should have Client_ID, Name, Phone,  Company, etc.  The Relationship graph should link the Client_ID fields together so that: Client -< Invoice.  Now one client can have many invoices.


          • Before adding a new Invoice, you must first add the Client information if it does not already exist.  You should have a Client layout for this purpose.


          •  To link a new Invoice to the Client, consider using a Drop-down List field of the Invoice::Client_ID on the Invoice Layout using a  Value List of Client::Client_ID.  There is an option to display the client name instead of the Client_ID numbers for this drop-down list.  Essentially, this inserts the Client_ID number into Invoice::Client_ID when the user selects a specific client name from the drop-down list.  This action links the invoice to the client.


          • 2. Re: Setting up my database - Please help

            Hi Jade,


            thanks for your help you rock.  i relized late last night that you are correct with doing a table within my file rather than an external file.   i like your idea with the client ID that definitely makes more sense.


            i was just using the name, phone number fields to test the relationship which is still not working using "=" . maybe there is something wrong with my filemaker pro 10?


            this is frustrating!!!

            • 3. Re: Setting up my database - Please help

              Hi again,


              How did you setup the relationship.  Was it Client::Name = Invoice::Name AND Client:: Phone = Invoice:: Phone ? 


              And how did you enter the names and phone numbers on the two tables?


              How are you "testing the relationship" ? 

              • 4. Re: Setting up my database - Please help

                yep i tried that.  im getting confused because there seems to be many ways of setting up a simple relationship. i create a table in the fields then i can create or add relationships on the graph.  im dizzy :P


                to make it easier let me ask you this.   on the myfile.fp7 file i made 2 tables on it  "invoices"  and "clients"

                i have a text field on both called "Name" just to see if im doing this right.


                i just wanted to make a test that when i entered name on invoices it automatically store the name in clients PLUS if the same name was entered more than once on invoices it would NOT create a dupliacte name on clients (since the name already exsists) 


                you recommended that i  always create the client first then enter invoice which i can do.


                so basically how would you do this?  i figured out ho to do an import script but it can be done without scripts, correct?

                • 5. Re: Setting up my database - Please help

                  First of all, this can be done without a script.


                  This is an over simplification but just think of the relationship lines between the table occurrences on the Relationship Graph as defining the way the fields are related between tables.  


                  So when you just add a client name to an invoice record it will not automatically create a new client record. However, if the client name already exists on the client table, it will link this invoice to the client.


                  Instead of typing names which is error-prone, we can use a drop-down list field on the Invoice Layout that contains the list of all names from the client table.   (Please remember that using names for keys is not recommended.  Use the Client_ID discussed above.)

                  • 6. Re: Setting up my database - Please help

                    oh ok that is cool and it worked.   but now everytime i use the dropdown menu it adds another client in clients (duplicates even if it exsisted)?


                    also on the drop down box it sometimes shows clients:clientid as an option and when i go through fields on dropdown and select one it doesnt stick.  nothing is entered into the field?



                    • 7. Re: Setting up my database - Please help

                      Good, progress!


                      Let's verify a few things:-

                      • The Invoice layout is based on the Invoice table.

                      • The drop-down list field on the Invoice layout should be based on the clientid in the Invoice table (Invoice::clientid).  

                      • The value list used by the drop-down field should be based on the clientid from the Client table (Client::clientid)



                      I sent you a private message.  Check the envelope at the top right of this form.  If you would care to send your sample file to the email address in the private message, I will try to spot the problem for you.