Hello community,

 

I have a case where I need to get details of records which match the list of Ids. Ie

select * from table where id in [list of ids];

 

How do I implement this using server esq?

 

The following 

  var fileIdFilter = fileLinkESQ.CreateFilterWithParameters(FilterComparisonType.Equal,
                    "UsrFileId", FileIds.ToArray()); //FileIds is a list of Guid

throws the error : System.FormatException: Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).

Like 0

Like

2 comments

Shivani,

Here is a sample scenario,

Step 1: Take the complete table into a collection

var entitySchemaManager = UserConnection.GetSchemaManager("EntitySchemaManager") as EntitySchemaManager;
var contactESQ = new EntitySchemaQuery(entitySchemaManager,"Contact");
contactESQ.JoinRightState = Core.DB.QueryJoinRightLevel.Disabled;
contactESQ.AddAllSchemaColumns();
EntityCollection entities = contactESQ.GetEntityCollection(UserConnection);

 

Step 2: Parse through the collection via Linq
 

var count = (from elements in entities
             where elementsGetTypedColumValue<string>("FirstName") == "RequiredText"
             select elements).count();

 

Bhoobalan Palanivelu,

Thank you, but the table in question can hold 1000s of records at any given time. Bringing the whole table into memory and then performing LINQ may not be efficient. 

Also, I already know the list of records I need to fetch. Instead of
select * from table where id = A

select * from table where id = B

select * from table where id = C

 

I am looking for a query using "In" operator which will reduce the number of DB hits to 1 through

select * from table where id in (A,B,C).

 

This way I will have just the records I want to process in memory instead of whole table.

 

Show all comments

Hi community,

 

In the following example I have this case :

 

 

How can I do to, for example, get the name of the Customer (which obviously is an account) ? I don't want only the informations displayed in the box but I want to have access to all the informations of the "Account" section. How to do this ?

 

I've tried something like this :

 

 

But it doesn't work. When put "Id" instead of "Account.Name" it works and prints the Id of the opportunity in an information dialog box. 

 

I obviously want to get the name of the account that is directly related to the opportunity where the button is.

 

Any idea on how to do this ? This doesn't seem to be complicated, i've tried many things but nothing worked. 

 

P.S. I'm on the opportunitySectionV2 source code.

 

Many thanks,

Jonathan

Like 0

Like

0 comments
Show all comments

Hi everyone,

I have created a script task to create new records from a csv.

I am trying to implement a method to search the db for a matching record and then update it (in case it exists).

