I have a solution that people will enter a File ID # into and a script will create a ne record in a FMP table. This File ID # is the primary key from an ESS ODBC connected table. When the new record is created, there is a lookup field that gets populated with a secondary key in the ODBC table. This secondary key joins the first ODBC table to a three other ODBC related table that I need to do lookup on for other data. Most of the time all of the data is looked up and displayed properly on my layout. However about every fifth to ten record that Is created, the secondary lookups do not populate or populate with data from an incorrect record. Sometimes I can tell it to relookup the data and it does it correctly, but most of the time it does not.
If I go to the related ODBC tables and manually find the records by the secondary key the data is there. Then if I go back to my FMP table and tell it to relookup the data again, my fields get populated correctly.
The ODBC tables have about 125,000 records each and there are four ODBC tables that data needs to be looked up from. It is looking up one to eight fields from these ODBC tables. Small number in comparison to the number of fields in each of these tables. The relationship to each table is off of my main FMP table with one join by the primary key of the main table from this other application data, and the other three table from the secondary key that is used by this vendor to join their other tables back to the first table in there system.
I started off with the script adding a record, enter the FileID #, and Commits the record. Then since it wasn't consistently doing the lookup, I added relookup sets to the script to force it to look up the data, but that didn't help.
Since I can manually find the data in the ODBC tables, and then the relookups will work, does anyone have suggestions on how I can clean this up to work consistently?
Thanks in advance!