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.

ABOUT THE AUTHOR

Ryan Farley

Ryan Farley is the Director of Development for Customer FX and creator of slxdeveloper.com. He's been blogging regularly about SalesLogix, now Infor CRM, since 2001 and believes in sharing with the community. His new passion for CRM is Creatio, formerly bpm'online. He loves C#, Javascript, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!