eQL for when file is "Not required" when Suitability Status is also "Not required"

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

  • 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

  • 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.

  • All you have to do is add the attribute in the select list for the query

    START WITH Document 
    SELECT 
       Id,
       Code,
       Revision,
       Name,
       ApprovalStatus,
      Attributes["Global", "Suitability Status"].Value,
       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