Topic: Syspro OData Service

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.

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

   • 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

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

   Considerations:

   • During the installation, the Syspro Installer Application checks if you have the correct prerequisites installed and displays the Prerequisites window if it discovers anything missing. Simply click on the Microsoft .NET Core 3.1.12 or higher Windows Hosting Bundle link to install the missing prerequisites.

     The Syspro OData Service is then installed.

   • SYSPRO OData is configured by Internet Information Services (IIS) and uses the Syspro 8 e.NET Communications Load Balancer to communicate.

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

3. Define the required setup options:

   1. Open the Setup Options Program: IMPCFG 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://localhost/SYSPRO8Odata/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:

   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.

      Ensure that this user has ADMIN rights.

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

      1. Enable the OData user option.

      2. Select the Generate credentials function.

         The system prompts you to first save your changes before generating an associated OData SQL user and password with READ-ONLY permissions. Once saved:

         • The new credentials are populated automatically within the OData user name and OData SQL login name fields

         • The system prompts you to confirm if you want to email the credentials to the operator. If you consent, the password is then encrypted and emailed to the user (using SMTP).

         • The credentials are stored within the AdmOperatorPwd table.

      3. Optional

         Use the Configure OData email template function to define the template used when emailing OData credentials to the operator.

         Once configured, this template is stored in the \Base\Samples folder with the file name EML_ODASVC_EN.TXT.

         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://localhost/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).

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.

Operators

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

String Functions

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)

Date Functions

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)

Mathematical Functions

Microsoft SQL Server	OData
ROUND(X)	round(X)
FLOOR(X)	floor(X)
CEILING(X)	ceiling(X)

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

Query AdmOperator table

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:

Copy

{
    "@odata.context": "http://localhost/SYSPRO8OData/SYSPROODATA/system/$metadata#AdmOperator(Operator,Name,GroupCode)",
    
    "value": [
        {
            "Operator": "ADMIN",
            "Name": "SYSPRO Administrator",
            "GroupCode": "ADMIN"
        },
        {
            "Operator": "ALAN ",
            "Name": "ALAN BROWN2",
            "GroupCode": "OPS"
        }
    ]
}

Query SysproAdmin table

In this scenario:

• Select everything from the SysproAdmin table

Sample call:

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

Sample output:

Copy

{
    "@odata.context": "http://localhost/SYSPRO8OData/SYSPROODATA/system/$metadata#SysproAdmin",
    "value": [
        {
            "Company": "EDU1",
            "DatabaseName": "DS001_CMP_EDU1_800 ",
            "CollationName": "Latin1_General_BIN"
        },
        { 
            "Company": "R ", 
            "DatabaseName": "SysproCompanyR", 
            "CollationName": "Latin1_General_BIN" 
        }
    ]
}

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

Retrieve all rows

Sample call:

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

Sample output:

This will return all the rows of the InvMaster table.

Retrieve single stock code

Sample call:

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

Sample output:

Copy

{
    "@odata.context": "http://localhost/SYSPRO8OData/SYSPROODATA/edu1/$metadata#INVMaster",
    "value": [ 
        {
            "StockCode": "A100",
            "Description": "15 Speed Mountain Bike Boys",
            "LongDesc": "Distribution",
            "AlternateKey1": " "
            "AlternateKey2": " ",
            "EccUser": " ",
            "StockUom": "EA",
            "AlternateUom": "EA",
            "OtherUom": "EA",
            "ConvFactAltUom": 1.000000,
            "ConvMulDiv": "M",
            "ConvFactOthUom": 1.000000,
            "MulDiv": "M",
            "Mass": 10.000000,
            "Volume": 0.300000,
...

Retrieve stock levels

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:

Copy

{
    "@odata.context": "http://localhost/SYSPRO8OData/SYSPROODATA/EDU1/$metadata#INVMaster(StockCode,Description,INVWarehouse(Warehouse,QtyOnHand))",
    "value": [
        {
            "StockCode": "A100",
            "Description": "15 Speed Mountain Bike Boys",
            "INVWarehouse": [
                {
                    "Warehouse": "E",
                    "QtyOnHand": 962.000000
                },
                {
                    "Warehouse": "FG",
                    "QtyOnHand": 0.000000
                },
                {
                    "Warehouse": "N", 
                    "QtyOnHand": 910.000000
                },
                { 
                    "Warehouse": "S",
                    "QtyOnHand": 435.000000 
                }
            ]
        }
    ] 
}

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.

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.

Query a custom form table

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:

Copy

“value”: [
    {
        “Customer”: “000000000000001”,
        “NrOfEmployee”: 5145,
        “Color”: “Red”
    },
    {
        “Customer”: “000000000000002”,
        “NrOfEmployee”: 0,
        “Color”: “ ”
    },
    {
        “Customer”: “000000000000003”,
        “NrOfEmployee”: 10,
        “Color”: “Blue”
    },
    {
        “Customer”: “000000000000005”,
        “NrOfEmployee”: 200,
        “Color”: “Silver”
    },

Joining a custom form table ($expand)

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:

Copy

“value”: [
    {
        “Name”: “Spider Man Ind”,
        “ArCustomer_2”:  {
            “Color”: “Red”
        }
    },
    {
        “Name”: “Bikes & Blades - North”,
        “ArCustomer_2”:  {
            “Color”: “ ”
        }
    },
    {
        “Name”: “Bikes & Blades - South”,
        “ArCustomer_2”:  {
            “Color”: “Blue”
        }
    },
    {
        “Name”: “Cash Sales”,
        “ArCustomer_2”:  {
            “Color”: “Silver”
        }
    },
    {
        “Name”: “Country Gardens - South”,
        “ArCustomer_2”:  {
            “Color”: null
        }
    },