We use a watched folder to load documents into ECM, which are then attached to the appropriate E10 record using a workflow.
There have been a few issues with the workflow, so I am trying to find where we may have a document in DocStar but the attachment link in E10 is for a previously deleted version of the file (resulting in Document Not Found error in E10, even though a newer document attached to the same E10 record is in ECM).
I would really like to do a lookup against the XFileAttach and/or XFileRef tables in E10 using a list of documents in ECM, so I can see where an E10 attachment may be missing.
So far I have not been able to find a document-unique reference field between E10 and ECM.
This should be everything you need and more. I’ve got a few more scripts in my toolbox if there is something these don’t cover.
--this grabs a document out of Epicor
select *
from ice.XFileAttch fa join ice.XFileRef fr on fa.XFileRefNum=fr.XFileRefNum and fa.Company=fr.Company
where RelatedToFile='jobhead' and fa.company=200 and fa.XFileRefNum=253822
order by fa.XFileRefNum desc
-- this is the same document in ECM
SELECT *
from AAAA0001.dbo.Document where id='092c7202-f2e6-ee11-8106-005056a198f3'
-- this grabs details from Epicor related trans tables (Jobs in this case but will need to be modified for other trans)
select fa.RelatedToFile
, trannum = fa.key1
, Partnum = jh.partnum
, [_TableSysRowID] = jh.SysRowID
, Company = fa.company
-- These are my functions to manipulate these fields into useable data
, Title = case when left(xfilename,4)='http' then dbo.ashgetfilenamefromurl(xfilename) else dbo.ashgetfilenamefrompath(xfilename) end
, [FileName] = dbo.AshConvertAttachmentPath_SP(xfilename)
, XFileName
, [ReplacedXfileRefNum] = fa.XFileRefNum
, fr.DocTypeID
, xfilename
, xfiledesc
, fr.DocTypeID
, dt.Description
, dt.StorageType
from ice.XFileAttch fa join ice.XFileRef fr on fa.XFileRefNum=fr.XFileRefNum and fa.Company=fr.Company
left join erp.JobHead jh on fa.ForeignSysRowID=jh.SysRowID and jh.company=fa.Company
left join ice.DocType dt on fr.DocTypeID=dt.DocTypeID and fr.Company=dt.Company
where RelatedToFile='jobhead' and fa.company=200 and fa.XFileRefNum=253822
order by fr.XFileName
-- this grabs the metadata fields from ECM for the document
SELECT ContentType.Name
, CustomFieldMeta.Name AS Property
, CustomFieldValue.StringValue
--, dbo.CustomFieldValue.BoolValue
--, dbo.CustomFieldValue.IntValue
, [Document].Id
, DocumentVersion.Title
FROM AAAA0001.dbo.ContentType
INNER JOIN AAAA0001.dbo.[ContentTypeCustomFields] ON ContentType.Id = [ContentTypeCustomFields].ContentTypeID
INNER JOIN AAAA0001.dbo.CustomFieldMeta ON [ContentTypeCustomFields].CustomFieldMetaID = CustomFieldMeta.Id
INNER JOIN AAAA0001.dbo.CustomFieldValue ON CustomFieldMeta.Id = CustomFieldValue.CustomFieldMetaId
INNER JOIN AAAA0001.dbo.[Document] ON ContentType.Id = [Document].ContentTypeId
INNER JOIN AAAA0001.dbo.DocumentVersion ON [Document].Id = DocumentVersion.DocumentId AND CustomFieldValue.DocumentVersionId = DocumentVersion.Id
--WHERE (dbo.CustomFieldMeta.Name IN (N'ACP', N'PDR:', N'ACP Number', N'PDR Number',N'InvoiceNum',N'zAltecDocId'))
-- WHERE (dbo.CustomFieldValue.StringValue IS NOT NULL)
WHERE [Document].Id='092c7202-f2e6-ee11-8106-005056a198f3'
Order by [Document].Id,CustomFieldMeta.Name
The “Document” table that @MikeGross is referring to is found in the AAAA0001 database. Whether you access it through SSMS or another method, it is found in the same location.
