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

}

 

  

Like 0

Like

1 comments
Best reply

Hello Jerry, 



Please try to pass Date into Creatio with the following format in UTC time:

 

      "dataValueType":8,
      "value":"\"2000-01-01T00:00:00.000\""

Kind regards,
Roman

Hello Jerry, 



Please try to pass Date into Creatio with the following format in UTC time:

 

      "dataValueType":8,
      "value":"\"2000-01-01T00:00:00.000\""

Kind regards,
Roman
Show all comments

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

Like 0

Like

3 comments

Hello Luca,

 

I am not sure I understand you correctly. Can you please send an expected result and an actual result you receive?

 

Thank you!

 

Best regards,

Oscar

Hi Oscar Dylan,

I have a visual studio C# project binded to OdataV3 endpoint ("/0/ServiceModel/EntityDataService.svc/") and I don't find the custom entities created by me.

 

Using the following URL: "http://localhost/0/odata/$metadata" (ODataV4) I can see the expected entities.

 

The problem is that if I try to generate ODataV4 client code using "Unchase OData Connected Service" add-On it returns this error when I use ODataV4 endpoint.

 

While if I use ODataV3 endpoint with "Unchase OData Connected Service" add-On everything runs fine but the custom entities are missing (first picture). Furthermore if I use ODataV3 in a simple package every custom entity endpoint is available.

 

Did I make myself clear?

Thanks,

Luca

Luca Tavasanis,

 

Yes, it's clear now, thank you!

 

The problem is that OData3 forms its metadata based on the default assembly and doesn't include separate assemblies (where your objects are located), while OData4 uses a separate special assembly (neither default nor the custom one). And that's why its impossible to get access objects in packages compiled in the separate assembly using OData 3.

 

Our core R&D team will review this logic in the future releases, meanwhile the only way to access the object is using OData 4 only.

 

Best regards,

Oscar

Show all comments

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

Like 0

Like

4 comments
Best reply

Hello,

 

In order to avoid CORS error please follow the steps that are mentioned on this link https://community.creatio.com/articles/web-service-available-without-au….  

 

In case you want to continue with the initial way we can provide you with the clean binary files for the application so you could deploy it locally and test CORS policies modification. After that you can contact us at support@creatio.com so you could provide us with the complete instruction of changes that has to be applied to the binary files of the application so that CORS policies couldn't interrupt your integration and we will apply these changes during the next maintenance hours for the app in cloud.

 

Best Regards,

Tetiana Bakai

Hello,



Please check the following article https://developer.mozilla.org/en-US/docs/Web/HTTP/CORS/Errors/CORSMissi… . It's specified there that it's not good to use * wildcard as header's value for Private APIs, but instead it's necessary to have a specific domain or domains set. In addition, the wildcard works only for requests made with the crossorigin attribute set to anonymous that prevents sending credentials like cookies in requests. 



Your issue is not related to Creatio platform for what we are responsible, it's regarding problems with http requests. 



Best Regards,

Tetiana Bakai

Tetiana Bakai,



In the link you provided it says:

If the server is under your control, add the origin of the requesting site to the set of domains permitted access by adding it to the Access-Control-Allow-Origin header's value.

For example, to allow a site at https://amazing.site to access the resource using CORS, the header should be:

Access-Control-Allow-Origin: https://amazing.site

The server is not under my control so I can't set the Access-Control-Allow-Origin.

Hello,

 

In order to avoid CORS error please follow the steps that are mentioned on this link https://community.creatio.com/articles/web-service-available-without-au….  

 

In case you want to continue with the initial way we can provide you with the clean binary files for the application so you could deploy it locally and test CORS policies modification. After that you can contact us at support@creatio.com so you could provide us with the complete instruction of changes that has to be applied to the binary files of the application so that CORS policies couldn't interrupt your integration and we will apply these changes during the next maintenance hours for the app in cloud.

 

Best Regards,

Tetiana Bakai

Tetiana Bakai,



This is very nice of you. 



Upon checking the link provided, I initially did what she did as well then I realize I can't actually edit the website's configs hosted @ creatio.com to make this happen which is why I tried to do it via dataservice instead.



But let me retry it again, if everything works I'll let you know.



Best Regards.

Solem A.



 

Show all comments

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.

Like 0

Like

4 comments

Dear Riddhi,

Unfortunately, it is not possible to add data to the “Contact” and “ContactAddress” tables simultaneously using the approach that was described early. However, we can offer two ways solving the issue:

1. In order to add data via DataService please do the following:

-Create custom columns in the “Contact” object for storing the corresponding address values.

-Create a business process that will take the values from these columns and add them to the corresponding columns from the “ContactAddress” table.

Please note that we do not recommend using DataService for adding data to the database.

2. Create a web service for adding data. This approach is recommended for solving such types of the issues.

Best regards,

Norton

Norton Lingard,

Thanks, Norton. Can you please provide any reference link or example for adding records with web service? It would be really grateful.

Dear Riddhi,

Please find more information by link below:

https://academy.bpmonline.com/documents/technic-sdk/7-14/how-run-bpmonline-processes-web-service

Best regards,

Norton

Norton Lingard,

Thanks

Show all comments

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,

Like 0

Like

6 comments

Try to catch the request with Fiddler and then compare it with the correct requests in the article by the link below.

https://academy.bpmonline.com/documents/technic-sdk/7-13/executing-odat…



Additionally, the application by the link below send requests to bpm'online. Please use it as an example. 

