Question

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<string, ColumnExpression>()
                                 {
                                     {
                                         "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

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

Log in or register to comment
Question

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

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

Log in or register to comment
Question

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

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

Log in or register to comment
Question

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

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

Log in or register to comment
Article
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)

 

Share

0 comments
Log in or register to comment
Question

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 &gt;= '2016-09-01' AND fd.departure_date &lt;= '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 &gt;= '2017-09-01' AND fd_ly.departure_date &lt;= '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 &gt;= '2016-09-01' AND fd.departure_date &lt;= '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

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. 

Log in or register to comment
Question

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

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#.

Log in or register to comment
Question

Hi Team,

I am fetching list of records from account which are modified yesterday or certain date. But it throws Internal Server Error. 

Below is my code snippet:

 

         var selectFilters = new Filters()
         {

                    FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.FilterGroup,

                Items = new Dictionary<string, Filter>
                {
                    {
                        "FilterByCreatedOn",
                        // Value.
                        new Filter
                        {

                            FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.CompareFilter,

                            ComparisonType = FilterComparisonType.GreaterOrEqual,

                            LeftExpression = new BaseExpression ()
                            {
                                // Expression type - SchemaColumn.
                                ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
                                // Path to the column relative to the root schema.
                                ColumnPath = "ModifiedOn",
                            },
                            // Filter range final expression.
                            RightExpression= new BaseExpression ()
                            {

                                ExpressionType = EntitySchemaQueryExpressionType.Parameter,

                                Parameter = new Parameter ()
                                {

                                    DataValueType = DataValueType.DateTime,
                                    Value = "5/11/2018" or new DateTime(2018,05,11)
                                }
                            }
                        }
                    }
                }
           };
 

Like

11 comments

Hi

If you remember, bpmonline itself uses DataService for connecting to server. So the better way to apply a correct filter is to look at a browser console. Just apply an advanced filter you want in the interface and then watch Network section in the browser console.

In c# you should use a standart TimeStamp: 

const string startDate = @"""\""2000-12-31T09:41:59\"""""""""";

 

Peter Vdovukhin,

Thank you so much. It is really helpful for me. I have tried exactly like this but still getting Internal Server Error.

I do not understand, It was helpful or you still getting the error?

It is helpful that I get to know some of the things from this. But I have tried exactly like console filter and still getting error. Not able to resolve issue.

Please, post here a complete code example with validation. So I could reproduce it under my environment

 

 

 

 

 

 

Here is my complete code of my console application

 

 class ResponseStatus
    {
        public int Code { get; set; }
        public string Message { get; set; }
        public object Exception { get; set; }
        public object PasswordChangeUrl { get; set; }
        public object RedirectUrl { get; set; }
    }
    class Program
    {
        private const string baseUri = @"https://[example].bpmonline.com";
        // Query string to the Login method of the AuthService.svc service.
        private const string authServiceUri = baseUri + @"/ServiceModel/AuthService.svc/Login";
        // SelectQuery path string.
        private const string selectQueryUri = baseUri + @"/0/DataService/json/SyncReply/SelectQuery";
        // Bpm'online authentication cookie.
        private static CookieContainer AuthCookie = new CookieContainer();
        private static bool TryLogin(string userName, string userPassword)
        {
            // Creating an instance of the authentication service request.
            var authRequest = HttpWebRequest.Create(authServiceUri) as HttpWebRequest;
            // Defining the request's method.
            authRequest.Method = "POST";
            // Defining the request's content type.
            authRequest.ContentType = "application/json";
            // Enabling the use of cookie in the request.
            authRequest.CookieContainer = AuthCookie;

            // Placing user credentials to the request.
            using (var requestStream = authRequest.GetRequestStream())
            {
                using (var writer = new StreamWriter(requestStream))
                {
                    writer.Write(@"{
                ""UserName"":""" + userName + @""",
                ""UserPassword"":""" + userPassword + @"""
                }");
                }
            }

            // Auxiliary object where the HTTP reply data will be de-serialized.
            ResponseStatus status = null;
            // Getting a reply from the server. If the authentication is successful, cookie will be placed to the AuthCookie property.
            // These cookies can be used for subsequent requests.
            using (var response = (HttpWebResponse)authRequest.GetResponse())
            {
                using (var reader = new StreamReader(response.GetResponseStream()))
                {
                    // De-serialization of the HTTP reply to an auxiliary object.
                    string responseText = reader.ReadToEnd();
                    status = new System.Web.Script.Serialization.JavaScriptSerializer().Deserialize<ResponseStatus>(responseText);
                }

            }

            // Checking authentication status.
            if (status != null)
            {
                // Authentication is successful.
                if (status.Code == 0)
                {
                    return true;
                }
                // Authentication is unsuccessful.
                Console.WriteLine(status.Message);
            }
            return false;
        }
        static void AddAuthCookie(HttpWebRequest request)
        {
            request.CookieContainer = AuthCookie;
            // Adding a CSRF token to the request title.
            CookieCollection cookieCollection = AuthCookie.GetCookies(new Uri(authServiceUri));
            string csrfToken = cookieCollection["BPMCSRF"].Value;
            ((HttpWebRequest)request).Headers.Add("BPMCSRF", csrfToken);
        }
        static void Main(string[] args)
        {
            if (!TryLogin("Supervisor", "Supervisor1"))
            {
                return;
            }
            try
            {
                var selectQuery = new SelectQuery()
                {
                    RootSchemaName = "Account",
                    Columns = new SelectQueryColumns()
                };

                var columnExpressionName = new ColumnExpression()
                {
                    ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
                    ColumnPath = "Name"
                };
                var selectQueryColumnName = new SelectQueryColumn()
                {
                    Caption = "Company Name",
                    OrderDirection = OrderDirection.Ascending,
                    // Sorting order position.
                    OrderPosition = 0,
                    // Expression that specifies column type.
                    Expression = columnExpressionName
                };

                var columnExpressionContactName = new ColumnExpression()
                {
                    // Expression type — subquery.
                    ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
                    // Path to column in relation to root schema.
                    ColumnPath = "PrimaryContact",
                };
                var selectQueryColumnContactName = new SelectQueryColumn()
                {
                    //Title.
                    Caption = "Full Name",
                    // Expression, which specifies column type.
                    Expression = columnExpressionContactName
                };

                var columnExpressionJobTitle = new ColumnExpression()
                {
                    // Expression type — subquery.
                    ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
                    // Path to column in relation to root schema.
                    ColumnPath = "JobTitle"
                };
                var selectQueryColumnJobTitle = new SelectQueryColumn()
                {
                    //Title.
                    Caption = "Job Title",
                    // Sorting direction — ascending.
                    OrderDirection = OrderDirection.Ascending,
                    // Sorting order position.
                    OrderPosition = 1,
                    // Expression, which specifies column type.
                    Expression = columnExpressionJobTitle
                };

                var columnExpressionREPCode = new ColumnExpression()
                {
                    // Expression type — subquery.
                    ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
                    // Path to column in relation to root schema.
                    ColumnPath = "UsrREPCode"
                };
                var selectQueryColumnREPCode = new SelectQueryColumn()
                {
                    //Title.
                    Caption = "REP Code",
                    // Sorting direction — ascending.
                    OrderDirection = OrderDirection.Ascending,
                    // Sorting order position.
                    OrderPosition = 2,
                    // Expression, which specifies column type.
                    Expression = columnExpressionREPCode
                };

                var columnExpressionDAC = new ColumnExpression()
                {
                    // Expression type — subquery.
                    ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
                    // Path to column in relation to root schema.
                    ColumnPath = "UsrDAC"
                };
                var selectQueryColumnDAC = new SelectQueryColumn()
                {
                    //Title.
                    Caption = "DAC",
                    // Expression, which specifies column type.
                    Expression = columnExpressionDAC
                };

                var columnExpressionOwner = new ColumnExpression()
                {
                    // Expression type — subquery.
                    ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
                    // Path to column in relation to root schema.
                    ColumnPath = "Owner"
                };
                var selectQueryColumnOwner = new SelectQueryColumn()
                {
                    //Title.
                    Caption = "Owner",
                    // Sorting direction — ascending.
                    OrderDirection = OrderDirection.Ascending,
                    // Sorting order position.
                    OrderPosition = 4,
                    // Expression, which specifies column type.
                    Expression = columnExpressionOwner
                };

                var columnExpressionPhoneNumber = new ColumnExpression()
                {
                    // Expression type — SchemaColumn.
                    ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
                    // Path to column in relation to root schema.
                    ColumnPath = "Phone"
                };
                var selectQueryColumnPhoneNumber = new SelectQueryColumn()
                {
                    //Title.
                    Caption = "Contact Number",
                    // Expression, which specifies column type.
                    Expression = columnExpressionPhoneNumber
                };

                var columnExpressionEmail = new ColumnExpression()
                {
                    // Expression type — subquery.
                    ExpressionType = EntitySchemaQueryExpressionType.SubQuery,
                    // Path to column in relation to root schema.
                    ColumnPath = "[Account:Contact].Email"
                };
                var selectQueryColumnEmail = new SelectQueryColumn()
                {
                    //Title.
                    Caption = "Email",
                    // Sorting direction — ascending.
                    OrderDirection = OrderDirection.Ascending,
                    // Sorting order position.
                    OrderPosition = 1,
                    // Expression, which specifies column type.
                    Expression = columnExpressionEmail
                };

                var columnExpressionCity = new ColumnExpression()
                {
                    // Expression type — subquery.
                    ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
                    // Path to column in relation to root schema.
                    ColumnPath = "City"
                };
                var selectQueryColumnCity = new SelectQueryColumn()
                {
                    //Title.
                    Caption = "City",
                    // Expression, which specifies column type.
                    Expression = columnExpressionCity
                };

                var columnExpressionState = new ColumnExpression()
                {
                    // Expression type — subquery.
                    ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
                    // Path to column in relation to root schema.
                    ColumnPath = "Region"
                };
                var selectQueryColumnState = new SelectQueryColumn()
                {
                    //Title.
                    Caption = "State",
                    // Expression, which specifies column type.
                    Expression = columnExpressionState
                };

                var columnExpressionCountry = new ColumnExpression()
                {
                    // Expression type — subquery.
                    ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
                    // Path to column in relation to root schema.
                    ColumnPath = "Country"
                };
                var selectQueryColumnCountry = new SelectQueryColumn()
                {
                    //Title.
                    Caption = "Country",
                    // Expression, which specifies column type.
                    Expression = columnExpressionCountry
                };

                // Adding columns to query.
                selectQuery.Columns.Items = new Dictionary<string, SelectQueryColumn>()
                {
                    {
                        "Company Name",
                        selectQueryColumnName
                    },
                    {
                        "Full Name",
                        selectQueryColumnContactName
                    },
                    {
                        "Job Title",
                        selectQueryColumnJobTitle
                    },
                    {
                        "REP Code",
                        selectQueryColumnREPCode
                    },
                    {
                        "DAC",
                        selectQueryColumnDAC
                    },
                    {
                        "Owner",
                        selectQueryColumnOwner
                    },
                    {
                        "Contact Number",
                        selectQueryColumnPhoneNumber
                    },
                    //{
                    //    "Email",
                    //    selectQueryColumnEmail
                    //},
                    {
                        "City",
                        selectQueryColumnCity
                    },
                    {
                        "State",
                        selectQueryColumnState
                    },
                    {
                        "Country",
                        selectQueryColumnCountry
                    }
                };
                // Query filters.
                var selectFilters = new Filters()
                {
                    FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.FilterGroup,
                    IsEnabled = true,
                    RootSchemaName = "Account",
                    Items = new Dictionary<string, Filter>
                    {
                        {
                            "FilterByCreatedOn",
                            new Filter
                            {
                                FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.CompareFilter,
                                ComparisonType = FilterComparisonType.Greater,
                                IsEnabled = true,
                                LeftExpression = new BaseExpression ()
                                {
                                    ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
                                    ColumnPath = "ModifiedOn",
                                },
                                RightExpression= new BaseExpression ()
                                {
                                    ExpressionType = EntitySchemaQueryExpressionType.Parameter,

                                    Parameter = new Parameter ()
                                    {
                                        DataValueType = DataValueType.DateTime,
                                        Value= "2018-05-11T11:17:04.000"
                                    }
                                },
                                TrimDateTimeParameterToDate = true,
                            }
                        },
                        {
                            "FilterByName",
                            new Filter
                            {
                                FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.CompareFilter,
                                ComparisonType = FilterComparisonType.Contain,
                                IsEnabled = true,
                                LeftExpression = new BaseExpression ()
                                {
                                    ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
                                    ColumnPath = "Name",
                                },
                                RightExpression= new BaseExpression ()
                                {
                                    ExpressionType = EntitySchemaQueryExpressionType.Parameter,

                                    Parameter = new Parameter ()
                                    {
                                        DataValueType = DataValueType.Text,
                                        Value= "Test"
                                    }
                                },
                                TrimDateTimeParameterToDate = true,
                            }
                        }
                    }
                };
                selectQuery.Filters = selectFilters;
                // Serialization of an instance of query class to add to JSON string.
                var json = new JavaScriptSerializer().Serialize(selectQuery);
                byte[] jsonArray = Encoding.UTF8.GetBytes(json);

                var selectRequest = HttpWebRequest.Create(selectQueryUri) as HttpWebRequest;
                selectRequest.Method = "POST";
                selectRequest.ContentType = "application/json";
                // Adding earlier received authentication cookies to a data fetch query.
                selectRequest.CookieContainer = AuthCookie;
                selectRequest.ContentLength = jsonArray.Length;

                AddAuthCookie(selectRequest);
                using (var requestStream = selectRequest.GetRequestStream())
                {
                    requestStream.Write(jsonArray, 0, jsonArray.Length);
                }
                using (var response = (HttpWebResponse)selectRequest.GetResponse())
                {
                    using (StreamReader reader = new StreamReader(response.GetResponseStream()))
                    {
                        var result = reader.ReadToEnd();
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);                
            }
        }
    }

Pay attention to this code: private const string baseUri = @"https://[example].bpmonline.com";
it should be the address of your application

yes i know... I have just write down example in this code only... for comment

 

You can put there 'example' instead of 'example'. It will give you exact scenario. Thanks

You passed a wrong date format. Again, if look at browser console when applying a date filter you will see the next format: ""2018-06-05T23:59:59.999"". So you should escape one more quote mark to rich the goal in c#: Value = "\""+DateTime.Now+ "\""

Okay thanks.

Log in or register to comment
Question

I Riddhi Jadeja partner with bpm'online, developing first project in bpm'online. I want call bpm'online Data-Service for reading records. I have created third party console application and write code to call data service.

For that I have added reference for :

Terrasoft.Nui.ServiceModel.DataContract

Terrasoft.Core.Entities

Terrasoft.Common

But when I build my project it gives me error like following:

 

The type or namespace name 'Terrasoft' could not be found (are you missing a using directive or an assembly reference?)

 

Like

5 comments

Hello.

According to this article step 1 in the References section of the project, add dependencies from the following libraries:

  • System.Web.Extensions.dll – class library included in .NET Farmework;
  • Terrasoft.Core.dll – library of base bpm'online server core classes. It can be found using the following path: [Bpm'online setup catalog]\Terrasoft.WebApp\bin\Terrasoft.Core.dll;
  • Terrasoft.Nui.ServiceModel.dll — application service class library. It can be found using the following path: [Bpm'online setup catalog]\Terrasoft.WebApp\bin\Terrasoft.Nui.ServiceModel.dll;
  • Terrasoft.Common.dll – library of base bpm'online server core classes. It can be found using the following path: [Bpm'online setup catalog]\Terrasoft.WebApp\bin\Terrasoft.Common.dll.

And then your using directives will work.

Hello,

Thanks for you reply.

I have already added reference for above dependencies. But still its not working.

See below code snippet:

I've just created a console app and all works fine. Maybe you use an old version of .NET Framework or reference wrong dlls... Compare your code with mine:

Peter Vdovukhin,

Have you used core console application or normal one?

I have used normal one. See below:

Issue has been resolved now. It was framework version issue. It requires .Net Framework 4.7.1 or above.

Thanks.

Log in or register to comment
Question

Has anyone been able to use integration SaaS solutions such as Zapier or MuleSoft with bmp'online?  I realize the bmp'online is RESTful and may require some additional effort.  I am mostly focused on a one-way sync to create leads.   Hoping to find a way to hit the DataServices side of bmp'online.

Like

1 comments

Dear Stephen,

We already have the suggestion to add the out-of-the-box integration with the services registered and passed to the appropriate team. It will be taken into consideration for the further releases. 

At the moment the integration can be created via OData/DataService in case the mentioned services have the open API. 

Lisa

Log in or register to comment