The Creatio Freedom UI makes it easy to display lists of associated data using a List element that can be configured to show any data that is linkable to the record being viewed.

For example, when viewing an account, a list can be added to show contacts, activities, or any information on the account page, as they all have a link back to an account record.

Let’s say we have such a list based on some data that could potentially have a lot of records. For example, if a lot of phone calls are being made each day, we could have many user Activity records that represent various calls and meetings with various customers. So, if we set up the account screen to show activity records, we could end up pulling more information than necessary.

It may make sense to have a built-in filter that always pulls data past a certain cutoff date. We don’t want to show all the activities, only those that were in the current financial year.

Because financial years are defined differently for different organizations, we want the filter to also be driven by a system setting so the setting can be easily changed by an admin, which then will then apply to all users.

So, let’s start with the first step. We want to show data related to activities, and we want it to be automatically filtered. Let’s create an SQL query that can retrieve a list of activities filtered based on the activity due date.

The filter would look like this:

In the above case, we’ve chosen an arbitrary April 1 date, but now we want to replace that date by a system setting.

So, let’s go ahead and create a system setting named FiscalYearStartDate. This is easy to do in the system settings area.

Once the setting is created and properly named, we can retrieve its value using the query:

Now if we put both queries together, we end up with a query that retrieves Activity record Ids past a set date:

When this is executed, it will pull any Activity records that are past the cutoff date specified in the 'FiscalYearStartDate' setting.

So now that we know how to pull the necessary data, we need to encapsulate the query we developed into a Creatio object so it can be used to feed the UI elements on the screen.

A Creatio object can either be based on an actual data table, or could be based on a query (aka SQL view). In this case we will just create an SQL view that uses the query we wrote. To encapsulate the query into an SQL view, use the command below:

Once the SQL view is created, we need to create an object that specifies which columns are available. Once this is done, save and publish the changes.

Now that we have the object created, it should have the necessary Account link and an Activity link. The Account column (UsrAccount) is what we need to hook to the account page. And the Activity column (UsrActivity) links to any activities that would be associated with the account being viewed.

Finally, add a list component to the Account page, with a name like “Financial Year Activities” or in our case “FY Activities” to make the name a little shorter.

Configure the data source of the list element to point to the Fiscal Year Activity data and link it to the page using the AccountID so it shows data for the account being viewed.

Save your changes and refresh the account page. The new list should now show data that will be pre-filtered based on the system setting named FiscalYearStartDate.

In the previous screenshot we used a cutoff date of 4/1/2024, so let’s change it to 7/1/2024.

Now, let’s go back to the same account and see how the data looks. It should show data only data posted in July.

The activity information shows only the information for 7/19, which confirms that the other data is automatically being filtered out. This is exactly what we’re expecting to see!

The example in this article used just the Activity data, but as you can see, the same approach could also be used to show pre-filtered data from any date-based information available in the system, such as Opportunities (based on close date) etc., or Orders based on their order date.