When working with building a custom report, you may run into a relationship that is established through a link table (fusionlink).
A link table allows the application to store many to many relationship information between objects.
Due to that, linking the data together in SQL is not simple and requires a join through the fusion link table.
You want to show all CI items linked to an Incident.
Incident to CI is a many to many relationship that uses the fusionlink table.
1. Open the relationship that you are attempting to use via 'Configure Application'
2. The main information that needs to be taken from the relationship screen is the relationship 'Display Name'
3. The display name is used in the query to search for FusionLink.RelationshipName
SELECT Incident.IncidentNumber, Incident.CreatedDateTime, CI.CIType, CI.Name
INNER JOIN Incident ON FusionLink.sourceID = Incident.RecId
Inner join CI ON FusionLink.targetID = CI.RecId and FusionLink.targetbase = 'CI' and FusionLink.SourceBase = 'Incident'
WHERE FusionLink.RelationshipName = 'IncidentAssociatesCI'
AND FusionLink.SourceID in
Order by Incident.IncidentNumber desc