Query To Find Objects Related To Another Object in Creatio

If you need to determine the objects that are related to another object in Creatio, there’s a few ways to do this. For example, let’s say you want to find all of the objects that have a relationship to the account object – meaning, find all the objects that have an account lookup. We’ll take a look at two different ways to do this in Creatio.

Via SQL Query

-- find objects related to Account
select 
	distinct SysSchema.Name as SysSchemaName
from 
	SysEntitySchemaReference
	inner join SysSchema on SysSchema.Id = SysEntitySchemaReference.SysSchemaId
	inner join SysSchema as ReferenceSchema on ReferenceSchema.Id = SysEntitySchemaReference.ReferenceSchemaId
where 
	ReferenceSchema.Name = 'Account'
	and OBJECT_ID([SysSchema].[Name], 'U') is not null

The above will allow you to run a SQL query to get the names of all related objects. Note, this should work as-is for Postgresql as well by enclosing all table & field names in quotes (although I didn’t test there)

Via a Select Object

var objectToFindRelatedObjectsFor = "Account";

var select = new Select(UserConnection)
	.Distinct()
	.Column("SysSchema", "Name").As("SysSchemaName")
	.From("SysEntitySchemaReference")
	.InnerJoin("SysSchema").On("SysSchema", "Id").IsEqual("SysSchemaId")
	.InnerJoin("SysSchema").As("ReferenceSchema").On("ReferenceSchema", "Id").IsEqual("ReferenceSchemaId")
	.Where("ReferenceSchema", "Name").IsEqual(Column.Parameter(objectToFindRelatedObjectsFor))
	.And(Column.SqlText("OBJECT_ID([SysSchema].[Name], 'U')")).Not().IsNull()
	as Select;

using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection())
{
	using (IDataReader reader = select.ExecuteReader(dbExecutor))
	{
		while (reader.Read())
		{
			var sysSchemaName = reader.GetColumnValue<string>("SysSchemaName");
			// sysSchemaName contains the name of a related object
		}
	}
}

If you need to do this in code in Creatio, you can do it with a Select object as shown above.