1 2 3 4 Previous Next 48 Replies Latest reply on Sep 16, 2010 1:27 PM by philmodjunk

    Creating Relationships within 2 different databases

    sunwelding

      Title

      Creating Relationships within 2 different databases

      Post

      Hello. How can I make information from database appear in a field on another database.  For instance I want to type the customer name and then have their information (address, city, state, zip, and phone) appear in fields below.  That way I am not typing the information in again.

      I have a database created currently where my customers information is stored.  I also have an invoice database.  What I would like to do on the invoice database is have a field for customer name, enter their name, and then have the other information automatically appear. 

        • 1. Re: Creating Relationships within 2 different databases
          philmodjunk

          Two relationship based options: Option 1 physically copies the data from the Customer Information table. Option 2 just displays the current value from the Customer information table. Most invoicing system use Option 1 as they don't want this information to change in existing invoice records when a customer's information is changed--they need to know what was "current" at the time the invoice was created.

          To link the two tables in a relationship, I strongly suggest that you not use the customers name. People/companies change their names; two or more people/companies can have the same name and they're vulnerable to data-entry errors.

          Define a serial number field, CustomerID in your customer information table and use it to link your two tables:

          Invoices::CustomerID = CustomerInfo::CustomerID

          Selecting/entering a customer ID number will link that invoice to a CustomerInfo record with the same number.

          For Option 1, once this relationship is established, you can use the Looked Up Value auto-enter field option to copy data from a corresponding field in CustomerInfo. For Option 2, You'd just place the actual field from CustomerInfo on your invoice layout and it'll display data from the matching customer Info record when you enter/select the customer's ID number.

          To select a customer by name and get the ID: Define a 2 column value list of customer ID's, Make column 1 the ID number, Make column 2 at least the customer name field. Many developers create a calculation field that combines name and address and/or name and phone number in a single field for column 2 to help select from duplicate names.

          A more sophisticated approach can be done with scripting that enables the auto-complete feature of a drop down list of names yet handles duplicate names and enters the ID number instead of the name:  Tutorial: How to use an auto-complete drop down list when selecting records related by ID number

          • 2. Re: Creating Relationships within 2 different databases
            sunwelding

            I see how to set relationships by using the tables.  How do I get the information from one database into another databases table?  I can't seem to be able to add the other database into the tables.

            I would like to do the option one because I need to save the information that was taken at the time of the order.

            • 3. Re: Creating Relationships within 2 different databases
              rjlevesque

              Go to File | Manage | External Data Sources and add the additional database to your existing database and then you can use all it's tables and data just like they are the same DB

              NOTE:You will still need to develop relationships between tables of course just like you would with tables in the single database.

              • 4. Re: Creating Relationships within 2 different databases
                sunwelding

                I have added the database through external data sources.  When I click the relationship tab the new database does not show.  Is there a way to get it to show?

                • 5. Re: Creating Relationships within 2 different databases
                  rjlevesque

                  Ok go to Manage (Tables tab), in the bottom right corner you see Import, import a table from the additional database that you need to use. Then you will be able to now use the other database tables to build relationships.

                  • 6. Re: Creating Relationships within 2 different databases
                    sunwelding

                    Ok I got the database into the table.  Now, these are the fields that I have

                    Customer (database 1)

                    Customer Name (database 2)

                    Address1

                    City1, State1 ZipCode1

                    What I would like to be able to do is if I enter a dealer name in the customer field I would like it to fill in the database 2 information (customer name, address1, city1, state1, zipcode1).  If it is not a dealer name in the customer field I would like to be able to enter information in these fields.

                    • 7. Re: Creating Relationships within 2 different databases
                      sunwelding

                      Is there a way to create a CustomerID without loosing the information?  I would like to use the option 1 as stated in the first post.

                      • 8. Re: Creating Relationships within 2 different databases
                        philmodjunk

                        Sorry I haven't gotten back to you sooner. I've been dealing with a family crisis that's kept me off the forum all week for the most part. What information are you concerned about losing?

                        You can add CustomerID as a number field and specify that it be an auto-entered serial number. You can then use Replace Field Contents with the serial number option to assign CustomerID numbers to all your existing customers. The looked up value option will not affect existing data so you should be able to define a new relationship based on CustomerID and use it to look up data in each new field.

                        • 9. Re: Creating Relationships within 2 different databases
                          sunwelding

                          Ok I have done that.  So now what I want is when I start typing the name the address and phone number of that customer will appear.  How can I do that.  I know thats a relationship.  Right now I have fields for customer name (which I turned into the serial number), address, city, state, zip, phone.

                          • 10. Re: Creating Relationships within 2 different databases
                            philmodjunk

                            Instead of typing the name, it will be easier to select if from a value list. Typing in a name and having filemaker find the person can be done but it takes a bit of programming.

                            First is the key detail I first posted:

                            Invoices::CustomerID = CustomerInfo::CustomerID

                            1. Make sure you have that relationship, then you'll need a value list for selecting a customer. Open manage Value Lists, click New and name the value list something like CustomerNames.
                            2. Click the Use Values from field option.
                            3. There are two drop downs at the top of this new dialog, select CustomerInfo for both.
                            4. In the column 1 box, select CustomerID. In column 2, select customer name.
                            5. Click OK on each of the three dialogs to close them.
                            6. Now format your customerID field to use this drop down. This is a simplified set up--there are several details you can use to improve on it, but lets get the basics working first.
                            7. For the address, city, state, zip, phone fields, find them in Manage Database Fields and double click each in turn. In the dialog that pops up, select the auto-enter tab, click the looked up value option and specify the matching address, city, etc. fields from customer info.
                            8. Enter browse mode, click in the CustomerID field and select a customer to see the customer's contact info copied into the Invoice fields.
                            • 11. Re: Creating Relationships within 2 different databases
                              sunwelding

                              I hope everything is ok with your family.  Again thanks for helping me though this.  Ok, can we back up on second.  In the post 2 above you state "replace field contact with the serial number" Where is the option?  I have selected the field under manage database then selected my field customer, turned it into a number, then chose auto enter serial number.

                              To create this relationship "Invoices::CustomerID = CustomerInfo::CustomerID" Are these new fields that I am creating? or just the customer ID field? and customer info?

                              • 12. Re: Creating Relationships within 2 different databases
                                philmodjunk

                                To create this relationship "Invoices::CustomerID = CustomerInfo::CustomerID" Are these new fields that I am creating? or just the customer ID field? and customer info?

                                This represents the entries in Manage | Database | Relationships where you have an Invoices "box" (called a table occurrence) linking the CustomerInfo table occurrence. CustomerID::CustomerID refers to the serial number field you have defined in Customer Info. Invoices::CustomerID is the matching number field in Invoices that must store the same value as a CustomerID number in CustomerInfo in order for the two records to be linked.

                                • 13. Re: Creating Relationships within 2 different databases
                                  sunwelding

                                  I am a little confused.  Should I create new fields called "customerID" "customerInfo"and "Invoices"?  I currently do not have any fields that a called that.  i do not have any relationships set in my database.

                                  • 14. Re: Creating Relationships within 2 different databases
                                    philmodjunk

                                    You haven't given the names of the fields and tables you have so I have used those names as example names. You may or may not need to define additional fields. It depends on what you already have.

                                    Invoices and CustomerInfo are the names I've used for the two tables you already have.

                                    CustomerID would be an auto-entered serial number Defined in your table of customer information and you'd use it to link a customer record to records in other tables. You may already have such a field in one or both tables or you may need to define new fields.

                                    1 2 3 4 Previous Next