Hello Kamran,

Can you try rewriting the code and adding the Terrasoft.chain usage in it? As described here - https://community.creatio.com/articles/getentity-schema-works-asynchron….

Hi Kamran, 
What are you trying to achieve? 

If you just put the whole logic into the callback function, it'll work. 
Also you can wrap the esq call into a promise.

Hi,

Example from the OmnichannelContactIdentifier (OmnichannelMessaging package):

var esq = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "ContactIdentity") {
                UseAdminRights = false
            };
            esq.PrimaryQueryColumn.IsAlwaysSelect = true;
            esq.AddColumn("Contact");
            esq.AddColumn("Channel");

...

Please try this approach of specifying the UseAdminRights flag for the server esq.

In addition to Oleg's response, you can also use the Select, Insert, Update, etc classes, they bypass security since they are executing direct in the database. See https://academy.creatio.com/docs/8.x/dev/development-on-creatio-platform/8.1/direct-access-references

Also, you can get the SystemUserConnection to use for the ESQ.

Also worth noting for people who haven't used the Select, Insert, Update etc classes, they bypass all Creatio application logic, so things like Business Process triggers on record creation or update too. They are very useful, but need to be aware of that to avoid issues.

One more detail, if it isn't clear by now: the UseAdminRights flag doesn't mean "use the permissions of the administrator"...actually, the opposite is the case. It means "enable the usage of permissions".
I find the wording pretty misleading, but that's how it is ;)

Yes, naming leads to opposite meaning

Thx a lot for answers

The EntitySchemaQuery classes in the devkit don't do anything, at least not how it is exposed. The ESQ classes require an executor class that actually *runs* the query and provides the connection. We don't have access to this, the executor that runs the ESQ is not part of the devkit sdk. This is intentional/by design since they want us to use the new Model classes instead. This better anyways, the Model class is far easier and more intuitive IMO.

The intended way to get data now is the Model class, which is in the devkit sdk. Here's some articles on the topic: 

Model Query Using Filters: https://customerfx.com/article/querying-data-using-filter-conditions-vi…

Model Query for a Single Record Given it's Id: https://customerfx.com/article/retrieving-a-record-via-the-model-class-…

Also, the model class does inserts/updates/deletes:

Inserting a Record: https://customerfx.com/article/inserting-a-record-from-client-side-code…

Updating a Record: https://customerfx.com/article/updating-a-record-from-client-side-code-…

Deleting a Record: https://customerfx.com/article/deleting-a-record-from-client-side-code-…

Copying a Record: https://customerfx.com/article/copying-a-record-from-client-side-code-u…

You can also use the Model class to get an object's schema:

Get Object Schema: https://customerfx.com/article/getting-an-object-schema-using-the-model…

Ryan

Hi, Ryan. Thanks for quick reply.

Maybe you could suggest how do I need to prepare aggregate columns for the query using Model class? I tried it as described below

  async ngOnChanges(changes: SimpleChanges) {
    console.log(changes);
 
    if (isGuid(changes["recordId"]?.currentValue)) {
      const dataModel = await Model.create("ClvObject");
      const filters = new FilterGroup();
      filters.addSchemaColumnFilterWithParameter(ComparisonType.Equal, "ClvProject", this.recordId, "currentFilter");
      const records = await dataModel.load({
//        attributes: ["Id", "ClvName", "ClvType", "ClvProject.ClvCommissioning"],
        attributes: [{ 
          aggregationConfig: {
            aggregationFunction: AggregationFunction.Count,
          },
          type: "aggregation",
          path: "Id",
          name: "AppCount",
          caption: "AppCountCaption",
          dataValueType: DataValueType.Integer
        }],
        parameters: [{
            type: ModelParameterType.Filter,
            value: filters
        }]
      });
      console.log(records);
 
    }
  }

But instead of getting one record with one column "AppCount" containig 2, I get two records each containg "Id" and "AppCount" 

