How to Add Activities to a Custom Section in Creatio

Out of the box in Creatio, you can connect an Activity to several different types of entities/sections. You can also add support for a custom section to have activities as well with a few manual steps. I’ll outline those steps in this article with code that supports both MSSQL and Postgresql implementations.

STEP 1 – Create a Lookup column for your entity on the Activity object

The first thing you’ll need to do is create a lookup column for your custom section on the Activity object. You can do this by creating a replacing object for Activity and then add a lookup column for you entity to it. For the purpose of this article, we’ll assume your custom section is named “Item” and the column name you’ve added to Activity is “UsrItem”.

STEP 2- Execute SQL statement to register your column and entity as an entity connected to Activity

For this step, we’ll need to execute some SQL. If you’re working in a cloud Creatio system, you’ll need to install SQL Executor from the marketplace to execute the statement. Please note, there is different code below depending on if you are working in a Postgresql version of MSSQL version. For any recent cloud hosted Creatio, you’re probably on Postgresql. If you’re unsure, you can execute the following:

select @@version

If you’re on MSSQL, you’ll get back the version of SQL your system is on. If you’re on Postgresql, you’ll get back an error saying “Npgsql.PostgresException (0x80004005): 42703: column “version” does not exist”.

Postgresql Version – Note: change the “UsrItem” value for the CustomActivityLookupColumn to the name of your lookup column you added in step 1.

do $$
declare
	-- Add column for your entity and add name of column in Activity object below
	CustomActivityLookupColumn char(20) := 'UsrItem';

	ActivityUId uuid;
	CustomColumnUId uuid;
begin

  select "UId" into ActivityUId from "SysSchema" where "Name" = 'Activity' and "ExtendParent" = false;
    
  select
    	"ColumnUId"
  into
    	CustomColumnUId
  from 
		"SysEntitySchemaReference" esr 
		inner join "SysSchema" sch on esr."SysSchemaId" = sch."Id"
	where 
		esr."ColumnName" = CustomActivityLookupColumn
		and
		sch."Name" = 'Activity';

	if CustomColumnUId is null then
		raise notice 'Could not locate column specified in CustomActivityLookupColumn variable. Make sure the column has been added to Activity';
	else
		if not exists (select null from "EntityConnection" where "SysEntitySchemaUId" = ActivityUId and "ColumnUId" = CustomColumnUId) then
			insert into "EntityConnection" ("SysEntitySchemaUId", "ColumnUId") values (ActivityUId, CustomColumnUId);
		end if;
	end if;

end $$;

MSSQL Version – Note: change the “UsrItem” value for the @customActivityLookupColumn to the name of your lookup column you added in step 1.

-- Set as name of the Lookup column for the custom object you created on Activity
declare @customActivityLookupColumn varchar(20) = 'UsrItem' 

declare @activityUId uniqueidentifier
declare @customColumnUId uniqueidentifier

select @activityUId = UId from SysSchema where Name = 'Activity' and ExtendParent = 0

select 
	@customColumnUId = ColumnUId 
from 
	SysEntitySchemaReference esr 
	inner join SysSchema sch on esr.SysSchemaId = sch.Id 
where 
	esr.ColumnName = @customActivityLookupColumn
	and 
	sch.Name = 'Activity' 

if @customColumnUId is null 
	throw 61000, 'Could not locate column specified in @customActivityLookupColumn variable. Make sure the column has been added to Activity', 1
else
begin
	if (not exists(select null from EntityConnection where SysEntitySchemaUId = @activityUId and ColumnUId = @customColumnUId))
	begin
		insert into EntityConnection (SysEntitySchemaUId, ColumnUId) values (@activityUId, @customColumnUId) 
		print 'Custom column added to Activity Connected To'
	end
end

STEP 3- Add the Activities detail schema to your custom section

The last step is you simply add the Activities detail to your custom section. This is done just like adding any detail to a page. Open your section in the section wizard and add the Activities detail using the column you added in step 1 to connect it to the page.