c# | Community Creatio

13:25 Apr 12, 2021

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?

4 comments

Best reply

Oleg Drobina

14:41 Apr 12, 2021

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

Oleg Drobina

13:59 Apr 12, 2021

Hi,

Try SBLServiceId instead and also take a look in the database and check how is this column called there.

Best regards,

Oscar

Roman Raj Bajracharya

14:31 Apr 12, 2021

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

Oleg Drobina

14:41 Apr 12, 2021

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

Roman Raj Bajracharya

12:13 Apr 13, 2021

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

Is there a keep alive when using an ESQ ?

Arthur Hertz

12:35 Mar 31, 2021

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

Do I have to clear the cache or set the select to null ?

8 comments

Best reply

Arthur Hertz

12:16 Apr 15, 2021

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

Oleg Drobina

12:55 Apr 14, 2021

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

Arthur Hertz

15:09 Apr 14, 2021

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

Oleg Drobina

15:16 Apr 14, 2021

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

Arthur Hertz

16:52 Apr 14, 2021

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

Oleg Drobina

18:15 Apr 14, 2021

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

Arthur Hertz

10:32 Apr 15, 2021

Oscar Dylan,

The first request ActivityConnectionId is equal to NULL and for the second one there are no records.

Regards,

Arthur

Oleg Drobina

11:44 Apr 15, 2021

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

Arthur Hertz

12:16 Apr 15, 2021

Oscar Dylan,

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

Json parsing empty value

Marvyn

15:48 Mar 23, 2021

Hello,

I've implemented some code because of the changes on the new version which is coming and do not support DB.Executor.

But when i'm executing my source code, the json result is dynamic : i've only the fields which are not empty.

In order to have a great code i've parse the json. So the format is correct that's a great json but I don't know how can I include empty field/value.

Is there somebody who can explain me how can I do in order to include all fields in the object to the json return list?

Thanks a lot.

0 comments

Show all comments

how to get value from all fields in script task (server-side event)

Yosef

15:49 Dec 11, 2020

Dear Community,

I am trying to copy values from a field and lookups to put it in the "Name" fields before saving.

I use the following code in the script task but I don't know how to get the value from a lookup:

Entity.Name = $"{Entity.Code } - {Entity.Description}";

Code being the lookup.

So if you create a record in a lookup it would go like this:

[Name][Code][Description]

[ ][ 001 ][Code for info]

The Name should automatically be filled with "001 - Code for info" after saving.

What is the correct way to set the script?

Thank you!

Kind regards,

Yosef

2 comments

Dmytro Oliinyk

02:12 Dec 14, 2020

Hi!

Just add on saving event in your lookup entity, and add a script

