Has anyone been successful using the CreatioSDK in sending date updates via either InsertQuery or UpdateQuery?

I've tried sending dates in all the following formats but all result in an error:

yyyy-MM-dd

yyyy-MM-ddTHH:mm:ss

yyyy-MM-ddTHH:mm:ss.fff 

yyyy-MM-ddTHH:mm:ss.fffffffZ

M/d/yyyy

d/M/yyyy

dd/MM/yyyy

The dates I'm trying to send are defined as DataValueType.Date and I've tried specifying that in the Update/InsertQuery and in desperation also tried specifying as DataValueType.DateTime. 

If I omit dates from the Insert/Update queries the updates are successful. 

I can successfully enter dates in the records I'm trying to update via the Creatio UI.

Here's an example of a stripped down InsertQuery request, including the DueDate column causes an error 500, if excluded the insert is successful:

{

  "QueryId": null,

  "QueryKind": 0,

  "ColumnValues": {

    "Items": {

      "Title": {

        "ExpressionType": 2,

        "IsBlock": false,

        "ColumnPath": null,

        "Parameter": {

          "DataValueType": 1,

          "Value": "Test SDK Three",

          "ArrayValue": null,

          "ShouldSkipConvertion": false

        },

        "FunctionType": 0,

        "MacrosType": 0,

        "FunctionArgument": null,

        "FunctionArguments": null,

        "DateDiffInterval": 0,

        "DatePartType": 0,

        "AggregationType": 0,

        "AggregationEvalType": 0,

        "SubFilters": null,

        "ArithmeticOperation": 0,

        "LeftArithmeticOperand": null,

        "RightArithmeticOperand": null

      },

      "Contact": {

        "ExpressionType": 2,

        "IsBlock": false,

        "ColumnPath": null,

        "Parameter": {

          "DataValueType": 0,

          "Value": "fede06a3-1270-4464-8049-c7afb144a018",

          "ArrayValue": null,

          "ShouldSkipConvertion": false

        },

        "FunctionType": 0,

        "MacrosType": 0,

        "FunctionArgument": null,

        "FunctionArguments": null,

        "DateDiffInterval": 0,

        "DatePartType": 0,

        "AggregationType": 0,

        "AggregationEvalType": 0,

        "SubFilters": null,

        "ArithmeticOperation": 0,

        "LeftArithmeticOperand": null,

        "RightArithmeticOperand": null

      },

      "DueDate": {

        "ExpressionType": 2,

        "IsBlock": false,

        "ColumnPath": null,

        "Parameter": {

          "DataValueType": 8,

          "Value": "2022-05-28",

          "ArrayValue": null,

          "ShouldSkipConvertion": false

        },

        "FunctionType": 0,

        "MacrosType": 0,

        "FunctionArgument": null,

        "FunctionArguments": null,

        "DateDiffInterval": 0,

        "DatePartType": 0,

        "AggregationType": 0,

        "AggregationEvalType": 0,

        "SubFilters": null,

        "ArithmeticOperation": 0,

        "LeftArithmeticOperand": null,

        "RightArithmeticOperand": null

      },

      "Amount": {

        "ExpressionType": 2,

        "IsBlock": false,

        "ColumnPath": null,

        "Parameter": {

          "DataValueType": 5,

          "Value": 6718.35,

          "ArrayValue": null,

          "ShouldSkipConvertion": false

        },

        "FunctionType": 0,

        "MacrosType": 0,

        "FunctionArgument": null,

        "FunctionArguments": null,

        "DateDiffInterval": 0,

        "DatePartType": 0,

        "AggregationType": 0,

        "AggregationEvalType": 0,

        "SubFilters": null,

        "ArithmeticOperation": 0,

        "LeftArithmeticOperand": null,

        "RightArithmeticOperand": null

      },

      "Budget": {

        "ExpressionType": 2,

        "IsBlock": false,

        "ColumnPath": null,

        "Parameter": {

          "DataValueType": 5,

          "Value": 0.0,

          "ArrayValue": null,

          "ShouldSkipConvertion": false

        },

        "FunctionType": 0,

        "MacrosType": 0,

        "FunctionArgument": null,

        "FunctionArguments": null,

        "DateDiffInterval": 0,

        "DatePartType": 0,

        "AggregationType": 0,

        "AggregationEvalType": 0,

        "SubFilters": null,

        "ArithmeticOperation": 0,

        "LeftArithmeticOperand": null,

        "RightArithmeticOperand": null

      }

    }

  },

  "RootSchemaName": "Opportunity",

  "IncludeProcessExecutionData": false

}

 

