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
Answer Verified By: Chris Marsh
Thank you so much for this, Eric. It's worked a charm! :-)