We are developing an external application to Creatio with integration via oData.

We need to know how to obtain with odata the required fields of a schema, for example contact and the data type of each field.

 

Thank you!

Regards

Like 0

Like

3 comments
Best reply

Hello Uriel,

Once authenticated normally, you can do a POST to the following (it's DataService, but when you authenticate for OData it will also work for DataService, even if you're using OData for the other requests):

/0/DataService/json/SyncReply/RuntimeEntitySchemaRequest

Include the following payload in the request:

{"uId":"25d7c1ab-1de0-4501-b402-02e0e5a72d6e"}

Where the uId included is the UId for the entity (you can get this from SysSchema or VwSysSchemaInWorkspace where ExtendParent = false). You'll get back a json payload with details for all columns, including the "isRequired" value for the column.

Ryan

Hello Uriel,

Once authenticated normally, you can do a POST to the following (it's DataService, but when you authenticate for OData it will also work for DataService, even if you're using OData for the other requests):

/0/DataService/json/SyncReply/RuntimeEntitySchemaRequest

Include the following payload in the request:

{"uId":"25d7c1ab-1de0-4501-b402-02e0e5a72d6e"}

Where the uId included is the UId for the entity (you can get this from SysSchema or VwSysSchemaInWorkspace where ExtendParent = false). You'll get back a json payload with details for all columns, including the "isRequired" value for the column.

Ryan

Ryan Farley,

Thank you! Have a nice weekend.

Ryan Farley,

Hello,

I need to update, using odata, a record in Creatio, but there are one or more fields that are required to be completed, on the Creatio side (Form_Page) that are not completed and I get error when updating those records using odata.


Is there any way to skip the required field rules (fields not related to the integration) and get the object update using odata?

Show all comments

We have a PowerBI report connecting to Creatio through OData, with a data source refresh every day at 7am MST. Every single day, it fails with this message.

---------------------------------------------------------------------------------------------------------------

Failure details: The last refresh attempt failed because of an internal service error. This is usually a transient issue. If you try again later and still see this message, contact support.

{"error":{"code":"ModelRefresh_ShortMessage_ProcessingError","pbi.error":{"code":"ModelRefresh_ShortMessage_ProcessingError","parameters":{},"details":[{"code":"Message","detail":{"type":1,"value":"OData: Request failed: The maximum number of redirections allowed was reached. (Found)"}}],"exceptionCulprit":1}}} Table: KwlBusinessLineCollection.

Then our Administrator goes into PowerBI and resubmits the refresh manually, and it works.

----------------------------------------------------------------------------------------------------------------

Has anyone encountered this or know the cause?

Thanks,

Like 0

Like

4 comments

Dear Heather, 

Please make sure that the flag LogSessionForSessionLessRequest is set to false in web.config in the root folder in installation files of your site. 

Dennis Hudson,

Is that something I have access to through the development console?

Heather,

 

If you have an on-site application you can find the web config file in the installation files. If you have a cloud application, you can request this information from the support team (support@creatio.com).

Heather,

 Did you manage to connect using Odata?

Show all comments
Question

Hi Community,

 

https://academy.creatio.com/documents/technic-sdk/7-15/working-creatio-objects-over-odata-protocol-using-http-request

 

As per Academy,  "Records are returned by pages, 40 records per page. If a request is supposed to return more than 40 records, the reception of the next page must be ensured to reach the end of the current page.". How can I execute automatically and get the data from next page? Any idea? Thanks.  

 

Like 0

Like

4 comments

Hello Fulgen,

 

The $top query parameter can be used to indicate how many records to return. While 40 is the default, if you use a value larger than the total number of records, all records will be returned. For example: 

ContactCollection?$top=100000

Will return 100,000 contacts, or if less exist, all will be returned. Meaning if only 9000 contacts exist, all will be returned. 

 

You can also page the results, which is a better option in many/most cases. You can page the results by using $top along with $skip. For example, to page 100 records at a time, the first request would be:

ContactCollection?$top=100

Then the next request would be:

ContactCollection?$top=100&$skip=100

to get the next 100 records. Then the next would be:

ContactCollection?$top=100&$skip=200

You would keep incrementing the $skip by the number used for $top with each request to get the next page of results.

 

Ryan

Ryan Farley,

Neither DataService nor Odata will return over 20,000 records

Ryan Farley,



