Hi all
I have created a web service that accepts values from a third party server. Next, I need to transfer one value from the request to the section field.
Question:
How do I call my finished web service in C#?

Like 0

Like

4 comments

Hi,

Are you talking about a service created in the configuration or in the "Web services" section?

Dmytro Vovchenko, in the web services section

 

the question is how to set up its call in c#, so that in the future to use its result in one of the fields of the section

 

Alexandr Bezbozhnov,

As far as I know, currently, there is no direct way of running this sort of web service from code. However, you can run it inside a business process and you can run this process inside C# code using

ProcessEngineService.svc, with it you even can get the result values of the process.

Show all comments

Hi everyone,

how to use filter month and year Esq Server (EntitySchemaQuery)?

SELECT 
* 
FROM UsrTable 
WHERE 
MONTH(CreatedOn) = 1 AND YEAR(CreatedOn) = 2022

I found there is a function at https://academy.creatio.com/api/netcoreapi/7.17.0/#Terrasoft.Core~Terrasoft.Core.Entities.EntitySchemaQuery~CreateMonthFunction.html for get month and year.

https://prnt.sc/8brMGtYq3qhP

But how to use in filter?

esq.Filters.Add(esq.CreateFilterWithParameters(FilterComparisonType.Equal, "CreatedOn", Month));
esq.Filters.Add(esq.CreateFilterWithParameters(FilterComparisonType.Equal, "CreatedOn", Year));

Thank you.

Like 1

Like

1 comments
Best reply

Hello Romadan,

You can do filters like this using macros: 

// CreatedOn is the 1st month (January)
esq.Filters.Add(esq.CreateFilter(FilterComparisonType.Equal, "CreatedOn", EntitySchemaQueryMacrosType.Month, 1));
 
// CreatedOn is the first day of the month
esq.Filters.Add(esq.CreateFilter(FilterComparisonType.Equal, "CreatedOn", EntitySchemaQueryMacrosType.DayOfMonth, 1));
 
// CreatedOn is in the year 2022
esq.Filters.Add(esq.CreateFilter(FilterComparisonType.Equal, "CreatedOn", EntitySchemaQueryMacrosType.Year, 2022));

Ryan

Hello Romadan,

You can do filters like this using macros: 

// CreatedOn is the 1st month (January)
esq.Filters.Add(esq.CreateFilter(FilterComparisonType.Equal, "CreatedOn", EntitySchemaQueryMacrosType.Month, 1));
 
// CreatedOn is the first day of the month
esq.Filters.Add(esq.CreateFilter(FilterComparisonType.Equal, "CreatedOn", EntitySchemaQueryMacrosType.DayOfMonth, 1));
 
// CreatedOn is in the year 2022
esq.Filters.Add(esq.CreateFilter(FilterComparisonType.Equal, "CreatedOn", EntitySchemaQueryMacrosType.Year, 2022));

Ryan

Show all comments

Hello Community,

 

We have a requirement for fetching all the column names of an object using source code.

 

I have taken this community post https://community.creatio.com/questions/get-title-columns-esq as reference and tried.

 

As per the article, the below code is used for fetching the column names after fetching the entity collection.

 titleColumnCaption = entity.Schema.Columns.GetByName("Title").Caption.Value;

But the code returns the null value.

 

Kindly let us know whether the above line of code is correct or not?

If not, suggest us an other way to achieve the functionality.

 

Thanks,

Sivaranjani

Like 0

Like

2 comments

Hello Sivaranjani,

 

It happens because the code above returns the "Title" column caption only and if your object doesn't have the column with such a name the result is null.

 

You need to use something like:

var columnCaptionsArray = activityEntity.Schema.Columns;
    foreach (var columnCaption in columnCaptionsArray)
    {
    	var captionForColumn = columnCaption.Caption.Value;
    }

and use the captionForColumn in your logic furhter.

 

Best regards,

Oscar

Oscar Dylan,

Thanks for the reply. The above code is working.

Show all comments

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