Precisely Enterworks

Hello All,

I'm looking for some help with a change notification that combines Vendor Style (item repo) with Vendor SKU (SKU repo) to create a new Vendor Item Name (SKU repo).  If the Vendor Style in Item repo is changed, then it needs to trigger and update the Vendor Item Name on all of the SKU records for that item.  But if the Vendor SKU in the SKU repo is changed, it only needs to update the Vendor Item Name for that one particular SKU.

I'm new to change notifications, but if I understand it right I need one cn registry that triggers on Vendor Style in Item with a transformation that will update Vendor Item Name for all SKUs.  And I need a cn registry that triggers on Vendor SKU with a transformation that will update just that one SKU.

So far I have the CN registry and transformation at the SKU level working.  If the Vendor SKU is changed, it will update the Vendor Item Name for just that SKU with the new correct Vendor Item Name value of Vendor Style + Vendor SKU.

What I can't figure out is the one to many change to update all of the SKUs when Vendor Style at the Item level is changed.  Right now it's updating all of the SKUs with the same data. It's pulling in the correct Vendor Style from Item, but then where it should add the different Vendor SKU for each SKU, it's just using the first Vendor SKU for all.  I guess it's pulling the target value for just the first row and then applying that to all rows for that item.

For my CN Registry I have it triggering in my Item_Staging repo and the attribute as Vendor Style.
Here is what I have set up for the transformation rule since I'm assuming that's where I'm going wrong.

CN_Target_Repo
SKU_Staging
CN_Target_Attribute_Name
Vendor Item Name
CN_Target_SQL

SELECT InternalRecordId
FROM SKU_Staging 
WHERE Item_Number = ||Item Number||

CN_Target_Value_Source_SQL 

SELECT 
    LEFT(LTRIM(RTRIM(CONCAT(ISNULL(i.Vendor_Style,''), ' ', ISNULL(s.Vendor_SKU,'')))), 20)
    AS Vendor_Item_Name
FROM
    Item_Staging i
INNER JOIN
    SKU_Staging s ON i.item_number = s.item_number
WHERE 
    s.Item_Number = ||Item Number||

CN_Target_Value_Source_Restricted_Name
Vendor_Item_Name

CN_Condition_Repo
SKU_Staging
CN_Condition_SQL

SELECT 
     ISNULL(Vendor_Item_Name,'') AS Vendor_Item_Name 
FROM 
     SKU_Staging 
WHERE 
     Item_Number = ||Item Number||

CN_Condtion_Attribute_Restricted_Name
Vendor_Item_Name
CN_Condition_Operator
<>
CN_Condtion_Data_Type
Text
CN_Condition_Value_SQL

SELECT
    LEFT(LTRIM(RTRIM(CONCAT(ISNULL(i.Vendor_Style,''), ' ', ISNULL(s.Vendor_SKU,'')))), 20)
    AS Vendor_Item_Name
FROM
    Item_Staging i
INNER JOIN
    SKU_Staging s ON i.item_number = s.item_number
WHERE 
    s.Item_Number = ||Item Number||

Thanks!

------------------------------
Ryan Hayes
Sundance Catalog Co
UT
------------------------------

Ryan,

Instead of populating CN_Target_Value_Source_SQL and its associated fields, change the CN_Target_SQL to produce a result set where the first column is the InternalRecordId of the records that need to be updated and the subsequent columns are the attribute names (not restricted names) of each attribute in that target record to be updated (along with the value).  You can update as many attributes in the same record as you wish and update as many records as you wish.  I'll leave it to you to figure out the SQL that will combine those InternalRecordIds with the appropriate values, but here is a simple example:

Let's suppose there are three repositories:  Product and Item, and Size where each Product record will have one or more Item records linked to it through the Product ID attribute, and each Item record has one or more Size records linked to it through the Item ID attribute.  For some reason, the Size repository has the attribute "Size Class" which is the concatenation of the Product's "Taxonomy" value and the Item's "Item Class" value and the Size's "Size" value.. Any time the Product record's Taxonomy is changed, the Sizes for the linked Items need to have their Size Class attribute updated.  The SQL would be something like the following (please note this SQL was not tested for syntax errors):

