ACCEPTED SOLUTION
Accepted Solutions
Hi Arun
The error message for the import says :
Cannot insert the value NULL into column 'reasonhierarchyuid', table 'RTPPM.dbo.downtimefault': column does not allow nulls. INSERT fails.
This is because it reached the Downtime Fault section, which is based on Reason Hierarchy. And in the Downtime Fault it could not find the related Reason Hierarchy. And there's a few places in the Excel, like Downtime Fault, which have an annoying constraint : you must configure some of it in the screens/UI before you can finish the import. Reason Hierarchy is one of those constraints you need to fill in the screens. To configure them, go in the main menu -> Configuration -> Reason Trees, and look at the Reasons & Reason Trees tabs. Note that the "hierarchy" is the 2nd tab in the reason tree, here is an example from my test server :
I see that you have 4 Downtime Faults you want to import. Let's take the first as an example, you have Reason Tree Name = "Problem in the filler", Reason Level = 1, and Reason Name = "Hardware and network failure". So you need 3 things here : a reason with that name, a tree with that name, and it that tree this reason must be at level 1. If one of those 3 conditions fails, then you will get the error that you saw in SQL. You can do the following queries to check :
DECLARE
@TreeName NVARCHAR(255),
@ReasonName NVARCHAR(255),
@Level INT
SET @TreeName = 'Problem in the filler' -- change this
SET @ReasonName = 'Hardware and network failure' -- change this
SET @Level = 1 -- change this
SELECT CASE WHEN name = @TreeName THEN 1 ELSE 0 END AS 'This one?', * FROM reasontree ORDER BY [This one?] DESC, name ASC
SELECT CASE WHEN name = @ReasonName THEN 1 ELSE 0 END AS 'This one?', * FROM reason ORDER BY [This one?] DESC, name ASC
SELECT CASE WHEN rt.name = @TreeName AND r.name = @ReasonName AND rh.reasonlevel = @Level THEN 1 ELSE 0 END AS 'This one?', rt.name 'ReasonTreeName', r.name 'ReasonName', rh.reasonlevel
FROM reasonhierarchy rh
JOIN reasontree rt ON rt.uid = rh.reasontreeuid
JOIN reason r ON r.uid = rh.reasonuid
ORDER
BY [This one?] DESC,
rt.name ASC,
r.name ASC
In each query, if it exists with the correct name then it will appear on top of the list.
Edit : I want to point out the same problem is to be expected with Waste Faults, which also rely on the Reason Hierarchy section. And for people using the Bom tabs (CWC, not RTPPM), they require setting up the products in the screens/UI first.
Hi Arun
The error message for the import says :
Cannot insert the value NULL into column 'reasonhierarchyuid', table 'RTPPM.dbo.downtimefault': column does not allow nulls. INSERT fails.
This is because it reached the Downtime Fault section, which is based on Reason Hierarchy. And in the Downtime Fault it could not find the related Reason Hierarchy. And there's a few places in the Excel, like Downtime Fault, which have an annoying constraint : you must configure some of it in the screens/UI before you can finish the import. Reason Hierarchy is one of those constraints you need to fill in the screens. To configure them, go in the main menu -> Configuration -> Reason Trees, and look at the Reasons & Reason Trees tabs. Note that the "hierarchy" is the 2nd tab in the reason tree, here is an example from my test server :
I see that you have 4 Downtime Faults you want to import. Let's take the first as an example, you have Reason Tree Name = "Problem in the filler", Reason Level = 1, and Reason Name = "Hardware and network failure". So you need 3 things here : a reason with that name, a tree with that name, and it that tree this reason must be at level 1. If one of those 3 conditions fails, then you will get the error that you saw in SQL. You can do the following queries to check :
DECLARE
@TreeName NVARCHAR(255),
@ReasonName NVARCHAR(255),
@Level INT
SET @TreeName = 'Problem in the filler' -- change this
SET @ReasonName = 'Hardware and network failure' -- change this
SET @Level = 1 -- change this
SELECT CASE WHEN name = @TreeName THEN 1 ELSE 0 END AS 'This one?', * FROM reasontree ORDER BY [This one?] DESC, name ASC
SELECT CASE WHEN name = @ReasonName THEN 1 ELSE 0 END AS 'This one?', * FROM reason ORDER BY [This one?] DESC, name ASC
SELECT CASE WHEN rt.name = @TreeName AND r.name = @ReasonName AND rh.reasonlevel = @Level THEN 1 ELSE 0 END AS 'This one?', rt.name 'ReasonTreeName', r.name 'ReasonName', rh.reasonlevel
FROM reasonhierarchy rh
JOIN reasontree rt ON rt.uid = rh.reasontreeuid
JOIN reason r ON r.uid = rh.reasonuid
ORDER
BY [This one?] DESC,
rt.name ASC,
r.name ASC
In each query, if it exists with the correct name then it will appear on top of the list.
Edit : I want to point out the same problem is to be expected with Waste Faults, which also rely on the Reason Hierarchy section. And for people using the Bom tabs (CWC, not RTPPM), they require setting up the products in the screens/UI first.
