Precisely Enterworks

Hi!

How can I find the hierarchy information within SQL so that I can compare Node Values in Hierarchy Catalog and trigger an error if the Node Value doesn't match what is in the hierarchy itself?

Thanks,
-Jon

------------------------------
Jon Varo
Fender Musical Instruments Corporation
AZ
------------------------------

Hi Jon,

We've found that hierarchy node details are actually stored as code sets in the database tables and will query those on occasion. So you can use the Hierarchy Name to search the table B_CODE_SET and join the CODE_SET_ID to the B_CODE_SET_DETAIL table. Each node in the hierarchy is stored in the CODE column and you can see the parent info in the PARENT_CODE column.

I'm questioning if you need to write a special validation rule for this scenario you're describing though. I've noticed for us that when a user uploads a node value via spreadsheet, if the value doesn't match exactly, the system will automatically raise a "code set" type error without us needing to make a special validation rule. For example, in the screenshot, the Hierarchy Node value should be "Outdoor Table + Shade" and when the user uploaded "Outdoor + Shade", the system already recognized that value isn't in the hierarchy.

But maybe if you have some additional logic that needs to be included along with the hierarchy node, then it would require a special validation rule.

Hope this helps!
Josh

------------------------------
Joshua Swett
Steelcase, Inc
MI
------------------------------

Hi Jon,

Here is a query you can use to report on this info:

SELECT Product_Id, NodeValue, Id
FROM Hierarchy_Catalog_Staging
WHERE Hierarchy_Name = 'MSS Product Catalog 2022' AND NodeValue NOT IN (SELECT Code FROM B_CODE_SET_DETAIL WHERE CODE_SET_ID = 14144)

This query selects records in the Hierarchy Catalog that are assigned to a specific Hierarchy Name and have a NodeValue that doesn't match the schema of the Hierarchy.   There is a knowledge base article that will help with adding the syntax required to set a Bulk Callout rule to support this query if you need validation on records in the Hierarchy Catalog.  Here is the link:

https://partner.precisely.com/s/article/How-to-Create-a-Bulk-Callout-Validation-Rule-360041232771

Hope all is well in the world of Fender and let me know if you need anything else.

Thanks,

------------------------------
Eric Gilboe
Practice Director - EnterWorks
eric.gilboe@goamplifi.com
Atlanta | Chicago | Dallas | Los Angeles | London
------------------------------

Thanks a lot Eric! Really appreciate your help.

Question... why is your account listed as "Accounts Payable"?

------------------------------
Jon Varo
Fender Musical Instruments Corporation
AZ
------------------------------

Original Message:
Sent: 05-04-2023 08:04
From: Accounts Payable
Subject: Creating A Validation Error If A Hierarchy Catalog Record Node Value Doesn't Exist In The Hierarchy Itself

Hi Jon,

Here is a query you can use to report on this info:

SELECT Product_Id, NodeValue, Id
FROM Hierarchy_Catalog_Staging
WHERE Hierarchy_Name = 'MSS Product Catalog 2022' AND NodeValue NOT IN (SELECT Code FROM B_CODE_SET_DETAIL WHERE CODE_SET_ID = 14144)

This query selects records in the Hierarchy Catalog that are assigned to a specific Hierarchy Name and have a NodeValue that doesn't match the schema of the Hierarchy.   There is a knowledge base article that will help with adding the syntax required to set a Bulk Callout rule to support this query if you need validation on records in the Hierarchy Catalog.  Here is the link:

https://partner.precisely.com/s/article/How-to-Create-a-Bulk-Callout-Validation-Rule-360041232771

Hope all is well in the world of Fender and let me know if you need anything else.

Thanks,

------------------------------
Eric Gilboe
Practice Director - EnterWorks
eric.gilboe@goamplifi.com
Atlanta | Chicago | Dallas | Los Angeles | London
------------------------------

Ha ha. Not quite sure why.  Think it was just set up that way with Precisely for Amplifi.  Either that or it's my code name when I play Fortnite with the kids. :-)   Hope all is well sir!

------------------------------
Eric Gilboe
Practice Director - EnterWorks
eric.gilboe@goamplifi.com
Atlanta | Chicago | Dallas | Los Angeles | London
------------------------------

Original Message:
Sent: 05-09-2023 16:04
From: Jon Varo
Subject: Creating A Validation Error If A Hierarchy Catalog Record Node Value Doesn't Exist In The Hierarchy Itself

Thanks a lot Eric! Really appreciate your help.

Question... why is your account listed as "Accounts Payable"?

------------------------------
Jon Varo
Fender Musical Instruments Corporation
AZ
------------------------------


Original Message:
Sent: 05-04-2023 08:04
From: Accounts Payable
Subject: Creating A Validation Error If A Hierarchy Catalog Record Node Value Doesn't Exist In The Hierarchy Itself

Hi Jon,

Here is a query you can use to report on this info:

SELECT Product_Id, NodeValue, Id
FROM Hierarchy_Catalog_Staging
WHERE Hierarchy_Name = 'MSS Product Catalog 2022' AND NodeValue NOT IN (SELECT Code FROM B_CODE_SET_DETAIL WHERE CODE_SET_ID = 14144)

This query selects records in the Hierarchy Catalog that are assigned to a specific Hierarchy Name and have a NodeValue that doesn't match the schema of the Hierarchy.   There is a knowledge base article that will help with adding the syntax required to set a Bulk Callout rule to support this query if you need validation on records in the Hierarchy Catalog.  Here is the link:

https://partner.precisely.com/s/article/How-to-Create-a-Bulk-Callout-Validation-Rule-360041232771

Hope all is well in the world of Fender and let me know if you need anything else.

Thanks,

------------------------------
Eric Gilboe
Practice Director - EnterWorks
eric.gilboe@goamplifi.com
Atlanta | Chicago | Dallas | Los Angeles | London