File Name of a document from Query Builder

[STEVEG]

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.

[MikeLockwood · Accepted solution]

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.

[Marco_Tosin]

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

[BineshKumar1]

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

Regards

Binesh Kumar

[MikeLockwood]

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

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

[RandyJones]

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;

[MikeLockwood]

Thank you!  Works perfectly.

Just learned that we have an unbelievable 5927 of these...

[STEVEG]

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

[Marco_Tosin]

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.

[STEVEG]

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.

[MikeLockwood]

What I've found to be an effective way to eliminate all the viewable / thumbnail files is to select Application Data Role = Primary

[STEVEG]

Fantastic.  Thank you Mike!

[Marco_Tosin]

Steve,

have you been able to create your report in query builder or did you use an SQL query?

[STEVEG]

I was able to get it to work in query builder.

[Marco_Tosin]

If it's possible, could you share it?

Then I'll add to my document with all other reports

Thanks

[STEVEG]

Sure.  I couldn't have gotten this without Mike, Dax, and your help.

One thing is about this I don't have the Holder To Content added in the From tab.  It didn't seem like it needed it.  But I did use that on the Join tab.

I would also ask that you try it to make sure I have it right.

[STEVEG]

One other point is that this uses the Sub-select so it will have to be added with the site admin.