Hi All.
Just wanted to share something that was so simple, yet we never thought to put a rule like this in place until recently.
In our SKU repository, we have our records assigned to our master Product records through a "ProductNo" field
(highlighted below). This field is a Required field in Profile so, if left blank, it will trigger a severe validation error.
We realized that any value placed in the ProductNo field, even an incorrect one (ex: Referencing a Product record that didn't exist), would still pass validations and move to Production. We created a rule to cause a severe error in this case:
SELECT InternalRecordId, ErrorMessage
FROM
( SELECT DISTINCT s.InternalRecordId, 'Record will not save until a valid master product record is linked.' AS ErrorMessage
FROM SKU_Staging s
LEFT JOIN Product_Staging p ON s.ProductNo = p.productNo
WHERE p.productNo IS NULL
AND s.ProductNo IS NOT NULL)
AS T1 WHERE 1=1
Every once in a while, our Product Managers will forget to update the field in SKU if the Product record number is changed or forget to request a Product record to be built by our team. This will not allow them to save their SKU record until that step is complete.
If anyone can use this, please feel free to take this SQL and modify it to your architecture. I'm also open to any improvements to the code if anyone has any suggestions. Seems pretty simple enough though.
Best regards,
------------------------------
Jonathan Varo | PIM Lead
Fender Musical Instruments | \+1 480 596 9690
------------------------------