Maximo Open Forum

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

Sorry to hijack this thread but I'm looking for the exact opposite. Trying to generate a list of Workorders that do not have a document attached in the document column on the doclinks table.

I've modified it to the best of the ability for my situation, but getting no results or a syntax error message.

((status = 'CAN' or status = 'CLOSE' or status = 'COMP')  and ((upper(description) like '%A LEVEL%'))AND EXISTS (SELECT 1FROM workorderWHERE WONUM IN (SELECT 1 FROM doclinks WHERE ownertable = 'WORKORDER')  AND DOCUMENT IS NULL)

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 11-20-2023 10:13
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

The following would simply find Work Orders that do not have a direct attachment (that is to say, I'm not considering inherited attachments).  Looks like you have some further filtering (status, description, etc.) to then add to this:

SELECT * FROM workorder WHERE workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Sorry to hijack this thread but I'm looking for the exact opposite. Trying to generate a list of Workorders that do not have a document attached in the document column on the doclinks table.

I've modified it to the best of the ability for my situation, but getting no results or a syntax error message.

((status = 'CAN' or status = 'CLOSE' or status = 'COMP')  and ((upper(description) like '%A LEVEL%'))AND EXISTS (SELECT 1FROM workorderWHERE WONUM IN (SELECT 1 FROM doclinks WHERE ownertable = 'WORKORDER')  AND DOCUMENT IS NULL)

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 11-20-2023 10:13
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

Thanks Travis! Though I'm not sure the "workorderid" is valid for our application of Maximo, but I definitely could be wrong. Also, the further filtering for status and description you mention is performed earlier in the query, could that be causing my errors/lack of results?

Here is another variant I tried but I'm a novice at this so I may be way off.

(((status = 'CAN' or status = 'CLOSE' or status = 'COMP') and ((upper(description) like '%A LEVEL%')) and exists (  select 1     -Also tried select * and select document-  from doclinks  where document is null))

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 04-09-2024 10:31
From: Travis Herron
Subject: Generate a list of workorders that contain attachments directly in Maximo

The following would simply find Work Orders that do not have a direct attachment (that is to say, I'm not considering inherited attachments).  Looks like you have some further filtering (status, description, etc.) to then add to this:

SELECT * FROM workorder WHERE workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Sorry to hijack this thread but I'm looking for the exact opposite. Trying to generate a list of Workorders that do not have a document attached in the document column on the doclinks table.

I've modified it to the best of the ability for my situation, but getting no results or a syntax error message.

((status = 'CAN' or status = 'CLOSE' or status = 'COMP')  and ((upper(description) like '%A LEVEL%'))AND EXISTS (SELECT 1FROM workorderWHERE WONUM IN (SELECT 1 FROM doclinks WHERE ownertable = 'WORKORDER')  AND DOCUMENT IS NULL)

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 11-20-2023 10:13
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

The workorderid is the true GUID field on the WORKORDER table.  Generally we like to think of WONUM, but that is only unique to the Org, I believe.  If you have multiple Orgs in your Maximo implementation, there could be multiple Work Orders with the same WONUM, but each one pertaining to a different Org.

Anyways, try this:

(status = 'CAN' or status = 'CLOSE' or status = 'COMP') and upper(description) like '%A LEVEL%' and workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Thanks Travis! Though I'm not sure the "workorderid" is valid for our application of Maximo, but I definitely could be wrong. Also, the further filtering for status and description you mention is performed earlier in the query, could that be causing my errors/lack of results?

Here is another variant I tried but I'm a novice at this so I may be way off.

(((status = 'CAN' or status = 'CLOSE' or status = 'COMP') and ((upper(description) like '%A LEVEL%')) and exists (  select 1     -Also tried select * and select document-  from doclinks  where document is null))

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 04-09-2024 10:31
From: Travis Herron
Subject: Generate a list of workorders that contain attachments directly in Maximo

The following would simply find Work Orders that do not have a direct attachment (that is to say, I'm not considering inherited attachments).  Looks like you have some further filtering (status, description, etc.) to then add to this:

SELECT * FROM workorder WHERE workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Sorry to hijack this thread but I'm looking for the exact opposite. Trying to generate a list of Workorders that do not have a document attached in the document column on the doclinks table.

I've modified it to the best of the ability for my situation, but getting no results or a syntax error message.

((status = 'CAN' or status = 'CLOSE' or status = 'COMP')  and ((upper(description) like '%A LEVEL%'))AND EXISTS (SELECT 1FROM workorderWHERE WONUM IN (SELECT 1 FROM doclinks WHERE ownertable = 'WORKORDER')  AND DOCUMENT IS NULL)

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 11-20-2023 10:13
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

Fantastic! That did it!

Just out of curiosity, why doesn't my "exists" clause that looks for NULL document column records in the doclinks table not work?

I've used an almost identical where clause in another screen with great success.

The workorderid is the true GUID field on the WORKORDER table.  Generally we like to think of WONUM, but that is only unique to the Org, I believe.  If you have multiple Orgs in your Maximo implementation, there could be multiple Work Orders with the same WONUM, but each one pertaining to a different Org.

Anyways, try this:

(status = 'CAN' or status = 'CLOSE' or status = 'COMP') and upper(description) like '%A LEVEL%' and workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Thanks Travis! Though I'm not sure the "workorderid" is valid for our application of Maximo, but I definitely could be wrong. Also, the further filtering for status and description you mention is performed earlier in the query, could that be causing my errors/lack of results?

Here is another variant I tried but I'm a novice at this so I may be way off.

(((status = 'CAN' or status = 'CLOSE' or status = 'COMP') and ((upper(description) like '%A LEVEL%')) and exists (  select 1     -Also tried select * and select document-  from doclinks  where document is null))

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 04-09-2024 10:31
From: Travis Herron
Subject: Generate a list of workorders that contain attachments directly in Maximo

The following would simply find Work Orders that do not have a direct attachment (that is to say, I'm not considering inherited attachments).  Looks like you have some further filtering (status, description, etc.) to then add to this:

SELECT * FROM workorder WHERE workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Sorry to hijack this thread but I'm looking for the exact opposite. Trying to generate a list of Workorders that do not have a document attached in the document column on the doclinks table.

I've modified it to the best of the ability for my situation, but getting no results or a syntax error message.

((status = 'CAN' or status = 'CLOSE' or status = 'COMP')  and ((upper(description) like '%A LEVEL%'))AND EXISTS (SELECT 1FROM workorderWHERE WONUM IN (SELECT 1 FROM doclinks WHERE ownertable = 'WORKORDER')  AND DOCUMENT IS NULL)

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 11-20-2023 10:13
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

At least in my environment, with 106,000 entries in the doclinks table. . .the DOCUMENT column is never null.  Even if the user doesn't provide a value, Maximo should dynamically populate that field.

Fantastic! That did it!

Just out of curiosity, why doesn't my "exists" clause that looks for NULL document column records in the doclinks table not work?

I've used an almost identical where clause in another screen with great success.

The workorderid is the true GUID field on the WORKORDER table.  Generally we like to think of WONUM, but that is only unique to the Org, I believe.  If you have multiple Orgs in your Maximo implementation, there could be multiple Work Orders with the same WONUM, but each one pertaining to a different Org.

Anyways, try this:

(status = 'CAN' or status = 'CLOSE' or status = 'COMP') and upper(description) like '%A LEVEL%' and workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Thanks Travis! Though I'm not sure the "workorderid" is valid for our application of Maximo, but I definitely could be wrong. Also, the further filtering for status and description you mention is performed earlier in the query, could that be causing my errors/lack of results?

Here is another variant I tried but I'm a novice at this so I may be way off.

(((status = 'CAN' or status = 'CLOSE' or status = 'COMP') and ((upper(description) like '%A LEVEL%')) and exists (  select 1     -Also tried select * and select document-  from doclinks  where document is null))

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 04-09-2024 10:31
From: Travis Herron
Subject: Generate a list of workorders that contain attachments directly in Maximo

The following would simply find Work Orders that do not have a direct attachment (that is to say, I'm not considering inherited attachments).  Looks like you have some further filtering (status, description, etc.) to then add to this:

SELECT * FROM workorder WHERE workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Sorry to hijack this thread but I'm looking for the exact opposite. Trying to generate a list of Workorders that do not have a document attached in the document column on the doclinks table.

I've modified it to the best of the ability for my situation, but getting no results or a syntax error message.

((status = 'CAN' or status = 'CLOSE' or status = 'COMP')  and ((upper(description) like '%A LEVEL%'))AND EXISTS (SELECT 1FROM workorderWHERE WONUM IN (SELECT 1 FROM doclinks WHERE ownertable = 'WORKORDER')  AND DOCUMENT IS NULL)

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 11-20-2023 10:13
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

At least in my environment, with 106,000 entries in the doclinks table. . .the DOCUMENT column is never null.  Even if the user doesn't provide a value, Maximo should dynamically populate that field.

Fantastic! That did it!

Just out of curiosity, why doesn't my "exists" clause that looks for NULL document column records in the doclinks table not work?

I've used an almost identical where clause in another screen with great success.

The workorderid is the true GUID field on the WORKORDER table.  Generally we like to think of WONUM, but that is only unique to the Org, I believe.  If you have multiple Orgs in your Maximo implementation, there could be multiple Work Orders with the same WONUM, but each one pertaining to a different Org.

Anyways, try this:

(status = 'CAN' or status = 'CLOSE' or status = 'COMP') and upper(description) like '%A LEVEL%' and workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Thanks Travis! Though I'm not sure the "workorderid" is valid for our application of Maximo, but I definitely could be wrong. Also, the further filtering for status and description you mention is performed earlier in the query, could that be causing my errors/lack of results?

Here is another variant I tried but I'm a novice at this so I may be way off.

(((status = 'CAN' or status = 'CLOSE' or status = 'COMP') and ((upper(description) like '%A LEVEL%')) and exists (  select 1     -Also tried select * and select document-  from doclinks  where document is null))

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 04-09-2024 10:31
From: Travis Herron
Subject: Generate a list of workorders that contain attachments directly in Maximo

The following would simply find Work Orders that do not have a direct attachment (that is to say, I'm not considering inherited attachments).  Looks like you have some further filtering (status, description, etc.) to then add to this:

SELECT * FROM workorder WHERE workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Sorry to hijack this thread but I'm looking for the exact opposite. Trying to generate a list of Workorders that do not have a document attached in the document column on the doclinks table.

I've modified it to the best of the ability for my situation, but getting no results or a syntax error message.

((status = 'CAN' or status = 'CLOSE' or status = 'COMP')  and ((upper(description) like '%A LEVEL%'))AND EXISTS (SELECT 1FROM workorderWHERE WONUM IN (SELECT 1 FROM doclinks WHERE ownertable = 'WORKORDER')  AND DOCUMENT IS NULL)

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 11-20-2023 10:13
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

(I tend not to instinctively use EXISTS, though it's probably more efficient. . .)

If you want to write the same thing using EXISTS, it'd be like this:

(status = 'CAN' or status = 'CLOSE' or status = 'COMP') and upper(description) like '%A LEVEL%' and not exists (select 1 from doclinks where ownertable = 'WORKORDER' and workorderid = ownerid)

At least in my environment, with 106,000 entries in the doclinks table. . .the DOCUMENT column is never null.  Even if the user doesn't provide a value, Maximo should dynamically populate that field.

Fantastic! That did it!

Just out of curiosity, why doesn't my "exists" clause that looks for NULL document column records in the doclinks table not work?

I've used an almost identical where clause in another screen with great success.

The workorderid is the true GUID field on the WORKORDER table.  Generally we like to think of WONUM, but that is only unique to the Org, I believe.  If you have multiple Orgs in your Maximo implementation, there could be multiple Work Orders with the same WONUM, but each one pertaining to a different Org.

Anyways, try this:

(status = 'CAN' or status = 'CLOSE' or status = 'COMP') and upper(description) like '%A LEVEL%' and workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Thanks Travis! Though I'm not sure the "workorderid" is valid for our application of Maximo, but I definitely could be wrong. Also, the further filtering for status and description you mention is performed earlier in the query, could that be causing my errors/lack of results?

Here is another variant I tried but I'm a novice at this so I may be way off.

(((status = 'CAN' or status = 'CLOSE' or status = 'COMP') and ((upper(description) like '%A LEVEL%')) and exists (  select 1     -Also tried select * and select document-  from doclinks  where document is null))

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 04-09-2024 10:31
From: Travis Herron
Subject: Generate a list of workorders that contain attachments directly in Maximo

The following would simply find Work Orders that do not have a direct attachment (that is to say, I'm not considering inherited attachments).  Looks like you have some further filtering (status, description, etc.) to then add to this:

SELECT * FROM workorder WHERE workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Sorry to hijack this thread but I'm looking for the exact opposite. Trying to generate a list of Workorders that do not have a document attached in the document column on the doclinks table.

I've modified it to the best of the ability for my situation, but getting no results or a syntax error message.

((status = 'CAN' or status = 'CLOSE' or status = 'COMP')  and ((upper(description) like '%A LEVEL%'))AND EXISTS (SELECT 1FROM workorderWHERE WONUM IN (SELECT 1 FROM doclinks WHERE ownertable = 'WORKORDER')  AND DOCUMENT IS NULL)

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 11-20-2023 10:13
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

OK understood. Now using that same where clause (admittedly off topic of Workorders), trying to find count books with no attachment.

Maximo accepts the query, but populates a list (smaller than all records) but some contain documents and some don't.

((status = 'COMP' and siteid = 'FXFRTSID') and countbooknum not in (select ownerid from doclinks where ownertable = 'PLUSTCB'))

(I tend not to instinctively use EXISTS, though it's probably more efficient. . .)

If you want to write the same thing using EXISTS, it'd be like this:

(status = 'CAN' or status = 'CLOSE' or status = 'COMP') and upper(description) like '%A LEVEL%' and not exists (select 1 from doclinks where ownertable = 'WORKORDER' and workorderid = ownerid)

At least in my environment, with 106,000 entries in the doclinks table. . .the DOCUMENT column is never null.  Even if the user doesn't provide a value, Maximo should dynamically populate that field.

Fantastic! That did it!

Just out of curiosity, why doesn't my "exists" clause that looks for NULL document column records in the doclinks table not work?

I've used an almost identical where clause in another screen with great success.

The workorderid is the true GUID field on the WORKORDER table.  Generally we like to think of WONUM, but that is only unique to the Org, I believe.  If you have multiple Orgs in your Maximo implementation, there could be multiple Work Orders with the same WONUM, but each one pertaining to a different Org.

Anyways, try this:

(status = 'CAN' or status = 'CLOSE' or status = 'COMP') and upper(description) like '%A LEVEL%' and workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Thanks Travis! Though I'm not sure the "workorderid" is valid for our application of Maximo, but I definitely could be wrong. Also, the further filtering for status and description you mention is performed earlier in the query, could that be causing my errors/lack of results?

Here is another variant I tried but I'm a novice at this so I may be way off.

(((status = 'CAN' or status = 'CLOSE' or status = 'COMP') and ((upper(description) like '%A LEVEL%')) and exists (  select 1     -Also tried select * and select document-  from doclinks  where document is null))

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 04-09-2024 10:31
From: Travis Herron
Subject: Generate a list of workorders that contain attachments directly in Maximo

The following would simply find Work Orders that do not have a direct attachment (that is to say, I'm not considering inherited attachments).  Looks like you have some further filtering (status, description, etc.) to then add to this:

SELECT * FROM workorder WHERE workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Sorry to hijack this thread but I'm looking for the exact opposite. Trying to generate a list of Workorders that do not have a document attached in the document column on the doclinks table.

I've modified it to the best of the ability for my situation, but getting no results or a syntax error message.

((status = 'CAN' or status = 'CLOSE' or status = 'COMP')  and ((upper(description) like '%A LEVEL%'))AND EXISTS (SELECT 1FROM workorderWHERE WONUM IN (SELECT 1 FROM doclinks WHERE ownertable = 'WORKORDER')  AND DOCUMENT IS NULL)

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 11-20-2023 10:13
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

OK understood. Now using that same where clause (admittedly off topic of Workorders), trying to find count books with no attachment.

Maximo accepts the query, but populates a list (smaller than all records) but some contain documents and some don't.

((status = 'COMP' and siteid = 'FXFRTSID') and countbooknum not in (select ownerid from doclinks where ownertable = 'PLUSTCB'))

(I tend not to instinctively use EXISTS, though it's probably more efficient. . .)

If you want to write the same thing using EXISTS, it'd be like this:

(status = 'CAN' or status = 'CLOSE' or status = 'COMP') and upper(description) like '%A LEVEL%' and not exists (select 1 from doclinks where ownertable = 'WORKORDER' and workorderid = ownerid)

At least in my environment, with 106,000 entries in the doclinks table. . .the DOCUMENT column is never null.  Even if the user doesn't provide a value, Maximo should dynamically populate that field.

Fantastic! That did it!

Just out of curiosity, why doesn't my "exists" clause that looks for NULL document column records in the doclinks table not work?

I've used an almost identical where clause in another screen with great success.

The workorderid is the true GUID field on the WORKORDER table.  Generally we like to think of WONUM, but that is only unique to the Org, I believe.  If you have multiple Orgs in your Maximo implementation, there could be multiple Work Orders with the same WONUM, but each one pertaining to a different Org.

Anyways, try this:

(status = 'CAN' or status = 'CLOSE' or status = 'COMP') and upper(description) like '%A LEVEL%' and workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Thanks Travis! Though I'm not sure the "workorderid" is valid for our application of Maximo, but I definitely could be wrong. Also, the further filtering for status and description you mention is performed earlier in the query, could that be causing my errors/lack of results?

Here is another variant I tried but I'm a novice at this so I may be way off.

(((status = 'CAN' or status = 'CLOSE' or status = 'COMP') and ((upper(description) like '%A LEVEL%')) and exists (  select 1     -Also tried select * and select document-  from doclinks  where document is null))

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 04-09-2024 10:31
From: Travis Herron
Subject: Generate a list of workorders that contain attachments directly in Maximo

The following would simply find Work Orders that do not have a direct attachment (that is to say, I'm not considering inherited attachments).  Looks like you have some further filtering (status, description, etc.) to then add to this:

SELECT * FROM workorder WHERE workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Sorry to hijack this thread but I'm looking for the exact opposite. Trying to generate a list of Workorders that do not have a document attached in the document column on the doclinks table.

I've modified it to the best of the ability for my situation, but getting no results or a syntax error message.

((status = 'CAN' or status = 'CLOSE' or status = 'COMP')  and ((upper(description) like '%A LEVEL%'))AND EXISTS (SELECT 1FROM workorderWHERE WONUM IN (SELECT 1 FROM doclinks WHERE ownertable = 'WORKORDER')  AND DOCUMENT IS NULL)

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 11-20-2023 10:13
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

Hi all,

There is no need to guess.  Go into the underlying object to the application (go into Application Designer to find that out), and the OWNERTABLE=(The OBJECT value) and the OWNERID = (The Unique Column).  You cannot guarantee that UID for the object uses this format <OBJECT> + 'UID'.  It should but doesn't and this is why until you know, you should do look it up.

OK understood. Now using that same where clause (admittedly off topic of Workorders), trying to find count books with no attachment.

Maximo accepts the query, but populates a list (smaller than all records) but some contain documents and some don't.

((status = 'COMP' and siteid = 'FXFRTSID') and countbooknum not in (select ownerid from doclinks where ownertable = 'PLUSTCB'))

(I tend not to instinctively use EXISTS, though it's probably more efficient. . .)

If you want to write the same thing using EXISTS, it'd be like this:

(status = 'CAN' or status = 'CLOSE' or status = 'COMP') and upper(description) like '%A LEVEL%' and not exists (select 1 from doclinks where ownertable = 'WORKORDER' and workorderid = ownerid)

At least in my environment, with 106,000 entries in the doclinks table. . .the DOCUMENT column is never null.  Even if the user doesn't provide a value, Maximo should dynamically populate that field.

Fantastic! That did it!

Just out of curiosity, why doesn't my "exists" clause that looks for NULL document column records in the doclinks table not work?

I've used an almost identical where clause in another screen with great success.

The workorderid is the true GUID field on the WORKORDER table.  Generally we like to think of WONUM, but that is only unique to the Org, I believe.  If you have multiple Orgs in your Maximo implementation, there could be multiple Work Orders with the same WONUM, but each one pertaining to a different Org.

Anyways, try this:

(status = 'CAN' or status = 'CLOSE' or status = 'COMP') and upper(description) like '%A LEVEL%' and workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Thanks Travis! Though I'm not sure the "workorderid" is valid for our application of Maximo, but I definitely could be wrong. Also, the further filtering for status and description you mention is performed earlier in the query, could that be causing my errors/lack of results?

Here is another variant I tried but I'm a novice at this so I may be way off.

(((status = 'CAN' or status = 'CLOSE' or status = 'COMP') and ((upper(description) like '%A LEVEL%')) and exists (  select 1     -Also tried select * and select document-  from doclinks  where document is null))

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 04-09-2024 10:31
From: Travis Herron
Subject: Generate a list of workorders that contain attachments directly in Maximo

The following would simply find Work Orders that do not have a direct attachment (that is to say, I'm not considering inherited attachments).  Looks like you have some further filtering (status, description, etc.) to then add to this:

SELECT * FROM workorder WHERE workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Sorry to hijack this thread but I'm looking for the exact opposite. Trying to generate a list of Workorders that do not have a document attached in the document column on the doclinks table.

I've modified it to the best of the ability for my situation, but getting no results or a syntax error message.

((status = 'CAN' or status = 'CLOSE' or status = 'COMP')  and ((upper(description) like '%A LEVEL%'))AND EXISTS (SELECT 1FROM workorderWHERE WONUM IN (SELECT 1 FROM doclinks WHERE ownertable = 'WORKORDER')  AND DOCUMENT IS NULL)

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 11-20-2023 10:13
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

Hi Craig, Unfortunately I don't have access to the Application Designer as I am just an end user of Maximo. I am able to see the OWNERTABLE = PLUSTCB, but I can't see the Unique Column name, and using COUNTBOOKID gave me an error message.

Hi all,

There is no need to guess.  Go into the underlying object to the application (go into Application Designer to find that out), and the OWNERTABLE=(The OBJECT value) and the OWNERID = (The Unique Column).  You cannot guarantee that UID for the object uses this format <OBJECT> + 'UID'.  It should but doesn't and this is why until you know, you should do look it up.

OK understood. Now using that same where clause (admittedly off topic of Workorders), trying to find count books with no attachment.

Maximo accepts the query, but populates a list (smaller than all records) but some contain documents and some don't.

((status = 'COMP' and siteid = 'FXFRTSID') and countbooknum not in (select ownerid from doclinks where ownertable = 'PLUSTCB'))

(I tend not to instinctively use EXISTS, though it's probably more efficient. . .)

If you want to write the same thing using EXISTS, it'd be like this:

(status = 'CAN' or status = 'CLOSE' or status = 'COMP') and upper(description) like '%A LEVEL%' and not exists (select 1 from doclinks where ownertable = 'WORKORDER' and workorderid = ownerid)

At least in my environment, with 106,000 entries in the doclinks table. . .the DOCUMENT column is never null.  Even if the user doesn't provide a value, Maximo should dynamically populate that field.

Fantastic! That did it!

Just out of curiosity, why doesn't my "exists" clause that looks for NULL document column records in the doclinks table not work?

I've used an almost identical where clause in another screen with great success.

The workorderid is the true GUID field on the WORKORDER table.  Generally we like to think of WONUM, but that is only unique to the Org, I believe.  If you have multiple Orgs in your Maximo implementation, there could be multiple Work Orders with the same WONUM, but each one pertaining to a different Org.

Anyways, try this:

(status = 'CAN' or status = 'CLOSE' or status = 'COMP') and upper(description) like '%A LEVEL%' and workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Thanks Travis! Though I'm not sure the "workorderid" is valid for our application of Maximo, but I definitely could be wrong. Also, the further filtering for status and description you mention is performed earlier in the query, could that be causing my errors/lack of results?

Here is another variant I tried but I'm a novice at this so I may be way off.

(((status = 'CAN' or status = 'CLOSE' or status = 'COMP') and ((upper(description) like '%A LEVEL%')) and exists (  select 1     -Also tried select * and select document-  from doclinks  where document is null))

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 04-09-2024 10:31
From: Travis Herron
Subject: Generate a list of workorders that contain attachments directly in Maximo

The following would simply find Work Orders that do not have a direct attachment (that is to say, I'm not considering inherited attachments).  Looks like you have some further filtering (status, description, etc.) to then add to this:

SELECT * FROM workorder WHERE workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Sorry to hijack this thread but I'm looking for the exact opposite. Trying to generate a list of Workorders that do not have a document attached in the document column on the doclinks table.

I've modified it to the best of the ability for my situation, but getting no results or a syntax error message.

((status = 'CAN' or status = 'CLOSE' or status = 'COMP')  and ((upper(description) like '%A LEVEL%'))AND EXISTS (SELECT 1FROM workorderWHERE WONUM IN (SELECT 1 FROM doclinks WHERE ownertable = 'WORKORDER')  AND DOCUMENT IS NULL)

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 11-20-2023 10:13
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

(I tend not to instinctively use EXISTS, though it's probably more efficient. . .)

If you want to write the same thing using EXISTS, it'd be like this:

(status = 'CAN' or status = 'CLOSE' or status = 'COMP') and upper(description) like '%A LEVEL%' and not exists (select 1 from doclinks where ownertable = 'WORKORDER' and workorderid = ownerid)

At least in my environment, with 106,000 entries in the doclinks table. . .the DOCUMENT column is never null.  Even if the user doesn't provide a value, Maximo should dynamically populate that field.

Fantastic! That did it!

Just out of curiosity, why doesn't my "exists" clause that looks for NULL document column records in the doclinks table not work?

I've used an almost identical where clause in another screen with great success.

The workorderid is the true GUID field on the WORKORDER table.  Generally we like to think of WONUM, but that is only unique to the Org, I believe.  If you have multiple Orgs in your Maximo implementation, there could be multiple Work Orders with the same WONUM, but each one pertaining to a different Org.

Anyways, try this:

(status = 'CAN' or status = 'CLOSE' or status = 'COMP') and upper(description) like '%A LEVEL%' and workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Thanks Travis! Though I'm not sure the "workorderid" is valid for our application of Maximo, but I definitely could be wrong. Also, the further filtering for status and description you mention is performed earlier in the query, could that be causing my errors/lack of results?

Here is another variant I tried but I'm a novice at this so I may be way off.

(((status = 'CAN' or status = 'CLOSE' or status = 'COMP') and ((upper(description) like '%A LEVEL%')) and exists (  select 1     -Also tried select * and select document-  from doclinks  where document is null))

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 04-09-2024 10:31
From: Travis Herron
Subject: Generate a list of workorders that contain attachments directly in Maximo

The following would simply find Work Orders that do not have a direct attachment (that is to say, I'm not considering inherited attachments).  Looks like you have some further filtering (status, description, etc.) to then add to this:

SELECT * FROM workorder WHERE workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Sorry to hijack this thread but I'm looking for the exact opposite. Trying to generate a list of Workorders that do not have a document attached in the document column on the doclinks table.

I've modified it to the best of the ability for my situation, but getting no results or a syntax error message.

((status = 'CAN' or status = 'CLOSE' or status = 'COMP')  and ((upper(description) like '%A LEVEL%'))AND EXISTS (SELECT 1FROM workorderWHERE WONUM IN (SELECT 1 FROM doclinks WHERE ownertable = 'WORKORDER')  AND DOCUMENT IS NULL)

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 11-20-2023 10:13
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

Fantastic! That did it!

Just out of curiosity, why doesn't my "exists" clause that looks for NULL document column records in the doclinks table not work?

I've used an almost identical where clause in another screen with great success.

The workorderid is the true GUID field on the WORKORDER table.  Generally we like to think of WONUM, but that is only unique to the Org, I believe.  If you have multiple Orgs in your Maximo implementation, there could be multiple Work Orders with the same WONUM, but each one pertaining to a different Org.

Anyways, try this:

(status = 'CAN' or status = 'CLOSE' or status = 'COMP') and upper(description) like '%A LEVEL%' and workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Thanks Travis! Though I'm not sure the "workorderid" is valid for our application of Maximo, but I definitely could be wrong. Also, the further filtering for status and description you mention is performed earlier in the query, could that be causing my errors/lack of results?

Here is another variant I tried but I'm a novice at this so I may be way off.

(((status = 'CAN' or status = 'CLOSE' or status = 'COMP') and ((upper(description) like '%A LEVEL%')) and exists (  select 1     -Also tried select * and select document-  from doclinks  where document is null))

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 04-09-2024 10:31
From: Travis Herron
Subject: Generate a list of workorders that contain attachments directly in Maximo

The following would simply find Work Orders that do not have a direct attachment (that is to say, I'm not considering inherited attachments).  Looks like you have some further filtering (status, description, etc.) to then add to this:

SELECT * FROM workorder WHERE workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Sorry to hijack this thread but I'm looking for the exact opposite. Trying to generate a list of Workorders that do not have a document attached in the document column on the doclinks table.

I've modified it to the best of the ability for my situation, but getting no results or a syntax error message.

((status = 'CAN' or status = 'CLOSE' or status = 'COMP')  and ((upper(description) like '%A LEVEL%'))AND EXISTS (SELECT 1FROM workorderWHERE WONUM IN (SELECT 1 FROM doclinks WHERE ownertable = 'WORKORDER')  AND DOCUMENT IS NULL)

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 11-20-2023 10:13
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

Thanks Travis! Though I'm not sure the "workorderid" is valid for our application of Maximo, but I definitely could be wrong. Also, the further filtering for status and description you mention is performed earlier in the query, could that be causing my errors/lack of results?

Here is another variant I tried but I'm a novice at this so I may be way off.

(((status = 'CAN' or status = 'CLOSE' or status = 'COMP') and ((upper(description) like '%A LEVEL%')) and exists (  select 1     -Also tried select * and select document-  from doclinks  where document is null))

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 04-09-2024 10:31
From: Travis Herron
Subject: Generate a list of workorders that contain attachments directly in Maximo

The following would simply find Work Orders that do not have a direct attachment (that is to say, I'm not considering inherited attachments).  Looks like you have some further filtering (status, description, etc.) to then add to this:

SELECT * FROM workorder WHERE workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Sorry to hijack this thread but I'm looking for the exact opposite. Trying to generate a list of Workorders that do not have a document attached in the document column on the doclinks table.

I've modified it to the best of the ability for my situation, but getting no results or a syntax error message.

((status = 'CAN' or status = 'CLOSE' or status = 'COMP')  and ((upper(description) like '%A LEVEL%'))AND EXISTS (SELECT 1FROM workorderWHERE WONUM IN (SELECT 1 FROM doclinks WHERE ownertable = 'WORKORDER')  AND DOCUMENT IS NULL)

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 11-20-2023 10:13
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

Hi Nathan,

The doclink not give you what you want as there is not relationship established between the WORKORDER and DOCLINK.  Travis is correct.

In the DOCLINK table, the document may come from any number of sources, but the linking ID is the OWNERID field, but should be further qualified with the OWNERTABLE which is that data source from the record.  If the source is SR, the OWNERTABLE = 'SR', if the work order, then it is OWNERTABLE = 'WORKORDER'.  Equally for the SR the OWNERID = 'TICKETID' or for the work order OWNERID = 'WORKORDERID'.  So, I'm not sure why you would say that 'workorderid' is not valid.

Hope that helps.

Thanks Travis! Though I'm not sure the "workorderid" is valid for our application of Maximo, but I definitely could be wrong. Also, the further filtering for status and description you mention is performed earlier in the query, could that be causing my errors/lack of results?

Here is another variant I tried but I'm a novice at this so I may be way off.

(((status = 'CAN' or status = 'CLOSE' or status = 'COMP') and ((upper(description) like '%A LEVEL%')) and exists (  select 1     -Also tried select * and select document-  from doclinks  where document is null))

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 04-09-2024 10:31
From: Travis Herron
Subject: Generate a list of workorders that contain attachments directly in Maximo

The following would simply find Work Orders that do not have a direct attachment (that is to say, I'm not considering inherited attachments).  Looks like you have some further filtering (status, description, etc.) to then add to this:

SELECT * FROM workorder WHERE workorderid NOT IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')

Sorry to hijack this thread but I'm looking for the exact opposite. Trying to generate a list of Workorders that do not have a document attached in the document column on the doclinks table.

I've modified it to the best of the ability for my situation, but getting no results or a syntax error message.

((status = 'CAN' or status = 'CLOSE' or status = 'COMP')  and ((upper(description) like '%A LEVEL%'))AND EXISTS (SELECT 1FROM workorderWHERE WONUM IN (SELECT 1 FROM doclinks WHERE ownertable = 'WORKORDER')  AND DOCUMENT IS NULL)

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 11-20-2023 10:13
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

Sorry to hijack this thread but I'm looking for the exact opposite. Trying to generate a list of Workorders that do not have a document attached in the document column on the doclinks table.

I've modified it to the best of the ability for my situation, but getting no results or a syntax error message.

((status = 'CAN' or status = 'CLOSE' or status = 'COMP')  and ((upper(description) like '%A LEVEL%'))AND EXISTS (SELECT 1FROM workorderWHERE WONUM IN (SELECT 1 FROM doclinks WHERE ownertable = 'WORKORDER')  AND DOCUMENT IS NULL)

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 11-20-2023 10:13
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

Hi Nathan

The query was never going to work because in the exists never had the link back to the work order.  I have rejigged it, though it's still not technically correct.  You have got historyflag = 0, yet you are wanting to include the CAN status.  Whenever a work order goes to CAN, it automatically is tagged as historyflag =1, and so would never be included in your query.  At what stage in the work order lifecycle are you actually looking to run the query against.

(
woclass in ('WORKORDER', 'ACTIVITY') 
and historyflag = 0 
and istask = 0 
and siteid = 'xxxxxxxxxxxxxxx'
and status in ('CAN', 'CLOSE', 'COMP')
and upper(description) like '%A LEVEL%' 
and 
    not EXISTS 
    ( 

              SELECT 1 
        FROM doclinks 
        WHERE ownertable = 'WORKORDER'
        and ownerid = workorder.workorderid
    )
;

Sorry to hijack this thread but I'm looking for the exact opposite. Trying to generate a list of Workorders that do not have a document attached in the document column on the doclinks table.

I've modified it to the best of the ability for my situation, but getting no results or a syntax error message.

((status = 'CAN' or status = 'CLOSE' or status = 'COMP')  and ((upper(description) like '%A LEVEL%'))AND EXISTS (SELECT 1FROM workorderWHERE WONUM IN (SELECT 1 FROM doclinks WHERE ownertable = 'WORKORDER')  AND DOCUMENT IS NULL)

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 11-20-2023 10:13
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

Hi Craig, yeah I ended up realizing I had some conflicting lines in the where clause and have since adjusted it accordingly. The end result I was looking for was all workorders in COMP status, the "A Level" description that do not have an attachment. Travis was able to help me out with that. Though I do have another question regarding filter this further into a date range. Specifically prior week Wednesday 12:00am - Tuesday 11:59pm.

Here's what i've come up with but it's not running all the way to Tuesday 4/9/24 @ 11:59:59pm

It lists all records from 4/3 @ 12:00am through Tuesday-24hours. So, any records completed after 6pm last night lets say, aren't populating.

((status = 'COMP' and upper(description) like '%A LEVEL%' and siteid = 'FXFRTSID' and (worktype = 'PM') and plustreason = '08') and workorderid not in (select ownerid from doclinks where ownertable='WORKORDER') and (statusdate between trunc(sysdate-7) and sysdate-1)) and ((plustrepord = 0))

Hi Nathan

The query was never going to work because in the exists never had the link back to the work order.  I have rejigged it, though it's still not technically correct.  You have got historyflag = 0, yet you are wanting to include the CAN status.  Whenever a work order goes to CAN, it automatically is tagged as historyflag =1, and so would never be included in your query.  At what stage in the work order lifecycle are you actually looking to run the query against.

(
woclass in ('WORKORDER', 'ACTIVITY') 
and historyflag = 0 
and istask = 0 
and siteid = 'xxxxxxxxxxxxxxx'
and status in ('CAN', 'CLOSE', 'COMP')
and upper(description) like '%A LEVEL%' 
and 
    not EXISTS 
    ( 

              SELECT 1 
        FROM doclinks 
        WHERE ownertable = 'WORKORDER'
        and ownerid = workorder.workorderid
    )
;

Sorry to hijack this thread but I'm looking for the exact opposite. Trying to generate a list of Workorders that do not have a document attached in the document column on the doclinks table.

I've modified it to the best of the ability for my situation, but getting no results or a syntax error message.

((status = 'CAN' or status = 'CLOSE' or status = 'COMP')  and ((upper(description) like '%A LEVEL%'))AND EXISTS (SELECT 1FROM workorderWHERE WONUM IN (SELECT 1 FROM doclinks WHERE ownertable = 'WORKORDER')  AND DOCUMENT IS NULL)

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 11-20-2023 10:13
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

Hi Nathan,

You didn't put the "TRUNC" around the "SYSDATE - 1".  You referred to wanting it go through the end of the day on Tuesday, 4/9, and I assume you're saying that because today is Wednesday, 4/10.  Assuming you want to list WOs whose Status changed during the 7 days ending yesterday, then you should say "(Status BETWEEN TRUNC(SYSDATE - 7) AND TRUNC(SYSDATE))" without the "-1".

------------------------------
Julio Hernandez
Ergonare, Inc.

Original Message:
Sent: 04-10-2024 13:35
From: Nathan Frail
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Craig, yeah I ended up realizing I had some conflicting lines in the where clause and have since adjusted it accordingly. The end result I was looking for was all workorders in COMP status, the "A Level" description that do not have an attachment. Travis was able to help me out with that. Though I do have another question regarding filter this further into a date range. Specifically prior week Wednesday 12:00am - Tuesday 11:59pm.

Here's what i've come up with but it's not running all the way to Tuesday 4/9/24 @ 11:59:59pm

It lists all records from 4/3 @ 12:00am through Tuesday-24hours. So, any records completed after 6pm last night lets say, aren't populating.

((status = 'COMP' and upper(description) like '%A LEVEL%' and siteid = 'FXFRTSID' and (worktype = 'PM') and plustreason = '08') and workorderid not in (select ownerid from doclinks where ownertable='WORKORDER') and (statusdate between trunc(sysdate-7) and sysdate-1)) and ((plustrepord = 0))

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 04-09-2024 20:01
From: Craig Kokay
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Nathan

The query was never going to work because in the exists never had the link back to the work order.  I have rejigged it, though it's still not technically correct.  You have got historyflag = 0, yet you are wanting to include the CAN status.  Whenever a work order goes to CAN, it automatically is tagged as historyflag =1, and so would never be included in your query.  At what stage in the work order lifecycle are you actually looking to run the query against.

(
woclass in ('WORKORDER', 'ACTIVITY') 
and historyflag = 0 
and istask = 0 
and siteid = 'xxxxxxxxxxxxxxx'
and status in ('CAN', 'CLOSE', 'COMP')
and upper(description) like '%A LEVEL%' 
and 
    not EXISTS 
    ( 

              SELECT 1 
        FROM doclinks 
        WHERE ownertable = 'WORKORDER'
        and ownerid = workorder.workorderid
    )
;

------------------------------
Craig Kokay
Principal Consultant
COSOL

email: craig.kokay@cosol.global
#IBMChampion

Original Message:
Sent: 04-09-2024 10:04
From: Nathan Frail
Subject: Generate a list of workorders that contain attachments directly in Maximo

Sorry to hijack this thread but I'm looking for the exact opposite. Trying to generate a list of Workorders that do not have a document attached in the document column on the doclinks table.

I've modified it to the best of the ability for my situation, but getting no results or a syntax error message.

((status = 'CAN' or status = 'CLOSE' or status = 'COMP')  and ((upper(description) like '%A LEVEL%'))AND EXISTS (SELECT 1FROM workorderWHERE WONUM IN (SELECT 1 FROM doclinks WHERE ownertable = 'WORKORDER')  AND DOCUMENT IS NULL)

------------------------------
Nathan Frail
FedEx

Original Message:
Sent: 11-20-2023 10:13
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hi Stephen,

Here is my SQL Query :

SELECT wonum,
description,
status,
workorderid
FROM workorder
WHERE workorderid IN (SELECT ownerid FROM doclinks WHERE ownertable = 'WORKORDER')
  AND workorder.status IN ('ASSIGNÉ', 'ENCOURS')

That query was converted to a Where Clause, based on your previous post (thanks!) :

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') and status in ('ASSIGNÉ','ENCOURS')

I will now save my query and make it available for my supervisors.

Thanks guys for your help!!

Danny

------------------------------
Danny Cordeau
Universite de Sherbrooke

Original Message:
Sent: 11-20-2023 09:29
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

workorderid in (select ownerid from doclinks where ownertable='WORKORDER') 

You can also limit it to only open work orders by adding "and historyflag = 0" to the query.

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-20-2023 08:57
From: Stephen Hume
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello Danny, can you convert your sql clause to a Maximo Where Clause and then save it as a query for the users to run themselves.  Once it is saved as a query in the work order application, you could also put a resultset on their start center.  (Work Orders with Attachments).

------------------------------
Stephen Hume
Sheffield Scientific LLC

Original Message:
Sent: 11-17-2023 15:03
From: Danny Cordeau
Subject: Generate a list of workorders that contain attachments directly in Maximo

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

#Administration
#Customizations
#MaximoUserGroups

------------------------------
Danny Cordeau
Universite de Sherbrooke
------------------------------

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny

I just want to ask the awkward question as you have said "directly", and everyone's assumption is that the attachment is on "the" work order, not its tasks or related inspections.  I ask because each of those have a different OWNERTABLE.

Hello guys!

Maximo 7.6.1.3 here. Supervisors want to be able to generate a list of workorders that contain attachments. They want to be able to generate that list as part of the workorder application or directly through their own start center. SQL query is pretty simple and it gives me the expected result but according to you experience what is the simpliest way to deploy that feature? Automation Script?

Thanks in advance.

Danny