OData API
GET Requests
A GET Request is the receiving of data from the SAP Sales Cloud. You send a request - in the form of a URL - to the API interface and then receive the information in a JSON or XML format
Navigation for OData API
1. Test SAP Sales Cloud OData API with Postman 1.1. OData 1.2. Postman 2. GET Requests 2.1. SAP Sales Cloud Object Collections 2.2 Metadata 2.2.1. Principle 2.2.2. Attributes 2.2.3. SAP-Attributes 2.3. Work with Parameters 2.3.1. Filter 2.3.2. Work with Dates and Datetimes 2.3.3. Orderby 2.3.4. Top 2.3.5. Skip 2.3.6. Format 2.3.7. Select 2.3.8. Count 2.3.9. Inlinecount 2.3.10. Expand 2.4. Examples of GET Requests 2.5. Build OData Queries for Reports 3. X-CSRF-Token 4. POST Request for creating 4.1. Single POST Request 4.2. Create multiple entries with $batch 5. PATCH Request for updating 5.1. Single PATCH Request 5.2. Change multiple entries with $batch 6. DELETE Requests 7. OData Monitor 8. OData Service Explorer 9. Enable customized fields for the API interface 10. Connect Mircosoft Power BI® with SAP Sales Cloud API 11. Deep Links for URL to SAP Sales Cloud Objects
It is important to understand what object information you want to receive and therefore how to work with parameters. You will quickly find in practice that you will be working with nested requests. This means that when you get an information from an object – you need it as a parameter for another request.
2.1. SAP Sales Cloud Object Collections
With a GET request you query the data of a specific data table. Basically, it should be understood that SAP objects usually consist of several data tables. This means that when you request Data from one object, you have to know in which data table the information is located.
The best way to understand this relationship is to use the example of an account. Each account consist of one master data table. In here you will find information such as Account Name or Role – such as Prospect, Sold-to party, Ship-to party, etc. In summary: any information that can be uniquely assigned to a customer (1:1 Relation). To request the master data table you have to use the collection CorporateAccountCollection. But there are also other tables for Accounts available, such as Address, Sales Data, Attachments, Notes, etc. Here you can have multiple relations (1:n) since one Account can have multiple Attachments or Sales Data information.
To get an overview about all different collections you should use following SAP-Help webpage OData Documentation. Here you will get an overview of all different kind of collections.
2.2. Metadata
OData metadata provides a description for each data field for your API request. Here you can read all attributes, which helps you to proceed with your application.
2.2.1. Principle
OData service metadata can be filtered for all SAP Sales Cloud Objects. A request can look like this for all Objects:
or for only specific ones – in this case Opportunities and Sales Quotes:
Under the following link you will find all Entity Type names – OData Documentation (sap.com). As a second step it is important to understand how to read the output of the metadata request. The attribute Name of EntityType provides the information of the Object – in the below output example: Opportunity. The next important Tag is PropertyRef which will provide information about the primary key of this object – in this case: ObjectID. The last Tag is Property, which shows information in his elements about Name, Type, Nullable, etc of each data field.
<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx">
<edmx:DataServices m:DataServiceVersion="1.0" xmlns:m="http://schemas.microsoft.com/ado/2007/08/datase
rvices/metadata">
<Schema Namespace="c4codata" xmlns="http://schemas.microsoft.com/ado/2008/09/edm" xmlns:sap="http://
www.sap.com/Protocols/SAPData" xmlns:c4c="http://www.sap.com/Protocols/C4CData">
<EntityType Name="Opportunity">
<Key><PropertyRef Name="ObjectID"/></Key>
<Property Name="ObjectID" Type="Edm.String" Nullable="false" MaxLength="70" FixedLength="true" s
ap:creatable="false" sap:updatable="false" sap:filterable="true"/>
<Property Name="ProcessingTypeCode" Type="Edm.String" Nullable="true" MaxLength="4" FixedLength ="true" sap:creatable="true" sap:updatable="false" sap:filterable="true" sap:text="ProcessingTypeCodeText" c4c:value-help="OpportunityProcessingTypeCodeCollection"/>
…
2.2.2. Attributes
The following describes the attributes for the property tag in more detail.
Name
Contains the information about the name of the data field.
Type
Contains the information about the type of the data field. Following types are available:
SetAsWon, EffectiveIndicator
Default
In this attribute the default value for a field can be read. Depending on the data type, different values are permitted – for example, true or false for the boolean or only integers for the integer data type.
FixedLength
This attribute tells whether a certain length must be met on entry. The value of this attribute can be either true or false.
MaxLength
Defines a limit for the input of characters, for example maxlength=”30” means that a maximum of 30 characters is allowed. The value has to be an integer.
Nullable
Allowed values are either true or false. When the value is true, that means it is allowed to leave this data field empty, when false it is mandatory to have a value.
Scale
The value of the attribute scale has to be an integer. Scale tells you the number of digits in a decimal value behind the point. For example the scale of the decimal value 199.56 is 2 (scale=”2”) and the scale of 3.145 is 3 (scale=”3”).
Precision
The value of the attribute precision has to be an integer as well. Precision is the number of digits in a decimal number. For example the precision of 199.56 is 5 (precision=”5”) and the precision of 3.145 is 4 (precision=”4”).
2.2.3. SAP-Attributes
You will also find SAP attributes in the property tag, which typically start with “sap:”. All occurring attributes are described in more detail in the following table.
false
false
false
2.3. Work with Parameters
Whenever you work with databases, you can use parameters when querying the data from this database. These not only ensure that your query runs more performantly – according to the motto: call only selected data instead of all of them – but it also makes the processing of the data in a programming source code much easier, since no superfluous data has to be removed here anymore.
2.3.1. Filter
Each or several data entries in a data table can be filtered. It is possible to use AND but also OR relations. The simplest case is of course that only one data entry is filtered. This technique is best explained by means of examples. The examples are explained on the basis of the object Sales Quote.
Simple filter
Example:
https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/SalesQuoteCollection?$filter=ID eq ‘104905’
Explanation:
As you can see in the query, the Filter parameter is written with the dollar sign ($) in the URL. After that comes the condition. In this case it is the condition that the ID should be equal to the value 104905 – abbreviated by eq. The value to be filtered must be placed between single apostrophes. The term eq is a so called log query option, you will find an overview later in this chapter.
AND
Example:
Explanation:
As you can see from this example, the Filter parameter exists only once in a query. The AND – and later also the OR relation – are assigned as one value to the filter parameter. In this example, all offers are selected where the ID is greater than or equal (short: ge) to the value 104866. Again, note that the value 104886 is written in single apostrophes. The AND relation is appended with the lowercase word and. After that the second condition is written. In this case, the second condition is that the CurrencyCode field is equal to the value PLN (short: eq). In full words it means that only the offers are returned that have the currency Polish zloty. Again, the value PLN is written in apostrophes.
OR
Example:
Explanation:
The structure of the request for an OR relationship is the same as for an AND. The OR condition is entered via the lower case word or. In this example you can see that the code of the document language – DocumentLanguageCode – is selected. As output you get here all offers which were made in PT – Portuguese or CN – Chinese.
AND & OR
Example:
Explanation:
With nested filter parameters you must always be careful in which order you insert them into the request and how you group them. In the example shown, all quotes are selected where the probability is greater (short: gt) than 50 and the account has (short: eq) the account number 1000638 or the account number 1090596. Note that fields of type Decimal Number are not written in apostrophes, since you are working with real numbers compared to the ID. The filter parameters you want to group are written in brackets, like the account numbers in this example.
Overview of Logs Query Options
There is a wide range of different logs query options. You have already seen some of them in the examples already shown – such as eq. A complete overview of all logs query options can be found in the table below.
At the end of this section you will find a list of the logical operators AND and OR.
At this point it should also be mentioned that you can work with the wildcard *. As an example, you can also specify the following in the request Name eq ‘*C4C*’. This means that the search term Admin is searched for in the Name, whereby it does not matter how many characters there are before or after the Admin search term.
2.3.2. Work with Dates and Datetimes
Interestingly, many people find it difficult to deal in programming with a date, a datetime or times. The technical handling is precisely written down in the ISO 8601 standard. In the following all techniques are explained with simple examples.
Datetime
Example:
Explanation:
In this example, all offers are queried that were created after (or in other words greater than – short: gt) the 1st of July 2021 at 10:00. The string input of the Datetime has to be written in single apostrophes into the function datetimeoffset. It is important at this point to understand how to write the Standard Datetime:
YYYY – MM – DD T hh : mm : ss . fff K
Date
Date fields contain only the date without specifying a time. When queried, the Kind Property can be skipped and the time is set to 0. For date only fields use the function datetime‘YYYY-MM-DD T 00:00:00’.
Example:
Explanation:
This query filters all offers where the ValidFromtDate is between January 01, 2018 and December 31, 2018. The date is written in the format YYYY-MM-DD T 00:00:00 and noted in single apostrophes in the datetime function.
Time
In SAP Sales Cloud, there are also data fields which only specify a time. The time is noted as follows:
PT ## H : ## M : ## S
The P stands for Period, T for Time, the H for Hours, the M for Minutes and the S for Seconds. The two # characters stand for the value, which must always be entered as two digits, even if the value is less than ten. A time is never empty on the database, but is specified as follows – if not filled: PT00H00M00S. For time fields you are using -when querying the function – time’PT##H:##M:##S’.
Example:
Explanation:
In this example, all offers are filtered where the SubmittedDate is after (greater than – short gt) 15 o’clock. As you can see here the time is noted in single apostrophes in the time function. If you want to filter for empty time fields then use following query:
Filter for date-fields which are not filled
In this example, all offers are queried were the Valid From Date is empty. Whenever a Date, Datetime or Time field is empty you will find the entry null in the database. A NULL value is a special indicator in database programming languages, which means that the data value does not exist in the database. In other words you can see it as a kind of placeholder to denote that values are missing in the database.
Output for Datetimes, Dates and Times
Below you can see the output formats in JSON and in XML format. In the XML output you can see the ISO 8601 formatting of the field types Date and Datetime. In JSON the output of Dates and Datetimes are noted in seconds after 1st of January in 1970, which is typically for JavaScript Programming. A recommended extension for JavaScript to work with date, datetime and clock times is moment.js (https://momentjs.com/). If you want to know more about converting from seconds to a date format, visit the following page: https://currentmillis.com/.
<!--?xml version="1.0" encoding="utf-8"?--><br />//XML<br />2021-07-27T09:43:48.7543320Z<br />2021-07-27T09:35:32.0000000Z<br />UTC<br />2021-07-27T00:00:00<br />2021-10-27T00:00:00<br />PT16H00M00S<br />
//JSON "DateTime": "/Date(1627379028754)/", "PriceDateTime": "/Date(1627378532000)/", "TimeZoneCode": "UTC", "ValidFromDate": "/Date(1627344000000)/", "ValidToDate": "/Date(1635292800000)/", "Z_SubmittedTime_KUT": "PT16H00M00S",
2.3.3. Orderby
When querying the data, you can already request the data in an ordered manner. This saves a lot of time in further processing in a source code. As an example it can be mentioned here that only the newest offer is to be processed in a program. Then you would have to work only the offer with the highest ID. In this case you work with the parameter $orderby. The best way to learn this Parameter better is again with examples.
Example 1:
Explanation:
In this example, all offers – since there is no $filter parameter here – are queried in descending order (short: desc) according to the ID. There is also the possibility to order ascending, then use the term asc – short for ascending. If you leave out both asc and desc in the query, the sorting will be ascending by default.
Example 2:
Explanation:
The second example orders two data fields. First, all offers are sorted in ascending order (asc) by the sales organization ID. Second, the offers are then sorted in descending order (desc) by probability. If there are multiple sorts in the query, separate the individual sorts with a comma.
2.3.4. Top
You can use the $top parameter to limit the number of entries in an XML or JSON output. For example, with $top=2 you output only the first two quotes in a query with SalesQuoteCollection. The $top parameter is best combined with the $orderby parameter. For example, you can sort the quotations by quotation value in descending order and $top=1 will return only one quotation – instead of all of them – with the highest quotation value.
2.3.5. Skip
You can use the $skip parameter to skip a certain amount of entries. The $skip parameter is best combined with the $orderby parameter. For example, you can sort the quotations by ID value in descending order and $skip=100 will return all entries after this 100th entry.
2.3.6. Format
You have the possibility to specify, with the $format parameter, whether you want to output a XML or a JSON format. Always write the $format parameter as the last parameter in your API query. If you do not define the $format parameter in the query, the XML format will always be selected by default. When querying, always write the value of the format – i.e. xml or json – in lowercase. The format parameter will then look like this:
XML stands for Extensible Markup Language and is a markup language for representing hierarchically structured data. Sometimes you also do find ATOM instead of XML. Atom is the name of an XML-based Web content and metadata syndication format. So instead of writing $format=xml you can also write $format=atom. The XML format can be processed in any programming language, but is best suited in programming languages such as PHP, ABAP, Pearl. Following one example how a XML file looks like when querying the Quote with ID 104821 from SAP Sales Cloud.
<?xml version="1.0" encoding="utf-8"?>
<feed xml:base="https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/" xmlns="http://www.w3.org/2005/Atom" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices">
<id>https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/SalesQuoteCollection
</id>
<title type="text">SalesQuoteCollection</title>
<updated>2021-07-23T17:57:14Z</updated>
<author>
<name/>
</author>
<link href="SalesQuoteCollection" rel="self" title="SalesQuoteCollection"/>
<entry m:etag="W/"datetimeoffset'2021-03-04T15%3A12%3A57.4644080Z'"">
<id>https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')</id>
<title type="text">SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')</title>
<updated>2021-07-23T17:57:14Z</updated>
<category term="c4codata.SalesQuote" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme"/>
<link href="SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')" rel="edit" title="SalesQuote"/>
<link href="SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/ObjectIdentifierMapping" rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/ObjectIdentifierMapping" type="application/atom+xml;type=feed" title="ObjectIdentifierMapping"/>
<link href="SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/SalesQuoteAttachmentFolder" rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/SalesQuoteAttachmentFolder" type="application/atom+xml;type=feed" title="SalesQuoteAttachmentFolder"/>
<link href="SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/SalesQuoteExternalPrice" rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/SalesQuoteExternalPrice" type="application/atom+xml;type=feed" title="SalesQuoteExternalPrice"/>
<link href="SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/SalesQuoteItem" rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/SalesQuoteItem" type="application/atom+xml;type=feed" title="SalesQuoteItem"/>
<link href="SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/SalesQuoteOutput" rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/SalesQuoteOutput" type="application/atom+xml;type=feed" title="SalesQuoteOutput"/>
<link href="SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/SalesQuoteParty" rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/SalesQuoteParty" type="application/atom+xml;type=feed" title="SalesQuoteParty"/>
<link href="SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/SalesQuotePrice" rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/SalesQuotePrice" type="application/atom+xml;type=feed" title="SalesQuotePrice"/>
<link href="SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/SalesQuoteReference" rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/SalesQuoteReference" type="application/atom+xml;type=feed" title="SalesQuoteReference"/>
<link href="SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/SalesQuoteReferenceObject" rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/SalesQuoteReferenceObject" type="application/atom+xml;type=feed" title="SalesQuoteReferenceObject"/>
<link href="SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/SalesQuoteTextCollection" rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/SalesQuoteTextCollection" type="application/atom+xml;type=feed" title="SalesQuoteTextCollection"/>
<link href="SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/SalesQuoteWorklistItem" rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/SalesQuoteWorklistItem" type="application/atom+xml;type=feed" title="SalesQuoteWorklistItem"/>
<content type="application/xml">
<m:properties>
<d:ObjectID>00163EA7293F1EEB9F9F832D1301A045</d:ObjectID>
<d:ID>104821</d:ID>
<d:BuyerID/>
<d:Name/>
<d:ProcessingTypeCode>QT</d:ProcessingTypeCode>
<d:ProcessingTypeCodeText>QT - Quotation</d:ProcessingTypeCodeText>
<d:BuyerPartyID/>
<d:BuyerPartyName/>
<d:BuyerContactPartyID/>
<d:BuyerContactPartyName/>
<d:ProductRecipientPartyID/>
<d:ProductRecipientPartyName/>
<d:EmployeeResponsiblePartyID/>
<d:EmployeeResponsiblePartyName/>
<d:SalesUnitPartyID/>
<d:SalesUnitPartyName/>
<d:SalesOrganisationID/>
<d:SalesOrganisationName/>
<d:SalesOfficeID/>
<d:SalesOfficeName/>
<d:SalesGroupID/>
<d:SalesGroupName/>
<d:DistributionChannelCode/>
<d:DistributionChannelCodeText/>
<d:DivisionCode/>
<d:DivisionCodeText/>
<d:SalesTerritoryID/>
<d:SalesTerritoryName/>
<d:DateTime>2021-03-04T15:12:56.9733880Z</d:DateTime>
<d:RequestedFulfillmentStartDateTime>2021-03-05T00:00:00Z</d:RequestedFulfillmentStartDateTime>
<d:PriceDateTime>2021-03-04T15:12:56.9733880Z</d:PriceDateTime>
<d:TimeZoneCode>UTC</d:TimeZoneCode>
<d:TimeZoneCodeText>UTC+0</d:TimeZoneCodeText>
<d:ValidFromDate>2021-03-04T00:00:00</d:ValidFromDate>
<d:ValidToDate>2021-03-06T00:00:00</d:ValidToDate>
<d:CurrencyCode/>
<d:CurrencyCodeText/>
<d:DocumentLanguageCode>EN</d:DocumentLanguageCode>
<d:DocumentLanguageCodeText>Englisch</d:DocumentLanguageCodeText>
<d:DeliveryPriorityCode/>
<d:DeliveryPriorityCodeText/>
<d:IncotermsClassificationCode/>
<d:IncotermsClassificationCodeText/>
<d:IncotermsTransferLocationName/>
<d:ProbabilityPercent>100.00</d:ProbabilityPercent>
<d:CancellationReasonCode/>
<d:CancellationReasonCodeText/>
<d:OrderReasonCode/>
<d:OrderReasonCodeText/>
<d:MainDiscount>0.00000000000000</d:MainDiscount>
<d:NetAmount>0.000000</d:NetAmount>
<d:NetAmountCurrencyCode/>
<d:NetAmountCurrencyCodeText/>
<d:GrossAmount>0.000000</d:GrossAmount>
<d:GrossAmountCurrencyCode/>
<d:GrossAmountCurrencyCodeText/>
<d:TaxAmount>0.000000</d:TaxAmount>
<d:TaxAmountCurrencyCode/>
<d:TaxAmountCurrencyCodeText/>
<d:CashDiscountTermsCode/>
<d:CashDiscountTermsCodeText/>
<d:ConfirmationExistenceIndicator>false</d:ConfirmationExistenceIndicator>
<d:ConsistencyStatusCode>2</d:ConsistencyStatusCode>
<d:ConsistencyStatusCodeText>Inconsistent </d:ConsistencyStatusCodeText>
<d:LifeCycleStatusCode>3</d:LifeCycleStatusCode>
<d:LifeCycleStatusCodeText> Completed</d:LifeCycleStatusCodeText>
<d:CancellationStatusCode>1</d:CancellationStatusCode>
<d:CancellationStatusCodeText> Not canceled </d:CancellationStatusCodeText>
<d:ResultStatusCode>2</d:ResultStatusCode>
<d:ResultStatusCodeText> In process</d:ResultStatusCodeText>
<d:ApprovalStatusCode/>
<d:ApprovalStatusCodeText/>
<d:OrderingStatusCode>1</d:OrderingStatusCode>
<d:OrderingStatusCodeText> Not started</d:OrderingStatusCodeText>
<d:CreditWorthinessStatusCode>1</d:CreditWorthinessStatusCode>
<d:CreditWorthinessStatusCodeText> Not relevant </d:CreditWorthinessStatusCodeText>
<d:ReplicationProcessingStatusCode>3</d:ReplicationProcessingStatusCode>
<d:ReplicationProcessingStatusCodeText> Finished </d:ReplicationProcessingStatusCodeText>
<d:ProductAvailabilityStatusCode>1</d:ProductAvailabilityStatusCode>
<d:ProductAvailabilityStatusCodeText> Confirmation pending </d:ProductAvailabilityStatusCodeText>
<d:PriceCalculationStatusCode/>
<d:PriceCalculationStatusCodeText/>
<d:PricingProcedureCode/>
<d:PricingProcedureCodeText/>
<d:ExternalPriceCalculationStatusCode/>
<d:ExternalPriceCalculationStatusCodeText/>
<d:ExternalPricingProcedureCode/>
<d:ExternalPricingProcedureCodeText/>
<d:CreationDateTime>2021-03-04T15:12:57.4644080Z</d:CreationDateTime>
<d:LastChangeDateTime>2021-03-04T15:12:57.4644080Z</d:LastChangeDateTime>
<d:CreatedBy/>
<d:LastChangedBy/>
<d:CreationIdentityUUID>00163EA7-293F-1EEA-B4AE-160B2EC00C1E</d:CreationIdentityUUID>
<d:LastChangeIdentityUUID>00163EA7-293F-1EEA-B4AE-160B2EC00C1E</d:LastChangeIdentityUUID>
<d:VersionGroupID>104821</d:VersionGroupID>
<d:VersionID>1</d:VersionID>
<d:External>false</d:External>
<d:Submit m:null="true"/>
<d:SetAsWon m:null="true"/>
<d:SetAsLost>false</d:SetAsLost>
<d:RequestExtData m:null="true"/>
<d:PrimaryQuote>false</d:PrimaryQuote>
<d:UpdateOpportunity m:null="true"/>
<d:RequestExtFollowup>false</d:RequestExtFollowup>
<d:GrossWeightMeasure>0.00000000000000</d:GrossWeightMeasure>
<d:GrossWeightUnitCode/>
<d:GrossWeightUnitCodeText/>
<d:NetWeightMeasure>0.00000000000000</d:NetWeightMeasure>
<d:NetWeightUnitCode/>
<d:NetWeightUnitCodeText/>
<d:VolumeMeasure>0.00000000000000</d:VolumeMeasure>
<d:VolumeUnitCode/>
<d:VolumeUnitCodeText/>
<d:ExternalApprovalStatusCode/>
<d:ExternalApprovalStatusCodeText/>
<d:PlantPartyID/>
<d:PlantPartyName/>
<d:EntityLastChangedOn>2021-03-04T15:12:57.4644080Z</d:EntityLastChangedOn>
</m:properties>
</content>
</entry>
</feed>
JSON stands for JavaScript Object Notation and is a markup language as well. A JSON file can contain objects, arrays and variables and is easy to read – for machines and humans. In most programming languages there is a parser available, to create a JSON object out of a string. It is common that JSON formats are widely used in programming language that are JavaScript based – e.g. React, Angular or Vue. Following the JSON output when querying the same Quote with ID 104821 from SAP Sales Cloud.
{
"d": {
"results": [
{
"__metadata": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')",
"type": "c4codata.SalesQuote",
"etag": "W/\"datetimeoffset'2021-03-04T15%3A12%3A57.4644080Z'\""
},
"ObjectID": "00163EA7293F1EEB9F9F832D1301A045",
"ID": "104821",
"BuyerID": "",
"Name": "",
"ProcessingTypeCode": "QT",
"ProcessingTypeCodeText": "QT – Quotation",
"BuyerPartyID": "",
"BuyerPartyName": "",
"BuyerContactPartyID": "",
"BuyerContactPartyName": "",
"ProductRecipientPartyID": "",
"ProductRecipientPartyName": "",
"EmployeeResponsiblePartyID": "",
"EmployeeResponsiblePartyName": "",
"SalesUnitPartyID": "",
"SalesUnitPartyName": "",
"SalesOrganisationID": "",
"SalesOrganisationName": "",
"SalesOfficeID": "",
"SalesOfficeName": "",
"SalesGroupID": "",
"SalesGroupName": "",
"DistributionChannelCode": "",
"DistributionChannelCodeText": "",
"DivisionCode": "",
"DivisionCodeText": "",
"SalesTerritoryID": "",
"SalesTerritoryName": "",
"DateTime": "/Date(1614870776973)/",
"RequestedFulfillmentStartDateTime": "2021-03-05T00:00:00Z",
"PriceDateTime": "/Date(1614870776973)/",
"TimeZoneCode": "UTC",
"TimeZoneCodeText": "UTC+0",
"ValidFromDate": "/Date(1614816000000)/",
"ValidToDate": "/Date(1614988800000)/",
"CurrencyCode": "",
"CurrencyCodeText": "",
"DocumentLanguageCode": "EN",
"DocumentLanguageCodeText": "English",
"DeliveryPriorityCode": "",
"DeliveryPriorityCodeText": "",
"IncotermsClassificationCode": "",
"IncotermsClassificationCodeText": "",
"IncotermsTransferLocationName": "",
"ProbabilityPercent": "100.00",
"CancellationReasonCode": "",
"CancellationReasonCodeText": "",
"OrderReasonCode": "",
"OrderReasonCodeText": "",
"MainDiscount": "0.00000000000000",
"NetAmount": "0.000000",
"NetAmountCurrencyCode": "",
"NetAmountCurrencyCodeText": "",
"GrossAmount": "0.000000",
"GrossAmountCurrencyCode": "",
"GrossAmountCurrencyCodeText": "",
"TaxAmount": "0.000000",
"TaxAmountCurrencyCode": "",
"TaxAmountCurrencyCodeText": "",
"CashDiscountTermsCode": "",
"CashDiscountTermsCodeText": "",
"ConfirmationExistenceIndicator": false,
"ConsistencyStatusCode": "2",
"ConsistencyStatusCodeText": "Inconsistent",
"LifeCycleStatusCode": "3",
"LifeCycleStatusCodeText": "Completed",
"CancellationStatusCode": "1",
"CancellationStatusCodeText": "Not canceled",
"ResultStatusCode": "2",
"ResultStatusCodeText": "In process",
"ApprovalStatusCode": "",
"ApprovalStatusCodeText": "",
"OrderingStatusCode": "1",
"OrderingStatusCodeText": "Not started",
"CreditWorthinessStatusCode": "1",
"CreditWorthinessStatusCodeText": "Not relevant",
"ReplicationProcessingStatusCode": "3",
"ReplicationProcessingStatusCodeText": "Finished",
"ProductAvailabilityStatusCode": "1",
"ProductAvailabilityStatusCodeText": "Confirmation pending",
"PriceCalculationStatusCode": "",
"PriceCalculationStatusCodeText": "",
"PricingProcedureCode": "",
"PricingProcedureCodeText": "",
"ExternalPriceCalculationStatusCode": "",
"ExternalPriceCalculationStatusCodeText": "",
"ExternalPricingProcedureCode": "",
"ExternalPricingProcedureCodeText": "",
"CreationDateTime": "/Date(1614870777464)/",
"LastChangeDateTime": "/Date(1614870777464)/",
"CreatedBy": "",
"LastChangedBy": "",
"CreationIdentityUUID": "00163EA7-293F-1EEA-B4AE-160B2EC00C1E",
"LastChangeIdentityUUID": "00163EA7-293F-1EEA-B4AE-160B2EC00C1E",
"VersionGroupID": "104821",
"VersionID": "1",
"External": false,
"Submit": null,
"SetAsWon": null,
"SetAsLost": false,
"RequestExtData": null,
"PrimaryQuote": false,
"UpdateOpportunity": null,
"RequestExtFollowup": false,
"GrossWeightMeasure": "0.00000000000000",
"GrossWeightUnitCode": "",
"GrossWeightUnitCodeText": "",
"NetWeightMeasure": "0.00000000000000",
"NetWeightUnitCode": "",
"NetWeightUnitCodeText": "",
"VolumeMeasure": "0.00000000000000",
"VolumeUnitCode": "",
"VolumeUnitCodeText": "",
"ExternalApprovalStatusCode": "",
"ExternalApprovalStatusCodeText": "",
"PlantPartyID": "",
"PlantPartyName": "",
"EntityLastChangedOn": "/Date(1614870777464)/",
"ETag": "/Date(1614870777464)/",
"ObjectIdentifierMapping": {
"__deferred": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/ObjectIdentifierMapping"
}
},
"SalesQuoteAttachmentFolder": {
"__deferred": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/SalesQuoteAttachmentFolder"
}
},
"SalesQuoteExternalPrice": {
"__deferred": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/SalesQuoteExternalPrice"
}
},
"SalesQuoteItem": {
"__deferred": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/SalesQuoteItem"
}
},
"SalesQuoteOutput": {
"__deferred": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/SalesQuoteOutput"
}
},
"SalesQuoteParty": {
"__deferred": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/SalesQuoteParty"
}
},
"SalesQuotePrice": {
"__deferred": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/SalesQuotePrice"
}
},
"SalesQuoteReference": {
"__deferred": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/SalesQuoteReference"
}
},
"SalesQuoteReferenceObject": {
"__deferred": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/SalesQuoteReferenceObject"
}
},
"SalesQuoteTextCollection": {
"__deferred": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/SalesQuoteTextCollection"
}
},
"SalesQuoteWorklistItem": {
"__deferred": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/SalesQuoteCollection('00163EA7293F1EEB9F9F832D1301A045')/SalesQuoteWorklistItem"
}
}
}
]
}
}
2.3.7. Select
The $select parameter allows only selected data fields to be output. The principle is quite simple: You simply specify the field names in the request query under this parameter. The following request only selects the data fields ID and ProbailityPercent for all Sales Quotes where the DocumentLanguageCode is ENglish. The output format is JSON.
Here is a part of an output for the shown request.
{
"d": {
"results": [
{
"__metadata": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/SalesQuoteCollection('00163E36C2761ED8A6A9B917C1AEC0C1')",
"type": "c4codata.SalesQuote",
"etag": "W/\"datetimeoffset'2021-07-23T15%3A06%3A28.6472510Z'\""
},
"ID": "32338",
"ProbabilityPercent": "100.00"
},
{
"__metadata": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/SalesQuoteCollection('00163E36C2761ED8A6AB02018E528E37')",
"type": "c4codata.SalesQuote",
"etag": "W/\"datetimeoffset'2021-07-23T15%3A06%3A28.6472510Z'\""
},
"ID": "32339",
"ProbabilityPercent": "100.00"
},
...
2.3.8. Count
Another useful parameter is $count, which has to be written directly after declaring the Collection in the API Request. As a result you will receive an integer, which represents the number of hits with respect do your filter settings. In the following example, all Accounts that can be located in the country (CountryCode) Poland (short: PL) are filtered. With the $count parameter you will receive an integer as a result.
2.3.9. Inlinecount
Besides $count parameter there is the other useful parameter $inlinecount. As a result you will receive all entries and in your output there will be also the Attribute “_ _count:” added, which will show you the number of all entries. $inlinecount knows two values: allpages or none, where none is the default if you leave the value empty.
Here is a part of an output for the shown request:
{
"d": {
"__count": "355",
"results": [
{
"__metadata": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/SalesQuoteCollection('00163E36C2761ED8B5D7F420689D4C4A')",
"type": "c4codata.SalesQuote",
"etag": "W/\"datetimeoffset'2021-07-23T15%3A06%3A28.6472510Z'\""
},
"ObjectID": "00163E36C2761ED8B5D7F420689D4C4A",
"ID": "40813",
"ProcessingTypeCode": "QT",
"BuyerPartyID": "1000638",
...
2.3.10. Expand
Using the $expand function, related entities will be included inline in the response. This means that you can bring data into a response that is actually only present in the response via a link. A good example of this are the contact persons in the GET request method of the AccountCollection.
Without Expand Function
Get Request:
Explanation:
As you can see in the response, the contact persons are displayed via a link to the CorporateAccountHasContactPerson data source
Response
{
"d": {
"results": [
{
"__metadata": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/CorporateAccountCollection('00163EA729E71EDBB5A0C20183036DE0')",
"type": "c4codata.CorporateAccount",
"etag": "W/\"datetimeoffset'2022-06-13T02%3A45%3A11.3646010Z'\""
},
"AccountID": "1222476",
"CorporateAccountHasContactPerson": {
"__deferred": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/CorporateAccountCollection('00163EA729E71EDBB5A0C20183036DE0')/CorporateAccountHasContactPerson"
}
}
},
{
"__metadata": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/CorporateAccountCollection('00163EA729E71EDBB5A0C20183046DE0')",
"type": "c4codata.CorporateAccount",
"etag": "W/\"datetimeoffset'2022-05-17T02%3A45%3A24.3011140Z'\""
},
"AccountID": "1222586",
"CorporateAccountHasContactPerson": {
"__deferred": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/CorporateAccountCollection('00163EA729E71EDBB5A0C20183046DE0')/CorporateAccountHasContactPerson"
}
}
}
]
}
}
With Expand Function
Get Request:
Explanation:
In the following response the $expand function gets the contact persons from the data source CorporateAccountHasContactPerson and displays them in the same response of the CorperateAccountCollection.
Response
{
"d": {
"results": [
{
"__metadata": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/CorporateAccountCollection('00163EA729E71EDBB5A0C20183036DE0')",
"type": "c4codata.CorporateAccount",
"etag": "W/\"datetimeoffset'2022-06-13T02%3A45%3A11.3646010Z'\""
},
"AccountID": "1222476",
"CorporateAccountHasContactPerson": [
{
"__metadata": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/CorporateAccountHasContactPersonCollection('00163EA7293F1EEC8EF7A52AF893340A')",
"type": "c4codata.CorporateAccountHasContactPerson",
"etag": "W/\"datetimeoffset'2022-04-14T12%3A51%3A56.3630250Z'\""
},
"ObjectID": "00163EA7293F1EEC8EF7A52AF893340A",
"ParentObjectID": "00163EA729E71EDBB5A0C20183036DE0",
"AccountID": "1222476",
"ContactID": "1292398",
"ContactFormattedName": "Lily Harris",
…
"Contact": {
"__deferred": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/CorporateAccountHasContactPersonCollection('00163EA7293F1EEC8EF7A52AF893340A')/Contact"
}
}
},
{
"__metadata": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/CorporateAccountHasContactPersonCollection('00163EA7293F1EEC8EF7A52AF89AB40A')",
"type": "c4codata.CorporateAccountHasContactPerson",
"etag": "W/\"datetimeoffset'2022-04-14T12%3A51%3A56.3630250Z'\""
},
"ObjectID": "00163EA7293F1EEC8EF7A52AF89AB40A",
"ParentObjectID": "00163EA729E71EDBB5A0C20183036DE0",
"AccountID": "1222476",
"ContactID": "1292397",
"ContactFormattedName": "Elijah Rodriguez",
…
"Contact": {
"__deferred": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/CorporateAccountHasContactPersonCollection('00163EA7293F1EEC8EF7A52AF89AB40A')/Contact"
}
}
}
…
]
},
{
"__metadata": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/CorporateAccountCollection('00163EA729E71EDBB5A0C20183046DE0')",
"type": "c4codata.CorporateAccount",
"etag": "W/\"datetimeoffset'2022-05-17T02%3A45%3A24.3011140Z'\""
},
"AccountID": "1222586",
"CorporateAccountHasContactPerson": [
{
{
"__metadata": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/CorporateAccountHasContactPersonCollection('00163EA7293F1EEC8EF7A51567F9D40A')",
"type": "c4codata.CorporateAccountHasContactPerson",
"etag": "W/\"datetimeoffset'2022-03-15T13%3A00%3A43.6972990Z'\""
},
"ObjectID": "00163EA7293F1EEC8EF7A51567F9D40A",
"ParentObjectID": "00163EA729E71EDBB5A0C20183046DE0",
"AccountID": "1222586",
"ContactID": "1292417",
"ContactFormattedName": "Madison Hernandez",
…
"Contact": {
"__deferred": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/CorporateAccountHasContactPersonCollection('00163EA7293F1EEC8EF7A51567F9D40A')/Contact"
}
}
},
{
"__metadata": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/CorporateAccountHasContactPersonCollection('00163EA7293F1EEC8EF7A5156801540A')",
"type": "c4codata.CorporateAccountHasContactPerson",
"etag": "W/\"datetimeoffset'2022-03-15T13%3A00%3A43.6972990Z'\""
},
"ObjectID": "00163EA7293F1EEC8EF7A5156801540A",
"ParentObjectID": "00163EA729E71EDBB5A0C20183046DE0",
"AccountID": "1222586",
"ContactID": "1292416",
"ContactFormattedName": "Owen Gonzalez",
…
"Contact": {
"__deferred": {
"uri": "https://my######.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/CorporateAccountHasContactPersonCollection('00163EA7293F1EEC8EF7A5156801540A')/Contact"
}
}
}
…
]
}
]
}
}
It is also possible to select only certain data fields within the expanded data using the $select function. Here is an example:
Select within Expanded Function
Get Request:
Filtering in an expanded data source is not possible in SAP Sales Cloud due to the complexity of the linkage.
2.4. Examples of GET Requests
In this section you will learn about different GET requests for different collections. The focus here is on the visualization of the different GET requests and no longer on the exact explanation.
Account CorporateAccountCollection
Filters for all Contacts which communication language is German and where the First Name start with the letters ‘To’, e.g. Tony, Toby, etc. The format is XML.
Business User BusinessUserCollection
Filters all Business Users where the ID is greater than 100 and displays all hits in JSON format.
Lead LeadCollection
Filters for all Leads where the Last Name of the Contact ends with the keyword ‘wa’. Format will be XML.
Opportunity OpportunityCollection
Filters for all Opportunitys, where the Expected Revenue is less or equal than 500.000 and the Prospect Name contains the key term ergo. You can always use * as wildcard character.
Phone Call PhoneCallCollection
Filters for all Phone Calls which are created after or on the 1st of January in 2020.
Sales Quote SalesQuoteCollection
Counts all Sales Quote where the Sales Territory Name is Spain or Denmark.
Sales Order CustomerOrderCollection
Filters for all Sales Orders which were created by Andrea and the Prossing Type is OR – stand for standard order. The output is ordered by the last changed on data descending and the output will be display in JSON format.
Metadata: https://my######.crm.ondemand.com/sap/c4c/odata/v1/salesorder/$metadata
Ticket / Service Request Service Request Collection
Filters for all Service Request – also known as Tickets – where the Object Category ID equals to CA_22 key. The output is in XML format.
Visit VisitCollection
Filters for Visits where the location of the visit is either in JP – Japan or DK – Denmark. The output will only contain the first 10 entries and will be display in JSON format.
A very good overview for all – even the very special – Collection can be found at the following link OData Documentation (sap.com). Here you will find more examples, an overview of all fields and further explanations.
ERP ID
If the SAP Sales Cloud System is connected via an ERP-System then it would be interested to read out via OData the ERP ID of your Business Object. For this porpuse you have to use the ReferenceCollection of a Business Objects. Here is an example of a Sales Quote
The Collection SalesQuoteReferenceCollection is basically showing the whole document flow of the Sales QuoteID 44123. When you specify the RelationshipRoleCode to 6, you get only documents which are copied from your Sales Quote, which is then the ERP document.
2.5. Build OData Queries for Reports
With SAP Sales Cloud and SAP Service Cloud it is possible that data from reports can be queried with a GET request. On the one hand, this is useful if you want to use this data in an external reporting tool. Another use case is when you make very complex queries that you can then cover directly via a report (e.g. jointed data sources).
Navigate to Work Center Business Analytics and then go to Work Center View Design Reports. In the OWL, select your report and click Build Odata Queries via More Options 
.
Within the Build Odata Queries function, you can now build your GET request for the report in a user-friendly way. In the General Settings section show on the right, you can set up the $count or $inlinecount for your request, choose between the Format JSON or ATOM and set up values for Top or Skip. Click in the top right corner on the function Generate Metadata Query or Generate Data Query to generate the GET-Response query, which will be shown then in the greyed-out text field under OData Query (bottom right corner). The Run Query Function, will open and execute the generated GET-Request in a different Browser Tab. More detailed settings for your queries can be found in the Attributes and Filters tabs.
In the Attributes tab you will find a list of all characteristics and key figures of the report. For each item within these Attributes table, you will find following settings:
Select
The Select Attribute means that the $select – Function will be built up. If you do not mark any line item as selected, it means that the query will not contain any $select and all attributes will be included in the GET-Request.
EDM ID
EDM ID of the Attribute
Attribute ID
Attribute ID of the Attribute
Description
Description of the Attribute
Element Type
The Element Type of the Attribute can by either Characteristic or Key Figure
Total
If you also query Key Figures, you can show results and intermediate results for Characteristics in the Response. The following figure shows the difference with and without Total property:
Order By
Select between Ascending and Descending to order the attribute
In the Filters tab you can select the filter type under the Option column. Decide between BT – Between, EQ – Equal to, GE – Greater than or equal to, GT – Greater than, LE – Less than or equal to, LT – Less than or NE – Not equal to. Then you can enter the values for filtering in the Low Value and High Value columns. The High Value field can only be edited if you select Between as the option.
Do you need consulting from our experts?
The Customer Experience team at Camelot ITLab deals with exciting and challenging CRM related topics every day and serves a large portfolio of different customers from a wide range of industries. Trust in this collaboration and feel free to contact us at tlil@camelot-itlab.com.
Was this article helpful?
If you like our content we would highly appreciate your review on Trustpilot
#SAP C4C #SAP Cloud 4 Customer #Cloud 4 Customer #Cloud for Customer #SAP Sales Cloud #Sales Cloud #OData #API #Request #Postman #GETRequest #Get
