[TN Database] BIZDOCTYPEDEF reference a wrong IDTYPE


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 :

  1. TN receives a document and finds the corresponding Document Type (“DOCTYPE1”)
  2. TN extracts value for SenderID from the document
  3. 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
  4. I make the modification described above
  5. 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”
  6. 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 !



I’d hate to be in your position. This has trouble written all over it. What you’ve found is that it’s a bad idea to define TN items separately in each environment. The use of artificial keys (UUIDs) and indirect identifiers makes hooking things up 1) critical; 2) hard to change. ID types are not the only things that use artificial keys/internal identifiers. The profiles themselves, document types, extended field types, extended fields, etc. all use internal IDs that must match or things won’t work.

I would suggest a different approach – start over. Anything you do to the TN DB risks destroying things and tech support won’t help you if you run into trouble. Pick one of your environments (production, perhaps) and export its TN configuration to your other environments. Trying to fix things up by modifying the TN DB directly is not a good idea.

Hello Reamon,

thanks for this answer that makes me feel so confident ! :uhoh:

At first, I thought about doing what you proposed about deploying on environment onto other, but we have over 66% over our developpement not on production yet (and hopefully waiting for the deployer tool). And there absolutly no possibility to overwrite production (what is working must not be changed… you know that, don’t you?).

I think the only best way we have will be to manually check all Doc Type and look for / modifiy wrong entries, that’s only 193 doctypes… :eek:

Nice days to come…

If someone has a faster, yet reliable way to do it… welcome !

Thanks Reamon

Sorry that I was the bearer of bad news!

IMO, that is exactly what you should to. Copying production to development is the safest way to not break production in the future. You can export the current development settings for reference. If I recall correctly, when you import something and the UUIDs don’t match, you’ll be prompted for what to do (overwrite, leave current setting). With this, you may be able to import the production settings into development. Then manually import the saved development settings, noting the items that did not import. Then manually redefine those items.

With this approach, development will be based on the production settings and all should be good.