Question
Hi.

Our intention is create a console app (C#) using EntitySchemaQuery  that query an entity (like Account or Contact) and gets its rows to populate a local table. 

We are trying to connect our bpmonline using this example:

source: https://www.bpmonline.cz/bpmonlinesdken/UsingEntitySchemaQuery.html

//////////
// Creating a query instance, adding columns and a data source in the query.
Select selectQuery = new Select(UserConnection)
                    .Column("Id")
                    .Column("Name")
                    .From("Contact");
// Executing a database query and getting the resulting dataset.
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection())
{
    using (IDataReader reader = selectQuery.ExecuteReader(dbExecutor))
    {
        while (reader.Read())
        {
            // Handling the query results.
        }
    }
} 

/////////////

The problem is we do not know how to create the connection string (UserConnection) to our cloud services, https://mycompany.bpmonline.com.

 

How to create this UserConnection? Someone has an example or may guide us?

 

 

Like

1 comments

Theoretically, it's possible to use bpm'online local .dll-s and create the connection. However, it's very hard and usually pointless. If you need to get data from bpm'online, please create a web service in bpm'online. The service should get the needed data and give it to the requested.

This is how to create a service. 

https://academy.bpmonline.com/documents/technic-sdk/7-14/creating-configuration-service

This is how to log in and call it

https://academy.bpmonline.com/documents/technic-sdk/7-14/executing-odata-queries-using-fiddler

Log in or register to comment
Question

Hi,

       I use EntitySchemaQuery to get Entity, but I can't get primary key value of result entity. The exception said: not found "Id" (something likes this)

EntitySchemaQuery esqDetails = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "UsrExportDetails");
            esqDetails.AddColumn("Id");
            esqDetails.AddColumn("UsrCodeLevel1");
            esqDetails.Filters.Add(esqDetails.CreateFilterWithParameters(FilterComparisonType.Equal, "UsrExport", id));
            var exportDetails = esqDetails.GetEntityCollection(UserConnection);
            
            foreach    (var details in exportDetails)
            {
                var refId = details.GetTypedColumnValue<Guid>("Id");
                var qrCodeRefId = details.GetTypedColumnValue<Guid>("UsrCodeLevel1Id");
                
                this.Push(refId, qrCodeRefId, name, address, tel, fax);
            }

Like

2 comments

Hi Toan Mai,

Try to name your Id column:

var idColumnName = esq.AddColumn("Id").Name;

then you can get in your foreach loop:

entity.GetTypedColumnValue<Guid>(idColumnName);

At least it worked for me.

Got the same thing yesterday : 

Replace :

esqDetails.AddColumn("Id");

 by 

esqDetails.PrimaryQueryColumn.IsAlwaysSelect = true;

And you can use the following to get the Id column : 

var recordId = details.PrimaryColumnValue;

 

Log in or register to comment
Question

How can I pass a callback function to GetEntityCollection function of ESQ? 

Like

1 comments

Please debug the function in the developers console in google chrome. Once you find how it works, you'll be able to find how to pass the parameter. Additionally, you can search for the examples with the global search trough all sources in the google developers console.

Log in or register to comment
Question

Can I know how I can create insert queries and execute those using esq on the detail? 

Like

7 comments

You can use the InsertQuery client-side. Here is a sample: 

var insert = Ext.create("Terrasoft.InsertQuery", {
	rootSchemaName: "UsrMyEntity"
});
 
insert.setParameterValue("UsrMyParentId", this.get("Id"), Terrasoft.DataValueType.GUID);
insert.setParameterValue("UsrMyDateProperty", new Date(), Terrasoft.DataValueType.DATE);
 
insert.execute(function() {
	// do any refreshing if needed here
}, this);

Ryan

Ryan Farley,

Great! this helps. Is there a way I can do bulk insertion of multiple records at 1 single time? Any documentation reference would be really helpful. 

Thank you

 

kumar,

To do any sort of insertions of multiple records, I would create a configuration service and call that instead from the client. It would be far more efficient that way.

Ryan

Ryan Farley,

Hi guys, that is helping me in another implementation. Is it also possible to use it as updateQuery?

Thanks.

Danilo Lage,

Yes, an UpateQuery is similar, see the following sample:

var update = Ext.create("Terrasoft.UpdateQuery", {
	rootSchemaName: "Contact"
});
 
update.filters.add("IdFilter", update.createColumnFilterWithParameter(
	Terrasoft.ComparisonType.EQUAL, "Contact", this.get("UsrContactId")));
 
