In the equipment table, you should have a field that contains the Primary Key ID of the vendor. If you do this, you will not have to replace the name of the vendor in the Equipment table when the name of the vendor is changed in the Vendor table.

Now all you have to do is join the tables to create a local view --

create local view MyViewName as select list_of_fields from Equipment join Vendor on Equipment.Vendor_ID = Vendors.KeyID where Equipment.Vendor_ID = ?nVendor_ID order by whatever


>All,
>
>I have tables in which I want to have an index containing fields from another table.
>
>For example I have a equipment table and a table of vendors who supply equipment. Equip is linked to Vendors by Vendors_id. In order to maintain an index "Supplied by" I have been keeping a copy of the vendors name in the equipment table.
>
>Naturally this means I have to scan through equipment each time I change a vendor name. The two tables will get out of sync at times. I keep chasing these down problems and keep writing small pieces code to prevent them. Or I can try something different.
>
>I do not like to keep an index tag in one table with fields from another. This causes problems opening the tables, sometimes there is loop; table_1 must be opened before table_2 which must be opened before table_1.
>
>So I am trying to build these indexes on the fly.
>
>The index is built when the user first needs it.
>
>index on vendors.vn_name + equip.eqno to c:/Ctemp/supp_sub
>
>When the user changes the the vendor I have a problem. It is as if the table were in a loop and the record is in both the new and the old position. Skipping through the table goes from the new positon of the record through records to the old postion of the table and then immediately to the new postion. Skipping the other way produces more normal results.
>
>Table has optimistic table buffering on. I have to cusorsetprop buffering to off to index the table, then set it back on.
>
>However this problem seems to exist when I keep data buffering off and setup everything from the command line.
>
>Any explanations for this? Any thoughts on how to handle this problem? It must come up in nearly every application.