Hi Maria,

You need to use ESQ to get the data from some record in the detail related to the section. In this case you need to get the Id of the current main record (where the detail is located) by using for example:

var currentRecordId = this.get("Id");

and then in the ESQ filter you need to specify this Id in the filtration ("CollateralsInProduct" should be replaced with your detail object code, "CollateralValue" should be replaced with the code of the column in the "CollateralsInProduct" object where the actual values are stored, "ProductId" should be replaced with the code of the column that connects you detail to the main product record):

var esq = Ext.create("Terrasoft.EntitySchemaQuery", {
    rootSchemaName: "CollateralsInProduct"
});
esq.addColumn("CollateralValue");
var esqFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "ProductId", currentRecordId);
esq.getEntityCollection(function (result) {
    if (result.success) {
        result.collection.each(function (item) {
            /* Process the collection elements. */
        });
    }
}, this);

As for more documentation on basic methods like asyncValidate - unfortunately we have none suitable one where all the methods are described. I will ask our Academy team to create this article where all the available methods and properties are described so anyone could easily search data they need, thank you for this suggestion.

Hi Maria,

I have an article outlining a simple place to start with EntitySchemaQuery (to query the database from client-side code) here: https://customerfx.com/article/an-introduction-to-performing-client-sid…

Also an article on using asyncValidate here: https://customerfx.com/article/asynchonous-validation-on-pages-in-creat…

Hope this helps get you started. 

Ryan

Oleg Drobina,

Thank you, your answer enlightened me very much. Though I have some trouble accessing the id of the product. 

Based on what you explained, 

In this case you need to get the Id of the current main record (where the detail is located) by using for example:

 var currentRecordId = this.get("Id");

I understood that through this line of code I am accessing the id of the product to which my collaterals are added. But by logging this id to the console, I noticed that each time it comes different. Is this the id of each collateral that I am adding? How can I get to the id of the product? That's what I am supposed to do, right?

Thank you so much for the attention.

For me, I usely add a debugger to the code. Then examine "this" In the console to see what values are already available.

Printscreen of Error Logs

Managed to solve the problem by re-compiling FinApplicationSpec

Maybe you could create a single column in Creatio to store order number + date, then in the Excel file use a formula to append the values together and map that value to link to the order? 

Ryan

Hello,

Hope you're doing well.

We tried to reproduce the behavior you have described for different types of DB (PostgreSQL and MSSQL) in two ways:

1. Create a column via Page Designer.

2. Create the column in Object Designer.

At first, we have set the string length 250 for the 1st column, then changed it to 50 in the Object Designer and published the object:

As a result we could see that the data type has been changed from varchar(250) to varchar(50):

Then we have repeated the same actions for another test column (added via Object designer) and result was the same:

That's why it would be really helpful if you could confirm if we were doing the reproducing correctly (maybe we should use another version or another product). Also, we can provide you with a fresh installation files so you can try to re-check it from your side once again (for that just contact our support team via this email: support@creatio.com).

About the second question: in general, we do not recommend to change field types. The system can transfer data type from one char to another when you need to change the length of the field. But it may behave unpredictably when the type is changed from one object to another.

Alos you can find some addtional information about data types in the links below:

• https://community.creatio.com/questions/it-possible-change-base-object-…
• https://community.creatio.com/questions/convert-lookup-value-string

Best regards,

Roman

Roman Rak,

Hi Roman, Appreciate your effort in trying to reproduce this.



Your steps does look right - 

1. Create as 250 in Object designer. Publish and check if the DB shows nvarchar(250).

2. Change to 50 in Object designer. Publish and check if the DB has changed to nvarchar(50).



I have verified this on 2 Creatio versions - 7.15.2 Studio and 7.16.3 CustomerCenter (Both MS SQL). Is is possible that this got fixed/changed in the latest 7.17 release?? Il also reach out to Creatio support ato replicate this in the latest release.

