Hi Community,

 

We've this situation where we need to execute a query to the database when we press the button that we created following this article https://academy.creatio.com/docs/developer/mobile_development/mobile_ap….

 

So far we manage to create the button itself and retrieve the record id when this button is pressed.

Ext.define("Terrasoft.controls.CustomRecordPanelItem", {
    extend: "Terrasoft.RecordPanelItem",
  	requires: ["Terrasoft.EntitySchemaQuery"],
    xtype: "cftestrecordpanelitem",
    config: {
        items: [
            {
                xtype: "container",
                layout: "hbox",
                items: [
                    {
                        xtype: "button",
                      	cls: 'btn',
                        id: "clickMeButton",
                      	text: '<div style="color: white">TERMINAR</div>',
                        flex: 5,
                    },
                ]
            }
        ]
    },
    initialize: function() {
        var clickMeButton = Ext.getCmp("clickMeButton");
        clickMeButton.element.on("tap", this.onClickMeButtonClick, this);
    },
    onClickMeButtonClick: function() {
        var record = this.getRecord();
      	var servicoId= record.data.imdServico.data.Id;
      	var activitycategoryId = '82e0f151-a00a-46c5-8916-b87992266de6';
 
      	var esq = this.Ext.create("Terrasoft.EntitySchemaQuery", {
            rootSchemaName: "Activity"
        });
      	esq.addColumn("imdServicos.Id", "ServicoId");
      	esq.addColumn("ActivityCategory.Id", "TipoCheckListId");
 
      	var esqFirstFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "imdServicos.Id", servicoId);
      	var esqSecondFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "ActivityCategory.Id", activitycategoryId);
 
      	esq.filters.logicalOperation = Terrasoft.LogicalOperatorType.AND;
 
      	esq.filters.add("esqFirstFilter", esqFirstFilter);
		esq.filters.add("esqSecondFilter", esqSecondFilter);
 
        esq.getEntityCollection(function (result) {
            if (result.success) {
              	window.console.log(result);	
                /*result.collection.each(function (item) {
 
                });*/
            }
  		}, this);
 
        Terrasoft.MessageBox.showMessage(record);
    }
});

We are having problems trying to execute the Ext.create("Terrasoft.EntitySchemaQuery") because we think that some dependencies are missing.

 

Any idea on how to solve this problem?

 

Thanks in Advance.

 

Best Regards,

Pedro Pinheiro

Like 0

Like

3 comments

Hi Pedro,

 

Can you send the debug result using the mobile application emulator? What is the error?

 

Best regards,

Oscar

Hi Oscar Dylan,

 

Thank you for your response. The following image shows the error that we are getting when pressing the button.

 

 

var esq = this.Ext.create("Terrasoft.EntitySchemaQuery", {
            rootSchemaName: "Activity"
        });

We think that the error might be related to the lack of dependencies.

 

Best Regards,

Pedro Pinheiro

Hello Pedro. Did you manage to get the result of the queries?

Show all comments

Dear Community,

 

I am using EntityEventListener to update a connected record of a records before deleting it using the following method:

public override void OnDeleting(object sender, EntityBeforeEventArgs e)

But when deleting the record it says there is connected records, I think this is linked to the count I am doing to check the number of records that match a condition.

 

Here the full code :

[EntityEventListener(SchemaName = "Activity")]
public class ActivityEventListener : BaseEntityEventListener
{
 
        public override void OnDeleting(object sender, EntityBeforeEventArgs e)
        {
                base.OnDeleting(sender, e);
                Entity activity = (Entity)sender;
                var userConnection = activity.UserConnection;
	        var accountId = activity.GetTypedColumnValue<Guid>("AccountId");
                var activityId = activity.GetTypedColumnValue<Guid>("Id");
                int count = CountNumberOfActivityRdv(accountId, userConnection);
	        Guid ContactClientOuRepresentantNon = new Guid("f550b45d-093e-43ba-bdd1-bc0bd43c8e16");
 
	        if (count > 0)
	        {
                        var update = new Update(userConnection, "Account")
                            .Set("ContactClientOuRepresentantId", Column.Parameter(ContactClientOuRepresentantNon))
                            .Where ("Id").IsEqual(activityId.ToString());
                        update.Execute();
	        }
        }
 
