We plan to load all lookup codes [Ex: Lender, Attorney etc] & the orderContactPersons for these lookup codes from SoftPro into our System using the "Lookup API"
We need the ability to select any given lookup code and its related OrderContact - Persons from our system (instead of going to SoftPro for this data)
Question I have is: When loading data from SoftPro - how do we relate items in the lookup code tables (Say Lender) to the items in OrderContactPersons
Is "OrderContact.LookupCode" foreign key.
It appears lookupcode can have duplicates
Regards
Hari
Lookup codes
-
Graham Campbell
- Posts: 61
- Joined: Fri Jul 01, 2011 10:06 am
- Location: Raleigh, North Carolina
- Contact:
Re: Lookup codes
On duplicate keys:
The OrderContact tables may allow duplicate keys, but this is dependent on a setting on the lookup table to allow them. You can choose to not allow duplicate keys. The best way to get your list of OrderContacts is probably to load it from the OrderContact tables and ignore the duplicates. If the duplicates are a concern, then consider evaluating those and figuring out whether they really should be the same. If not, consider removing them and changing the setting to disallow duplicates. This is a business decision that may or may not be appropriate for you.
On contact people:
OrderContactPersons are stored in their own lookup table. This lookup table has a Filter column which filters the list of people based on the OrderContact. You should be able to look at the filter column to determine which people are affiliated with which contact. Look for the column on the OrderContactPersons table where IsFilter = true.
You will need to parse this list to get the affiliated OrderContact lookup codes. The values in this column should be a comma or semicolon delimited list, depending on the version of the application. The filter columns do allow whitespace and the use of quotes to escape characters, but you likely won't need to deal with this for what you are doing.
The OrderContact tables may allow duplicate keys, but this is dependent on a setting on the lookup table to allow them. You can choose to not allow duplicate keys. The best way to get your list of OrderContacts is probably to load it from the OrderContact tables and ignore the duplicates. If the duplicates are a concern, then consider evaluating those and figuring out whether they really should be the same. If not, consider removing them and changing the setting to disallow duplicates. This is a business decision that may or may not be appropriate for you.
On contact people:
OrderContactPersons are stored in their own lookup table. This lookup table has a Filter column which filters the list of people based on the OrderContact. You should be able to look at the filter column to determine which people are affiliated with which contact. Look for the column on the OrderContactPersons table where IsFilter = true.
You will need to parse this list to get the affiliated OrderContact lookup codes. The values in this column should be a comma or semicolon delimited list, depending on the version of the application. The filter columns do allow whitespace and the use of quotes to escape characters, but you likely won't need to deal with this for what you are doing.
Graham Campbell
SoftPro Software Engineer
SoftPro Software Engineer