Hello community,

I'm working on an assembly package and I'm currently using OData3 for some integrations. I noticed that the end-point "/0/ServiceModel/EntityDataService.svc/" is not exposing the methods of the custom entities. Do you have any ideas to solve this problem?

Let me know.

Thanks in advance,

Luca

Hi,



I am receiving CORS error whenever I call from a different domain.

I am trying to integrate a call from a website to the company creatio hosted in creatio.com.

 

var myHeaders = new Headers();
myHeaders.append("Content-Type", "application/json");
myHeaders.append("Access-Control-Allow-Origin", "*");
myHeaders.append("Access-Control-Allow-Methods", "*");
 
var raw = JSON.stringify({
  "UserName": "xxxx",
  "UserPassword": "xxxx"
});
 
 
var requestOptions = {
  method: 'POST',
  headers: myHeaders,
  body: raw,
  redirect: 'follow',
  credentials: 'omit',
  cache: 'no-cache',
  mode: 'cors',
  referrerPolicy: 'no-referrer',
};
 
fetch("https://xxxxxx.creatio.com/ServiceModel/AuthService.svc/Login", requestOptions)
  .then(response => response.text())
  .then(result => console.log(result))
  .catch(error => console.log('error', error));

What could possibly solve this problem?



Best Regards,

Solem A

Hello,

I have developed one service which inserts contact and contact-address from the third party to bpm'online. I have written below code to add contact and its Address simultaneously:

var insertContactQuery = new InsertQuery()

{

                    // Root schema name.

                    RootSchemaName = "Contact",

                    OperationType = QueryOperationType.Insert,

                    // New column values.

                    ColumnValues = new ColumnValues()

                    {

                        // Key-value collection.

                        Items = new Dictionary()

                                 {

                                     {

                                         "Account",

                                         new ColumnExpression()

                                         {

                                             ExpressionType = EntitySchemaQueryExpressionType.Parameter,

                                             ColumnPath = "Account",

                                             Parameter = new Terrasoft.Nui.ServiceModel.DataContract.Parameter()

                                             {

                                                 Value = item.AccountId,

                                                 DataValueType = DataValueType.Lookup

                                             }

                                         }

                                     },

                                     {

                                         "Name",

                                         new ColumnExpression()

                                         {

                                             ExpressionType = EntitySchemaQueryExpressionType.Parameter,

                                             ColumnPath = "Name",

                                             Parameter = new Terrasoft.Nui.ServiceModel.DataContract.Parameter()

                                             {

                                                 Value = item.StoreContactFirstName + " " + item.StoreContactLastName,

                                                 DataValueType = DataValueType.Text

                                             }

                                         }

                                     },

                                     {

                                         "MobilePhone",

                                         new ColumnExpression()

                                         {

                                             ExpressionType = EntitySchemaQueryExpressionType.Parameter,

                                             ColumnPath = "MobilePhone",

                                             Parameter = new Terrasoft.Nui.ServiceModel.DataContract.Parameter()

                                             {

                                                 Value = item.StorePhoneNumber,

                                                 DataValueType = DataValueType.Text

                                             }

                                         }

                                     },

                                     {

                                         "ContactAddress",

                                         new ColumnExpression()

                                         {

                                             ExpressionType = EntitySchemaQueryExpressionType.Parameter,

                                             ColumnPath = "Address",

                                             Parameter = new Terrasoft.Nui.ServiceModel.DataContract.Parameter()

                                             {

                                                 Value = item.StoreStreetAddress,

                                                 DataValueType = DataValueType.Text

                                             }

                                         }

                                     },

                                     {

                                         "ContactCity",

                                         new ColumnExpression()

                                         {

                                             ExpressionType = EntitySchemaQueryExpressionType.Parameter,

                                             ColumnPath = "City",

                                             Parameter = new Terrasoft.Nui.ServiceModel.DataContract.Parameter()

                                             {

                                                 Value = item.StoreCityName,

                                                 DataValueType = DataValueType.Text

                                             }

                                         }

                                     },

                                     {

                                         "ContactZip",

                                         new ColumnExpression()

                                         {

                                             ExpressionType = EntitySchemaQueryExpressionType.Parameter,

                                             ColumnPath = "Zip",

                                             Parameter = new Terrasoft.Nui.ServiceModel.DataContract.Parameter()

                                             {

                                                 Value = item.StoreZipCode,

                                                 DataValueType = DataValueType.Text

                                             }

                                         }

                                     }

                                 }

                    }

                };