        public int CountNumberOfActivityRdv(Guid accountId, UserConnection userconnection)
        {
	        int count = 0;
                var select = new Select(userconnection)
                        .Column(Func.Count("Id"))
                    	.From("Activity")
                        .Where("ActivityCategoryId").IsEqual("42c74c49-58e6-df11-971b-001d60e938c6")
                	.And("AccountId").IsEqual(accountId.ToString()) as Select;
 
                count = select.ExecuteScalar<int>();
 
	        return count;
        }

 

Do I have to clear the cache or set the select to null ?

Like 0

Like

8 comments
Best reply

Oscar Dylan,

 

I've done it differently, when deleting the activity I send a message with the accountId linked to the activity deleted, and I catch it in the account edit page to do the necessaries processing. I had no problem with this method. Here is the code I've made for the sake of it.

onActivityDeletedReceived: function(scope, message) {
                var sender = message &amp;&amp; message.Header.Sender;
                // make sure the message received is the one you sent
                if (sender === "ActivityDeleted") {
                 	 // if you sent some data with the message you can get it from the message Body
                    var MessageText = message.Body;
					var array = [];
                    var esq = Ext.create("Terrasoft.EntitySchemaQuery", {
                    rootSchemaName: "Activity"
                    });
                    // Add column with account name that refers to given account.
                    esq.addColumn("Id", "Id");
                    esq.addColumn("Account.Id", "AccountId");
 
                  	esq.filters.logicalOperation = Terrasoft.LogicalOperatorType.AND;
                  	var esqAccountFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "Account.Id", MessageText);
                  	var esqRdvFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "ActivityCategory.Id", "42c74c49-58e6-df11-971b-001d60e938c6");
					var esqRealiseFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "Status.Id", "4bdbb88f-58e6-df11-971b-001d60e938c6");
 
 
                  	esq.filters.add("esqAccountFilter", esqAccountFilter);
                  	esq.filters.add("esqRdvFilter", esqRdvFilter);
					esq.filters.add("esqRealiseFilter", esqRealiseFilter);
                    // Get entire record collection
                    esq.getEntityCollection(function (result) {
                        if (!result.success) {
                            // error processing/logging, for example
                            this.showInformationDialog("Data query error");
                            return;
                        }
                        result.collection.each(function (item) {
                           array.push(item.get("AccountId"));
                        });
                      	if(array.length === 0)
                        {
							this.set("ContactClientOuRepresentant", {value:"f550b45d-093e-43ba-bdd1-bc0bd43c8e16" , displayValue:"Non"});
							this.save({isSilent:true});
                        }
                    }, this);
				}
            },

Regards,

 

Arthur

Hello Arthur,

 

And which connected records does the system show you? Also what happens when you drop the select result to null?

 

Best regards,

Oscar

Oscar Dylan,

 

When the screen of the connected records pop-up and I click to check the records there is nothing.

 

Also, when I set the result to null the same thing happen.

 

Regards,

 

Arthur

Arthur Hertz,

 

What is the result of the 

 

select top 5 * from MultiDeleteQueue

order by CreatedOn desc

 

right after trying to delete a record (message column values needed)? Also what error message do you receive in the application logs?

 

Best regards,

Oscar

Oscar Dylan,

 

I've put the csv file of the request in the post and also in the application logs in the "MultiDelete.log" file it's telling me this :

 

Terrasoft.Common.DbOperationException: L'instruction DELETE est en conflit avec la contrainte SAME TABLE REFERENCE "FKPkYRMonMU4O22bg1UtAWBnc3Y8". Le conflit s'est produit dans la base de données "MetropoleGestion", table "dbo.Activity", column 'ActivityConnectionId'.

 

File link : https://linkintouch-my.sharepoint.com/:x:/g/personal/ahertz_linkintouch_fr/ETEWcqG_sjxBtWFBLG7w0N8B5kXRMSIcl8gmZNvtBY-UKw?e=GhYepw

 

