Precisely Enterworks

 View Only

  • 1.  Creating A Validation Error If A Hierarchy Catalog Record Node Value Doesn't Exist In The Hierarchy Itself

    Posted 05-03-2023 12:50

    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
    ------------------------------


  • 2.  RE: Creating A Validation Error If A Hierarchy Catalog Record Node Value Doesn't Exist In The Hierarchy Itself

    Posted 05-03-2023 15:49
    Edited by Joshua Swett 05-03-2023 15:50
      |   view attached

    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
    ------------------------------

    Original Message


  • 3.  RE: Creating A Validation Error If A Hierarchy Catalog Record Node Value Doesn't Exist In The Hierarchy Itself

    Posted 05-04-2023 08:05

    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
    ------------------------------

    Original Message


  • 4.  RE: Creating A Validation Error If A Hierarchy Catalog Record Node Value Doesn't Exist In The Hierarchy Itself

    Posted 05-09-2023 16:04
      |   view attached

    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


  • 5.  RE: Creating A Validation Error If A Hierarchy Catalog Record Node Value Doesn't Exist In The Hierarchy Itself

    Posted 05-09-2023 16:15

    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