The second part (the 'else' part, it's working fine) 

Does anybody know how to filter and search a DB table? What am I getting wrong?

 

 

void saveLine(string line, int rowNumber){
    if(rowNumber!=0)
    {
        string lineFormatted = line.Replace("\"", "");
        string[] lineSplitted = lineFormatted.Split(',');
        var LRNMBL = lineSplitted[0];
        var LRRIGA = lineSplitted[1];
        var LRDTBL = lineSplitted[3];
        var LRCANA = lineSplitted[6];
        // Creation of query instance with "Contact" root schema. 
       
        // An EntitySchemaQuery instance that accesses the UsrTestFTPCall table of the database.
        var esq = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "UsrVedniteTestate");
        
       
        // Adding columns to the query.
        esq.AddColumn("UsrLRNMBL");
        esq.AddColumn("UsrLRRIGA");
        esq.AddColumn("UsrLRDTBL");
        esq.AddColumn("UsrLRCANA");

        // Filter the query data. according to LRNMBL and 
        var esqFirstFilter = esq.createColumnFilterWithParameters(Terrasoft.ComparisonType.EQUAL,"UsrLRNMBL", LRNMBL);
        var esqSecondFilter = esq.createColumnFilterWithParameters(Terrasoft.ComparisonType.EQUAL,"UsrLRRIGA", LRRIGA);
        
        esq.filters.logicalOperation = Terrasoft.LogicalOperatorType.AND;
        
        esq.filters.add("esqFirstFilter", esqFirstFilter);
        //esq.Filters.Add(esqSecondFilter);
          
      
        // Get the result of the query.
        var entities = esq.GetEntityCollection(UserConnection);
        // If the data is received.
        if (entities.Count > 0)//Record exists in the db --> update
        {
           var recordToUpd = entities[0];
           recordToUpd.SetColumnValue("UsrLRNMBL", LRNMBL);
           recordToUpd.SetColumnValue("UsrLRRIGA", LRRIGA);
           recordToUpd.SetColumnValue("UsrLRDTBL", LRDTBL);
           recordToUpd.SetColumnValue("UsrLRCANA", LRCANA);
        }
        else 
        {
            //the record doesn't exist --> Create one
               var TestObj = UserConnection.EntitySchemaManager.GetInstanceByName("UsrTestFTPCall").CreateEntity(UserConnection);
            //access the data position and assign it to its column
            TestObj.SetDefColumnValues();
            //Associate new column values to old Col values
            TestObj.SetColumnValue("UsrName", rowNumber.ToString());
            TestObj.SetColumnValue("UsrLRNMBL", LRNMBL);//riferimento testata di vendita
            TestObj.SetColumnValue("UsrLRRIGA", LRRIGA);//riferimento riga all'interno della stessa vendita
            TestObj.SetColumnValue("UsrLRDTBL", LRDTBL);
            TestObj.SetColumnValue("UsrLRCANA", LRCANA);
            TestObj.Save();
           }
    }
}
 

Like 0

Like

4 comments

Hello,

I don't see anywhere in the code where you're saving the updates made to the entity in recordToUpd. Try adding the following to have it save the values in the database:

recordToUpd.UpdateInDB();

Ryan

Hello Federica,

Yes, try to add the method that Ryan recommended you. It will help to resolve the issue.

Best Regards,
Tetiana

Hi Ryan,

 

The problem is to access and filter records in the db.

That part of the script ain't working.

Hi Tetiana Bakai,

The main issue atm lays in the filtering and db-access part.

Show all comments

Hi,

I am doing a 

var result = entity.Save();

for an object(entity). And I have a business process that works on the signal OnRecordAdded for this object. My concern is, is the entity.Save() synchronous with the business process that runs for the entity? In other words, is the value for the "result" variable assigned only after this business process completes?

Like 0

Like

1 comments

Hi,

 

The result is accessible in the context of the business process execution and once the process completes its execution the result becomes unavailable.

 

You can also test it by debugging process execution in Visual Studio. You need to activate development in the file system and connect to the app via VS. Then you need to set breakpoints in the process schema and check when the result parameter receives the value and when it loses the value.

 

Best regards,

Oscar

Show all comments

Hi all,

I am trying to insert a record to an entity using esq as follows in my web service.

[OperationContract]
        [WebInvoke(Method = "POST", RequestFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Bare,
        ResponseFormat = WebMessageFormat.Json)]
        public string InsertCaseESQPostForSATS(DebitCardDataForSATS debitCardData)
        {
            try
            {
                var caseSchema = SystemUserConnection.EntitySchemaManager.GetInstanceByName("SBLSRM");
                var entity = caseSchema.CreateEntity(SystemUserConnection);
                entity.SetColumnValue("SBLAccountNumber", debitCardData.AccountNo);
                entity.SetColumnValue("SBLService", debitCardData.ServiceId);
                
                entity.SetDefColumnValues();
                var result = entity.Save();
                return result.ToString();
            }
            catch(Exception ex)
            {
                return ex.Message;
            }
        }

 

Column 'SBLService' is a lookup column and I am having problem while inserting a record, says, "Value 'SBLService' was not found.". I have checked and confirmed that the object has the column and is published fine. Can somebody assist me on this? How can I set value for a lookup column?

 

 

Like 0

Like

4 comments
Best reply

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

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?

Show all comments

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

4 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.

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