Exclude WF Attachments from LiveReport

I have a Livereport that is based on a Custom Livelink category. It searches for documents that have the category applied to them based on certain criteria (Department and Review Date). The documents reside in a Livelink folder but also reside in an archived workflow attachments container. How can I limit the search to only the Livelink folder and not the Livelink Workflow Attachments (I am getting multiple results for the same document. The document names are the same in both the workflow and in Livelink and they both have the Category applied to them.

Following is my SQL and I am attaching a Word doc with a screenshot of my LiveReport.

SELECT a.name, b.valdate as "Docreview", decode(c.valstr,null,' ',c.valstr) as "Docnum", e.valstr as "Facility", f.valstr as "Dept", x.name as "Docowner" FROM kuaf x, dtree a, llattrdata b, llattrdata c, llattrdata e, llattrdata f, llattrdata g WHERE x.id = g.valint and a.dataid = b.id and a.versionnum = b.vernum and b.defid = 862314 and b.attrid=10 and a.dataid = c.id and a.versionnum = c.vernum and c.defid = 862314 and c.attrid=12 and a.dataid = e.id and a.versionnum = e.vernum and e.defid = 862314 and e.attrid=6 and a.dataid = f.id and a.versionnum = f.vernum and f.defid = 862314 and f.attrid=5 and a.dataid = g.id and a.versionnum = g.vernum and g.defid = 862314 and g.attrid=22 and a.subtype = 144 and f.valstr = 'Customer Service' and b.valdate >= %1 and b.valdate <= %2 ORDER BY "Docreview"

LiveReport.docx

Find more posts tagged with

Comments

Kyle_Swidrovich

Hi Mary,

All workflow attachments in initiated workflows reside in the Workflow Volume (161). There is only ever one of these volumes in your system.

So you should be able to exclude by OwnerID given the ID of the workflow volume.

Cheers,

Kyle

Mary_Slane_Corona

Kyle

Thank you for responding to my request. My skill in SQL is not very good so if you wouldn't mind showing how your recommendation would fit into my code would really help.

Thank you.

Mary

Kyle_Swidrovich

If this is just for your system, then find out the DataID of your Workflow Volume:

select DataID from DTree where SubType=161;

Let’s say that returned DataID=12345.

The “volume” version of this is the negative of the DataID – i.e., -12345.

Then just add something like this to your where clause:

AND a.OwnerID != -12345

The OwnerID in DTree represents the volume the object lives in.

Regards,

Kyle

Mary_Slane_Corona

Kyle

Sorry, I didn't get back to you sooner. The internal customer/department I was developing this LiveReport for had a reorganization going on and this report became unimportant because the user that was pushing for it moved to another department. The new process owner became interested in it and it became important again. I tried your solution and it has worked perfectly. No documents showing up that were in the workflow. Thank you so much.

Mary