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

Parents Reply Children
  • 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"].Value
    WHEN 'Not required'
    THEN
       'Not required'
    ELSE
       CASE Files.InDefaultVisibleScope WHEN 'Y' THEN 'Yes' ELSE 'No' END
    END

  • 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"].Value
    WHEN 'Not required'
    THEN
       'Not required'
    ELSE
       CASE Files.InDefaultVisibleScope WHEN 'Y' THEN 'Yes' ELSE 'No' END
    END  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