3 Replies Latest reply on Jan 14, 2010 10:47 AM by philmodjunk

    Lookup entry between related databases

    acrobat_1

      Title

      Lookup entry between related databases

      Post

      Dear all,

       

      I have two databases that are related by one field.  One of the databases in automatically populated by XML data.  I need to move data from entries in that database into the second database in related fields.  These fields have the same name as the fields in the XML populated database.  I am unsure of the syntax for doing this.  I was planning to do a loop in the second database with:

       

      Go to Layout [second database]

      Show All Records

      Go to Record/Request/Page[first]

      Loop

        Set Field [second database::value A; Lookup (first database::value A)]

        Go to Record/Request/Page[Next; Exit after last]

      End Loop

       

      The above does not work and I guess I am not using the Lookup command correctly.  Any wisdom?

       

      Thanks,

       

      -Rob 

        • 1. Re: Lookup entry between related databases
          philmodjunk
            

          Key Question: When the data changes in the first table (database), do you always want the new data to appear in all the fields of all the records in the 2nd table? If so, you shouldn't "look up" this information at all. Instead, you would simply use the field tool to add the fields from your first table to layouts where you need to see this related data. This approach assumes that you've defined a relationship between the two tables and that both are always accessible at the same time.

           

          If you don't want the data in existing records to change when data in the first data changes, then you'll need to use one method or another to physically copy the data from the first table to the second. A simple method for copying such data over for new records is to use the "looked up value" auto-enter field option to copy the data over to selected fields. With this option, you can also use "relookup" to update the data in all such fields in your found set in one step.

           

          You can also use a script to update the data, but if you've defined a relationship between the two tables, you won't need the look up function.

           

          This version of your script would do the job:

           

          Go to Layout [second database]

          Show All Records

          Go to Record/Request/Page[first]

          Loop

            Set Field [second database::value A; first database::value A]

            Go to Record/Request/Page[Next; Exit after last]

          End Loop

          • 2. Re: Lookup entry between related databases
            acrobat_1
              

            Thanks again Phil,

             

            I would like to use the lookup function on this and have established a relationship between the databases related to one filed (an identifier).  However, the only choice I have to automatically lookup the data is when there is a change to a value in the first database.  It's the second database that is changing and I want the data from that second database to automatically flow to certain fields in the first database.  Is there a way to get the lookup function to do its job when the SECOND database changes and automatically flow it's data to the defined lookup fields in the FIRST database?

             

            -Rob 

            • 3. Re: Lookup entry between related databases
              philmodjunk
                

              To repeat from my earlier post:

               

              "...you shouldn't "look up" this information at all. Instead, you would simply use the field tool to add the fields from your first table to layouts where you need to see this related data."

               

              If changes in table 2 should automatically appear when viewing records in table 1, just add the data table from table 1 to your layout for table 2. That will produce the result you have requested.