I have a 'UsrPayment' detail with a column 'UsrPaymentPackage' that is a lookup.  I would like to filter only for the 'UsrPayment' detail records for which the 'UsrPaymentPackage' lookup column is empty.  The code I have tried is below, however returns records which have both a lookup value and which are empty:

const caseId = this.get("MasterRecordId");
const esq = this.Ext.create(Terrasoft.EntitySchemaQuery, {
	rootSchemaName: "UsrPayment"
});
esq.addColumn("Id");
esq.addColumn("UsrCase");
esq.addColumn("UsrPaymentPackage");
const esqFirstFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL,
		"UsrCase", caseId);
esq.filters.add("esqFirstFilter", esqFirstFilter);
const esqSecondFilter = Terrasoft.createColumnFilterWithParameter(
		Terrasoft.ComparisonType.EQUAL, "UsrPaymentPackage", null);				
//const esqSecondFilter = Terrasoft.createColumnFilterWithParameter(
//		Terrasoft.ComparisonType.IS_NULL, "UsrPaymentPackage");
//const esqSecondFilter = Terrasoft.createIsNullFilter("UsrPaymentPackage");
esq.filters.add("esqSecondFilter", esqSecondFilter);
esq.getEntityCollection(function(result) {
	debugger;
	if (result.success) {
		Terrasoft.each(result.collection.getItems(), function(entity) {
			debugger;
		}, this);
	}
}, this);

I'm probably missing something obvious here but if anyone can point it out.  Cheers,

Like 0

Like

1 comments

Hi Gareth,

What you need is a createIsNullFilter. Like this:

esq.filters.addItem(Terrasoft.createIsNullFilter(
    Ext.create("Terrasoft.ColumnExpression", {columnPath: "UsrPaymentPackage"})
));

Ryan

Show all comments

Could I ask for an example of the 'createExistsFilter(columnPath)' ESQ filter in use.  I'm not sure what 'columnPath' is.

 

Thanks,

Like 0

Like

5 comments

Hello,

 

The examples can be found here and you can also create an advanced filter in some section and review its body. For example here is the filter to list all accounts that have any activity connected to an account:

If we take a look at the network tab of the console to get the SelectQuery (to identify the needed query you can clear the network tab and then hit the "Accounts" section header in the top left corner or click the "Apply" button in the filter and as a result the query will be sent) that went to the database to retrieve data we can see the path to the column in the filters object:

comparisonType and filterType are standing for the "exists" function (can be seen in the Terrasoft.ComparisonType and Terrasoft.FilterType objects).

 

So if you need to retrieve a correct path to some column you can use this approach to find the correct path.

 

Best regards,

Oscar

Oscar Dylan,

Thanks for the reply, and the 'columnPath' tips.

 

It's actually how the  'createExistsFilter(columnPath)' ESQ filter is used that I'm not sure of.  If you could give me an example of this function being used that would be great.

Gareth Osler,

 

It's not widespread in the system, but there was one use case when it was needed and it's described in this community thread. One of the examples in the system: please see the historyMessageEsqApply method from the PortalMessageHistoryItemPage module:

filterInnerGroup.add("PortalMessageExists",
							esq.createExistsFilter("[PortalMessage:Id:RecordId].Id"))

Best regards,

Oscar

Gareth Osler,

Hi Gareth, the usage of it is just like any ESQ, it returns the list of records that match the condition(s). For the example Oscar provided, to expand on that further, lets say you want a list of all accounts where an activity exists (and then to go further) where the activity has a certain owner. In this case we create an exists filter (for accounts where activities exist) that has a sub-filter (for activities with a certain owner).

That code would look like this:

var esq = Ext.create("Terrasoft.EntitySchemaQuery", { 
	rootSchemaName: "Account" 
});
 
esq.addColumn("Id");
esq.addColumn("Name");
 
// create the sub filter for the condition inside the exists
var subFilters = Terrasoft.createFilterGroup();
subFilters.addItem(Terrasoft.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "Owner", "a6b4ea0c-420c-45ab-81e7-6e76c8cc15f7"));
 