am I doing something wrong? Please guide.

hi,

I'm trying to get informations form bpm through dataservice with a php curl call, i passed the BPMCSRF in the request header , and cookies from auth as well but i'm getting 403 errors 

here is my call



 

<?php

$url= "http://localhost/7.13.0.284_BPM_ENU/ServiceModel/AuthService.svc/Login";

$cookie_file = 'cookies.txt';

$cookies = Array();

if (! file_exists($cookie_file) || ! is_writable($cookie_file)){

    echo 'Cookie file missing or not writable.';

    exit;

}

  $auth_arguments = array(

    "UserName" => "Supervisor",

    "UserPassword" => "Supervisor"

    );

$auth_request = curl_init($url);

curl_setopt($auth_request, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_0);

curl_setopt($auth_request, CURLOPT_HEADER, true);

curl_setopt($auth_request, CURLOPT_SSL_VERIFYPEER, 0);

curl_setopt($auth_request, CURLOPT_RETURNTRANSFER, 1);

curl_setopt($auth_request, CURLOPT_FOLLOWLOCATION, 0);

curl_setopt($auth_request, CURLOPT_HEADERFUNCTION, "curlResponseHeaderCallback");

curl_setopt($auth_request, CURLOPT_HTTPHEADER, array(

    "Content-Type: application/json"

));

$json_arguments = json_encode($auth_arguments);

curl_setopt($auth_request, CURLOPT_POSTFIELDS, $json_arguments);

curl_setopt ($auth_request, CURLOPT_COOKIEJAR, realpath($cookie_file));

$result = curl_exec($auth_request);

$CSRF= explode("=",$cookies[2][1]);

$bpmcsrf=$CSRF[1];

function curlResponseHeaderCallback($ch, $headerLine) {

    global $cookies;

    if (preg_match('/^Set-Cookie:\s*([^;]*)/mi', $headerLine, $cookie) == 1)

        $cookies[] = $cookie;

    return strlen($headerLine);

}

$urlSelect= "http://localhost/7.13.0.284_BPM_ENU/0/dataservice/json/reply/SelectQuery";

$args = array(

    "RootSchemaName"=>"Product",

    "OperationType"=>0,

    "Columns"=>array(

        "Item"=>array(

            "Name"=>array(

                "OrderDirection"=>Ascending,

                "OrderPosition"=>0,

                "Caption"=>"Name",

                "Expression"=>array(

                    "ExpressionType"=>2,

                )

            )

        )

    ),

    "AllColumns"=>true,

    "IsPageable"=>false,

    "IsDistinct"=>false,

    

   

);

$arguments = json_encode($args);

$request = curl_init($urlSelect);

curl_setopt($request, CURLOPT_CUSTOMREQUEST, "POST");

curl_setopt($request, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_0);

curl_setopt($request, CURLOPT_HEADER, false);

curl_setopt($request, CURLOPT_SSL_VERIFYPEER, 0);

curl_setopt($request, CURLOPT_RETURNTRANSFER, 1);

curl_setopt($request, CURLOPT_FOLLOWLOCATION,1);

curl_setopt($request, CURLOPT_HTTPHEADER, array(

    'Content-Type:application/json',

    'BPMCSRF:'.$bpmcsrf,

    'Content-Length:'.strlen($arguments)

));

curl_setopt($request, CURLOPT_POSTFIELDS, $arguments);

curl_setopt($request, CURLOPT_COOKIEFILE, realpath($cookie_file));

curl_setopt ($request, CURLOPT_COOKIEJAR, realpath($cookie_file));

$result = curl_exec($request);

var_dump($result);

//$products= json_decode($result,true);

?>



thanks in advance,

Hello,



Tell me, please, is there a template or application with which you can edit files (docx, txt, pdf) inside CRM and after editing use them as an attachment to a letter without saving these files on your computer?

There is a specific example where CRM should act as the main repository of information from which the original version of the file is taken and the edited version is saved here (i.e., FTP or cloud disks cannot be used). Is it possible to implement this?

What is the correct way to perform the query in JSON to obtain the records that start with the letter "Y"?

The next JSON get the columns GivenName, MiddleName and Surname from "Contact":

{
    "RootSchemaName":"Contact",
    "OperationType":0,
    "Columns":{
        "Items":{
            "GivenName":{
                "Expression":{
                    "ExpressionType":0,
                    "ColumnPath":"GivenName"
                }
            },
            "MiddleName":{
                "Expression":{
                    "ExpressionType":0,
                    "ColumnPath":"MiddleName"
                }
            },
            "Surname":{
                "Expression":{
                    "ExpressionType":0,
                    "ColumnPath":"Surname"
                }
            }
        }
    },
    "AllColumns":1
}

