Using Database Views in Creatio

There are many benefits to using database views in Creatio. It can make operations that would require several expensive reads simplified using joins. It can make what would be complex or impossible aggregates simple. The end result is that the view can be used in Creatio just like any object. They can be used in Entity Schema Queries or Model reads (loads). They  can be used in dashboards, printables, and even bound as lists or details on pages. Even using a view for a Freedom UI section is a simple task. This article will go through the steps for creating a SQL view and exposing the view as an object in Creatio.

Creating the View

The first step is to create the view. There are a few rules that the view columns and results must follow:

1. The view must contain the standard columns for BaseEntity, which are: Id, CreatedOn, CreatedById, ModifiedOn, ModifiedById, ProcessListeners (technically not required but is definitely a good idea)
2. The values in the Id column must be unique if the view is going to be bound to lists in Creatio. To accomplish this, I typically choose a main table for data in the view that will be unique and then expose that table’s Id as the view Id.
3. Any columns, besides the standard BaseEntity columns listed in #1 above, must include the prefix used in your system.
4. Any lookup columns must end in Id. For example, if my view will have a lookup to Account, my column would be named “UsrAccountId” (ending in Id) and contain valid Id Guids for account records. Note, for the object that is discussed later in this article, the lookup columns will NOT contain the Id. For a view column named “UsrAccountId” the associated lookup column would be named “UsrAccount” (without the Id).
5. Not required, but typically the naming convention for views in Creatio will also contain Vw, such as UsrVwAccountInfo to make it clear from naming that an object represents a view. I like to also include something in the title as well, such as “Account info (view)”.

For this article, I’ll be using the simple view shown below that returns a list of accounts with the total of each account’s closed won opportunities closed within the past 3 years for a Postgresql Creatio system (yes, there are definitely ways to do this particular example without using a view, however, this article is about using views).

drop view if exists "UsrVwAccountInfo";

create view "UsrVwAccountInfo"
as
    select 
        acc."Id",
        acc."CreatedOn",
        acc."CreatedById",
        acc."ModifiedOn",
        acc."ModifiedById",
        0 as "ProcessListeners",
        acc."Id" as "UsrAccountId",
        coalesce(accopptotal3year."3YearTotal", 0) as "Usr3YearTotal"
    from
        "Account" acc

        left join (
            select 
                "AccountId",
                sum("Amount") as "3YearTotal"
            from 
                "Opportunity"
            where
                "StageId" = '60d5310c-5be6-df11-971b-001d60e938c6' -- closed won
                and 
                "DueDate" >= current_date - interval '3' year and "DueDate" < current_date
            group by
                "AccountId"
        ) as accopptotal3year on acc."Id" = accopptotal3year."AccountId";

To execute this view, you can either use Clio Explorer or add a SQL Script schema to your package, paste in the SQL above and then, once saved, use the three-dot ellipsis button on the right of the row in the package and select “Install” (you’ll need to add this to your package anyway if delivering this package to another system anyway).

Creating the Object for the View

With the database view itself created, you’ll need to create an object for the view to expose and use it in Creatio. The object must follow these rules:

1. The object name must be the same as the database view name.
2. Parent object must be BaseEntity (technically not required but is definitely a good idea)
3. Check the box “Represents Structure of Database View”
4. Columns in the object must be the same names as the columns in your view. The only exception is for Lookup columns. In the database view those will end in Id, but the object Lookup column will not. For example, in the view my Account column is named UsrAccountId, but the Lookup column name is only UsrAccount.
5. The data types for the object columns must be the same as the data types returned by the view.

For my sample view above, my object would look like the following:

Using the View in Creatio

As I mentioned earlier, using the view is just like using any other object in Creatio. You can easily use it in dashboards, bound lists on pages, and my favorite usage is in Word printables to produce a complex report using built-in Word functionality.

Delivering Views to Other Systems

When delivering views in a package, you’ll likely need to take into consideration that Creatio can be Postgresql, MSSQL, or Oracle. To make the view work with any supported database platform, your package can include separate SQL Script schemas containing views with syntax for a specified environment.

With this in mind, your package can contain a different view for each MSSQL, Postgresql, and Oracle – each with the necessary syntax for that database platform.