>>>>>Hi,
>>>>>
>>>>>The Title of this thread could be misleading. I need to describe what I am considering.
>>>>>
>>>>>Several customers will be using my VFP application, the DB is in SQL Server database. Each VFP application has a separate database. But I am considering making one table to be a "library" for all databases. So I would create a separate database, just for this table (it is simplified but sufficient for understanding). So far, no problem, since I can make the application to connect to both, its "own/local" SQL Server as well as the "central" database where the "library" table resides.
>>>>>
>>>>>Now comes the challenge. Each record of this "library/central" table should have a value from a table in the "local/individual" database. And clearly, one field can have an entry from only one table.
>>>>>
>>>>>Example:
>>>>>The "library/central" table has field Vendor [ ] specifying which vendor is responsible for this record. But the Vendor table is specific to each database.
>>>>>One VFP application needs to have the Library Record 100 point to vendor "0001" in its database.
>>>>>Another VFP application needs to have the Library Record 100 point to vendor "ABC-222" in its database.
>>>>>
>>>>>How would you suggest to accomplish this? (if possible at all).
>>>>
>>>>I don't think I understand the role of the library table. What's its purpose? What does one record there represent?
>>>>
>>>>Tamar
>>>
>>>This one - library - table has list of certain procedures that will be used by multiple customers. That is, the same procedure, for example No 1234, will be assigned to asset "ABC" for one customer and to asset "CBC" for another customer. Currently each database has this Procedure table. But in this case, the Enterprise customer wants all of their sites to share the same procedures.
>>>Does it explain the case?
>>
>>Not entirely. I still don't see what the problem is. Make sure you have fields to represent what you need and populate them. From what you've said so far, I see at least these columns:
>>
>>PK
>>Customer
>>Procedure
>>RecordPK--the PK of the record to apply this procedure to
>>
>>I suspect you need more than that, but it's hard to tell from what you've said.
>>
>>Tamar
>
>The challenge is that the "library" table has a field that refers to another table, local to each database. So imagine that that in addition to the fields above (that you wrote) the table has the field VENDOR_ID which points to a Vendor Table. But the Vendor Table is a different table in each SQL Server database.
>Conceptually I resolved the problem (not yet coded though). I will have a new table, in pseudo name PROC_TO_VENDOR. This table will be a "local" to each database and will "connect" the "library" table PROCEDURE to the "local" table VENDOR. It will just take some time to change the design and the code. Of course, if the customer approves.

Ah, it wasn't clear at all that the issue was the the Vendor table has a different name for different customers.

Anyway, your proposed solution would work, or you could just add a field to the library table to point to the local vendor table, though I guess if you need it for more than once procedure, that would be redundant. Maybe a data dictionary kind of table in the master database, that connects the conceptual name to the real name, for any table that varies by database.

Tamar