DocStar Attachment Location

ECM
,
1

We are working on replacing APM with DocStar. One challenge we have is replacing logic from a homebrew web application that locates AR invoice PDF files that were sent to customers. To retrieve the file, we currently query tables in the APM database to locate the static copy of the invoice. This is fairly straightforward because both the invoice number and a pointer to the file’s location are clearly stored in the database tables.

We were told that we would be able to query the DocStar database similarly to find this information as it is attached in there as well. However, after looking at the SQL tables, it appears a bit more cumbersome.

  1. I cannot easily locate the key field (in this case invoice number) in the DocStar databases.
  2. If I start with Epicor to find the document from the invoice attachment, there is not a clear path to the content (ie, Document.Path + “/1_0_0/” + ContentItem.Filename).

Has anyone done this before or have suggestions on the best way to grab the file? Ideally, I could bypass Epicor and DocStar interfaces to retrieve the file as efficiently as possible by using a SQL query.

Thanks,
Tanner

2

Do you have InvoiceNum as a content field for the Invoice Document Type? You should be able to find the invoice number there which would give you the document GUID from DocStar. From there you can use https to display the document in an iFrame for your web site or download it via https as well. Something like:

https://yourDocStarServer/GetFile.ashx?functionName=DownloadZipToBrowser&path=

I won’t comment on the direct SQL access as efficient… :wink:

3

Thanks for the information, I really appreciate it. Do you know if the call to the service requires a license and/or login credentials?

4

Since DocStar has a solid security model, you’re definitely going to have to authenticate! And when you do, it will take a license. However, once you’re done, you can log out. I think that’s what Epicor does with DocStar attachments with View in DocStar verses View in Epicor. You can probably do a little network sniffing to see the call that Epicor is making.

2 Likes
5

Thanks. With the help of your suggestion I was able to figure out a solution

1 Like
6

Hi Tanner,

Can you share how you solved this? I am also looking to find the shortest path to get access to DocStar documents through a custom grid.

Thanks,
Ross

7

I ended up abandoning DocStar since the task agent was sending the documents too quickly for it to process, so this code never made it into our production environment. The query I made to get the file path is here but it didn’t end up going anywhere. Maybe it will help you or someone:

use AAAA0001

SELECT d.[Path]+ '\' + REPLACE(dv.VersionStamp,'.','_') + '\' + ci.FileName
FROM dbo.DocumentVersion dv WITH (NOLOCK) 
JOIN dbo.Document d WITH (NOLOCK) ON d.ID = dv.DocumentId
JOIN dbo.DocumentVersionContent dvc WITH (NOLOCK) ON dvc.DocumentVersionId = dv.ID AND dvc.[Sequence] = 1
JOIN dbo.ContentItem ci WITH (NOLOCK) ON ci.ID = dvc.ContentItemId 
JOIN dbo.ContentType ct WITH (NOLOCK) ON ct.ID = d.ContentTypeId
WHERE dv.CurrentState = 128
1 Like
8

Okay, thanks. I will take a look and see if I can do anything it.

Ross