I have to make a deep cleaning of our TN database in order to install the deployer.
The reason is that deployer need to have all UID’s from databases equal for all environments.
Hence, I started cleaning up the External IDs’ (tables IDTYPE, PARTNERID) by renaming, deleting or changing the ID of a IDTYPE record. Then, when the modification was taken into account, we have noticed that Document Types that were matching value against those ID, could not do it any more.
Let’s take an exemple :
- I have created “MyExtId” as an external ID. It has ID 100 in IDTYPE table, field TYPE
- I have a Document Type named “DOCTYPE1”.
- This “DOCTYPE1” Document Type is set to extract a SenderId then matches it against external ID “MyExtId” to find the partner Profile (in “Attribute to Extract” / “Transformation” / “Buit-in” of Document Type)
- thus ID “100” (from table IDTYPE field TYPE for DESCRIPTION=“MyExtId”) is stored for “MyExtId” in the TYPEDATA blob field of table BIZDOCTYPEDEF for TYPENAME=“DOCTYPE1”
- then I change “MyExtId” ID to “111” to be consistent with my production environement in table IDTYPE field TYPE
- I also create a field “MyExtId2” with ID “100” to be consistent with my production environement in table IDTYPE field TYPE
- then I want to modify all Document type that reference ID “100” to make them reference “111” in table BIZDOCTYPEDEF field TYPEDATA , problem : TYPEDATA is a blob field !
Here is how it would work if a document comes in :
- TN receives a document and finds the corresponding Document Type (“DOCTYPE1”)
- TN extracts value for SenderID from the document
- TN has an ID of “100” linked to “MyExtId” in the “Transformation / Built-in” zone wich correspond to the TYPE found in the IDTYPE table for this external ID
- I make the modification described above
- TN wants to match the extracted value against profiles external Id to find the corresponding partner.
Hece it reads DESCRIPTION field in IDTYPE table where TYPE=“100”, and finds “MyExtId2”
- TN tries to find the value in the partner profiles where “MyExtId2” equals the extracted value.
Problem : it should be trying to do this with “MyExtId”, thus step 3 should have returned ID “111” instead of “100”, thus the blob field must be updated.
Does anyone knows how to modify references to a Id Type in a document type USING SQL QUERIES (because faster, safer, roll back availability) ?
This is a bit hard to explain clearly as it involves references from TN Console, TN Database,…
Let me know if you want me to make even clearer !