Regards,

 

Arthur

Arthur Hertz,

 

And can you please perform this select:

 

select ActivityConnectionId from Activity where Id = 'Id of the activity you delete'

 

and

 

select Id from Activity where ActivityConnectionId = 'Id of the activity you delete'

 

?

 

Seems that this column is not empty for some activity and uses its value as a reference for the activity you delete. 

 

Best regards,

Oscar 

Oscar Dylan,

 

 

The first request ActivityConnectionId is equal to NULL and for the second one there are no records.

 

Regards,

 

Arthur

Arthur Hertz,

 

Then you need to connect your local app to the IDE and debug the execution of deletion in the Visual Studio. There is something with the delete that is provoked by the code you've developed and you need to debug this logic.

 

Best regards,

Oscar

Oscar Dylan,

 

I've done it differently, when deleting the activity I send a message with the accountId linked to the activity deleted, and I catch it in the account edit page to do the necessaries processing. I had no problem with this method. Here is the code I've made for the sake of it.

onActivityDeletedReceived: function(scope, message) {
                var sender = message &amp;&amp; message.Header.Sender;
                // make sure the message received is the one you sent
                if (sender === "ActivityDeleted") {
                 	 // if you sent some data with the message you can get it from the message Body
                    var MessageText = message.Body;
					var array = [];
                    var esq = Ext.create("Terrasoft.EntitySchemaQuery", {
                    rootSchemaName: "Activity"
                    });
                    // Add column with account name that refers to given account.
                    esq.addColumn("Id", "Id");
                    esq.addColumn("Account.Id", "AccountId");
 
                  	esq.filters.logicalOperation = Terrasoft.LogicalOperatorType.AND;
                  	var esqAccountFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "Account.Id", MessageText);
                  	var esqRdvFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "ActivityCategory.Id", "42c74c49-58e6-df11-971b-001d60e938c6");
					var esqRealiseFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "Status.Id", "4bdbb88f-58e6-df11-971b-001d60e938c6");
 
 
                  	esq.filters.add("esqAccountFilter", esqAccountFilter);
                  	esq.filters.add("esqRdvFilter", esqRdvFilter);
					esq.filters.add("esqRealiseFilter", esqRealiseFilter);
                    // Get entire record collection
                    esq.getEntityCollection(function (result) {
                        if (!result.success) {
                            // error processing/logging, for example
                            this.showInformationDialog("Data query error");
                            return;
                        }
                        result.collection.each(function (item) {
                           array.push(item.get("AccountId"));
                        });
                      	if(array.length === 0)
                        {
							this.set("ContactClientOuRepresentant", {value:"f550b45d-093e-43ba-bdd1-bc0bd43c8e16" , displayValue:"Non"});
							this.save({isSilent:true});
                        }
                    }, this);
				}
            },

Regards,

 

Arthur

Show all comments

Hello community,

 

There are numerous use cases where Creatio filters for some data before showing on the UI. Few examples below - 

  1. Writing ESQ from JavaScript where we filter an object for only select records by adding filter groups. 
  2. User applying of a filter in a section list page or a detail. 
  3. When we add a detail to a Section record page, we map a column from the detail to a column in a section record. This is how Creatio filters for only the detail records belonging to that section record and displays the records subsequently. 

Few questions below which will help us explain Creatio better with our customers- 

  1. At which layer does Creatio execute these filters? Is it at the Application layer or at the Database layer? It is unlikely that it is at the App layer. Can you confirm that these happen at the DB layer and only the filtered records are fetched to the App layer and subsequently passed back to the UI?
  2. I am guessing that the Creatio ORM would aggregate all of the filters and frame a query which filters for the required data and then execute this at the DB layer? Excuse the over-simplification. Can you confirm and share a few more details on how this done? This would give us insight into the Product architecture and help us understand the product better.

If these insights are already available in the Documentation, Request you to share the link. Thanks in advance!

Like 1

Like

5 comments

Hello,