Hi Ryan,

 

After exscuting this code below



XDocument xmlDoc = XDocument.Load(dataResponse.GetResponseStream());



I noticed there is a descendant "link" which value of attribute "rel" is "next", is that the link for next page?

Kirill Krylov CPA,

Thanks Kirill, I figured there was a hard limit somewhere and 20,000 makes sense as I've seen that limit with reads in processes as well - never tried loading more than 20,000 via OData/DataService, not a good idea - Paging is always the way to go!

Show all comments

Dear mates,

I m building an external form which must insert/update an object collection with oData.

GET - POST and DELETE works fine

But if i want to update (PUT), i ve got the following error: the item is not found

{"error":{"code":"1","message":{"lang":"","value":"L'\u00e9l\u00e9ment UsrExternalisation est

    introuvable
."},"innererror":{"message":"L'\u00e9l\u00e9ment UsrExternalisation est

    introuvable
.","type":"Terrasoft.Common.ItemNotFoundException","stacktrace":" at

    Terrasoft.Core.Entities.Services.EntityLazyProxy.<>c__DisplayClass28_0....

My PUT function works fine in other object update.

Do you have an idea why this error occurs ?

Thanks,

Nicolas

Like 0

Like

2 comments

What does the URL look like that you're doing the PUT on? It needs to look like this (but obviously with the record Id instead of an empty Guid):

https://creatioaddress/0/ServiceModel/EntityDataService.svc/ContactCollection(guid'00000000-0000-0000-0000-000000000000')

Also, even though the docs specify a PUT for updates, it will also accept a PATCH (which is what I usually use)

Ryan

Hi Ryan,

I don't understand why my PUT calls work fine today without any modification on my side.

Here's the URL:

https://urltocreatio/0/ServiceModel/EntityDataService.svc/UsrExternalisationCollection(guid'b83bedb1-ad72-4c6a-9965-88677a13dfe1')

I see with my support team if they have modified something.

Thank you for the answer, have a nice day.

Nicolas

 

=>>>>

I had the problem one more time.

it was because i didn't compile the database new elements before to try my oData access

Show all comments

Hi,

I need Users to get registered as Creatio Portal User and get authenticate on my own website where I currently showing some data from Creatio CRM using Odata service.

Is it possible through Odata service or else please suggest easiest way?

 

Any help will be highly appreciable

 

Regards

Like 0

Like

1 comments

Dear Muhammad,

Could you please provide more details on that you want to achieve?

Best regards,

Norton

Show all comments

Hi

I'm using bpm'online OData service to filter the account collection.

It looks like that the tolower function is not supported.

I'm using bpm'online 7.14.

 

http://localhost:7004/0/ServiceModel/EntityDataService.svc/AccountCollection?$top=20&$filter=substringof('test',tolower(Name))&$inlinecount=allpages

 

Any idea how to add tolower function to the supported functions list.

 

Thank you

Mohamed

Like 0

Like

3 comments

Unfortunately, bpm'online doesn't support the tolower function of work with the OData protocol strings. However, it's possible to use the toupper function instead.

According to the <edmx:DataServices /> element in the metadata, the bpm'online OData implementation is version 3.0, however, I've found that it doesn't always match up to the 3.0 spec (I'm not sure if that's the version that bpm'online officially claims their OData API implements). I don't really have any answers as to what is or isn't implemented. As Alina mentioned, toupper does work.

However, also worth pointing out, by default bpm'online OData string filters aren't case sensitive anyway. Using "Name eq 'Our company'" and "Name eq 'OuR COMpAnY'" both return the same record with the name "Our company". 

Ryan

Thanks. I have changed it to toupper and its working.

Show all comments

Hi Community,

Currently I am not able to filter record using ODATA for my lookup field, I am getting object reference error. Below is my request string.

UsrEmiratesId is the lookup field

"UsrAreaCollection?$top=1000&$filter=UsrEmiratesId eq guid'" + emirateId + "'&select=Id, Name, UsrEmiratesId"

 

But when I filter the record using Id which the PK column, request is successful, what is wrong with this lookup field why it is not working

 

Like 0

Like

1 comments

Dear Fulgen,

The possible reason for an error could be in syntax of lookup in the request. Please try to modify the request string, so that you filter by UsrEmirates/Id. For example, as if I would like to select orders with certain opportunitites, I would filter by Opportunity/Id

Hope you find it helpful,

Anastasia

Show all comments

Hi Community,

Currently I was able to get data from Order object using Odata calling "OrderCollection". Order is connected to Account object. Now I want to retrieve account details also. Is it posible to achieve this by having one request only from "OrderCollection" no need of calling "OrderCollection" then "AccountCollection". Any idea how can i achieve this?

Like 0

Like

1 comments

Dear Fulgen,

Unfortunately, there is no way to retrieve account data without making a request directly to the AccountCollection. You need firstly get the Id of needed account and afterwards make a request to Account object.

Regards,

Anastasia

Show all comments

Please don't forget to change patches and give an access to the file modifying permission.

using System;
using System.IO;
using System.Net;
using System.Xml;
using System.Xml.Linq;
using System.Runtime.Serialization.Formatters.Binary;
 
namespace ODataFile
{
    class ODataFileHelper
    {
        public string serverUri = "https://014246-studio.bpmonline.com/0/ServiceModel/EntityDataService.svc/";
        public string authServiceUri = "https://014246-studio.bpmonline.com/ServiceModel/AuthService.svc/Login";
        public string userName = "Supervisor";
        public string userPassword = "Supervisor";
        public int MaxLoginAttempts = 3;
        public string CookiesFilePath = Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location), "cookies.dat");
 
        public readonly XNamespace ds = "http://schemas.microsoft.com/ado/2007/08/dataservices";
        public readonly XNamespace dsmd = "http://schemas.microsoft.com/ado/2007/08/dataservices/metadata";
        public readonly XNamespace atom = "http://www.w3.org/2005/Atom";
 
        CookieContainer AuthCookie = new CookieContainer();
        string CsrfToken = "";
        int LoginAttempts = 0;        
 
        public HttpWebResponse SendRequest(string requestURIstring, string method = "GET", XElement entry = null)
        {
            ReadCookiesFromDisk();
            while (LoginAttempts < MaxLoginAttempts)
            {
                HttpWebRequest dataRequest = BuildRequest(requestURIstring, method, entry);
                dataRequest.CookieContainer = AuthCookie;
                dataRequest.Headers.Add("BPMCSRF", CsrfToken);
                try
                {
                    var dataResponse = (HttpWebResponse)dataRequest.GetResponse();
                    WriteCookiesToDisk();
                    LoginAttempts = 0;
                    return dataResponse;
                }
                catch (WebException ex)
                {
                    var webResponse = (HttpWebResponse)ex.Response;
                    if (webResponse != null && webResponse.StatusCode == HttpStatusCode.Unauthorized)
                    {
                        LoginAttempts += 1;
                        TryLogin();
                    }
                    else
                    {
                        return null;
                    }
 
                }
            }
            return null;
        }
 
        HttpWebRequest BuildRequest(string requestURIstring, string method = "GET", XElement entry = null)
        {
            var dataRequest = HttpWebRequest.Create(requestURIstring) as HttpWebRequest;
            dataRequest.Method = method;
            dataRequest.Accept = "application/atom+xml";
            dataRequest.ContentType = "application/atom+xml;type=entry";
 
            if (entry != null)
            {
                using (var writer = XmlWriter.Create(dataRequest.GetRequestStream()))
                {
                    entry.WriteTo(writer);
                }
            }
            return dataRequest;
        }
        void TryLogin()
        {
            var authRequest = HttpWebRequest.Create(authServiceUri) as HttpWebRequest;
            authRequest.Method = "POST";
            authRequest.ContentType = "application/json";
            authRequest.CookieContainer = AuthCookie;
            using (var requestStream = authRequest.GetRequestStream())
            {
                using (var writer = new StreamWriter(requestStream))
                {
                    writer.Write(@"{
                                ""UserName"":""" + userName + @""",
                                ""UserPassword"":""" + userPassword + @""",
                                ""SolutionName"":""TSBpm"",
                                ""TimeZoneOffset"":-120,
                                ""Language"":""En-us""
                                }");
                }
            }
 
            using (var response = (HttpWebResponse)authRequest.GetResponse())
            {
                WriteCookiesToDisk();
            }
        }
        void WriteCookiesToDisk()
        {
            using (Stream stream = File.Create(CookiesFilePath))
            {
                try
                {
                    BinaryFormatter formatter = new BinaryFormatter();
                    formatter.Serialize(stream, AuthCookie);
                }
                catch(Exception e)
                {
                    //ToDo: log error writing cookies to disk
                }
            }
        }
        void ReadCookiesFromDisk()
        {
            try
            {
                using (Stream stream = File.Open(CookiesFilePath, FileMode.Open))
                {
                    BinaryFormatter formatter = new BinaryFormatter();
                    AuthCookie = (CookieContainer)formatter.Deserialize(stream);
                    CookieCollection cookieCollection = AuthCookie.GetCookies(new Uri(authServiceUri));
                    CsrfToken = cookieCollection["BPMCSRF"].Value;
                }
            }
            catch(Exception e)
            {
                //ToDo: log error reading cookies from disk
            }
        }
    }
    class ODataFile
    {
        static void Main(string[] args)
        {
            GetOdataCollectionByAuthByHttpExample();
        }
        public static void GetOdataCollectionByAuthByHttpExample()
        {
            var helper = new ODataFileHelper();
            var uri = helper.serverUri + "ContactCollection?select=Id, Name";
            using (var dataResponse = helper.SendRequest(uri))
            {
                if (dataResponse != null)
                {
                    XDocument xmlDoc = XDocument.Load(dataResponse.GetResponseStream());
                }
            }
            using (var dataResponse = helper.SendRequest(uri))
            {
                if (dataResponse != null)
                {
                    XDocument xmlDoc = XDocument.Load(dataResponse.GetResponseStream());
                }
            }
            var content = new XElement(helper.dsmd + "properties",
                  new XElement(helper.ds + "Name", "Jhon Gilts"),
                  new XElement(helper.ds + "Dear", "Jhon"));
            var entry = new XElement(helper.atom + "entry",
                        new XElement(helper.atom + "content",
                        new XAttribute("type", "application/xml"), content));
            var uriForPost = helper.serverUri + "ContactCollection/";
            using (var dataResponse = helper.SendRequest(uriForPost, "POST", entry))
            {
                if (dataResponse != null)
                {
                    XDocument xmlDoc = XDocument.Load(dataResponse.GetResponseStream());
                }
            }
        }
    }
}

 

