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,

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

Thanks,

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.

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.

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

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

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();

           }

    }

}

 

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?

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?

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