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
I think that's the current issue I'm having - there is no longer a column called "CASEFilesInDefaultVisibleScope", either in the results screen, or the Format Layout (where I'd normally put the new name in under the Display Label heading). Below is the eQL I'm currently using. It works but like I've previously mentioned, there is no longer a column displaying whether the container has a file or not:
START WITH Document SELECT Id, Code, Revision, Name, ApprovalStatus, CASE Attributes["Global", "Suitability Status"].Value WHEN 'Not required' THEN 'Not required' ELSE CASE Files.InDefaultVisibleScope WHEN 'Y' THEN 'Yes' ELSE 'No' END END, Projects.Project.Name, Attributes["Global", "Grip Stage"].Value WHERE Projects.Project.Code LIKE @Project ORDER BY Name
Yes that would be correct. you need to name the columns if you want that. If you want to have the column named the same as it was before the eQL would be:
START WITH Document SELECT Id, Code, Revision, Name, ApprovalStatus, CASE Attributes["Global", "Suitability Status"].Value WHEN 'Not required' THEN 'Not required' ELSE CASE Files.InDefaultVisibleScope WHEN 'Y' THEN 'Yes' ELSE 'No' END END CASEFilesInDefaultVisibleScope, Projects.Project.Name, Attributes["Global", "Grip Stage"].Value WHERE Projects.Project.Code LIKE @Project ORDER BY Name
Thank you! This has worked.
Hello there. One final question, if I may. Is there any way of allowing the Suitability Status to appear too? At the moment, this seems to have been replaced with the Files.InDefaultVisibleScope column.