SELECT s.InternalRecordId, p.Taxonomy + '.' + i.Item_Class + '.' + s.Size as [Size Class]

FROM Product_Staging p

JOIN Item_Staging i on i.Product_ID = p.Product_ID

JOIN Size_Staging s on s.Item_ID = i.Item_ID

WHERE p.InternalRecord = ||InternalRecordId||

This would be defined in a CN_Transformation_Registry record linked to a CN_Registry record on the Product_Staging repository.  A similar rule would also be needed on the Item_Staging repository for when the Item Class is changed.  The CN_Target_SQL would have the same joins, but the WHERE clause would be:

WHERE i.InternalRecordId = ||InternalRecordId||

------------------------------
Brian Zupke
Precisely Software Inc.
------------------------------

That did it...thanks, Brian! 

I've been going in circles for longer than I would like to admit with this one.  The functionality that the change notifications provide are great and things that seem like they should have been baked into the software from the beginning.  But it feels like an afterthought workaround add-on that was added and forgotten.  What they can do is so helpful and necessary that I hope change notifications, and the functionality they provide, get better integrated with the software to make it easier to utilize and take advantage of.  Don't get me wrong, what's there seems well thought out and I can imagine the work that went into it, but for some of what it does, it seems overly complicated.  Simple things like mirroring an attribute's value from one repo to another seem pretty standard.  But here requires you to create and manage change notifications and transformations to get it done.  I know there are validation rules as well, but those aren't ideal in many cases.  I am still learning though, so it could very well be a lack of understanding on my part.  And there may even be things I'm missing.  We're also on the older version 10.3.4.  So maybe change notifications and some of their abilities have been updated and better integrated in a newer version of Enterworks?  We hope to update to the latest version soon...so hopefully that's the case! 

And I'm sure we're missing out on a lot of opportunities to use them rather than a validation rule, or some other method, when a change notification would be much better.  Or even provide some functionality that we don't currently have that would make a process easier.  However, the process of setting them up and managing them is not great...or intuitive.  So unfortunately for us, we've mostly avoided them.  Other more experienced users may feel differently, but unfortunately, that's been the case for us.

But I've watched all of the brown bags on them and constantly look over the Change Notification Admin Guide...so I hope to get a better understanding of them and change that!

Thanks for the help!

------------------------------
Ryan Hayes
Sundance Catalog Co
UT
------------------------------

Original Message:
Sent: 11-22-2023 02:28
From: Brian Zupke
Subject: Change notification help

Ryan,

Instead of populating CN_Target_Value_Source_SQL and its associated fields, change the CN_Target_SQL to produce a result set where the first column is the InternalRecordId of the records that need to be updated and the subsequent columns are the attribute names (not restricted names) of each attribute in that target record to be updated (along with the value).  You can update as many attributes in the same record as you wish and update as many records as you wish.  I'll leave it to you to figure out the SQL that will combine those InternalRecordIds with the appropriate values, but here is a simple example:

Let's suppose there are three repositories:  Product and Item, and Size where each Product record will have one or more Item records linked to it through the Product ID attribute, and each Item record has one or more Size records linked to it through the Item ID attribute.  For some reason, the Size repository has the attribute "Size Class" which is the concatenation of the Product's "Taxonomy" value and the Item's "Item Class" value and the Size's "Size" value.. Any time the Product record's Taxonomy is changed, the Sizes for the linked Items need to have their Size Class attribute updated.  The SQL would be something like the following (please note this SQL was not tested for syntax errors):

SELECT s.InternalRecordId, p.Taxonomy + '.' + i.Item_Class + '.' + s.Size as [Size Class]

FROM Product_Staging p

JOIN Item_Staging i on i.Product_ID = p.Product_ID

JOIN Size_Staging s on s.Item_ID = i.Item_ID

WHERE p.InternalRecord = ||InternalRecordId||

This would be defined in a CN_Transformation_Registry record linked to a CN_Registry record on the Product_Staging repository.  A similar rule would also be needed on the Item_Staging repository for when the Item Class is changed.  The CN_Target_SQL would have the same joins, but the WHERE clause would be:

WHERE i.InternalRecordId = ||InternalRecordId||

------------------------------
Brian Zupke
Precisely Software Inc.
------------------------------