Require Content Id value in attachments SQL
Hi Team,
Could you please include the Content ID with it's NAME in the attachments SQL and provide the updated SQL.=
https://community.archerirm.com/hc/en-us/articles/52079824023571-Attachments-SQL-script-to-get-a-list-of-attachments-and-details
Archer Version: 2025.12, Build: 6.15.01001.10164
Thanks
-
Official comment
The SQL in that article already includes the content ID. By Content Name, I assume you are referring to the value of the key field for that content record? If so, the level of effort required to pull that together is quite high. I did update the original query to include some additional join filters in case you have multiple languages installed in your instance. It will eliminate some of the noise associated with all the translations.
-- Get Attachment information.
SELECT
l.module_id,
mt.module_name,
l.level_id,
lt.level_name,
rl.content_id AS ContentID,
fd.field_id,
ft.field_name,
rf.file_id,
REVERSE(STUFF(REVERSE(rf.file_name),CHARINDEX('.',REVERSE(rf.file_name))+1,LEN(REVERSE(rf.file_name)),REVERSE(rf.file_id))) AS Archer_FileName,
rf.file_name AS Actual_FileName,
rf.file_length / 1024 AS SizeKB,
rf.download_cnt
FROM
tblXIVContentRepositoryLinks(NOLOCK) rl
JOIN tblRepositoryFile(NOLOCK) rf ON rl.file_id = rf.file_id
JOIN tblIVFieldDef(NOLOCK) fd ON fd.field_id = rl.field_id
JOIN tblFieldTranslation(NOLOCK) ft ON ft.field_id = fd.field_id and ft.is_default = 1
JOIN tblLevel(NOLOCK) l ON l.level_id = fd.level_id
JOIN tblModule(NOLOCK) m ON m.module_id = l.module_id
JOIN tblLevelTranslation LT(NOLOCK) ON L.level_id = LT.level_id and lt.is_default = 1
JOIN tblModuleTranslation MT(NOLOCK) ON MT.module_id = m.module_id and mt.is_default = 1
WHERE
ft.language_id = 1
Please sign in to leave a comment.
Comments
1 comment