SYSPRO OData Service

Exploring

The SYSPRO OData feature lets you connect securely to SYSPRO data without direct SQL access, and to retrieve data for use by third-party applications. It is a future-proof technology that provides a RESTful API to the SYSPRO database. It allows OData-compliant applications to query SYSPRO data in a standardized and controllable manner.

Although it is available for both on-premise and cloud solutions, SYSPRO OData is the only mechanism available when applications need access to SYSPRO data in the SYSPRO Cloud ERP environment (the SYSPRO Cloud ERP platform doesn't allow direct database access by any application or personnel).

The Data Connector - OData module is currently in BETA status and the connection to Microsoft SQL Server is managed manually. The full feature and its functionality is scheduled for release in SYSPRO 8 2021 R2.

The SYSPRO OData Service is a standalone OData service based on OData 4.0 standards that enables third party applications and solutions to connect to the SYSPRO data tables (i.e. company and system databases):

  • Customers can use it to connect to applications and solutions (such as analysis tools or subscriber feeds)

  • Independent Software Companies (ISV’s) can use it to connect their applications to SYSPRO for read-only data access.

The service queries the system upon start-up to retrieve all the SYSPRO operators that have been configured for OData access. Therefore, when the SQL query is issued, it uses the SQL credentials stored against the operator and not the credentials that were provided to the third party user.

The advantage of this is that the third party user doesn't know the SQL login used for the query, no-one will know what the password is and the SYSPRO administrator can still have over-site on what the third party user is accessing.

This service is used by:

  • Data Connector - OData

  • The default location for this service is:

    inetpub > wwwroot > SYSPRO8OData

SYSPRO's Connected Services consist of multiple capabilities that provide integration touch points between SYSPRO and external parties.

Existing Connected Services include:

  • e.net Solutions

  • SYSPRO Ken the BOT (available from SYSPRO 8 2018 R2)

  • SYSPRO Supply Chain Portal (available from SYSPRO 8 2019 R2)

  • Tax Connector - Avalara (available from SYSPRO 8 2021 R1)

  • Tax Connector - Vertex (available from SYSPRO 8 2021 R1)

  • Data Connector - OData (available from SYSPRO 8 2021 R2)

OData (i.e Open Data Protocol) is an ISO/IEC approved, OASIS standard that defines a set of best practices for building and consuming RESTful APIs. It's a REST-based protocol for querying and updating data. It is built on standardized technologies such as HTTP, Atom/XML, and JSON and is different from other REST-based web services in that it provides a uniform way to describe both the data and the data model.

OData helps you focus on your business logic while building RESTful APIs without having to worry about the various approaches to define request and response headers, status codes, HTTP methods, URL conventions, media types, payload formats, query options, etc.

It also provides guidance for tracking changes, defining functions/actions for reusable procedures and sending asynchronous/batch requests.

The OData metadata (a machine-readable description of the data model of the APIs) enables the creation of powerful generic client proxies and tools.

OData is considered to be a flexible technology for enabling interoperability between disparate data sources, applications, services and clients.

Visit https://www.odata.org for more information.

SYSPRO has commercial arrangements with several Independent Software Vendors (ISVs).

The products that ISVs develop require integration into SYSPRO, often using e.net Solutions to call e.net business objects. However, a requirement may exist for direct database access. Run Time Product licenses are issued for this purpose.

This allows a third-party product to connect seamlessly to e.net Solutions at a SYSPRO site that selects to license the Run Time Product without any further e.net licensing implications.

While the Run Time Product license doesn't require any additional configuration or assignment of licenses at the site, the SYSPRO site must have licensed the underlying required module.

For example:

If the Run Time Product accesses the Accounts Receivable Query Functional Area, then the SYSPRO site must have also licensed the Accounts Receivable module (which would inevitably be the case otherwise there wouldn't be any Accounts Receivable data).

Run Time Products were typically allowed open access to any e.net functional area. From SYSPRO 8 2021 R1 onwards, however, it's possible to have two types of Run Time Products access:

  • Standard

    This means that all functional areas are accessible and equates to the same as the traditional licenses issued).

  • Managed

    This means that a limited number of functional areas are accessible by the Run Time Products.

    In this case, the Run Time Products definition file lists the e.net functional areas required and the license generated and applied at the SYSPRO site ensures that only these functional areas are licensed.

You can check the type of access for your Run Time Product, by viewing the Run Time Products pane of the Company Maintenance program (indicated in the Access column).

Starting

To use this feature, the following module(s) must be installed according to the terms of your software license agreement:

  • e.net System Manager

  • Data Connector - OData

The following technology prerequisites are applicable to using the SYSPRO OData Service:

  • Microsoft .NET Framework 4.6

  • Internet Information Services (IIS) - including:

    • .NET Core hosting bundle (version 3.1.12 or higher)

      Minimum .NET Core frameworks required by the SYSPRO OData API:

      • Microsoft.AspNetCore.App (version 3.1.8 or higher)

      • Microsoft.NETCore.App (version 3.1.0 or higher)

      • NETStandardLibrary (version 2.1.0 or higher)

    • SSL Certificate for live production sites

      The SYSPRO OData deployment must be secured by an IIS certificate.

      An SSL certificate can be purchased online and deployed on the host server. It is essentially the remit of the solution platform provider to assist in deploying a valid SSL certificate into the IIS instance and to configure the solution for access on Port 443.

  • A connection to Microsoft SQL Server

  • A connection to the SYSPRO 8 e.net Communications Load Balancer via net.tcp

  • This service is installed using the SYSPRO Installer Application.

    We strongly recommend exposing this service over HTTPS to ensure security.

The following setup options must be configured to use this service:

Setup Options > System Setup > Connectivity

OData

  • OData endpoint

This configuration is required to connect to the SYSPRO OData Service (i.e. the OData layer).

The authentication used by the SYSPRO OData Service is Basic Authentication with the appropriate SQL user credentials. Therefore, a separate SQL login must be created for this service, as using the standard sa user is not recommended.

You can use the Generate Credentials function within the OData section of the Operator Maintenance program to automatically create these SQL users and link them to an operator defined as an OData user.

The SYSPRO OData Service completely bypasses SYSPRO’s security framework because it doesn't use a standard SYSPRO operator to login to SYSPRO OData, therefore security controls (such as branch access, etc.) doesn't apply.

Solving


The following files are located in the folder where you installed the service:

  • appsettings.json

  • web.config

If the SYSPRO OData Service is unable to start, a file named ODataFailedToStart.txt is created in the default install location (i.e. inetpub > wwwroot > SYSPRO8OData) and includes details of the error(s) for the startup failure.

An OData Validation error message is returned if an error occurs during the process of a URL request, such as incorrect parameter information contained within the endpoint.

For example:

OData Validation Error: Could not find a property named 'Name' on type 'SYSPRODBModel.INVMaster'.

Ensure that you capture the correct base URL and subsequent entities (e.g. company ID, table name, query parameters, etc.).

The URL used to query data within SYSPRO comprises the base URL for the OData website, followed by separate entities that change depending on the information you are querying (e.g. company ID, table name, query parameters, etc.).

For example:

https://localhost/SYSPRO8Odata/SYSPROOData/edu1/ArCustomer

where:

  • https://localhost/SYSPRO8Odata/SYSPROOData indicates the static base URL.

  • /edu1/ArCustomer indicates the dynamic entities added to the endpoint.

    • edu1 indicates the company being queried.

    • ArCustomer indicates the table within that company.

The only difference between on-premise and SYSPRO Cloud ERP sites connecting to SYSPRO OData is the base URL for the OData Website.

For example:

  • On premise:

    https://localhost/SYSPRO8Odata/SYSPROOData/edu1/ArCustomer

  • SYSPRO Cloud ERP:

    https://pmotst01.sysprocloud.com/SYSPRO8OData/SYSPROOdata/edu1

The format of the call to query company data is:

https:// ServiceBaseURL /SYSPROOData/ SYSPRO Company / SYSPRO Table ? Query parameters

For example:

https://localhost/SYSPRO8Odata/SYSPROOData/edu1/ArCustomer?select=Customer,Name

The format of the call to query system data is:

https:// ServiceBaseURL /SYSPROOData/ system / SYSPRO Table ? Query parameters

For example:

https://localhost/SYSPRO8Odata/SYSPROOData/system/AdmOperator?$Select=Operator, Name, GroupCode&$Top=2

The SYSPRO OData solution provides a standard way of connecting and integrating with other applications and is therefore applicable to anyone who wants to retrieve data from the SYSPRO databases using one of the following methods:

  • Excel, PowerBI, Tableau and other external analytical solutions.

  • PHP where a REST-friendly address is required, as well as the ability to query data and insert select statements in a browser (e.g. HTTP requests).

  • Developers working in Visual Studio who need to query SYSPRO data.

ISV's can access SYSPRO OData (without the customer having to license it for their SYSPRO site) by passing their runtime product code when calling the SYSPRO OData Service.

To pass a runtime product code with a call to the SYSPRO OData Service, the words #RunTimeProductCode# and the actual product code must be included as part of the username of the basic authentication information for all API requests.

The structure of the username field should be as follows:

[username]#RunTimeProductCode#[runtime product code]

For example:

  • Runtime product code: OData123

  • Username: Blogs

Based on this example, the username field for the basic authentication must be specified as:

Blogs#RunTimeProductCode#OData123

If no runtime product code is provided and the site isn't licensed for the Data Connector - OData module, then the SYSPRO OData functionality will not be available for use.

You would use the GetVersion call.

For example:

https://localhost/SYSPRO8OData/GetVersion

The GetVersion call not only returns the version of the SYSPRO OData Service; it also refreshes the company data structure to ensure that any database configuration changes made within SYSPRO are refreshed and used by the service.

SYSPRO OData supports paging as the $top and $skip clauses are built-in as standard.

All calls are GET type calls, as SYSPRO OData only supports reading data.

Currently, SYSPRO OData doesn't have rate limitations (e.g. rows per minute or per second limiting). However, to assist with faster calls and returns, developers and ISV's need to be mindful of how much data they are consuming, taking care to use query parameters to limit the amount of data being returned.

  1. Open you browser.

  2. Enter your URL and the $metadata clause.

    For example:

    https://localhost/SYSPRO8OData/SYSPROOData/edu1/$metadata

This returns an XML format listing of each table as an entity (i.e. table name in the database), properties (i.e. column names) and navigation properties.

To override the set limits of data returned, specify the normal OData URL conventions using OData structure and query language.

There are various ways to overcome the 5000 row limit:

  • You can limit the calls to weeks, periods, months or years and then combine multiple calls into one dataset within your BI tool.

  • You can use an ETL tool to refresh the data into a Data Lake or Repository and then run the BI tool on the dataset created from a feed within the SYSPRO OData solution.

  • A new module solution regarding Business Activity Queries will be made available with a later release of SYSPRO 8. This feature will let you create Views on the Database for OData consumption, thereby providing aggregation and summary custom datasets. These can then be used within Insight Tiles, reports, etc.

Custom form tables don't appear in the listing of tables as you would expect to see them in Microsoft SQL Server (i.e. SQL tables ending with a plus). Instead, they are now suffixed with _company.

For example:

Microsoft SQL Server: ApSupplier+

OData: ApSupplier_edu1

The SYSPRO OData solution can be deployed on-premise or within the SYSPRO Cloud offering:

  • On-Premise (or MCS Cloud):

    The SYSPRO Region/Partner Tech Consultant or Customer

  • Cloud:

    The SYSPRO Cloud support teams. Specifically, by change request to managed services or via the cloud portal.

For on-premise deployments, the deployment team should have the following skills:

  • Windows Service Deployment (to install and configure the solution)

  • SQL Instance Management (to configure users and manage permissions)

  • OData awareness (to output data)

  • SYSPRO OData best practice awareness (to optimize performance)

  • IIS SSL Certification Application (for security)

Requirements:

  • Your server certificate

  • Your intermediate certificates

  • Your private key

Installation instructions:

  1. Launch the Internet Information Services (IIS) Manager.

  2. Select your server name from the Connections menu (i.e. the host to which you want to install the certificate).

  3. Navigate to the Security section by selecting the Server Certificates icon under the Security section.

  4. Select the Complete Certificate Request function from the Actions menu.

  5. From the Complete Certificate Request wizard, browse for your Server Certificate file (previously saved on your server’s desktop) and select OK.

Binding your certificate to your website:

The following describes how to assign or bind the SSL certificate to the appropriate website:

  1. From the Connections menu in Internet Information Services (IIS) Manager, expand your server's name, followed by the Sites folder, and then select the site that you want to secure.

  2. Select the Bindings function of the Actions menu.

  3. From the Site Bindings window, select the Add function.

    If you already have the appropriate site binding created, select the Edit function to change the SSL Certificate accordingly.

  4. Enter the following within the Add Site Binding window:

    • Type: https

    • IP Address: All unassigned.

      If you have multiple IP addresses, select the correct one that applies.

    • Port: 443

    • SSL Certificate: Select the friendly name of the SSL certificate you've installed.

      Select the View function to confirm the validity of the certificate.

  5. Select OK to finish binding the SSL certificate to your live website.

As SSL certificates have an expiry date, ensure that you renew your certificates timeously to prevent any unexpected down time.

The SYSPRO OData deployment must be secured by an IIS certificate.

An SSL certificate can be purchased online and deployed on the host server. It is essentially the remit of the solution platform provider to assist in deploying a valid SSL certificate into the IIS instance and to configure the solution for access on Port 443.

The core function of an SSL certificate is to protect server-client communication. It protects your information online by encrypting any data sent to you, and then decrypting it once you've received it (i.e. the data is locked and can only be unlocked by the intended recipient as no-one else can have the key to open it).

SSL certificates also provide website authentication - identity verification being one of the most important aspects of web security.

The e.net business object layer is still the preferred mechanism of applications accessing any business logic, including data queries, as well as add, update, delete and transactional access.

The Data Connector - OData module is designed for cases where the e.net business object layer is unable to provide the data access required for the business solution.

The SYSPRO OData feature is a significant improvement over applications that use direct database access to the SYSPRO data.

This is because that type of direct database access doesn't provide traceability or visibility about what access is being performed, regardless of whether the access has the correct permissions and how frequently access is being made.

This lack of visibility and traceability leads to a high-risk site.

As direct SQL access is restricted in the SYSPRO Cloud ERP environment, sites that upgrade from SYSPRO on-premise to a SYSPRO Cloud deployment are required to re-skill in OData and update any linked solutions using OData (rather than ODBC access to the SQL database).

Using

The following outlines the steps you need to follow after purchasing the Data Connector - OData module:

  1. Ensure that the following technology prerequisites have been installed.

    • Microsoft .NET Framework 4.6

    • Microsoft SQL Server

    • SYSPRO 8 e.net Communications Load Balancer via net.tcp

    • Internet Information Services (IIS) - including:

      • .Net Core hosting bundle

      • SSL certificate (for authentication purposes)

        The SYSPRO OData deployment must be secured by an IIS certificate.

        An SSL certificate can be purchased online and deployed on the host server. It is essentially the remit of the solution platform provider to assist in deploying a valid SSL certificate into the IIS instance and to configure the solution for access on Port 443.

  2. Install the SYSPRO OData Service using the SYSPRO Installer Application.

    We strongly recommend exposing this service over HTTPS to ensure security.

  3. Define the required setup options

    1. Open the Setup Options program and navigate to the Connectivity System Setup form (Setup Options > System Setup > Connectivity).

    2. Indicate the base portion of the SYSPRO OData URL and your particular endpoint at the OData endpoint field.

      This lets operators who are configured as OData users to access data within the SYSPRO databases using the SYSPRO OData Service.

      For example:

      https://SYSPRO8OData.yourcompany.com/SYSPROOData/edu1

    3. Ensure that your Email/SMTP settings are configured correctly.

      This lets you use the Send email function of the Operator Maintenance program to email an operator their OData credentials.

      • SMTP server IP address
      • Outgoing email address
      • Username
      • Password
      • Server port
      • Use SSL

  4. Create your SYSPRO OData operators

    To use the SYSPRO OData solution, the required SQL users must be created within Microsoft SQL Server and configured with READ access to the relevant SQL tables and databases.

    You can achieve this within SYSPRO from SYSPRO 8 2021 R2 as follows:

    1. Open the Operator Maintenance program (SYSPRO Ribbon bar > Setup > Operators).

    2. Indicate the operator code you want to use for OData or create a new operator and enter the applicable Operator Details.

    3. Navigate to the Options tab of the Options pane:

      1. Enable the OData user option.

      2. Select the Generate credentials function.

        An associated OData SQL user and password are generated with READ-ONLY permissions. The new credentials are then populated automatically within the OData user name and OData SQL login name fields.

        This information is stored within the AdmOperatorPwd table.

      3. Select the Send Email function to email these credentials confidentially (using SMTP) to the email address defined against the operator, including a password reset and resend function in SYSPRO.

    4. Save your changes.

The following uses Microsoft Excel as an example of a third party application connecting to the SYSPRO data:

  1. Launch the third-party application - in this case Microsoft Excel.

  2. Select the Data tab and expand the Get Data menu, followed by From Other Sources.

    Select the From OData Feed option.

    In other third party applications, you would typically select the Choose OData Feed option from Available data sources (or similar wording).

  3. Select Basic on the OData feed window and enter your endpoint (i.e. URL) for the applicable entity (i.e. company id or system) that you want to query.

    URL example:

    https://pmotst01.sysprocloud.com/SYSPRO8OData/SYSPROOdata/edu1

  4. Select OK.

    The OData feed window is displayed.

  5. From the Basic tab, enter the user name and password applicable for your configured SYSPRO OData operator.

  6. Select the Connect function.

    The endpoint is queried and the Navigator window returns all available tables for the SQL database being queried.

  7. Select the tables that you want to pull into Microsoft Excel (i.e. your SQL based third party application).

The initial load of OData can take a few minutes in some instances, however subsequent calls are significantly faster as the system uses preloaded libraries, cached metadata, etc.

The SYSPRO OData Service exposes the metadata available that can be used by third party applications to understand the data, and the relationship between the data. Therefore, you can query the metadata for the service by utilizing the $metadata call (e.g. SYSPRO8OData/SYSPROOData/edu1/A/$metadata).

This applies for both standard database tables and custom form field tables as the $metadata call provides all the entity information, including the custom form entities.

This call (as per the OData standard) returns the EDMX (i.e. XML document) that contains a complete description of types, relationships and properties exposed by the service.

For example:

SYSPRO database structure:

https://localhost/SYSPRO8OData/SYSPROOData/edu1/$metadata

SYSPRO InvPrice table structure example:

https://localhost/SYSPRO8OData/SYSPROOData/edu1/$metadata#InvPrice

Sample output:

The SYSPRO OData Service exposes read-only data as entities that have a direct mapping to the SYSPRO database tables.

For example:

  • /SYSPRO8OData/SYSPROOData/edu1/ArCustomer returns data for the ArCustomer table from SYSPRO company EDU1.

  • /SYSPRO8OData/SYSPROOData/edu1/ArCustomer/$top=600 returns the top 600 rows of data for the ArCustomer table from SYSPRO company EDU1

Therefore, the SYSPRO OData Service supports the following OData query options on the SYSPRO entities:

  • $filter

    e.g. /SYSPRO8OData/SYSPROOData/edu1/ArCustomer?$filter=Name eq ‘Spider Man ind’

  • $count

    e.g. /SYSPRO8OData/SYSPROOData/edu1/ArCustomer?count=true

  • $orderby

    e.g. /SYSPRO8OData/SYSPROOData/edu1/ArCustomer?orderby=ShortName, Name

  • $skip

    e.g. /SYSPRO8OData/SYSPROOData/edu1/ArCustomer?skip=10

  • $top

    e.g. /SYSPRO8OData/SYSPROOData/edu1/ArCustomer?top=10

  • $expand (only 1st-level expansion is supported)

    e.g. /SYSPRO8OData/SYSPROOData/edu1/ArCustomer?expand=ArCustomer_2

  • $select

    e.g. /SYSPRO8OData/SYSPROOData/edu1/ArCustomer?select=Customer,Name

Paging is supported via the $skip / $top clauses.

OData protocol provides powerful features for querying data via URLs, very similar to Microsoft SQL Server. Therefore, the following cheat sheet can assist regular SQL users in understanding how to query data via OData, and how the OData features correspond to the most commonly used SQL features:

Feature

Microsoft SQL Server

OData

Number of records in a table

SELECT COUNT(*)

FROM Emp

/Emps/$count

Querying specific table columns

SELECT ENAME, JOB, SAL FROM Emp

/Emps?$select=ENAME,JOB,SAL

Querying the second 5 records

SELECT * FROM Emp ORDER BY (SELECT NULL)

OFFSET 5 ROWS FETCH FIRST 5 ROWS ONLY

/Emps?$top=5&$skip=5

Ordering Data

SELECT * FROM Emp ORDER BY ENAME DESC, SAL

/Emps?$orderby=ENAME desc,SAL

Querying joined tables

SELECT * FROM Dept LEFT OUTER JOIN Emp ON

Dept.DEPTNO = Emp.DEPTNO

/Depts?$expand=Emps

Filtering data

SELECT * FROM EMP WHERE (SAL/2 > 500 AND

HIREDATE <= '01/01/1985') OR (COMM IS NOT

NULL AND ENAME LIKE 'J%')

/Emps?$filter=(SAL div 2 gt 500 and HIREDATE le 1985-01-01) or (COMM ne null and startswith(ENAME,'J'))

Aggregating data

SELECT SUM(SAL) AS Sum, MAX(SAL) AS Max,

Min(SAL) AS Min, AVG(Sal) AS Avg FROM Emp

/Emps?$apply=aggregate(SAL with sum as Sum,SAL with max as Max,SAL with min as Min,SAL with average as Avg)

OData protocol supports a number of different mathematical, logical, etc. operators and functions in the $filter expression. Here you can find a brief list of these operators and functions that you can use in your OData requests together with their SQL analogs.

These are case-sensitive in OData requests.

Microsoft SQL Server

OData

=

eq

!=

ne

>

gt

>=

ge

<

lt

<=

le

AND

and

OR

or

NOT

not

+

add

-

sub

*

mul

/

div

%

mod

( )

( )

IS NULL

eq null

IS NOT NULL

ne null

Microsoft SQL Server

OData

REPLACE(X,'Y','Z')

replace(X,'Y','Z')

SUBSTRING(X,2,3)

substring(X,2,3)

TRIM(X)

trim(X)

CONCAT(X,Y)

concat(X,Y)

Microsoft SQL Server

OData

DATEPART(year,X)

year(X)

DATEPART(month,X)

month(X)

DATEPART(day,X)

day(X)

DATEPART(hour,X)

hour(X)

DATEPART(minute,X)

minute(X)

DATEPART(second,X)

second(X)

Microsoft SQL Server

OData

ROUND(X)

round(X)

FLOOR(X)

floor(X)

CEILING(X)

ceiling(X)

The following provides some examples of calls that are allowed by the SYSPRO OData Service:

Using the System entity enables you to call into the SYSPRO System database (e.g. Sysprodb).

The format of the call to query the System database is:

ServiceBaseURL /SYSPROOData/ system / SYSPRO Table ? Query parameters

In this scenario:

  • Select top 2 operators

  • Return only their Operator codes, Names and Group codes

Sample call:

https://localhost/SYSPRO8OData/SYSPROOData/system/AdmOperator?$Select=Operator, Name, GroupCode&$Top=2

Sample output:

In this scenario:

  • Select everything from the SysproAdmin table

Sample call:

https://localhost/SYSPRO8OData/SYSPROOData/system/SysproAdmin

Sample output:

The format of the call to query data is:

ServiceBaseURL /SYSPROOData/ SYSPRO Company / SYSPRO Table ? Query parameters

In these examples, we are querying the InvMaster table for the EDU1 company.

Sample call:

https://localhost/SYSPRO8OData/SYSPROOData/edu1/InvMaster

Sample output:

This will return all the rows of the InvMaster table.

Sample call:

https://localhost/SYSPRO8OData/SYSPROOData/edu1/InvMaster?$filter=StockCode eq 'A100'

Sample output:

In this example, we are joining the InvWarehouse to the InvMaster table in our call to get stock levels. To perform any expands (i.e. joins), you must include the key of the primary table in your call. Therefore, to expand InvWarehouse in InvMaster, you have to select StockCode from the InvMaster as part of the call.

In this scenario:

  • Select StockCode and Description from InvMaster

  • Join to InvWarehouse (SYSPRO handles the key joins)

  • Return Warehouse and QtyOnHand from the InvWarehouse record

Sample call:

All Stock Codes

https://localhost/SYSPRO8OData/SYSPROOData/edu1/InvMaster?$Select=StockCode, Description&$Expand=InvWarehouse($Select=Warehouse, QtyOnHand)

Single Stock Code

https://localhost/SYSPRO8OData/SYSPROOData/edu1/InvMaster?$Select=StockCode, Description&$Expand=InvWarehouse($Select=Warehouse, QtyOnHand)&$Filter=StockCode eq 'A100'

Sample output:

The GetVersion call returns the version of the SYSPRO OData Service and triggers a “refresh” of the company data structure. This ensures that any database configuration changes made within SYSPRO are refreshed and utilized by the service.

Sample call:

https://localhost/SYSPRO8OData/GetVersion

Sample output:

SYSPRO OData Version: 8.0.0.0
Copyright © 1994-2021 SYSPRO Ltd.

The SYSPRO OData Service allows for queries on custom form tables (i.e. SQL tables ending with a plus) within the company databases. Therefore, the service checks for these upon startup and performs the required build of the custom form tables to provide for the OData URL queries.

Custom form tables are referenced with the table name followed by an underscore and company id.

For example:

The ArCustomer+ table within the Edu1 company database will be referenced as ArCustomer_edu1 in the OData URL.

Custom form tables that don't follow the standard custom form field conventions are excluded and therefore not provided for.

In this scenario:

  • Query the ArCustomer+ custom form table within the EDU1 company database

Sample call:

https://localhost/SYSPRO8OData/SYSPROOData/edu1/ArCustomer_edu1

Sample output:

In this scenario:

  • Query the ArCustomer+ custom form table within the EDU1 company database

  • Expand on color and name

Sample call:

https://localhost/SYSPRO8OData/SYSPROOData/edu1/ArCustomer?$expand=ArCustomer_edu1($select=Color)&$select=Name

Sample output:

Referencing

You should only update this file for temporary debugging purposes (i.e. not as a method to update values for the service).

Any temporary changes made to the configuration files are reset to their original installation values when this application is next updated. Because these temporary changes are not carried forward, forgetting that you made them could hamper troubleshooting efforts if unexpected issues arise.

Key Description

ODataMaxRecords

5000

This indicates the default maximum records to be returned by the service.

WCFServiceAddress

net.tcp://localhost:31001/SYSPROWCFService

This indicates the SYSPRO 8 e.net Communications Load Balancer endpoint address as configured during the service installation via the SYSPRO Installer Application.

ServiceManagerAddress

net.tcp://localhost:30140/SYSPROServiceManager

This is reserved for future use.