Entity.SetColumnValue("Name", string.Format("[{0}][{1}]", Entity.GetTypedColumnValue&lt;string&gt;("Code"), Entity.GetTypedColumnValue&lt;string&gt;("Descritpion"));

Yosef

18:06 Dec 14, 2020

Dmytro Oliinyk,

Thank you but that didn't seem to work for me, this was the solution for me:

var name = Entity.GetColumnValue("Name");
var description = Entity.GetColumnValue("Description");
 
var esq = new EntitySchemaQuery(Entity.Schema);
esq.AddColumn("Code.Name");
 
var CodeEntity= esq. GetEntity(UserConnection, Entity.PrimaryColumnValue);
if (CodeEntity != null) {
 
	var columnUsrCodeName = CodeEntity .GetTypedColumnValue&lt;string&gt;("Code_Name");
 
	string fullName= $"{name}: {columnUsrCodeName } - {description}";
	Entity.SetColumnValue("Name", fullName);
}
return true;

Let me know if there's an easier way to do this.

Kind regards,

Yosef

Show all comments

parameter

7.16

Marketing_Creatio

C# data type used in 'decimal' process parameters

Ricardo Bigio

17:15 Oct 10, 2020

Which C# data type (decimal, double, etc) should I use in a script task "Get("ProcessParameter")" for decimal process parameters?

2 comments

Mohamed Ouederni

02:51 Oct 12, 2020

Hi Ricardo

You should use System.decimal C# type.

And it's a C# structure in .NET and .NET Core.

https://docs.microsoft.com/en-us/dotnet/api/system.decimal?view=netcore…

Thank you

Mohamed

Anastasiia Markina

12:17 Oct 12, 2020

Hi Ricardo,

Indeed, you can use System decimal C# type as Mohamed mentioned above. However, in case of any difficulties, please try the standard int datatype.

Regards,

Anastasiia

Show all comments

(SOLVED) How to insert from server side with access permissions ?

Midun

20:14 Sep 12, 2019

How to apply access permissions (object permission) / access right when insert data via server-side?

I have set the record access permission, but when inserting via server-side, the access right does not work. The insert method that I did was in accordance with the article above.

Thanks.

1 comments

Norton Lingard

13:12 Sep 13, 2019

Hi,

It is possible adding data via server-side with access permissions using the “Entity” class. Please find more information with the examples by the link below:

https://academy.bpmonline.com/documents/technic-sdk/7-13/working-database-entity-class

Best regards,

Norton

Show all comments

script task

emails

Run Process

Send Email to Organizational Role via Script Task

Michael Dorfman

20:17 Aug 30, 2019

Is there a way to read all the users inside an organizational role and then send those email addresses to the next step of a process?

2 comments

Alina Kazmirchuk

19:36 Sep 02, 2019

The functionality can be implemented using a script task element in order to select all contact emails which are included in the role. The contact emails are selected from the database via EntitySchemaQuery. Then join all selected emails into a string using ";" as separator. After that use the newly built string of emails as an email for sending.

The basic business process "Send email to case group" works in the same way. Please feel free to use the business process as an example. Pay attention to the script task element "Prepare Recipient Emails" (https://prnt.sc/p0tv3t).

KrishnaPrasad

17:36 Oct 06, 2020

you can try this template https://marketplace.creatio.com/template/email-users-within-functional-…

Show all comments

Auto generate case number from section field and date

Tebogo

10:31 Feb 16, 2019

Good day.

I am new to BPM Online(And C#). I just registered a 14 day free account to try it out.

I want to use the sub process to create a case number when a new record is created on a section. Please see the image below:

When the new record is saved the process starts and reads the zone filed from the request record as depicted below:

Upon reading the zone field I then assign the zone filed to the zone process variable using the formular task as depicted below:

I then use the zone process variable to formulate the case number in the code script as depicted below:

string unique_numer =  DateTime.Now.ToString("yyMMddhhmmss");
string  zone_val = Get<Guid>("zone").ToString();
string case_number=(zone_val+"-"+unique_numer)
 
Set("case_number",case_number);
return true;

The challenge I am facing is to get the value of the selected lookup zone field to store the value into zone_val string filed.

How to get the selected value from the zone lookup filed using C# code. The value that I get is a Guid data type and cannot get the value of the lookup.

I need to store the generated CaseNumber to the case_number field in the database as depicted below:

You help will be highly appreciated as I am currently stuck.

4 comments

Angela Reyes

23:18 Feb 16, 2019

Dear Tebogo,

You may create a system setting that will store this number and use it in the future number generation. You can find detailed example here: https://academy.bpmonline.com/documents/technic-sdk/7-13/how-add-auto-n…

Best regards,

Angela

Jerome BERGES

00:47 Feb 17, 2019

Assuming you want the field name in the Zone Table :

var uc = Get&lt;UserConnection&gt;("UserConnection");
 
Guid zoneId = Get&lt;Guid&gt;("zone");
 
var esq = new EntitySchemaQuery(uc.EntitySchemaManager.GetInstanceByName("Zone"));
esq.AddColumn("Name");
var entity = esq.GetEntity(uc, zoneId );
string zoneName = entity.GetTypedColumnValue&lt;string&gt;("Name");

Tebogo

07:14 Feb 17, 2019

Angela Reyes,

Thank you very much Angela.

Tebogo

07:17 Feb 17, 2019

Jerome BERGES,

Thank you very much Jerome, highly appreciate it!

Show all comments

Terrasoft.SysValue.CURRENT_USER.value equivalent in c#

Fulgen Ninofranco

07:34 Nov 04, 2018

Hi Community,

This code below gets the Guid of the current user in client side code

Terrasoft.SysValue.CURRENT_USER.value

How about in script task, using C# code what is its equivalent?

4 comments

Ryan Farley

17:52 Nov 04, 2018

In a script task you have access to UserConnection. This exposes a CurrentUser property (which provides an object of type SysUserInfo).

var currentUser = UserConnection.CurrentUser;
var userId = currentUser.Id;
var userName = currentUser.Name;

BTW If this was code in a source code schema/configuration service, you'd need to get the UserConnection via the AppConnection first:

var appConnection = HttpContext.Current.Application["AppConnection"] as AppConnection;
var currentUser = appConnection.SystemUserConnection.CurrentUser;

Fulgen Ninofranco

14:17 Nov 05, 2018

Ryan Farley,

Thanks Ryan,

Is there any documentation about CurrentUser. I want to know everything about its properties and attributes for future use.

Thanks.

Ryan Farley

18:13 Nov 05, 2018

Fulgen Ninofranco,

I don't know of any documentation, it is in Terrasoft.Core.dll, however, the SysUserInfo object inherits from the SysAdminUnit object. This object you can see in the configuration. Search for SysAdminUnit in the configuration and you'll see what is available in the SysUserInfo. The SysUserInfo object itself only adds 5 additional properties for TimeZone, PageRowsCounr, Culture, ClientIP, and DateTimeFormatCode.

Anastasia Botezat

12:30 Nov 08, 2018

Dear Fulgen,

Please use the example mentioned by Ryan, it covers the way you work with UserConnection in Script Task.

var currentUser = UserConnection.CurrentUser;

For more information on all .Net classes used in the system please see this article:

https://academy.bpmonline.com/api/netcoreapi/7.13.0/index.html#GeneralS…

Also, as for examples of UserConnection usage in web services you can check the examples here: