Jump to content

Tags not appearing in the documents table


samwoo

Recommended Posts

Hello,

I have created a report using the h_lib_documents table. I have selected the necessary columns, including the tags:

post-12454-0-31235200-1462534133.png

I changed the order of the tags column, then had look at the Data Preview... i can see that many Documents do not have any tags (some do). I then go to the actual document on the front end, and can visibly see tags on it.

See the highlighted document with no tags:

post-12454-0-36030000-1462534426.png

The document highlighted above does actually have tags:

(link)/docmanager/document/details/DOC20150900000004

post-12454-0-93887900-1462534548.png

Can someone advise on what's going on?

We need to report on these, as we are unsure what documents have tags or not. Originally we though most of them don't have tags, but because the report doesn't work that is probably untrue.

Thanks,

Samuel.

Link to comment
Share on other sites

Hi Samuel

You are looking in the wrong table the following Query will get you document and there tags

SELECT h_lib_documents.h_doc_id,h_lib_documents.h_title,h_tags.h_name FROM h_lib_documents


LEFT JOIN h_tag_links
ON h_tag_links.h_object_ref_urn = concat("urn:lib:document:",h_lib_documents.h_doc_id)

LEFT JOIN h_tags
ON h_tags.h_tag_id = h_tag_links.h_tag_id

post-8238-0-43136500-1462541475.png

Kind regards

Trevor Killick

Link to comment
Share on other sites

Hi Trevor,

Wonderful thank you very much for your response. Your code was good, however it brought tags per document per line.

I went a bit further to group the tags per document into a single cell:

SELECT  d.h_doc_id,
           	d.h_title,
           	group_concat(ta.h_name  SEPARATOR ', ') tags
FROM 	h_lib_documents d

LEFT JOIN h_tag_links tl
ON     	tl.h_object_ref_urn = concat("urn:lib:document:",d.h_doc_id)

LEFT JOIN h_tags ta
ON      	ta.h_tag_id = tl.h_tag_id
group by d.h_doc_id

Thanks again :D

Samuel

  • Like 1
Link to comment
Share on other sites

Hello,

Again i would like to thank you for your script. I have expanded my original script even further in case anyone wants to use it in Database Direct.

SELECT  d.h_doc_id Ref,
          	( SELECT a.h_name FROM h_sys_accounts a WHERE a.h_user_id = REPLACE(d.h_owner, 'urn:sys:user:','') ) Owner,
          	d.h_title Title,
          	d.h_description Description,
          	d.h_status Status,
          	d.h_version Version,
          	DATE_FORMAT(d.h_created_on,'%d-%M-%Y') CreatedOn,
          	DATE_FORMAT(d.h_updated_on,'%d-%M-%Y')  UpdatedOn,
          	( SELECT a.h_name FROM h_sys_accounts a WHERE a.h_user_id = REPLACE(d.h_updated_by, 'urn:sys:user:','') ) UpdatedBy,
          	DATE_FORMAT(d.h_retired_on,'%d-%M-%Y')  RetiredOn,
          	DATE_FORMAT(d.h_review_date,'%d-%M-%Y') ReviewDate,
          	group_concat(ta.h_name  SEPARATOR ', ') Tags
FROM     h_lib_documents d

LEFT JOIN   h_tag_links tl
ON                tl.h_object_ref_urn = concat("urn:lib:document:",d.h_doc_id)

LEFT JOIN   h_tags ta
ON               ta.h_tag_id = tl.h_tag_id

GROUP BY   d.h_doc_id
ORDER BY   d.h_doc_id asc

Link to comment
Share on other sites

  • 2 weeks later...

Hello,

It seems in the new Admin UI my code above does not pull back the group_concat Tags column. Can someone have a look and advise?

Will there be a possibility to create a report such as the one above in the "Reports" area rather than Database Direct? It makes more sense to the users who just want to report on certain things in Hornbill in the reports area, yet there is nowhere to write SQL.

Thanks,

Samuel

Link to comment
Share on other sites

Hello,

It seems in the new Admin UI my code above does not pull back the group_concat Tags column. Can someone have a look and advise?

Will there be a possibility to create a report such as the one above in the "Reports" area rather than Database Direct? It makes more sense to the users who just want to report on certain things in Hornbill in the reports area, yet there is nowhere to write SQL.

Thanks,

Samuel

Hi,

Just to add (as i cannot edit my previous post), i actually can see the tags column when running the query (I forgot to scroll far to the right), but when i export the results of the Database Direct code above into a CSV file, the tags column doesn't come out.

Thanks,

Samuel

Link to comment
Share on other sites

Hi Samuel

Apologies for the delayed response, i have found the issue with the CSV export and it will be resolved in the next update to the Admin Tool early next week.

Kind Regards

Trevor Killick

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...