IBM Support

Query to show if record has an attachment or not (DOCLINKS)

Technical Blog Post


Abstract

Query to show if record has an attachment or not (DOCLINKS)

Body

Is there a query that I can run on my SB to show if an object has an attachment or not in Maximo ?

 

YES !   Here is how you can check the doclink table to see what references are.

 
select wonum,description,workorderid from workorder where workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 
 
Repeat for the other applications if you would like to see its behavior.
 
PM 
select pmnum,description,pmid from pm where pmid in (select ownerid from doclinks where ownertable='PM') 
 
Assets
select assetnum,description,assetid from asset where assetid in (select ownerid from doclinks where ownertable='ASSET') 
 
etc...locations...etc... 
 
This will show the records which have a document attached, you then check the docinfo to see the document info and check if this document exists. 
 

For missing doclinks info on workorders, normally we query the following way, for example :

1. Find the workorderid for the wonum=1000 
select wonum,workorderid from workorder where wonum='1000' 
 
result: 
WONUM    WORKORDERID
1000                           37 
 
2.  Check the doclink table  (in this case, there are three results). 
 
select document,ownertable,ownerid,doclinksid,docinfoid from doclinks where ownertable='WORKORDER' and ownerid='37' 
 
result: 
1015 WORKORDER 37 51 24 
1016 WORKORDER 37 42 25 
1017 WORKORDER 37 43 26 

 
3. Check the docinfo table 
 
select document,urlname,docinfoid from docinfo where docinfoid in (select docinfoid from doclinks where ownertable='WORKORDER' and ownerid='37') 
 
result: 
 
1015 \DOCLINKS\AAA.DOC 24 
1016 \DOCLINKS\BBB.DOC 25 
1017 \DOCLINKS\CCC.DOC 26 


NOTE: Check the doclink table for the ownerid. Check that the docinfo table has a record for this doclink. 


Other suggestions 
 
Attempt to attach a new document and see if this is listed. If you can attach a new document and it shows then the problem isn't with  attach documents setup, it may have to do with your historical doclinks documents attached in the past. 
 
select * from doclinks,docinfo where doclinks.docinfoid = docinfo.docinfoid order by ownertable

 
The ownerid value is used for this mapping from the doclinks table. You would look up the ownertable to find asset records (for example), then use the 
ownerid values in the result set to look up the ASSET table using the assetid column in order to find the assetnum. 
 
e.g. 
SELECT * FROM doclinks where ownertable='ASSET' 
 

Then, run this to find the asset numbers: 
 
SELECT * FROM ASSET WHERE ASSETID='26'       (or any other record ID value)
 
This info should help you get your data.

 

 

See ya

[{"Business Unit":{"code":"BU005","label":"IoT"}, "Product":{"code":"SSLKT6","label":"Maximo Asset Management"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11112727