Hi community,

 

When implementing a web service, I have a GET method with a parameter :

 

public int GetMethod(string name)

 

In this method, I did a query to get the age of the person in parameter :

 

var select = new Select(UserConnection)

     .Column("Age")

     .From("Contact")

     .Where("Contact", "Name").IsEqual(Column.Parameter(name)) as Select;

 

Then I save the age into an int var :

 

var age = select.ExecuteScalar<int>();

 

return age;

 

The problem is the following :

 

How can I check if the name of a contact in the method parameter exsists in the DB or not ?

 

Example :

 

if(name != exists) {

   return 0;

} else {

  return age;

}

 

Thanks a lot.

 

Best regards,

Jonathan

Like 0

Like

4 comments
Best reply

Hi Jonathan,

 

Usually you use dataReader to get the value from your select query. What you can do is:

bool hasRecord = false;
using (DBExecutor executor = UserConnection.EnsureDBConnection()) {
using (IDataReader dataReader = select.ExecuteReader(executor)) {
  while (dataReader.Read()) {
    hasRecord  = true;
  }
 }
}
if(hasrecord){
  return age
}else{
  return 0
}

Or you can just set the default value of the age =0. Therefore if the dataReader does not return any record, the default value age (0) will be returned.

regards,

Cheng Gong

Hi Jonathan

 

Please add the next using -     

     using Terrasoft.Common;   

on the top of the page(if you didn't have this one). 

 

Then in the part of the code when you are trying to retrieve the age:

 

if (name.IsNullOrEmpty())

{

return 0;

}

else

{

return age;

}

 

Best Regards, 

 

Bogdan L.

Hi Jonathan,

 

Usually you use dataReader to get the value from your select query. What you can do is:

bool hasRecord = false;
using (DBExecutor executor = UserConnection.EnsureDBConnection()) {
using (IDataReader dataReader = select.ExecuteReader(executor)) {
  while (dataReader.Read()) {
    hasRecord  = true;
  }
 }
}
if(hasrecord){
  return age
}else{
  return 0
}

Or you can just set the default value of the age =0. Therefore if the dataReader does not return any record, the default value age (0) will be returned.

regards,

Cheng Gong

Bogdan Lesyk,

Thanks for your answer. It helped a lot !

Cheng Gong,

 

Your answer is really perfect ! Thanks a lot, i'll definitely save this snippet of code for my future implementations.

 

Regards,

Jonathan

Show all comments

In the cloud version, is it possible to remove "SQL query console" menu item from System Designer without losing the ability to use SQL Executor? I mean setting Access to “SQL query console” section permission to No won't solve the question, because it will deny access to SQL Executor, while I want just to remove the link from UI, keeping the ability to use the tool in requests.

Like 1

Like

1 comments
Best reply

If you want to remove it completely:

1) Make sure your package has Samarasoft.SqlConsole as a dependency (or add this in Custom package)

2) Create a replacing view model and select parent "SystemDesigner"

3) Add this code:

define("SystemDesigner", [], function() {
	return {
		diff: [
			{
				"operation": "remove",
				"name": "SqlConsoleLink"
			}
		]
	};
});

If you want to only show it for the Supervisor user, do steps 1 & 2 above, but use this code instead:

define("SystemDesigner", [], function() {
	return {
		attributes: {
			"IsSqlConsoleVisible": {
				dataValueType: Terrasoft.DataValueType.BOOLEAN
			}
		},
		methods: {
			init: function() {
				this.callParent(arguments);
				this.set("IsSqlConsoleVisible", 
					Terrasoft.SysValue.CURRENT_USER_CONTACT.displayValue === "Supervisor"
				);
			}
		},
		diff: [
			{
				"operation": "merge",
				"name": "SqlConsoleLink",
				"values": {
					"visible": { "bindTo": "IsSqlConsoleVisible" }
				}
			}
		]
	};
});

Ryan

If you want to remove it completely:

1) Make sure your package has Samarasoft.SqlConsole as a dependency (or add this in Custom package)

2) Create a replacing view model and select parent "SystemDesigner"

3) Add this code:

