Using Virtual Objects in Creatio

In Creatio, you can create virtual objects that work similar to any other object. The difference being that a virtual object doesn’t represent a database table. Instead, a virtual object contains data that is populated from code, the data itself could come from anywhere. Some common uses for virtual objects is to natively expose data that might come from a source such as an API or from some complex queries or calculations using data inside of Creatio. The idea is that the virtual object defines the data structure and allows for things like binding to lists, using in lookups, etc. However, when the data is requested, it uses code to retrieve the data, populate the objects, and return the collection of results. Using a virtual object with a source like an API allows you to have native lookups or lists in Creatio, that expose data from the API, but for the end user it works just like any other list.

Creating the Virtual Object

First thing you’ll need to do is create the virtual object. This is where you’ll define the data structure. You’ll create it like any other object, defining the name, setting the parent as BaseEntity, adding the necessary columns, but most importantly, checking the box that the object is virtual (so the designer doesn’t create a table for the object).

The next step is to create code that is called to populate the collection of objects whenever the data is requested by the UI.

Adding Code to Populate the Virtual Object Collection when Requested by the UI

When the UI requests the data for this virtual object, for times such as when a list or lookup bound to this object is loaded, we’ll need to create a class that implements the IEntityQueryExecutor interface. This interface defines a function called GetEntityCollection (which should sound familiar if you’ve used EntitySchemaQuery). The class will also include a DefaultBinding attribute and will be named [VirtualObjectName]QueryExecutor. For the example object above, “UsrMyVirtualObjectQueryExecutor”. In the sample code below, I’ll be just creating some sample objects and adding to a collection that gets returned. In a real-world usage, we’d be gathering data from somewhere else such as an API, then creating the objects using that data.

One thing to note. If we bind the virtual object to a list, the user could potentially sort columns and filter, if we’ve included filtering options for our list such as a search and QuickFilters. Also, if we’re bound to a record page, we’ll need the record Id that we’re bound to. The example code will show how to retrieve the filters. It will be up to you to implement using them based on how you’re gathering the data, so the sample only outputs them to the log for viewing. The sample code also retrieves the sort column and sort order. It uses this to simply sort the collection before it’s returned, however, you could use that in other ways if needed.

namespace Terrasoft.Configuration
{
	using System;
	using System.Linq;
	using Terrasoft.Core;
	using Terrasoft.Core.Entities;
	using Terrasoft.Core.Factories;
	using global::Common.Logging;
	
	[DefaultBinding(typeof(IEntityQueryExecutor), Name = "UsrMyVirtualObjectQueryExecutor")]
	public class UsrMyVirtualObjectQueryExecutor: IEntityQueryExecutor
	{
		private readonly UserConnection _userConnection;
		public UsrMyVirtualObjectQueryExecutor(UserConnection userConnection)
		{
			_userConnection = userConnection;
		}
		
		public EntityCollection GetEntityCollection(EntitySchemaQuery esq)
		{
			var log = LogManager.GetLogger("UsrMyVirtualObjectLogger");
			
			// Optionally get filters
			if (esq.Filters != null)
			{
				foreach(var filterObj in esq.Filters)
				{
					var filerInstances = filterObj.GetFilterInstances();
					foreach(var filter in filerInstances)
					{
						var compType = filter.ComparisonType.ToString();
						var column = filter.LeftExpression.SchemaColumn.Name;
						var value = filter.RightExpressions[0].ParameterValue.ToString();
						
						log.Info("FILTER: " + column + " " + compType + " '" + value + "'");
						// Example when bound to page record - UsrContact Equal '6366310f-5741-4111-84e9-aca2b99e78d1'
						// Example when using quickfilter account lookup - UsrAccount Equal '405947d0-2ffb-4ded-8675-0475f19f5a81'
						// Example when using list search - UsrName Contain 'Test'
					}
				}
			}
			
			
			// create collection and add entity data
			var collection = new EntityCollection(_userConnection, "UsrMyVirtualObject");
			var schema = _userConnection.EntitySchemaManager.GetInstanceByName("UsrMyVirtualObject");

			// add sample record 1
			var entity1 = schema.CreateEntity(_userConnection);
			entity1.SetColumnValue("Id", Guid.NewGuid());
			entity1.SetColumnValue("UsrName", "Record 1");
			entity1.SetColumnValue("UsrIntValue", 1111);
			collection.Add(entity1);

			// add sample record 2
			var entity2 = schema.CreateEntity(_userConnection);
			entity2.SetColumnValue("Id", Guid.NewGuid());
			entity2.SetColumnValue("UsrName", "Record 2");
			entity2.SetColumnValue("UsrIntValue", 2222);
			collection.Add(entity2);

			// add sample record 3
			var entity3 = schema.CreateEntity(_userConnection);
			entity3.SetColumnValue("Id", Guid.NewGuid());
			entity3.SetColumnValue("UsrName", "Record 3");
			entity3.SetColumnValue("UsrIntValue", 3333);
			collection.Add(entity3);

			
			// Optionally get list sort order and sort collection
			var col = esq.Columns.FirstOrDefault(x => x.OrderDirection != Common.OrderDirection.None);
			if (col != null)
			{
				var sortDirection = col.OrderDirection;
				var sortColumn = col.Name;
				// sort collection
				if (!string.IsNullOrEmpty(sortColumn)) collection.Order(sortColumn, sortDirection);
				
				log.Info("SORT BY: " + sortColumn + " " + sortDirection.ToString());
				// Example result - SORT BY: UsrName Descending
			}
			
			return collection;
		}
	}
}

If you also want to page the results like standard Creatio objects (all the lists will do paging automatically), you just need to read these properties from the passed esq object:

• esq.UseOffsetFetchPaging – Boolean, if true use paging
• esq.RowCount – Integer, the number of rows to return in a page (typically will be 30)
• esq.SkipRowCount – Integer, the number of rows to skip for the returned results

Now that we have something that populates and returns the data, we’ll bind this to a list.

Binding the Virtual Object to a List

For our sample, we’ll be binding the list of virtual objects to a list on the account page.

There’s a bit of trickiness you need in order to use the form designer with a virtual object. The designer intentionally doesn’t allow virtual objects to be selected. In order to use the designer to set up our list of virtual objects, we need to do the following:

1. Open the object
2. UNCHECK the Virtual checkbox
3. SAVE but do not PUBLISH the object (important!)
4. Now go set-up the form using the object. In our case setting up the list
5. After you’ve bound the list to the form, open the object again RE-CHECK the Virtual checkbox and this time PUBLISH

Going through those steps, you can use the designer as you would any object. Just be sure to follow them exactly.