M Shrikanth,

As for your question - no, it was not changed in the latest versions.



Best Regards,

Ivanna Yatsura

Roman Rak,

Hi Roman. Checked this use case once again. The behavior I have stated in my question is correct. I have used a on-premises Creatio sales v7.17 on MSSQL. The datatype does change if you increase the length (Eg 50 to 250) but not from 250 to 50.



Have you checked this on an on-premises or trial instance??

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

Due to security reasons the direct access to the databases is not available. If there is a necessity to migrate some data from\to Creatio - the enterprise level customers use different integrations via webservices, OOTB API and Odata, DB server linking, ftp\sftp storages and different custom API integrations. SQL scripts can be also involved.  

The direct connection with our cloud and customer's storages is never set up due to the security policies. Usually there can be some shared storage where customers send their data and it is imported into our cloud.  As for Starfish ETL, it is also used but not frequently. Usually enterprises use their own integrations.

Regards,

Dean

Dean Parrett,

Could you elaborate on the "DB Server Linking" part here, Dean? Thanks

Dear Amanthena,

This is typical on-site\non-cloud integration. It's basically looks like this:

Creatio database<--->buffer database<--->customer's storage.

Typically the buffer database is kind of shared storage for customer and Creatio used to import and export data to both sides. It is typically used on regular basis rather than one time data migration.   It has some particular access rules assigned to the required tables. It is not usually used for migrating data into Cloud database due to some security limitations, however some enterprise customers have similar solutions where Web server is connected to similar storage and it pulls data into Creatio. Again, server linking is not really commonly used for cloud integration solutions, however, under some conditions, specific setup and prior negotiation with cloud team- it may be possible. If you are interested in it, I suggest you to discuss it with your account manager.

Regards,

Dean    

I have done data migrations directly to the database on several occasions with no issues. The most challenging part is the creation of the access rights records (well, not really challenging once you figure out the RightLevel 0=read/1=edit/3=delete, Position, SourceId=user/role values, but something to be aware of). Each table has a separate table that stores access rights for the records, for example for Contact it stores it's access rights records in SysContactRight. For objects with record level permissions, you need to create these records to give read/edit/delete permissions to the records. However, in recent versions of Creatio the object permissions propagate to records nicely, so I would likely turn off record access rights for the migration, then set afterwords and let Creatio do that work. 

In my scenario, I had a customer with multiple millions of records. They are cloud hosted, so I had support provide me with a database backup, I performed the migration, then provided the complete database loaded with data back to support as a backup and they put in place on the cloud again. This was significantly faster to load using SQL inserts from selects etc than loading record by record via the API. Keep in mind that if a customer is first a cloud customer, the backup you get from support is likely going to be a Postgresql database, not MSSQL. In another scenario, with Creatio on premise, creating views that combine data from other SQL databases and exposing as objects in Creatio was very straightforward as well.

Hope this helps.

Ryan

Hello Halludeen,

Hope you're doing well.

By default, the Creatio application can handle the database indexing. For cloud solutions, we arrange the defragmentation of indexes every day to provide stable site productivity. For on-site deployments, indexing should be configured by the client's/partner's DBA. In the second case, index defragmenting should be done for all available indexes (you can skip this operation for clustered indexes, as it doesn't make a big difference before).

About the non-indexed database, the application also can work with a kind of DB, but we can not assure that the application performance will be as fast as with indexed DB. That's why we recommend to perform index operation for DB records at least once per month.

Thank you for your questions.

Best regards,

Roman

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

Hello Nicolas,

This method is only good for the development environments deployed on-site. Since the modifications are implemented directly in the database, they are not bound to any package. That is why the modifications will not be implemented in the database if the package with the view models and the tile view models is installed into another application. For the correct transfer of the developed functions, you need to bind the SQL-scripts that implement the corresponding modifications in the database when installing the package.

Best regards, 

Olga.