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'

The above will allow you to run a SQL query to get the names of all related objects. Note, this will work as-is for both MSSQL and Postgresql as well. Also note, however, this will return not just objects that are normal objects that represent tables in Creatio, but also virtual objects and objects that represent views.

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))
	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.