Ok< I found out the way. Looks like aggregationConfig isn't yet working well and functionConfig should be used instead. Here is working exampe.

  async ngOnChanges(changes: SimpleChanges) {
    console.log(changes);
 
    if (isGuid(changes["recordId"]?.currentValue)) {
 
      const dataModel = await Model.create("ClvObject");
      const filters = new FilterGroup();
      filters.addSchemaColumnFilterWithParameter(ComparisonType.Equal, "ClvProject", this.recordId, "currentFilter");
      const records = await dataModel.load({
//        attributes: ["Id", "ClvName", "ClvType", "ClvProject.ClvCommissioning"],
        attributes: [
          { 
            type: "function",
            path: "Id",
            name: "AppCount",
            caption: "AppCountCaption",
            dataValueType: DataValueType.Integer,
            functionConfig: {
              aggregation: AggregationFunction.Count,
              type: "aggregation",
              aggregationEval: "all",
            }
          }],
          parameters: [{
              type: ModelParameterType.Filter,
              value: filters
          }
        ]
      });
      console.log(records);
 
    }
  }

And result

Hello,

Could you please provide us with an example of the InsertQuery which is not working?

Hello,

Object name is ContactInFolder. If you try to catch an event ( when contact is added to a Static Folder) through a Business Process, the business Process wont trigger.

My question is how to catch the event of adding Contacts to a Static Folder?

Sasori Oshigaki,

Hello,

And is the option with the business process with the timer that starts each 1 hour and reads records that were created in the previous 1 hour suitable here? You can read data and process it in this way. 

Hello Oleg,

Thats the solution we are providing for the moment. Thought that it was better to do something more performant, that doesnt overload the system by executing each hour.

Thank you

Hello,

Besides the option that Oleg provided, another variant is to create a trigger in the DB because normal events like OnInserted won't work with INSERT INTO.

Hello Dmytro,

Thank you for your answer. Is there anywhere in the system a similiar implementation of a trigger, so that we can take it as a reference while we develop our own ?

Thank you,

Sasori

The system rarely uses database triggers so it would be hard to provide a suitable example.

It would be better to search trigger examples in the DB documentation

Thank you for your reply Dmytro!

Hello Dmytro,

I created this trigger

CREATE OR ALTER Trigger [dbo].[trigger_InsertContactInFolderAfterEvent] 
ON [dbo].[ContactInFolder]
AFTER INSERT AS
BEGIN
SET NOCOUNT ON;
DELETE cf
FROM ContactInFolder AS cf
inner join Contact as co on cf.ContactId=co.Id
where co.[Name] like 'V3%'
END;

It practically deletes Contacts (whose name start with V3),that are added in the Static Folders. Does this added trigger may cause problems for the overall system ?

Thank you Sasori

Dmytro Vovchenko,

Hi Dmytro

Any Update ?

All the best Sasori

Hello,

Here is the example.

function OwnerFilter() {
		return Terrasoft.createColumnIsNotNullFilter('[SysAdminUnit:Contact].Id');
	}

You can also find example it in BaseFiltersGenerateModule of NUI package.

Regards,

Dean

Hi,

Try SBLServiceId instead and also take a look in the database and check how is this column called there.

Best regards,

Oscar

Oscar Dylan,

Hi Oscar,

Thank you for the reply. How do I provide the value of Id of SBLService lookup? In the database, it is just 'Id' for the lookup id. I tried to access by doing SBLService.Id but it didn't help.

entity.SetColumnValue("SBLService.Id", debitCardData.ServiceId);

Roman Raj Bajracharya,

the value should be passed as an actual Id and also the column name shouldn't contain the . separator:

entity.SetColumnValue("SBLServiceId", Id here);

For example see this post https://community.creatio.com/questions/how-create-or-update-record-usi… where Dmytro provided an example of working with GUID columns.

Best regards,

Oscar

Oscar Dylan,

Hi Oscar,

Thank you very much. The record is now inserted. But I am having problem on the execution of a process that should occur when a record on this entity is created. This process is executing well when I create a new record from frontend(UI). Any ideas on this?

Hi Pedro,