update.setParameterValue("UsrSomeField", "value", Terrasoft.DataValueType.TEXT);
update.setParameterValue("UsrSomeId", someId, Terrasoft.DataValueType.GUID);
 
update.execute(function() {
	// do any needed refreshing here etc
}, this);

Ryan

Danilo Lage,

kumar,

Please see the following articles, there you can find instructions on building update, insert, delete and batch queries. 

https://academy.bpmonline.com/documents/technic-sdk/7-13/dataservice-ad…

https://academy.bpmonline.com/documents/technic-sdk/7-13/dataservice-up…

https://academy.bpmonline.com/documents/technic-sdk/7-13/dataservice-ba…

https://academy.bpmonline.com/documents/technic-sdk/7-13/creating-detai…

The examples are done using C# language in case you decide to create a web service. Also there is a JavaScript example. They are particularly the same in the syntax. There also you can find examples of cases using insert, update, delete and batch queries.

Hope you will find it helpful.

Regards,

Anastasia

Ryan Farley,

Thanks, Ryan!

 

Anastasia Botezat,

Great, Anastasia...

Log in or register to comment
Question

How to Create a new filter “Attention” in the Activity section grid page (near Owner and Date filters). When turned on, only overdue activities and high priority 
 today activities should be displayed. Filter should use current user time zone.

Please Help.

Like

4 comments

Hello,

It is better to use advanced filter and apply its settings like on the screenshot http://prntscr.com/kzo5os and save it as a folder and name it for example "Overdue activities". As a result when you open this folder you will get the list of activities that are overdue due to filtering conditions. Or you can create a dashboard that will represent the list of overdue activities due to filtering conditions specified.

Best regards,

Oscar

Oscar Dylan,

Thank you It was helpful , but I need to know how to create a new custom filter (check box) in the Activity section grid page (near Owner and Date filters) , when I check it , filter the grid  (coding )

mohamad abdallah,

Here is the academy article that describes the process of adding new buttons to the section. It also contains the list of actions that can be performed in the section. This academy article describes how to add the filter block in the section. Hope it helps!

Hi, 

you can go through the link given below. Similarly you can do it in activity page.

http://agiliztech.com/2019/03/25/bpmonline-custom-section-filters/

Log in or register to comment
Question

How to calculate the  sum of a user’s activity duration in minutes, for all activities in the last 60 days , and set the value in a field (entity schema query)

Like

1 comments

Hello,

It can be achieved with the help of the business process. First of all you should add the "boolean" type field on the contact's page and on the activity page and name it for example "Is processed". After that you should create a business process with the "Simple" start event that will read "Contact" object, after that there should be "Read data" element that will read "Activity" object with the data read mode set as "Calculate function" and specify the function value as "Sum" by column "Duration (minutes)" and filter activities by field "Owner" that should have Id from the first "Read data" element that reads "Contacts" object. After that "Modified data" element should be added for the "Activity" object that should change the value of boolean field to "True". Then should go "Modify data" element that will modify data in contact section and will set value of the previous element to the specified field and that will change the value of the boolean field added at the beginning to "True" value and this element should be also interconnected via activity Id in the "How to filter records" block. After that you should make a cycle of this process and it should look like on the screenshot http://prntscr.com/kyx20n (conditional flow 2 should satisfy the condition that boolean field for "Contact" object is "False", conditional flow 4 should satisfy the condition that boolean field for "Activity" object is "False" so to read all activities and all contacts). Don't forget to filter activities by Start and End dates so to get all activities for the last 60 days.

Best regards,

Oscar

Log in or register to comment
Question

I found that query method Execute can be run with DBExecutor passed as parameter and without it. Do you know what's the difference? I didn't find documentation explaining this.

Like

10 comments

Dear Carlos,

There is no Execute method in EntitySchemaQuery class. Could you write here a code example to show the context of your question?

I meant the literal Query class :) The one from which for example Delete inherits. Example code 1:
 

var query = new Delete(userConnection);
query.From("UsrBook");
query.Where("UsrAuthor").IsEqual(Column.Parameter(authorId));
query.Execute();

Example code 2:
 

using (DBExecutor exec = userConnection.EnsureDBConnection())
{
    var query = new Delete(userConnection);
    query.From("UsrBook");
    query.Where("UsrAuthor").IsEqual(Column.Parameter(authorId));
    query.Execute(exec);
}

 

