Hello everyone!
Hope you're all happy, safe and well.
I require some help with an eQL statement. I have created one that searches if a container has a file, with the result showing Yes or No (see below line-grab):
CASE Files.InDefaultVisibleScope WHEN 'Y' THEN 'Yes' ELSE 'No' END
Is there a way of linking this to the container's Suitability Status field, so that when someone has selected a Suitability Status of "Not required" then the file result also appear as "Not required" instead of just Yes or No? Basically, if someone has said that there will never be a file needed for a container, the report (eQL) should say so, rather than Yes or No. At the moment, I have to manually change "No" to Not required" for those containers marked accordingly.
I do hope this makes sense!
Kind regards
Chris
With eQL you can nest Case statements.
So if for example you are starting with Document and Suitability Status is an attribute on the document you can use the below eQL to get what you want.
START WITH Document SELECT CASE Attributes["Global", "Suitability Status"].Value WHEN 'Not required' THEN 'Not required' ELSE CASE Files.InDefaultVisibleScope WHEN 'Y' THEN 'Yes' ELSE 'No' END END WHERE IsTemplate = 'N'
Thank you so much for the speedy response! Very much appreciated. Do you have any advice on how I incorporate the above into the below eQL report? Many thanks.
START WITH Document SELECT Id, Code, Revision, Name, Attributes["Global", "Suitability Status"].Value, ApprovalStatus, CASE Files.InDefaultVisibleScope WHEN 'Y' THEN 'Yes' ELSE 'No' END, Projects.Project.Name, Attributes["Global", "Grip Stage"].Value WHERE Projects.Project.Code LIKE @Project ORDER BY Name
I would replace the CASE statement you have with:
CASE Attributes["Global", "Suitability Status"].ValueWHEN 'Not required'THEN 'Not required'ELSE CASE Files.InDefaultVisibleScope WHEN 'Y' THEN 'Yes' ELSE 'No' ENDEND
Thanks again for the response. Unfortunately this results in the column telling me if there's a file in the container no longer appearing in the report results (I think the column was previously called "CASEFilesInDefaultVisibleScope"). It doesn't appear in the Format Layout either.
Since you were not naming your columns it uses the eql itself for the name, that is why it was called "CASEFilesInDefaultVisibleScope", because the eql changed the column name will have changed as well. If you want the updated column to be called the same as it was before you can have a column name, no spaces, at the end of the entry for that column. This can be anything you want but to keep it the same name you had before use:
CASE Attributes["Global", "Suitability Status"].ValueWHEN 'Not required'THEN 'Not required'ELSE CASE Files.InDefaultVisibleScope WHEN 'Y' THEN 'Yes' ELSE 'No' ENDEND CASEFilesInDefaultVisibleScope