Hi,

I want to use function that are not included in System library/package.

I need to add the following in order to use the functions.

using System.Linq;

I tried adding this to the script but got an error message: " Feature 'using declarations' is not available in C# 7.3. Please use language version 8.0 or greater."

Is there a way to add the 'System.Linq' somewhere?

Thanks,

Chani

 

Like 0

Like

1 comments

Hi Chani,

 

This error means that either the .Net Framework version in your server where the app is hosted is not 4.7.2 and higher or .Net Core version is not 3.1.301 and higher. Also you need to double-check that all the required components were enabled in the IIS settings.

 

Best regards,
Oscar

Show all comments

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

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

Like 0

Like

4 comments

Hello,

I don't see anywhere in the code where you're saving the updates made to the entity in recordToUpd. Try adding the following to have it save the values in the database:

recordToUpd.UpdateInDB();

Ryan

Hello Federica,

Yes, try to add the method that Ryan recommended you. It will help to resolve the issue.

Best Regards,
Tetiana

Hi Ryan,

 

The problem is to access and filter records in the db.

That part of the script ain't working.

Hi Tetiana Bakai,

The main issue atm lays in the filtering and db-access part.

Show all comments

Hi everyone, 

I'd like to automate a data Import from a FTP server using a business process, without downloading the file locally.

