eQL to show Related Document (i.e. Parent) information

Hello everyone

Can anyone provide any advice on what to include in an eQL statement that would return details of a related document (container) in the search results? I'm searching for all containers with "HSF" in the title of the container and where the GRIP Stage is "Not Applicable". I'd like the results to list the parent container - name, number, and approval status. The closest I can get is with the below. This returns a column that lists "Parent" but I'm unsure how to request further information about this parent container. Any help would be very much appreciated. Many thanks. Chris

START WITH Document
SELECT
Id,
Code,
Name,
ApprovalStatus,
Attributes["Global", "Grip Stage"].Value,
Documents.RelationshipType.LeftName
WHERE
Name CONTAINS '%HSF%'
AND (
Attributes["Global", "Grip Stage"].Value = 'Not Applicable'
)
ORDER BY
Code

  • This is what I think you want:

    * * * * *

    START WITH Document
    SELECT
    Id,
    Code,
    Name,
    ApprovalStatus,
    Attributes["Global""Grip Stage"].Value,
    Documents.Left.Code "Parent Code",
    Documents.Left.Name "Parent Name",
    DISPLAY_NAME(Documents.Left.ApprovalStatus) "Parent Status"
    WHERE
    Documents.RelationshipType.LeftName = 'Parent'
    AND
    Documents.RelationshipType.RightName = 'Document'
    AND
    Name CONTAINS '%HSF%'
    AND (
    Attributes["Global""Grip Stage"].Value = 'Not Applicable'
    )
    ORDER BY
    Code

    * * * * *

    This will return any document that has a parent in addition to your other filters. The AND Documents.RelationshipType.Right = 'Document' filter is not really needed, I've included it to show that you can filter based on either side of the relationship. In some communities, the right name may be 'Child', so you might have to change or remove this filter.

    Also, I've added the DISPLAY_NAME function as an fyi. It converts the raw status value (A,N,P) to the full name (Approved, Not Approved, Planned). You can also use it on the child document's approval status.

    Eric Rajala | Consultant

    Cohesive

    Email: eric.rajala@cohesivegroup.com

    www.bentley.com

       
    This is a test

    Answer Verified By: Chris Marsh 

  • Thank you so much for this, Eric. It's worked a charm! :-)