Happy Christmas everyone!
I would like to construct a System Report that returns documents (containers) that have a "Warning" message that an item of mandatory metadata is missing. The "Warning" appears in orange-coloured font on a container's front screen, and prevents a user from Approving a container. Is this possible? If so, I'd be extremely grateful for any assistance on the eQL required.
I apologize. I was about to answer this last week but I got sidetracked and then forgot.
The good news is yes you can do this. The bad news is that there isn't a clean, concise way to write this report.
First you have to know which attributes are mandatory. You can look at the attributes by object type in Director>system administration (F8) or you can write an attribute definition eQL query, as below, if you want a convenient list.
* * * * * *
START WITH AttributeDef SELECT Id, Name, DISPLAY_NAME( DataType ) WHERE IsMandatory = 'Y' AND ObjectType = 3 ORDER BY Name
This will list all the attributes that are mandatory for documents (that's what ObjectType = 3 means). Other types, for example are 1 for physical items, 6 for change requests and 212 for tags.
If you want to look at attributes across object types, remove AND ObjectType = 3 from the WHERE clause and add ObjectType to the SELECT list. Unfortunately DISPLAY_NAME() does not apply to object types, so if you want to translate the numbers into human-readable names you'll have to do it manually with a CASE statement.
Anyway, once you know which attributes are mandatory, you can write the report(s). At this point, if you are concerned with more than one object type, you need to write a separate report for each type, For example:
START WITH Document SELECT Id, Code, Revision, Name WHERE IsTemplate = 'N' AND
Attributes["Global", "[Your Mandatory Attribute 1]".Value IS NULL
Attributes["Global", "[Your Mandatory Attribute 2]".Value IS NULL
Attributes["Global", "[Your Mandatory Attribute 3]".Value IS NULL
[OR . . .]
)ORDER BY Code, Revision
The text in square brackets  is informational, and not literally part of the query. Note that the IsTemplate = 'N' is important here, because mandatory attributes are not usually populated on templates and you don't want these cluttering up the output.
So if your configuration has a large number of mandatory attributes and you are concerned with them all, this query may get pretty long. This is not a problem for eQL, some queries have run into thousands of characters, it is just cumbersome for us humans.
Eric Rajala | Consultant
Answer Verified By: Chris Marsh
Thanks again for your help! :-)