AnsweredAssumed Answered

Building a table link

Question asked by mgrahamor on Oct 12, 2018
Latest reply on Oct 16, 2018 by mgrahamor

I just imported a table from another source, ProjData that has several thousand records. I want to link it to an existing table, ProjList, that also has several thousand records. Here is a short version of what I am working with:


Fields for ProjData (the new table):
ProjData _DataID (unique number key)
ProjData _Name (text)
ProjData _Address (text)
ProjData _OrderNumber (text, which looks like “ORD1234”)
<more fields>


Fields for ProjList (the existing table):
ProjList_ID (unique number key)
ProjData _DataID (number field I added in preparation to link the tables, currently all blank)
ProjList_OrderNumbers (text, with order numbers list “ORD1234, OR1235, ORD2234”)
ProjList_Date (date)
<more fields>


I want to link the tables using this logic:

ProjList::ProjData_DataID needs to be set to ProjData::ProjData_DataID when ProjData::ProjData _OrderNumber is in ProjList::ProjList_OrderNumbers


Any best ideas on how to accomplish this?