Need URLs for file attachments for training system – question on URL from webclient
I’m trying to create a list of documents where training is required and include a url that our training system could use to get the user to the files tab of the folder for attachments for those training documents.
I’m looking at some examples from webclient and trying to reconcile with what I see in the agile vault.
For instance there is a document DOC0000877 that has a pdf attachment in FOLDER104832.
When I go to that tab in the webclient and use Actions>copy URL to clipboard I get this url:
What I noticed is that the only number that changes in that url when I get it from various attachments is the ‘7830012’ so I assumed that was the file id that agile is storing the file in the vault as ‘agile7830012’. But it isn’t. I don’t have that fileid in the agile vault – and what I found by looking for the file just by date is that actually the file id for that file in the vault is ‘agile7829965’. A different number.
So I’m wondering if there is a file in the db that links this id showing in the url to the actual agile file so that I can get a list of those from the agile db using SQL so I can create the urls I need for all the training documents. Or if the URL a user can use to get to the file attachment is already stored in Agile. I saw the ‘content_url’ but according to another post that is actually when the files is indexed and we don’t do indexing.
I suspect that “7830012” is the ID of the document object under which the file resides. That or the folder ID. Run the following query to confirm :
select am.attach_id, a.attachment_number, am.parent_id, i.item_number, am.file_id, f.filename
from attachment_map am, attachment a, item i, files f
where am.file_id = 7829965 and am.attach_id = a.id and am.parent_id = i.id and am.file_id=f.id order by f.id;
Note that the URL calls out “tabid%3D2“, which basically means to display tab 2 for the object. For the folders (class=6159), that happens to be the Files tab. So it looks like the URL what you want.
If you need to build your own URLs and need to get the correct ID values, modify the above query to specify the file name from the FILES table, use whatever file ID is latest (there will mostly likely be multiples of the file name) and you should be good.
Thank you so much! That was perfect – yes the id in the url was the folder id and your query connected the dots perfectly – I had missed the attachment_map table.
I’m piecing things together bit by bit. I used to play in sql many years ago and I’m having to reboot my brain after all these years.
My end goal is to get a list of all the documents where ‘training required’ = yes. From those documents I need
Item released date
Last change order that released document
From attachments tab of that document I need
I’ll use that folder ID to create that URL that the training system will use. (the one I sent previously). I figure I can use the concat function for that just replacing the folder id in the long url string.
I know that training required field is in PAGE_TWO LIST20 and YES answer is 3496296
So far I’ve been able to find most of the files and data I need. My issue is with trying to join them in the right order and I only want the lastest rev of the item and the attachment in the folder that goes with it. The query I’ve been playing with below doesn’t have attachments in it yet (the part you solved!), but I’ve got the other fields I need except I’m getting all the Revs instead of just the last one cause I’m not sure how to do that.
Can you give me any hints?
substr(item.description,1,100) “Document Description”,
substr(rev.rev_number,1,10) “Document Rev”,
rev.release_date “Released Date”,
(select change_number from change where item.latest_released_eco = change.id and rev.change = change.id) “latest_eco_change_number”from item
join page_two on item.id=page_two.id join rev on item.id=rev.item join change on rev.change=change.id
where page two.list20 = 3496296 and item.class=9000
order by item.item_number;
Attached is the output for just a couple of documents.
Thanks for your help. It is very much appreciated.