I’m looking for a way to query the Epicor database for strings matching a certain format. These strings are actually file paths stored in Kinetic records. E.g. we have links to PDF’s, other documents that exist on our local network file server. We do plan to use ECM once it’s ready for GovCloud. Until then though I’m hoping to have some idea of just how many records exist that include references to an externally linked document. Path would follow a typical ‘E:\folder1\folder2\foo\doc.pdf’ type string.
Has anyone created a query that can do something like this?
You’ll probably have to get a little more specific than that. You want to query the whole database? All tables all fields? Or you have a specific table and field you are looking for?
The latter is doable, the first… I don’t really know how to do, you would need some pretty advanced query (that you would have to do directly in SQL) to loop through the names of all the tables and all the columns then look at the values in each of them to examine string format. It would take a LONG time to complete, if it’s even possible.
This doesn’t specifically answer your question, but I have a BAQ that does something similar, for BAQs. I think I got the BAQ from this site somewhere…
select
[QueryHdr].[QueryID] as [QueryHdr_QueryID],
[QueryHdr].[AuthorID] as [QueryHdr_AuthorID],
[QueryHdr].[Description] as [QueryHdr_Description]
from Ice.QueryHdr as [QueryHdr]
where (QueryHdr.DisplayPhrase LIKE '%Your Search String%')
I’m also not sure how you would go about searching the whole database. You will be better off to understand where the file strings can be listed, then search those locations using a BAQ similar to this.