I also found this article (https://community.creatio.com/questions/how-automate-imports-ftp-locati…) , but the links have either been removed or deleted.

I would like to know whether it is possible to use C# code in a Script Task to store the data stream in RAM and eventually recover the data to import. 

Is it reasonable for a cloud- stored enviroment to handle this kind of process with CSV files of approx. 2 MB?

Like 1

Like

3 comments

Hi Federica, 

 

Actually you are still capable to use OData with business task to achieve required implementation. You don't need to use Scheduler for this, so disregard these deleted links, I will give you fresh one , please take a look on the actual OData 4 link with guide how to set up the integration: 

 

https://documenter.getpostman.com/view/10204500/SztHX5Qb?version=latest

 

Please also check out this link from Marketplace where you may find another solution for you task: 

 

https://marketplace.creatio.com/app/file-manager-creatio

 

here you may set up key features of needed requirements, so you can make "One click" implementation and it might be much easier for you.

 

Best Regards, 

 

Bogdan L. 

The .NET FtpWebRequest class can download a file to an in-memory stream (without actually saving the file anywhere) that you can then process from the stream. This could be used from a ScriptTask in a process etc

FtpWebRequest request =
    (FtpWebRequest)WebRequest.Create("ftp://ftp.example.com/path/file.txt");
request.Credentials = new NetworkCredential("username", "password");
request.Method = WebRequestMethods.Ftp.DownloadFile;
 
using (Stream stream = request.GetResponse().GetResponseStream())
using (StreamReader reader = new StreamReader(stream))
{
    while (!reader.EndOfStream)
    {
        string line = reader.ReadLine();
        // process the line as needed
    }
}

Ryan

Ryan Farley,

That's the script I'm running at the moment and it works!

Show all comments

Hello community,

Can you please advise me how to run the server code (C#) from a client page (JS).
The business process has a delay when launching, so this method is not the best solution in my case.

 

Thank you in advance.

Mariia

Like 0

Like

2 comments
Best reply

Hello,

 

Please check the following link https://academy.creatio.com/docs/developer/back-end_development/configu… 

Best Regards,
Tetiana Bakai

Hello,

 

Please check the following link https://academy.creatio.com/docs/developer/back-end_development/configu… 

Best Regards,
Tetiana Bakai

As Tetiana mentioned, how this is typically done in Creatio is by creating a configuration web service that you call from your client-side js code. It's actually one of my favorite parts of Creatio and makes for a very powerful development experience and allows you to keep the UI very quick by passing things between the client and the server and performing operations on the server rather than the client. Along with the Academy article Tetiana shared, I also have a write up of how to create a configuration web service and call it from client-side code here: https://customerfx.com/article/creating-a-web-service-and-consuming-it-…

Ryan

Show all comments

Hi Team,

 

I want an schedule an email weekly with a excel report as attachment that will  be generated by system 

 

I used These application to generate excel report and attach to email automatically 

 

Excel pivot report for Creatio

Excel reports builder for Creatio

Printable attachment in email for Creatio

'Save printable' process element

Send email with attachments

 

But was unable to generate and attach excel to email on a scheduled process

Excel attachment is mandatory 

 

Please suggest a work around to solve this.

 

Thank You in Advance

 

Like 0

Like

1 comments

Hello,

There is no way to generate the excel report since it is not piece of default functionality. The workaround to attach the excel report manually on the attachments detail and use simple process to send it. Here is the example that send attachments from account:

 

 

 

 

Other than that, it is necessary to develop custom integration that will call for endpoint of the excel report generation, get file, send it to Creatio and then the process would re-send it according to your needs.

 

When clicking on generate excel report, the query calls for https://yourwebsite.com/0/rest/IntExcelReportService/GetExportFiltersKey with the following parameters:

EsqString: "{\"rootSchemaName\":\"Contact\",\"operationType\":0,\"includeProcessExecutionData\":true,\"filters\":{\"items\":{\"8488ded0-40f6-4167-95ca-20bccc49bfc1\":{\"filterType\":1,\"comparisonType\":3,\"isEnabled\":true,\"trimDateTimeParameterToDate\":false,\"leftExpression\":{\"expressionType\":0,\"columnPath\":\"Id\"},\"rightExpression\":{\"expressionType\":2,\"parameter\":{\"dataValueType\":1,\"value\":\"c4ed336c-3e9b-40fe-8b82-5632476472b4\"}}}},\"logicalOperation\":0,\"isEnabled\":true,\"filterType\":6},\"columns\":{\"items\":{\"Full name\":{\"caption\":\"Full name\",\"orderDirection\":0,\"orderPosition\":-1,\"isVisible\":true,\"expression\":{\"expressionType\":0,\"columnPath\":\"Name\"}},\"Age\":{\"caption\":\"Age\",\"orderDirection\":0,\"orderPosition\":-1,\"isVisible\":true,\"expression\":{\"expressionType\":0,\"columnPath\":\"Age\"}},\"Email\":{\"caption\":\"Email\",\"orderDirection\":0,\"orderPosition\":-1,\"isVisible\":true,\"expression\":{\"expressionType\":0,\"columnPath\":\"Email\"}}}},\"isDistinct\":false,\"rowCount\":-1,\"rowsOffset\":-1,\"isPageable\":false,\"allColumns\":false,\"useLocalization\":true,\"useRecordDeactivation\":false,\"serverESQCacheParameters\":{\"cacheLevel\":0,\"cacheGroup\":\"\",\"cacheItemName\":\"\"},\"queryOptimize\":false,\"useMetrics\":false,\"adminUnitRoleSources\":0,\"querySource\":0,\"ignoreDisplayValues\":false,\"isHierarchical\":false}"
RecordCollection: ["c4ed336c-3e9b-40fe-8b82-5632476472b4"]
ReportId: "ec1724f7-e58e-491f-8471-0ce19b61bc02"

 

so it is necessary to generate the POST request to this endpoint  /0/rest/IntExcelReportService/GetExportFiltersKey with similar parameters as above. As the response - you will get the file. 

Here is the similar post describing how to generate such POST request

 

https://community.creatio.com/questions/how-can-i-generate-excel-report…

 

Regards,

Dean

Show all comments

Hi Team,

 

My requirement is to run a scheduler job on monthly basis, Which would retrieve all employee and then group by a specific conditions and do some calculations and update them.

 

To do calculation we need to retrieve the few more objects.

So that we can do this using C# in console application i can run in task scheduler as i am using OnPremise Creatio CRM.

Like 0

Like

1 comments

Hi Nagaraju,

 

To run this action once per month a business process with a timer can be created that will create a scheduler task. And then you can either add a script task to the process, or use read data and read a collection of records or implement any other logic required.

 

Best regards,

Oscar

Show all comments

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?

 

 

Like 0

Like

4 comments
Best reply

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

Hi,

 

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

 

Best regards,

Oscar

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

 

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

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

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

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.

Like 0

Like

0 comments
Show all comments