Tags not appearing in the documents table

[samwoo]

Hello,

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

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:

The document highlighted above does actually have tags:

(link)/docmanager/document/details/DOC20150900000004

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.

[TrevorKillick]

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

Kind regards

Trevor Killick

[samwoo]

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

Samuel

[samwoo]

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

[samwoo]

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

[samwoo]

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

[TrevorKillick]

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

[samwoo]

Hi Trevor,

Thank you again for your response and support.

Samuel

[TrevorKillick]

Hi Samuel

This fix is now available on Live

Kind Regards

Trevor Killick