Seems like in your snippet you are setting brackets '[]' in wrong order. Systems tries to add column 'SysUserInRole' to the query which does not exist. I assume that you need to change it for the following:

esq.addColumn("=[SysUserInRole:SysUser].SysRole", "Role")



To make it more clear I have put together an example:

 

var esq = this.Ext.create("Terrasoft.EntitySchemaQuery", {
					rootSchemaName: "SysAdminUnit"
				});
 
				esq.addColumn("Name");
				esq.addColumn("Contact.Name");
				esq.addColumn("=[SysUserInRole:SysRole].SysUser");
 
				var currentUserFilter = this.Terrasoft.createColumnFilterWithParameter(
						this.Terrasoft.ComparisonType.EQUAL,
						"=[SysUserInRole:SysRole].SysUser",
						Terrasoft.SysValue.CURRENT_USER.value);
 
				esq.filters.addItem(currentUserFilter);
 
				esq.getEntityCollection(function(response) {
					debugger;
				}, this);

This example generates following sql query:

 

exec sp_executesql N'
SELECT
	[SysAdminUnit].[Id] [Id],
	[SysAdminUnit].[Name] [Name],
	[Contact].[Name] [Contact.Name],
	[SysUserInRole].[SysUserId] [SysUserInRole.SysUserId],
	[SysUser].[Name] [SysUser.Name]
FROM
	[dbo].[SysAdminUnit] [SysAdminUnit] WITH(NOLOCK)
	LEFT OUTER JOIN [dbo].[Contact] [Contact] WITH(NOLOCK) ON ([Contact].[Id] = [SysAdminUnit].[ContactId])
	LEFT OUTER JOIN [dbo].[SysUserInRole] [SysUserInRole] WITH(NOLOCK) ON ([SysUserInRole].[SysRoleId] = [SysAdminUnit].[Id])
	LEFT OUTER JOIN [dbo].[SysAdminUnit] [SysUser] WITH(NOLOCK) ON ([SysUser].[Id] = [SysUserInRole].[SysUserId])
WHERE
	[SysUserInRole].[SysUserId] = @P1',N'@P1 uniqueidentifier',@P1='7F3B869F-34F3-4F20-AB4D-7480A5FDF647'

Hope it helps!



Regards,

Dmytro

Theoretically, it's possible to use bpm'online local .dll-s and create the connection. However, it's very hard and usually pointless. If you need to get data from bpm'online, please create a web service in bpm'online. The service should get the needed data and give it to the requested.

This is how to create a service. 

https://academy.bpmonline.com/documents/technic-sdk/7-14/creating-configuration-service

This is how to log in and call it

https://academy.bpmonline.com/documents/technic-sdk/7-14/executing-odata-queries-using-fiddler

Eugene Podkovka writes:

However, it's very hard and usually pointless.

I don't think it's useless.

I'm trying to achieve the same goal to increase our developers' productivity.

We usually use task script in the process designer to manipulate data using EntitySchemaQuery.

Each time, we have to update the process, run it and check the log or attach visual studio debugger (which is painful) to verify our code.

Imagine if we are able to get a UserConnection instance directly into a standalone app. we can test our functions faster and increase the overall productivity.

 @Eugene Podkovka: can you please help us!

Thank you

Mohamed

We can use this approach!

https://academy.creatio.com/documents/technic-sdk/7-15/developing-configuration-server-code-user-project

Thank you

Mohamed

Hi Toan Mai,

Try to name your Id column:

var idColumnName = esq.AddColumn("Id").Name;

then you can get in your foreach loop:

entity.GetTypedColumnValue<Guid>(idColumnName);

At least it worked for me.

Got the same thing yesterday : 

Replace :

esqDetails.AddColumn("Id");

 by 

esqDetails.PrimaryQueryColumn.IsAlwaysSelect = true;

And you can use the following to get the Id column : 

var recordId = details.PrimaryColumnValue;

Please debug the function in the developers console in google chrome. Once you find how it works, you'll be able to find how to pass the parameter. Additionally, you can search for the examples with the global search trough all sources in the google developers console.