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