Yes, all filter conditions are added as conditions for queries and are performed at the database layer. You can see this if you install Creatio locally and run SQL profiler. You'll notice as an ESQ gets executed that ultimately this is translated to a SQL query with the filter conditions in the where clause.

Additionally, as a related side topic to mention to your original question which might be of interest, you can also create SQL views in the database containing aggregates/joins/unions/ranks/partions/etc and create an entity/object for that view. These objects can then be used normally in section lists, details, dashboards, reports/printables, and even read via OData/DataService APIs etc. This is a fantastic way to create what would be a complex or time consuming query and make the results readily available in the client.

Hope this helps with your question.

Ryan

Hello Shrikanth,

 

We can confirm that filtering conditions are configured as conditions for queries and they are being performed on the DB layer, but not the application layer.

 

We will ask the responsible team to add more information relateable to this kind of topic to the Academy. At the same time, please check the articles included in this section, they should be useful for further development operations:

https://academy.creatio.com/docs/developer/back-end_development

 

Thank you Ryan for your help!

 

Best regards,

Roman

Ryan Farley,

Thank you Ryan. Yes, I was aware of the views and similarly noticed usage of Stored procedures in the community. This prompted my question. 

Roman Rak,

Thank you Roman for the answer.

Ryan Farley,

 

If I can quote:

you can also create SQL views in the database containing aggregates/joins/unions/ranks/partions/etc and create an entity/object for that view

If I were to create a SQL view, how would I create an entity/object of the view?  Thanks,

 

[Edit]

I think I have the answer here Creating View Type Object in Creatio | Community Creatio

Show all comments

Hi all,

I'm trying to filter some city data by city name

The following line will not match because it's not case-insensitive : 

esqFilter = esq.CreateFilterWithParameters(FilterComparisonType.Equal, "City", "PARIS");

Is there a way to ignore case so it works for the "Paris" value ?

Like 0

Like

1 comments

Dear Jerome, 

 

ESQ EQUAL comparison is not case sensitive so you are not getting a result either because you have other filters or there are no records with City column equals to Paris. 

As an alternative you can try using Select class: 

https://academy.creatio.com/documents/technic-sdk/7-16/retrieving-data-database

 

Best regards, 

Dennis 

Show all comments

When I try to implement esq and multiple filters, only last added filter will work. Rest will be kind of overrided by the next filter.

For example, If I use 3 filters - 


esq.filters.add(esqFirstFilter);
esq.filters.add(esqSecondFilter);
esq.filters.add(esqThirdFilter);

I am getting results on the basis of last filter.

If I use 2 filters - 

 

esq.filters.add(esqFirstFilter);
esq.filters.add(esqSecondFilter);

 

Then last filter esqSecondFilter will filter the records not the first one. And I need both filters.

Is there anything need to be added to this code snippet - before or after.

 

Like 0

Like

1 comments

Dear Ramnath,

 

In order to resolve the issue please use the following code for adding filters:

 

esq.filters.add("esqFirstFilter", esqFirstFilter);

esq.filters.add("esqSecondFilter", esqSecondFilter);

 

Please find more information about esq filters in the article by the link below:

 

https://academy.creatio.com/documents/technic-sdk/7-16/entityschemaquery-class-filters-handling

 

Best regards,

Norton

Show all comments

Hi Community,

I need to have a validation on my edit page and this validation will involve complex sql query, now please help me how i can convert below sql query in esq client side.

The query below involves some joins and group by function

select a.Table2Id, b.Name, a.CreatedOn from Table1 a 

inner join Table2 b on a.Table2Id = b.Id 

join ( 

select b.Name as Name, Max(a.CreatedOn) as CreatedOn from Table1 a 

inner join Table2 b on a.Table2Id = b.Id  

group by b.Name ) 

c on c.Name = b.Name and c.CreatedOn = a.CreatedOn

 

Like 0

Like

5 comments

If you're not able to come up with an ESQ solution for this (I've never tried a groupby with ESQ, not sure if it can do those - nothing mentioned in the docs), often when I have a complex SQL statement, I instead put it in an configuration web service and do the query using the Select class or a direct SQL statement, then call/consume the service from the client side code.