https://github.com/EugenePodkovka/Forms-3rd-Party-Integration-Bpmonline

I tried with Postman and it's working! I'm using DataService web service instead of Odata by following this documentation

https://academy.bpmonline.com/documents/technic-sdk/7-13/dataservice-re…

https://academy.bpmonline.com/documents/technic-sdk/7-12/how-call-confi…

 

I'm working on classings to connect to bpm'online. One of those classes I developed in PHP.



I have published a repository on Github with the source code.

https://github.com/idevol/bpmonline-dataservice-connectors

Thank you for your reply .

the problem was the way I passed cookies , it's working with   'curl_setopt($request, CURLOPT_COOKIE, $cookieString);'

Louis Tisseur,

Hi,



I am getting the same error 403 - Forbidden: Access is denied. Have you fixed your issue? Could you please share how you are able to fix it.

 

Fulgen Ninofranco,

 

Hi,

 

If you receive the “403 - Forbidden: Access is denied” error when calling AuthService.svc/Login, most likely, the problem is related to cookies.

Please pay attention to the previous comments, as they should be helpful.

 

You can read more about working with cookies in this article - https://academy.creatio.com/documents/technic-sdk/7-13/how-call-configuration-services-using-postman.

 

Best regards,

Natalia

Show all comments

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?

 

Like 0

Like

3 comments

Ryan Farley,

Thank you! But maybe somebody else can help.

Hello

The following functionality is best achieved with the application like this:

https://marketplace.bpmonline.com/app/file-x-bpmonline

Since the bpmonline does not allow editing the files online, whereas the application allows it via the integration with GDocs or OneDrive.

Best regards,

Matt

Show all comments

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?

Like 0

Like

2 comments

Try changing the ComparisonType from:

"ComparisonType":16

To 

ComparisonType":9

9 = STARTS_WITH (16 = "NOT_EXISTS)

https://academy.bpmonline.com/api/jscoreapi/7.12.0/index.html#!/api/Ter…

Thank you.

It worked

Show all comments
import json
import requests
 
login_url = "https://my.bpmonline.com/ServiceModel/AuthService.svc/Login"
headers = {"Content-Type": "application/json"}
login_json = {"UserName": "Supervisor", "UserPassword": "password"}
 
# Login and get the session cookie
session = requests.post(login_url, headers=headers, json=login_json)
 
select_url = 'https://my.bpmonline.com/0/dataservice/json/reply/SelectQuery'
 
# Set the http header BPMCSRF to run the JSON query
headers['BPMCSRF'] = session.cookies.get_dict()['BPMCSRF']
 
# The JSON query to DataService
select_contact_json = {
    "RootSchemaName":"Contact",
    "OperationType":0,
    "Columns":{
        "Items":{
            "GivenName":{
                "Expression":{
                    "ExpressionType":0,
                    "ColumnPath":"GivenName"
                }
            },
            "MiddleName":{
                "Expression":{
                    "ExpressionType":0,
                    "ColumnPath":"MiddleName"
                }
            },
            "Surname":{
                "Expression":{
                    "ExpressionType":0,
                    "ColumnPath":"Surname"
                }
            },
            "BirthDate":{
                "Expression":{
                    "ExpressionType":0,
                    "ColumnPath":"BirthDate"
                }
            }
        }
    },
    "AllColumns":1
}
 
select_contact_response = requests.post(select_url, headers=headers, cookies=session.cookies, json=select_contact_json)
select_contact_json_data = json.loads(select_contact_response.text)
 
# print(select_contact_response.status_code)
print(select_contact_response.text)

 

Like 0

Like

Share

0 comments
Show all comments

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.

Like 0

Like

1 comments

Dear Romadan,

Not all queries can be easily translated in a single DataService request. In the most complex cases consider refactoring a query into few smaller queries and sending them one by one storing the result from first query and passing it as a parameter to the next query. 

Show all comments

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.

Like 0

Like

3 comments

Hi Romadan, 

Have you tired the business process web call element? You could even try the script element.

I had the same issue and it looked to me that you have to use the C# language. 

 

Dear Romadan,

Firstly, based on the error, it is most likely, that authorization hasn't passed well. Please make sure, that you have done all the steps described in the article as an example, particularly adding needed namespaces for the DataService to work, as well as, adding methods to authenticate and create records.

https://academy.bpmonline.com/documents/technic-sdk/7-12/dataservice-adding-records 

Also, yes, as Philip already mentioned, you have to use C# language. Moreover, you can see the example of the working InsertQuery by opening web debugger application like Telerik Fiddler and tracing the insert action made within the bpm'online system. You will find all needed information on what InsertQuery is made of, as insert, update, delete queries in the system are executed by the means of DataService.

To sum up with, if you are not comfortable to use C# language I would recommend to use OData protocol instead. Please see the implementation of OData requests with its examples in the system:

https://academy.bpmonline.com/documents/technic-sdk/7-12/possibilities-bpmonline-integration-over-odata-protocol

Finally, here is a documentation on how to call configuration services using Postman:

https://academy.bpmonline.com/documents/technic-sdk/7-12/how-call-configuration-services-using-postman

Hope you find it helpful!

Regards,

Anastasia

Thanks Anastasia Botezat for your feedback.

I have solved this problem.

The problem is like you say. I dont complete passed auth in header, and have miss passed data json to web service bpm'online.



Philip Wierciszewski, I success implement data service using another language beside use c#.

Show all comments