define("SystemDesigner", [], function() {
	return {
		diff: [
			{
				"operation": "remove",
				"name": "SqlConsoleLink"
			}
		]
	};
});

If you want to only show it for the Supervisor user, do steps 1 & 2 above, but use this code instead:

define("SystemDesigner", [], function() {
	return {
		attributes: {
			"IsSqlConsoleVisible": {
				dataValueType: Terrasoft.DataValueType.BOOLEAN
			}
		},
		methods: {
			init: function() {
				this.callParent(arguments);
				this.set("IsSqlConsoleVisible", 
					Terrasoft.SysValue.CURRENT_USER_CONTACT.displayValue === "Supervisor"
				);
			}
		},
		diff: [
			{
				"operation": "merge",
				"name": "SqlConsoleLink",
				"values": {
					"visible": { "bindTo": "IsSqlConsoleVisible" }
				}
			}
		]
	};
});

Ryan

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&lt;Guid&gt;("AccountId");
                var activityId = activity.GetTypedColumnValue&lt;Guid&gt;("Id");
                int count = CountNumberOfActivityRdv(accountId, userConnection);
	        Guid ContactClientOuRepresentantNon = new Guid("f550b45d-093e-43ba-bdd1-bc0bd43c8e16");
 
	        if (count &gt; 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&lt;int&gt;();
 
	        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 &amp;&amp; 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 &amp;&amp; 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

Dear Community,

 

Is it possible to have a changelog as a detail?

We would like to see all the changes made to a field of an object visible in a list.

I assume that this list is being saved to an object as well?

 

Let's say that I create an entry in the Contact Change log to see whenever a name is changed, we can find this info through:

https://prnt.sc/zza09f

But it would be nice to see the list/data in a detail on the contact page itself.

 

Any help is appreciated, thank you!

 

 

Kind regards,

Yosef

 

Like 0

Like

2 comments

Dear Yosef, 

I guess that you can take a look at the following marketplace application which should cover your use case:
https://marketplace.creatio.com/app/change-log-creatio

Kind regards,
Roman

Dear Roman,

 

Thank you for this.

I installed it but it gave me an error... The only thing I need to know is where the info is being stored.

There should be a sys object somewhere?

 

Either way, thank you for the suggestion.

 

 

Kind regards,

Yosef

Show all comments

Dear, how can I see the code to create a database view? For example, I have a UsrVwContactAddress view and I would like to get the creation code for that view in sql console:

CREATE VIEW dbo.UsrVwContactAddress
AS
SELECT...

 

Thanks!

Like 0

Like

2 comments

Hi Sebastian, 

 

Here are the example how you can do it:

 

 

Best Regards, 

 

Bogdan L

If this is a cloud system, and you don't have access to local tools, you can use the following commands via SQL Executor to get the SQL for the view.

If database is MSSQL:

SELECT
    definition
FROM
    sys.sql_modules
WHERE
    object_id = object_id('viewname');

If database is Postgresql:

select pg_get_viewdef('"viewname"', true)

Ryan

Show all comments

Hi Team,

I couldn't find the SQL Console, which is a very useful tool in marketplace. Is it discontinued?

 

Like 1

Like

5 comments
Best reply

Krishna, 

In the mean time, if you have it in an existing system, you can export the package to install elsewhere.

Ryan 

Hello Krishna, 

 

The SQL Console add-on is currently being refined. Our Marketplace team will publish the application once it is done. Most likely it will become available in the next two weeks. 

 

Please, let us know in case any further information is required. 

 

Best regards,

Olga. 

Krishna, 

In the mean time, if you have it in an existing system, you can export the package to install elsewhere.

Ryan 

Ryan Farley,

Ryan, I was thinking so but was worried if it will work in latest 7.17 version. Have you tried it in new version?

I would recommend creating a trial app that will be 7.17 and installing the package with SQL-executor to it.

 

Best regards,

Oscar

Oscar Dylan,

Thanks Oscar, will try that.

Show all comments

Hello community,

 

We have a use case where Creatio co-exists with a legacy application which is SQL heavy - A lot of logic is written using SQL Stored procedures. There are certain custom objects on Creatio that needs to be populated and read by the legacy app on a weekly basis. Integration via APIs is not an option (owing to all the business logic being already available in Stored procedures). Using Excel Import/Export is also not an option. 

We are considering an ongoing ETL via SQL where a stored procedure is used to directly write/read info from Creatio's custom tables. Assume that the Legacy application has a good understanding of the schema and relationships and transformation/mapping to/from Creatio tables is not an issue. 

Pls comment on the following - 

1. Is this feasible? - In my view, this should definitely be feasible as at the end of the day, It is an SQL DB. Any data read out of Creatio's DB does not disturb the state of the data in the DB. Any data modified (correctly) in the DB should reflect correctly in the Creatio application. Can you confirm?
2. Is this recommended? - Apart from ease of use and need for SQL development, Are there any reasons from a Creatio product/architecture stand point why this might not be recommended? The load on the WebServer is a negligible factor as these SQL import/export will only be done during low usage periods weekly.

Thanks in advance!

Like 0

Like

8 comments
Best reply

Hello,

 

Thank you for your question!

Can you please check this Marketplace add-on, I believe it can help you with your business task.

But still, if you decide to use your approach, then:

1. Yes, that is feasible. The data will be available in the Application but you need to understand that if you miss to insert something, the data might be corrupted. Also, this kind if data transfer won't trigger BP as this will be considered to be direct data insert into the DB.

2. I can not say that this approach is recommended but it is possible. And again, you need to perform a lot of testing before real data transfer just not to lose data.

 

Best regards,

Bogdan S.

 

Hello,

 

Thank you for your question!

Can you please check this Marketplace add-on, I believe it can help you with your business task.

But still, if you decide to use your approach, then:

1. Yes, that is feasible. The data will be available in the Application but you need to understand that if you miss to insert something, the data might be corrupted. Also, this kind if data transfer won't trigger BP as this will be considered to be direct data insert into the DB.

2. I can not say that this approach is recommended but it is possible. And again, you need to perform a lot of testing before real data transfer just not to lose data.

 

Best regards,

Bogdan S.

 

Bogdan Spasibov,

Hi Bogdan. Appreciate your very clear answer. 

Yes, We did consider the plugin. But our use case needs to migrate data from a few custom SQL databases and on an ongoing ETL for 2 weeks.  Also, We mostly have custom objects on Creatio and it is not a typical Creatio CRM that we are building. 

#1 Yes, I understand that this won't trigger BPs. We are only doing this as a 1 time migration activity as our client is migrating an existing custom application to Creatio. 

Bogdan Spasibov,

HI Bogdan. Have a follow up question - 

We have workflows & business logic configured at the Application layer (Via business processes, entity event listeners & incoming API integrations). However, we need to transfer some data into our Database only via direct SQL. Is there any way at all of triggering application level logic on some Database level triggers?

 

I can think of a few round about/hacky ways of achieving this. Want some input wrt support for DB to Application layer execution flow from a Creatio product stand point and any clean ways of doing this. 

Bogdan Spasibov,

HI Bogdan. Have a follow up question - 

We have workflows & business logic configured at the Application layer (Via business processes, entity event listeners & incoming API integrations). However, we need to transfer some data into our Database only via direct SQL. Is there any way at all of triggering application level logic on some Database level triggers?

 

I can think of a few round about/hacky ways of achieving this. Want some input wrt support for DB to Application layer execution flow from a Creatio product stand point and any clean ways of doing this. 

M Shrikanth,

 

In case you need to interact with the database directly you need to use either stored procedures or triggers that could trigger additional actions upon some actions on the application level. This will be the easiest way to achieve your target.

 

Additionally there is a separate dbExecutor class that allows interacting with the database directly - https://academy.creatio.com/docs/developer/back-end_development/working….

 

 Please use one of these two options to achieve your business task.

 

Best regards,

Oscar

Oscar Dylan,

Hi Oscar. Thank you for the response. 

I was referring to use cases like the following - When a SQL Trigger gets activated, Run a business process at the Application layer. There is no direct way to do this I would presume?

Also, the dbExecutor class will help perform operations unidirectionally from the Application layer to the DB layer. I don't think something like this is available - When a trigger runs at the DB layer, it is available as an Event to catch or subscribe to at the Application layer. Is this right?

A very distant example would be something like this - 

https://stackoverflow.com/questions/3512026/how-can-i-execute-net-code-…

 

M Shrikanth,

 

Hello,

 

Yes, you are right, there is no way to call a business process execution using database triggers. You can call a business process to execute using ProcessEngineService.svc https://academy.creatio.com/docs/developer/front-end_development/creati… but this is the application layer.

 

You can call stored procedure using dbExecutor, but not visa versa (trigger calls a process). You can use another approach: insert data to the database and then call the process manually for all the records by their CreatedOn date value. As a result data will be inserted to the system and processed by a business process.

 

Best regards,

Oscar

Oscar Dylan,

Thank you Oscar for your response. Yes, the final approach you have suggested is one of the ways I also thought about. It is still round about and non optimal. Appreciate your answer nevertheless. 

Show all comments

Hi Community!

 

as there is no out-of-the-box way of deploying permissions from one system to another, I'm currently trying to achieve this via SQL.

 

I'm able to add a static SQL script to the package and it will be executed fine on the target system. My problem is that the content of the SQL script is not static. I have to execute an SQL script in the source system to get all the information about the current permission setup and then use these results to build the update/insert statements for the target system.

 

Did anyone try to accompish this before?

 

Thanks for any hints!

Like 0

Like

5 comments

Dear Robert,

 

Organization Roles can be transferred only by SQL scripts. Functional roles are transferred by binding data of the System administration object. The rights to existing records should be transferred via scripts as well. To distribute rights to new records you can either set up a business process or configure object permission directly on the production environment.

 

Best regards,

Angela

Angela Reyes writes:

Dear Robert,

Organization Roles can be transferred only by SQL scripts. Functional roles are transferred by binding data of the System administration object. The rights to existing records should be transferred via scripts as well. To distribute rights to new records you can either set up a business process or configure object permission directly on the production environment.

Best regards,

Angela

Thanks Angela!

 

But the question is how can I transfer the definition of the permissions, without rewriting the SQL script over and over again?

Is the following procedure the only way?

  1. Select the current permissions manually with SQL
    the tables are:

    1. SysEntitySchemaOperationRight for object permissions
    2. SysEntitySchemaRecordDefRight for record permissions
    3. SysEntitySchemaColumnRight for column permissions
  2. transform the result to and insert/update statement
  3. save those scripts in the package SQL section
  4. export/import package

And do I have to/am I allowed to truncate those 3 tables first?

Is there a stored procedure that applies the new permissions on the target systems' records?

 

Thanks a lot and best regards

Robert Pordes,

Your description is almost correct. Please see all steps:

1. Create scrip for update/ insert records from SysAdminUnit table to use these records in the next steps

2. Select the current permissions manually from SQL the tables:

2.1 SysEntitySchemaOperationRight for object permissions

2.2 SysEntitySchemaRecordDefRight for record permissions

2.3 SysEntitySchemaColumnRight for column permissions

2.4 Also there are tables like Sys{ObjectName}Right (e.g. SysAccountRight) in which record rights are stored, so you should create script for each object. 

3. Transform the result to and insert/update statement

4. save those scripts in the package SQL section

5. export/import package

 

Best regards,
Angela

Hi Angela,

thanks, but aren't the tables Sys{ObjectName}Right storing the applied rights (they will be populated by clicking "update record permissions", correct?). If I create scripts on eg DEV system, the data will not be the same! I mean we will definitely have different eg. account and contact records on dev/test/prod system.

That's exactly why I asked this in my previous post: 

Is there a stored procedure that applies the new permissions on the target systems' records?

 

Thanks,

Robert

Robert Pordes,

There is no stored procedure. This is why I suggested workarounds like

binding data and business processes.

 

Best regards,
Angela

Show all comments

One of our clients already has an extensive SQL server 2012 enterprise edition setup in place for their internal apps. They would like to continue using this for their Creatio instance as well. We are aware that Creatio supports 2012 SP 3 and higher. A few questions in this regard -

 

1. Would there be support for SQL Server 2012 in future versions of Creatio as well?
2. How easy/difficult is it to move from an older to a newer version of SQL Server in a working Creatio instance? 

Like 0

Like

6 comments

Hello,

 

1. We already do not recommend using SQL Server 2012 for onsite deployment and use 2016 version instead. 

2. It depends on the system administrator and server structure and many other variables that are hard to predict. 

 

Best regards,
Angela

Angela Reyes,

Hi Angela, 

Are there any known issues with Creatio on SQL 2012? The documentation says that Creatio is compatible with 2012 SP3 and higher. 

M Shrikanth,

There are no known issues so far. If needed you may stay on SQL 2012.

Angela Reyes,

Thank you very much Angela. Appreciate your response. Quick follow up question - 

Are there any known performance, security, availability or any other benefits at all, to Creatio using SQL 2016 instead of 2012? We want to understand if there are reasons why Creatio would recommend 2016. We would like to pass this on to our client to enable them to make a conscious choice to move to SQL 2016. 

We need high availability, redundancy and fail over and are going with a web farm environment on a private cloud. Also, We will be using the Machine learning service in Creatio. Let me know if this makes any difference to the SQL version choice. 

M Shrikanth,

We recommend SQL 2016 due to its own features that were implemented in that version, like security features, ect.

 

Best regards,
Angela

Angela Reyes,

Got it. Thank you Angela. So the Creatio application per say does not benefit from moving to SQL 2016. But owing to inherent advantages in SQL 2016, it is recommended. 

Show all comments

Hi,

I've this query and i would like to translate to ESQ, so far i managed to create this:

but i'm having this error: "uncaught exception: Collection item with name SysUserInRole not found".

I would like to know, why i'm getting this error? Is there a way to fix it? And if there is a better way to do that?

Best regards,

Pedro Pinheiro.

 

Like 0

Like

1 comments

Hi Pedro,

Seems like in your snippet you are setting brackets '[]' in wrong order. Systems tries to add column 'SysUserInRole' to the query which does not exist. I assume that you need to change it for the following:
esq.addColumn("=[SysUserInRole:SysUser].SysRole", "Role")

To make it more clear I have put together an example:
 

var esq = this.Ext.create("Terrasoft.EntitySchemaQuery", {
					rootSchemaName: "SysAdminUnit"
				});
 
				esq.addColumn("Name");
				esq.addColumn("Contact.Name");
				esq.addColumn("=[SysUserInRole:SysRole].SysUser");
 
				var currentUserFilter = this.Terrasoft.createColumnFilterWithParameter(
						this.Terrasoft.ComparisonType.EQUAL,
						"=[SysUserInRole:SysRole].SysUser",
						Terrasoft.SysValue.CURRENT_USER.value);
 
				esq.filters.addItem(currentUserFilter);
 
				esq.getEntityCollection(function(response) {
					debugger;
				}, this);

This example generates following sql query:
 

exec sp_executesql N'
SELECT
	[SysAdminUnit].[Id] [Id],
	[SysAdminUnit].[Name] [Name],
	[Contact].[Name] [Contact.Name],
	[SysUserInRole].[SysUserId] [SysUserInRole.SysUserId],
	[SysUser].[Name] [SysUser.Name]
FROM
	[dbo].[SysAdminUnit] [SysAdminUnit] WITH(NOLOCK)
	LEFT OUTER JOIN [dbo].[Contact] [Contact] WITH(NOLOCK) ON ([Contact].[Id] = [SysAdminUnit].[ContactId])
	LEFT OUTER JOIN [dbo].[SysUserInRole] [SysUserInRole] WITH(NOLOCK) ON ([SysUserInRole].[SysRoleId] = [SysAdminUnit].[Id])
	LEFT OUTER JOIN [dbo].[SysAdminUnit] [SysUser] WITH(NOLOCK) ON ([SysUser].[Id] = [SysUserInRole].[SysUserId])
WHERE
	[SysUserInRole].[SysUserId] = @P1',N'@P1 uniqueidentifier',@P1='7F3B869F-34F3-4F20-AB4D-7480A5FDF647'

Hope it helps!

Regards,
Dmytro

Show all comments