If you go that route, here's some articles on those topics:

Select class: https://academy.creatio.com/documents/technic-sdk/7-15/retrieving-infor…

Direct SQL: https://customerfx.com/article/executing-direct-sql-statements-in-a-pro…

Creating and calling configuration services: https://customerfx.com/article/creating-a-web-service-and-consuming-it-…

Ryan

I'm not 100% on that, but I'm fairly sure that you can use an aggregration column for the nested SQL statement, which handles the group by for you. You could then run the first 2 lines as a separate ESQ inside the first ESQ callback, and then do the rest of the logic separately in JS.

Dear Fulgen, 

 

Unfortunately, there is no way to create a complex query with subqueries via client ESQ.

You can create a business process that will perform the request to the database, fetch all needed data, process it and display it on the page.

Hi Ryan Farley,



When using CustomQuery, what library should I declare? I am getting "The type or namespace name CustomQuery could not be fould".



Thank you

Fulgen Ninofranco,

CustomQuery is located in Terrasoft.Core.DB

Ryan

Show all comments

Dear mates,

How can i format date in Creation source code ?

When i do this:

var esq = this.Ext.create("Terrasoft.EntitySchemaQuery", {
   rootSchemaName: "Order"
});
 
esq.addColumn("Date");
 
esq.getEntityCollection( function(result) {
    result.collection.each ( function (order) {
       this.console.log(Date);
    });
});

the system return me:

function Date() { [native code] }

What the way to format Date ?

Thanks,

Nicolas

 

Like 0

Like

2 comments

The Date can be formatted via default js functions. Please feel free to find out more about working with Date in JavaScript in the article by the link below:

https://css-tricks.com/everything-you-need-to-know-about-date-in-javascript/

Also there are some examples of working with Date in ESQ in the article on academy :

https://academy.creatio.com/documents/technic-sdk/7-15/entityschemaquery-class-filters-handling

 

You can format the date based on the current users culture settings using the following:

var formattedDateString = myDate.toLocaleDateString(Terrasoft.currentUserCultureName);

Ryan

Show all comments

Hi,

I've this query and i would like to translate to ESQ, so far i managed to create this:

but i'm having this error: "uncaught exception: Collection item with name SysUserInRole not found".

I would like to know, why i'm getting this error? Is there a way to fix it? And if there is a better way to do that?

Best regards,

Pedro Pinheiro.

 

Like 0

Like

1 comments

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

Show all comments

Am I able to use esq to get detail data on a page? Ideally I want to be able to get data from the detail on the parent page, but there will be multiple rows on a detail and I would like to loop through all of them and do calculations with it. I'm not sure how I would be able to get the detail data, there's no lookup connected to it, just the object column and detail column create a relationship in the page designer. I would like to do this with client-side code thanks.

Like 0

Like

1 comments

Hi, How am I able to return a value of a Lookup (the Name) using ESQ, JS? I have defined the Lookup as an attribute: "DevCertPublicationFrequency": { DataValueType: Terrasoft.DataValueType.LOOKUP, }, I have the following function: getLookupValue: function (DevCertPublicationFrequency, safe) { var column = this.columns[DevCertPublicationFrequency]; if (column && (column.isLookup || true)) { var columnValue = this.get(DevCertPublicationFrequency); this.set("DevCertPublicationFrequency", columnValue); } }, When the record is saved I want to check the value of the Lookup and call a function if the Lookup value selected prior to the save = 'Daily' Save: function() { this.callParent(arguments); this.getLookupValue(); var textmessage = "Verification will be performed"; //if periodical edition = daily then perform verification var DevCertPubFrequency = this.get("DevCertPublicationFrequency"); if (DevCertPubFrequency === "Daily") { Terrasoft.showInformation(textmessage); this.verification(); } },

Like 0

Like

1 comments

It's pretty easy to get a value from a lookup attribute using this.get("NameOfLookupAttribute"). Please debug your js code using the article below 

https://academy.bpmonline.com/documents/technic-sdk/7-13/client-code-debugging 

Show all comments