Like 0

Like

Share

3 comments

Thanks, Eugene.



Also, it is extremely important to include the code below to avoid creating a large number of requests in the SysUserSession table.

A new session is created for each OData request. A large number of requests may cause a database block.Use the ForceUseSession parameter (for GET-requests) or ForceUseSession header (for all request types) to use an existing session when sending an OData request. See details here https://academy.bpmonline.com/documents/technic-sdk/7-13/possibilities-…

 

C# sample:

 

HttpWebRequest dataRequest = BuildRequest(requestURIstring, method, entry);

dataRequest.CookieContainer = AuthCookie;

dataRequest.Headers.Add("BPMCSRF", CsrfToken);

dataRequest.Headers.Set("ForceUseSession", "true");

Thanks for sharing it.

 

Eric Garcia de ...,

The code provided above works correctly with the sessions. 

I've heard that "ForceUseSession" should be specified but i'm not sure if it actually affects something. Anyway, it doesn't make worse so I agree that it's better to use it. 

Additionally, any time when you create an integration, I recommend attaching fiddler and making sure that the BPMSESSIONID and the cookies are the same.

Correct, thanks a lot Eugene!

Show all comments

You'll need to adjust it to your needs, but it shows the main idea.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Xml;
using System.Xml.Linq;
using System.Drawing;
using System.Net.Http;
 
