Reselecting Item in 450 records for looked-up value

[bac mac]

I have a database of laboratory Orders which is linked to a table of Companies through the shared field, Vendor. Companies includes fields for Vendor, Street, City, etc.

I originally set up the Orders table with the relationship between the Vendor and included the Companies fields for Street, City, etc. on the Orders table.

This all worked fine and automatically populates the address info on Orders as soon as the Vendor is entered.

Unfortunately, as addresses change or companies merge, I need to change the data for Company records and, as currently set up, this changes the data for any Order records that use that company.

I am about to change the Order field to a looked-up value from the Companies table, rather than the actual field from the Companies table and this will solve things for new orders and preserve the original address info in older records.

Unfortunately, I seem to have to manually reselect the Vendor in the Orders Vendor popdown menu to get the addresses to repopulate the new looked-up value field in the existing records and there are 450 records.

•Is there a way to automatically get the 450 records to repopulate with the desired existing address info in the Looked-up value field? Or some other solution that would be recommended?

Thank you,

Bruce

[David Jondreau]

You can Replace Field Contents by calculation on the Vendor ID with the calc being the Vendor ID field itself to trigger the look up.

Test it on a found set of a single record a couple times to make sure it's working the way you want it to before blasting it on all 450. And back up first.

[AudioFreak]

Or a script using Relookup Field Contents.

Show all records

Go to Record/Request/Page[First]

Relookup Field Contents[Your Table::Your Vendor Field]

Loop

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

Relookup Field Contents[Your Table::Your Vendor Field]

End Loop

As stated above do this to a copy of your file first to make sure it is what you want.

Michael

[Lee Smith]

If I'm understanding you correctly, you are going to make a field in your file Look up its value in a second file / field. Once you have it set up, you can then do a Relookup.

With FileMaker open, open the Online Help, and do a search for Relookup for more on how to do this.

Lee

p.s. I see Michael has posted what I was saying, but my second paragraph can be helpful, so I will leave it.

Edited July 24, 2007 by Guest
p.s.

[bac mac]

Fully solved- I'm very impressed with the quick response.

Relookup worked great and thanks very much for the script steps- it helped a lot.

Thanks to all!

-Bruce