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]
Set Field [second database::value A; first database::value A]
Go to Record/Request/Page[Next; Exit after last]
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?
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.