I’m just putting this out there to anyone who gets the same error: (no answers needed)
The cast to value type ‘System.Guid’ failed because the materialized value is null. Either the result type’s generic parameter or the query must use a nullable type.
This relates when parts are being checked in from an engineering workbench AND a attachment has been changed or removed. and its been happening ages and is pants.
When you check in a part to the engineering work bench, the xfileattach creates rows cross referencing the mtlpart attachments to the ecomtl records. Thats fine.
What isnt fine is that this reference stays in the xfileattach table. so if you make a change to any of the parts’ attachments then check any part that has this as a material into the EB - its screwed.
In my mind. as soon as you check in a part from the EB, It should remove this reference. Come on Epicor - this has been causing problems for years
Hi, we’ve also noticed this while working in the engineering workbench. How do you go about checking in the part if this issue occurs? Is there a way around it?
I was thinking of going into the table through SQL and updating but I know Epicor doesn’t like that and so its better to stay away from that approach.
So it copies the xfile reference from the EcoMtl row to the PartMtl row, but doesn’t remove the EcoMtl attachment row too?
If so, that kinda makes sense from an Epicor perspective. The system leaves a copy of everything in the eco tables after check-in, which means that attachment still has a corresponding ecomtl record.
I think the thought process is that you create an ECO group, revise/create your parts, then close down the eco group. When you want to revise again, create a new ECO. Which then leaves the old ECO records as kind of a change history. A crappy change history, but still.
Problem is that nobody uses it like that. Also, I’m guessing this barfs when somebody accidentally checks a part in too early. What if you purge ECO? Does it remove the xfile record too?
So it copies the xfile reference from the EcoMtl row to the PartMtl row, but doesn’t remove the EcoMtl attachment row too? - vice versa and yes it keeps the ecomtl attachment.
Therefore a newer version of an attachment causes the check in process to crash unless you manually remove the old version of the ecomtl .
We use a DMT group for all our “Auto” imports - to create a new group for each import is…impossible!
There is a Epicor Fix Out there - we used it, but it only works on a one to one basis. we do hundreds of changes a day so its not practical . I’ve created a sql script to clear the xfileattch table that is linked to the group in question and ref the ecomtl. its the only way round the problem.
Dynamically generate a new import ID. Like “AutoYYYYMMDD”. Pretty sure you can write a powershell script for that, but I don’t really use DMT for that kind of thing. I usually stick to API calls that handle 1-2 records at a time in realtime.
There’s also FX_Del_ECORev_byGroup. It removes the old checked-in part records from the eco tables. I run it monthly-ish. First time I ran it, after letting things stack up for a couple years, it removed roughly a million rows from the various eco tables. Workbench runs noticeably faster since.
It doesn’t seem to remove the corresponding XFile records though. That isn’t a huge issue for us, since we don’t reuse old revisions for traceability reasons (BPM’s autoincrement rev on checkout). But it might still be an issue for you. Maybe there’s a fix that explicitly removes orphaned XFile records?
"Hi, I regret the late feedback on this. After my last update, I made several attempts to replicate the issue again, but no success. Later on I found out that this has already been reported and is fixed in 2023.2. I am attaching the bug number and will set the case to suggested resolution sent. as a temporary workaround for earlier versions, it is still suggested to run the generic fix you mentioned to remove the orphaned material records that cause the error.