namespace ODataFileTransfer
{
    class ODataFileTransfer
    {
        private const string serverUri = "https://014246-studio.bpmonline.com/0/ServiceModel/EntityDataService.svc/";
        private const string authServiceUri = "https://014246-studio.bpmonline.com/ServiceModel/AuthService.svc/Login";
        private const string userName = "Supervisor";
        private const string userPassword = "Supervisor";
 
        private static readonly XNamespace ds = "http://schemas.microsoft.com/ado/2007/08/dataservices";
        private static readonly XNamespace dsmd = "http://schemas.microsoft.com/ado/2007/08/dataservices/metadata";
        private static readonly XNamespace atom = "http://www.w3.org/2005/Atom";
 
        public static CookieContainer AuthCookie = new CookieContainer();
        private static string CsrfToken = "";
        private static int LoginAttempts = 0;
 
        static void Main(string[] args)
        {
            GetOdataCollectionByAuthByHttpExample();
        }
 
        static void TryLogin()
        {
            var authRequest = HttpWebRequest.Create(authServiceUri) as HttpWebRequest;
            authRequest.Method = "POST";
            authRequest.ContentType = "application/json";
            authRequest.CookieContainer = AuthCookie;
            using (var requestStream = authRequest.GetRequestStream())
            {
                using (var writer = new StreamWriter(requestStream))
                {
                    writer.Write(@"{
                                ""UserName"":""" + userName + @""",
                                ""UserPassword"":""" + userPassword + @""",
                                ""SolutionName"":""TSBpm"",
                                ""TimeZoneOffset"":-120,
                                ""Language"":""En-us""
                                }");
                }
            }
            using (var response = (HttpWebResponse)authRequest.GetResponse())
            {
                CookieCollection cookieCollection = AuthCookie.GetCookies(new Uri(authServiceUri));
                CsrfToken = cookieCollection["BPMCSRF"].Value;
 
            }
        }
 