Here you will find Query class documentation. Click on Execute method to see all overloads and theirs description.

Peter Vdovukhin,

I saw that already but it doesn't explain much. What's exactly the difference between:

 

Executes the query text and returns the number of rows affected when executing the query.

and:

Executes the query text, using the specified DBExecutor instance, and returns the number of rows affected when executing the query.

?

What is this DBExecutor class for if it's not necessary to execute queries?

DBExecutor is a class that manages connection to the database. It is called always when you work with queries. By default every time you execute ESQ query, Select, Delete or any other query a new instance of this class is created. You can pass an existing DBExecutor instance just for optimization in order not to create an extra object or for using transactions. For example, if you need to run 3 queries in a row, you can create one DBExecutor and use it again in all these quires. How to create a new instance of this class and use it correctly you can read here

Ok, thanks, that clears things up :)

Welcome. As a bonus take an implementation of these two methods in a Query class:

public virtual int Execute() {
            string sqlText = GetSqlText();
            using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
                return HasParameters ? dbExecutor.Execute(sqlText, Parameters) :
                    dbExecutor.Execute(sqlText);
            }
        }

public virtual int Execute(DBExecutor dbExecutor) {
            string sqlText = GetSqlText();
            return HasParameters ? dbExecutor.Execute(sqlText, Parameters) :
                dbExecutor.Execute(sqlText);
        }

Peter Vdovukhin,

So I want to add couple of objects to database using EntitySchemaQuery and I want to reuse the same database connection. Is what I'm doing here correct?

using (dbExecutor = userConnection.EnsureDBConnection())
{
    var schema = dbExecutor.UserConnection.EntitySchemaManager.GetInstanceByName("UsrExampleSchema");
    Entity entity1 = schema.CreateEntity(dbExecutor.UserConnection);
    entity1.SetDefColumnValues();
    entity1.CreateInsert().Execute(dbExecutor);
 
    Entity entity2 = schema.CreateEntity(dbExecutor.UserConnection);
    entity2.SetDefColumnValues();
    entity2.CreateInsert().Execute(dbExecutor);
}

Or is it possible to avoid using CreateInsert method and use DBExecutor directly with EntitySchemaQuery?

Dear Carlos,

Your code seems too complex and I cannot predict if it works. You can try it to check the correctness of this code. The one thing I can tell: you cannot directly use Execute method on Entity.

 Please, be advised that in 99.9% cases you do not need a dbExecutor class. First, when using dbExecutor directly with Select, Update, Create, Delete classes you bypass any events such as running business processes or validation at application level so you may finish with incorrect object state in the DB. You also bypass rights check because you execute a direct sql query.

Second, the overhead of using every time a new dbExecutor is very small

In your case just use entity.save() without dealing with dbExecutor at all.
So after refactoring your code will look like this:
 

var schema = UserConnection.EntitySchemaManager.GetInstanceByName("UsrExampleSchema");
    Entity entity1 = schema.CreateEntity(UserConnection);
    entity1.SetDefColumnValues();
    entity1.Save();
 
    Entity entity2 = schema.CreateEntity(UserConnection);
    entity2.SetDefColumnValues();
    entity2.Save();

 

You do need dbExecutor when work only with Update, Select, Create, Delete classes. It is mandatory.

Peter Vdovukhin,

Ok, thanks.

Log in or register to comment
Question

dear my friend

i create query with EntitySchemaQuery. can i order root schema with colomn on root schema

getModulePrintFormsESQ: function() {
			var entitySchemaName = this.getEntitySchemaName();
			var esq = Ext.create("Terrasoft.EntitySchemaQuery", {
				rootSchema: SysModuleReport,
				isDistinct: true,
				rowViewModelClassName: "Terrasoft.BasePrintFormViewModel"
			});

thx before

Chairul Anwar

 

Like

2 comments

Yes, you can.
Just push the result columns to the esq, and set sorting by specifying the index of the column and the sort order

var entitySchemaQuery = Ext.create("Terrasoft.EntitySchemaQuery", {
	rootSchemaName: entitySchemaName
});
 
var modifiedOnColumn = entitySchemaQuery.addColumn("ModifiedOn", "ModifiedOn");
 
modifiedOnColumn.orderPosition = 0;
modifiedOnColumn.orderDirection = Terrasoft.OrderDirection.ASC;

 

Anastasia Botezat,

thanks so much anastasia. i can order that page.

Log in or register to comment