Skip to main content
S
STEVEG21-Topaz I
21-Topaz I
June 30, 2016
Solved

File Name of a document from Query Builder

  • June 30, 2016
  • 4 replies
  • 7774 views

I wanted to create a report that shows the File Name of a document but it's not listed.  Is there a way to get that in a query builder report?

Background:

Some of our documents are not publishing.  I created a call with PTC to see if we can figure out why.  It turns out that the macro-enabled Office documents are not supported by Adobe LiveCycle.  DOCM, XLSM, PPTM.

I wanted to find out how many of those types of documents we have in our system.  I did a keyword search but that returns other documents that are not those types.  However, even though they are not supported the majority of the DOCM files that came back in the search did get published.

When we migrated our documents a few years ago the Number field matched the File Name and the file extension is included in the Number field.  But anything since then doesn't get the file extension added in the Number field.

Best answer by MikeLockwood

May be able to show filename in search results.  I recall that getting the file name using query builder reports was just a bit tricky.  Have to go thru Application Data and Maybe Stream Data.  Can easily get the vaulted filename (hashed) thru FVITEM but that doesn't help for current need.

4 replies

M
MikeLockwood22-Sapphire IAnswer
22-Sapphire I
June 30, 2016

May be able to show filename in search results.  I recall that getting the file name using query builder reports was just a bit tricky.  Have to go thru Application Data and Maybe Stream Data.  Can easily get the vaulted filename (hashed) thru FVITEM but that doesn't help for current need.

MarcoTosin
MarcoTosin21-Topaz I
21-Topaz I
July 1, 2016

You could start from this report by Dax Williams.

WTDocuments with File Size

https://www.ptcusercommunity.com/servlet/JiveServlet/download/8686-1-111787/ADMINISTRATIVE_WTDocuments%20with%20File%20Size.qml.zip

Marco
B
BineshKumar11-Visitor
1-Visitor
July 1, 2016

We can run query like this select filename from applicationdata where filename like '%.docm%';. Link application data to wtdocument using holdertocontent.

Regards

Binesh Kumar

M
MikeLockwood22-Sapphire I
22-Sapphire I
July 1, 2016

This is helpful.  A bit of a tangent, but I'm finding we have a long history of users renaming and leaving the extension (e.g. .PRT) off of the Number field.  I'd like to find all of these, and so thinking of a simple query builder report for EPM Doc joined to applicationdata with the criteria that EPMDoc.Number <> applicationdata.filename.  Would this work?  I haven't been able to quite get it working yet.

thanks in advance

screen capture

QB report.png

the database just lists the placeholder - different for CAD vs. WT Docs

applicationdata filename.png

R
RandyJones20-Turquoise
20-Turquoise
July 1, 2016

Mike Lockwood wrote:

This is helpful.  A bit of a tangent, but I'm finding we have a long history of users renaming and leaving the extension (e.g. .PRT) off of the Number field.  I'd like to find all of these, and so thinking of a simple query builder report for EPM Doc joined to applicationdata with the criteria that EPMDoc.Number <> applicationdata.filename.  Would this work?  I haven't been able to quite get it working yet.

.

.

.

You could also run a simple database query:

select cadname,documentnumber from epmdocumentmaster where UPPER(cadname) != documentnumber;

S
STEVEG21-Topaz IAuthor
21-Topaz I
July 5, 2016

Mike,

You showed the Application Data is what gives the File Name.  That is great so thank you.  However, the actual thumbnail files are also listed.  Any idea how to get rid of them from the reports?

For example I have these 3 files listed:

123.xlsx

123_xlsx.jpg

123_xlsx_small.jpg

MarcoTosin
MarcoTosin21-Topaz I
21-Topaz I
July 5, 2016

Steve,

in criteria use Not instead of = or Like.

This way you can exclude jpg or other file extensions from results.

For example we have a document with State like color (White, Black, Red, Blue)

Using Not criteria we report only for some state, not for all.

Marco
S
STEVEG21-Topaz IAuthor
21-Topaz I
July 5, 2016

But we have many jpg files in our system that are legitimate so that won't work.

I've been trying to figure out if it is a different type than the document but they come up as the same.

Terms & ConditionsAccessibility statement