// now create the exists filter and pass the sub filter conditions to it
// note, this second param of subFilters is optional if all you want is the exists condition
var existsFilter = Terrasoft.createExistsFilter("[Activity:Account].Id", subFilters)
esq.filters.addItem(existsFilter);
 
// now use the results how you would any ESQ
// in this case a list of accounts where an activity exists with a certain owner
esq.getEntityCollection(function(result) {
	if (result.success) {
		Terrasoft.each(result.collection.getItems(), function(entity) {
			var name = entity.values.Name;
			var id = entity.values.Id;
			console.log(name, id);
		}, this);
	}
}, this);

This is the same as this: 

Ryan

Thank you for that. I think I should now be able to shave a few machine cycles off by implementing the correct query operator.

 

I have to admit I didn't fully understand the advanced filter 'exists' operator until posting this question and finding this post which gives the SQL equivalent of an ESQ 'createExistsFilter(columnPath)' filter.  The Creatio exists operator essentially implements a SQL EXISTS operator with a correlated subquery.  The column path using a reverse connection sets up a join to another table with the option of adding more filters to the subquery.

Show all comments

Hi All,

I want to get value from a column "X" present in Parent Section "A" into the edit page of Child Section "B" which is present as detail in Section "A". Can anyone guide me how i can achieve it without using ESQ.

Like 0

Like

3 comments

You can do it by setting the "defaultValues" of the detail. I have an article on this here: https://customerfx.com/article/passing-values-from-a-page-to-a-new-reco…

Ryan

Hi Ryan,

I need to get the values dynamically i.e. if the value of field changes in parent section, i need to get the updated value.is there a way in which i can use this.get() for it?

rajat patidar,

You'd either need to do ESQ from the detail page to read the parent record, or you could do it as a process that fires when the parent is updated to update the detail row(s).

Ryan

Show all comments

Hi everyone,

how to use filter month and year Esq Server (EntitySchemaQuery)?

SELECT 
* 
FROM UsrTable 
WHERE 
MONTH(CreatedOn) = 1 AND YEAR(CreatedOn) = 2022

I found there is a function at https://academy.creatio.com/api/netcoreapi/7.17.0/#Terrasoft.Core~Terrasoft.Core.Entities.EntitySchemaQuery~CreateMonthFunction.html for get month and year.

https://prnt.sc/8brMGtYq3qhP

But how to use in filter?

esq.Filters.Add(esq.CreateFilterWithParameters(FilterComparisonType.Equal, "CreatedOn", Month));
esq.Filters.Add(esq.CreateFilterWithParameters(FilterComparisonType.Equal, "CreatedOn", Year));

Thank you.

Like 1

Like

1 comments
Best reply

Hello Romadan,

You can do filters like this using macros: 

// CreatedOn is the 1st month (January)
esq.Filters.Add(esq.CreateFilter(FilterComparisonType.Equal, "CreatedOn", EntitySchemaQueryMacrosType.Month, 1));
 
// CreatedOn is the first day of the month
esq.Filters.Add(esq.CreateFilter(FilterComparisonType.Equal, "CreatedOn", EntitySchemaQueryMacrosType.DayOfMonth, 1));
 
// CreatedOn is in the year 2022
esq.Filters.Add(esq.CreateFilter(FilterComparisonType.Equal, "CreatedOn", EntitySchemaQueryMacrosType.Year, 2022));

Ryan

Hello Romadan,

You can do filters like this using macros: 

// CreatedOn is the 1st month (January)
esq.Filters.Add(esq.CreateFilter(FilterComparisonType.Equal, "CreatedOn", EntitySchemaQueryMacrosType.Month, 1));
 
// CreatedOn is the first day of the month
esq.Filters.Add(esq.CreateFilter(FilterComparisonType.Equal, "CreatedOn", EntitySchemaQueryMacrosType.DayOfMonth, 1));
 
// CreatedOn is in the year 2022
esq.Filters.Add(esq.CreateFilter(FilterComparisonType.Equal, "CreatedOn", EntitySchemaQueryMacrosType.Year, 2022));

Ryan

Show all comments

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