        static HttpWebResponse SendRequest(string requestURIstring, string method = "GET", XElement entry = null)
        {
            var dataRequest = HttpWebRequest.Create(requestURIstring) as HttpWebRequest;
            dataRequest.Method = method;
            dataRequest.CookieContainer = AuthCookie;
            dataRequest.Accept = "application/atom+xml";
            dataRequest.ContentType = "application/atom+xml;type=entry";
            dataRequest.Headers.Add("BPMCSRF", CsrfToken);
 
            if (entry!= null)
            {
                using (var writer = XmlWriter.Create(dataRequest.GetRequestStream()))
                {
                    entry.WriteTo(writer);
                }
            }
 
            try
            {
                var dataResponse = (HttpWebResponse)dataRequest.GetResponse();
                LoginAttempts = 0;
                return dataResponse;
            }
            catch (WebException ex)
            {
                var webResponse = (HttpWebResponse)ex.Response;
                if (webResponse != null && webResponse.StatusCode == HttpStatusCode.Unauthorized)
                {
 
                    TryLogin();
                    if(LoginAttempts < 3)
                    {
                        LoginAttempts += 1;
                        SendRequest(requestURIstring);
                    }
                    else
                    {
                        //here can be some handler or logger
                    }
                }
 
            }
            return null;
        }
 
        public static void GetOdataCollectionByAuthByHttpExample()
        {
            var uri = serverUri + "ContactCollection?select=Id, Name";
            using (var dataResponse = SendRequest(uri))
            {
                if (dataResponse != null)
                {
                    XDocument xmlDoc = XDocument.Load(dataResponse.GetResponseStream());
                    ProcessCollection(xmlDoc);
                }
            }
 
            using (var dataResponse = SendRequest(uri))
            {
                if (dataResponse != null)
                {
                    XDocument xmlDoc = XDocument.Load(dataResponse.GetResponseStream());
                    ProcessCollection(xmlDoc);
                }
            }
 
            var content = new XElement(dsmd + "properties",
                  new XElement(ds + "Name", "Jhon Gilts"),
                  new XElement(ds + "Dear", "Jhon"));
            var entry = new XElement(atom + "entry",
                        new XElement(atom + "content",
                        new XAttribute("type", "application/xml"), content));
 
            var uriForPost = serverUri + "ContactCollection/";
 
            using (var dataResponse = SendRequest(uriForPost, "POST", entry))
            {
                if (dataResponse != null)
                {
                    XDocument xmlDoc = XDocument.Load(dataResponse.GetResponseStream());
                }
            }
        }
 
        public static void ProcessCollection(XDocument xmlDoc)
        {
            var contacts = from entry in xmlDoc.Descendants(atom + "entry")
                           select new
                           {
                               Id = new Guid(entry.Element(atom + "content")
                                                       .Element(dsmd + "properties")
                                                       .Element(ds + "Id").Value),
                               Name = entry.Element(atom + "content")
                                               .Element(dsmd + "properties")
                                               .Element(ds + "Name").Value
                           };
            foreach (var contact in contacts)
            {
                // Implementing actions with contacts.
            }
        }
    }
}

Enjoy.

Like 1

Like

Share

0 comments
Show all comments