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 && 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,

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

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 

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

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

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

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

Solved using filters on esq

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 

Try this

var opportunityCarQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager,"OpportunityCar");
opportunityCarQuery.AddAllSchemaColumns();
 
var filter = opportunityCarQuery.CreateFilterWithParameters(FilterComparisonType.Equal, "Car", (Guid)carNodeId);
opportunityCarQuery.Filters.Add(filter);
 
var opportunityCarEntities = opportunityCarQuery.GetEntityCollection(UserConnection);
 
foreach (var opportunityCarEntity in opportunityCarEntities)
{
	var theId = opportunityCarEntity.GetTypedColumnValue<Guid>("Id");
}