4 Replies Latest reply on Feb 6, 2014 3:11 PM by Stephen Huston

    I am trying to make a field in one database that will access another database.

    automaticmike

      I am very new here, and have only made simple database files.

       

      I am trying to create a job ticket. So far it does everything I need.

      But I would like to be able to have a drop down menu that accesses another database.

      So when I go to put my customer name in this field, it will bring up a list from my customer database and let me choose one.

      Then it needs to put that customers name, address, city, state, zip phone and contact name in that field.

       

      Any ideas how to make this field?

       

      thanks

        • 1. Re: I am trying to make a field in one database that will access another database.
          Mike_Mitchell

          Mike -

           

          You need to add a few things:

           

          1) You need a unique key for the customers. This field must be unique and be automatically generated in the same table as the customers. It can be a serial number, a text value using Get ( UUID ), or whatever suits your pleasure, but it must be unique. Call it "CustomerID", for example. Use an auto-enter calculation (under the Options button in the Manage Database dialog). Choose File > Manage > Database.

           

          2) You need another field of the same type in your job ticket table. Call it the same thing ("CustomerID", if you want to go with that).

           

          3) You need a relationship between CustomerID in the job ticket table and CustomerID in the customer database. Do this on the Relationships Graph (just drag CustomerID on one table occurrence onto CustomerID on the other). Choose File > Manage > Database.

           

          4) You need a Value List based on Customer. Choose File > Manage > Value Lists. You should select "Use values from field" and "All values" when you make the list. Choose the CustomerID as the primary value, and the person's name as the secondary field.

           

          5) For the name, address, city, state, etc., you can do it one of two ways, depending on the need. If you want the job ticket to reflect the current values (i.e., if you change the person's information in the Customer databse, you want the job ticket to update automatically), you can just put the related fields from Customer on the job ticket layout. On the other hand, if you want to preserve what the contact information was at the time you entered the customer's ID on the ticket, you can do it as a Lookup or an auto-enter calculation, where you insert the values from the Customer table whenever the CustomerID field is updated. (Do this under the Options button in the Manage Database dialog.)

           

          HTH

           

          Mike

          • 2. Re: I am trying to make a field in one database that will access another database.
            automaticmike

            It sounds tricky. I am not quite that savvy.

             

            I could create a new field in the cust database, but is there no way to just use the customer name from that database?

             

            So that when I am in the job ticket database and I get to that field, I can just pick the customer name from the other data base and it will put the info into the job ticket field I am currently in?

            I do not need the address to update or anything, this job ticket would be for where the customer is now.

            For archive purposes, I would not want this to change.

             

            I appreciate your quick reply, and I will give this a shot.

             

            thanks

            • 4. Re: I am trying to make a field in one database that will access another database.
              Stephen Huston

              One should not use "meaningful data" for primary keys between related tables. Anything with meaning is susceptible to change.

               

              For example, image you build this using Customer Names to match records. 2 years from now your largest customer calls you up to proudly tell you that they have just changed their business name. You update it in your customer records. All your related jobs for that customer immeditately lose their relationship!

               

              I inherited a system built that way ten years before I took it over. By the time I as asked to fix it, a Million $$$ worth of jobs had already been lost in the records over the years due to customer name changes.

               

              Use the "Get ( UUID )" function to auto-enter Primary Keys for records, and then use that field to create your relationships that need to survive all future changes to the records.