6 Replies Latest reply on Sep 14, 2011 3:58 PM by SaraBaziotis

    Auto-populating data between databases

    SaraBaziotis

      Title

      Auto-populating data between databases

      Post

      How do I create relationships between two databases so that data from one database can auto-populate in another?

      Forum_example.jpg

        • 1. Re: Auto-populating data between databases
          philmodjunk

          Can you describe what you want in more detail?

          It depends on the design of your tables and how they need to link each other.

          It depends on whether your "databases" represent separate files or just different tables in the same file.

          It depends on whether that "auto populate" action should physically copy data from one table to the other or just should display the current values of the related record(s) in the second table...

          • 2. Re: Auto-populating data between databases
            SaraBaziotis

            I have a customer database and a job database, I believe them to be separate files.

            The customer database holds all known locations for each boat on the second tab

            What I would like to happen is when the corresponding name of the boat is entered into the job database, all the location information is automatically generated based from the customer database.  So the job database should 'physically copy' data from the customer database and the trigger should be the name of the boat.

            Please let me know if I can be more specific or answer any related questions and thank you for your answers!

            • 3. Re: Auto-populating data between databases
              philmodjunk

              Can't a customer have more than one boat? (Sounds like you need a separate table to list each boat so that customers can have more than one.)

              I'm referring strictly to tables here as it makes no real difference whether the two tables are in the same file or in different files except for a few extra steps needed to set up the relationship if the two tables are in different files. (That's why I asked about this.)

              Do you have a field in the Customer table that identifies each and every customer uniquely? You should have a serial number for each.

              WIth this relationship between the Customer and Job tables:

              Jobs::CustomerID = Customer::CustomerID

              You can define looked up value settings in field options for each field in Jobs that you want to receive data from Customer. Jobs::CustomerID can be formatted with a value list that lists the CustomerID values from Customer in field 1 and the customer names in field 2. (If you have separate last and first name fields, define a text field with an auto-entered calculation that combines them and use it for field 2.) With this value list, you can select a customer by name, but enter their ID number into the field. WIth the looked up value settings, any such update of the Jobs::customerID field automatically looks up (copies) the data from the customer table.

              Some things to think about when it comes to copying data from table to table like this:

              If you need to record the data that is current at the time this record is created, then this is the way to go. This way, you can look back at older job records and see the boat location at the time that the Job Records was created--which could be different from the boat's current location.

              Other data should not be copied in this fashion as changes to the original table (the customer table) will not automatically update the matching fields in the Jobs table. In those cases, you should just add the fields from the original table to your layout. That way, the data in them will always display the current values without any extra effort on your part to keep them up to date.

              • 4. Re: Auto-populating data between databases
                SaraBaziotis

                Although the customer can have multiple boats, they are usually kept at the same location therefore there is only one field for name/ address, etc

                So the customer field that identifies with the job field should be 'boat name' as opposed to 'customer ID' as we do not want the customer information, only the boat location information.

                The only option that comes up in the 'Inspector' is the current Table as opposed to an option to include a separate database, I hope this makes sense as it is obviously not very easy to explain.  It appears to me that I cannot figure out how to 'access' or 'relate' one database from another at all.

                Thank you for your patience

                 

                 

                • 5. Re: Auto-populating data between databases
                  philmodjunk

                  So the customer field that identifies with the job field should be 'boat name' as opposed to 'customer ID' as we do not want the customer information, only the boat location information.

                  No, this should be customerID and should be an auto-entered serial number. This value is simply used to link the curent jobs record to the correct customer record in the customer table. You can't use the name of the boat for this because boat names are not always unique and (though considered bad luck) the name might be changed.

                  To link tables in relationships, you open manage | Database | relationship and link them there. Since Customers is in a separate file, you'll need to click the far lower left button in this window to add a new blank table occurrence box to this map of your relationships. Then you select "Add FileMaker Data source" in the data source drop down. This opens up a dialog where you can find and open the customer file. From there you can find and select the correct table. After you have done that, you can click Ok to close the dialogs and drag from the customer ID field in Jobs to the customer ID field in the customer table.

                  Although the customer can have multiple boats, they are usually kept at the same location

                  You might want to start thinking about how you will handle the unusual customer that does not store all their boats at one location...

                  • 6. Re: Auto-populating data between databases
                    SaraBaziotis

                    Thank you.

                     

                    My question has been answered and you have been a great help.  Almost a bit snarky, but served the purpose.

                     

                    Again, thank you!