And return all que records from "Contact".

When I apply the filter GivenName = Yxyxyx

{
    "RootSchemaName":"Contact",
    "OperationType":0,
    "Columns":{
        "Items":{
            "GivenName":{
                "Expression":{
                    "ExpressionType":0,
                    "ColumnPath":"GivenName"
                }
            },
            "MiddleName":{
                "Expression":{
                    "ExpressionType":0,
                    "ColumnPath":"MiddleName"
                }
            },
            "Surname":{
                "Expression":{
                    "ExpressionType":0,
                    "ColumnPath":"Surname"
                }
            }
        }
    },
    "AllColumns":1,
    "Filters":{
        "RootSchemaName":"Contact",
        "FilterType":1,
        "ComparisonType":3,
        "LeftExpression":{
            "ExpressionType":0,
            "ColumnPath":"GivenName"
        },
        "RightExpression":{
            "ExpressionType":2,
            "Parameter":{
                "DataValueType":1,
                "Value":"Yxyxyx"
            }
        }
    }
}

Returns all records with the name GivenName equal to "Yxyxyx"

But an error returned when I sent the query GivenName starts with "Y", like this:

{
    "RootSchemaName":"Contact",
    "OperationType":0,
    "Columns":{
        "Items":{
            "GivenName":{
                "Expression":{
                    "ExpressionType":0,
                    "ColumnPath":"GivenName"
                }
            },
            "MiddleName":{
                "Expression":{
                    "ExpressionType":0,
                    "ColumnPath":"MiddleName"
                }
            },
            "Surname":{
                "Expression":{
                    "ExpressionType":0,
                    "ColumnPath":"Surname"
                }
            }
        }
    },
    "AllColumns":1,
    "Filters":{
        "RootSchemaName":"Contact",
        "FilterType":1,
        "ComparisonType":16,
        "LeftExpression":{
            "ExpressionType":0,
            "ColumnPath":"GivenName"
        },
        "RightExpression":{
            "ExpressionType":2,
            "Parameter":{
                "DataValueType":1,
                "Value":"Y"
            }
        }
    }
}

The error return is:

{
    "responseStatus": {
        "ErrorCode": "SqlException",
        "Message": "Incorrect syntax near '@P1'.",
        "Errors": []
    },
    "rowsAffected": -1,
    "nextPrcElReady": false,
    "success": false
}

And, How do you filter two or more columns?

Hi everyone,



I have query:

SELECT (
	SELECT
		count(DISTINCT(c.id))
	-- c.id, c.name
	FROM flight_detail fd
		JOIN contact c
			ON c.id = fd.contact_id
		WHERE
			-- get last year 
			(fd.departure_date >= '2016-09-01' AND fd.departure_date <= '2017-09-30')	
			AND
			-- get this year
			fd.contact_id IN (
				SELECT fd_ly.contact_id
				FROM flight_detail fd_ly
				WHERE 
					fd_ly.departure_date >= '2017-09-01' AND fd_ly.departure_date <= '2018-09-30'
			)
) AS 'Customer Last Year Who Still Buy Ticket This Year',
(
	SELECT
		count(DISTINCT(c.id))
		-- c.id, c.name
	FROM flight_detail fd
		JOIN contact c
			ON c.id = fd.contact_id
		WHERE
			-- get last year 
			(fd.departure_date >= '2016-09-01' AND fd.departure_date <= '2017-09-30')
) AS 'Customer Last Year';

How to transform query to JSON format for sending data paramater DataService.

This query for get data from bpm and I want show the result to my 3rd app.



Thanks.

Hi Everyone,



I have a case about data integration using DataService (JSON).

I have read documentation but I can't cleary understand cause example use C# languange, I'm not comfortable with that.



I have try using postman to integration DataService, but I have a trouble,

Below screenshot setting in my postman:



1. I access the login for get the cookies for Auth, I dont have any problem in Auth login.

2. I access url DataService, but I got a problem, I can't access the page what I want, the response is 403. I'm sure when I access the url I have put the cookies from auth login.

What's wrong in my method ?

Anyone have a same case or can give me some example to use DataService beside using C# language ?

Or any advise / suggest ??

 

Thanks.



FYI: I try in local BPM



SOLVED:

Check again header cookies, csrf, and format data JSON will sent to web service bpm'online.

And you can implement data service using any language along support JSON and http request.