1. REST API for JD Edwards EnterpriseOne AIS Server
  2. Tasks
  3. Data Service

Query or Aggregate Tables and Views v1

post

/jderest/dataservice

Execute queries over EnterpriseOne tables and business views.

Request

Supported Media Types
Header Parameters
Body ()
The input to a data service request.
Root Schema : DataRequest
Type: object
The input to a data service request.
Show Source
Nested Schema : AggregationInfo
Type: object
Provide information about the requested aggregation.
Show Source
Nested Schema : formActions
Type: array
An array of actions to be performed in a specified order on a particular form.
Show Source
Nested Schema : Having
Type: object
The details of the having clause to be applied to an aggregation request.
Show Source
Nested Schema : Query
Type: object
The query portion of a data service or form service request.
Show Source
Nested Schema : aggregations
Type: array
The list of aggregations requested.
Show Source
Nested Schema : CurrencyProcessing
Type: object
Details for instructing the service to format the results for currency (currency decimals).
Show Source
  • If you would like different currencies in the table to be converted to a single currency, indicate it here. You must have conversion rates in the currency conversion table for the currencies involved.
  • Specify the date to be used for the currency conversion. If not provided, today's date will be used.
  • If not using a specific type of processing, specify a specific currency code to use for formatting.
  • currencyCols
    Specify the columns to be formatted. You only need to add a column here once, even if you have several aggregations over that column. Identify each distinct column to which the currency processing should apply. If you do not identify currency columns here, then currency processing will be applied to all columns identified as currency columns in the data dictionary. A currency column is defined in the data dictionary with class CURRENCY.
  • keyCols
    Specify the columns to be used as the key based on the type of processing. (For example for COMPANY processing, which field in the table will have the company value.)
  • Rounding Mode override.
  • Allowed Values: [ "CO", "CRCD", "MCU", "AID", "LT_CO_CRCD", "LT_CRCD", "STATIC" ]
    The type of currency processing to use.
Nested Schema : groupBy
Type: array
The list of fields to group the results by.
Show Source
Nested Schema : orderBy
Type: array
The list fields to order the results by.
Show Source
Nested Schema : AggregationItem
Type: object
Information provided for the aggregation of a specific column.
Show Source
  • Allowed Values: [ "SUM", "MIN", "MAX", "AVG", "COUNT", "COUNT_DISTINCT", "AVG_DISTINCT", "SUM_DISTINCT" ]
    The aggregation to perform.
  • The column to perform the aggregation over.
  • If this is set the associated description will be returned as part of the group by results.
  • Allowed Values: [ "ASC", "DESC" ]
    If this is a group by, indicate the order.
  • Allowed Values: [ "USER", "CALQTR", "<SimpleDateFormatString>" ]
    If this is a group by for a date field, indicate the simple date format for the dates. The result will be grouped and formated based on the input. 'USER': Uses the EnterpriseOne user's preferred date format. 'CALQTR': Uses the four digit year and one digit quarter format, for example 2020-1. SimpleDateFormat: Uses the simple date format that you supply, such as yyyy-MM-dd.
Nested Schema : currencyCols
Type: array
Specify the columns to be formatted. You only need to add a column here once, even if you have several aggregations over that column. Identify each distinct column to which the currency processing should apply. If you do not identify currency columns here, then currency processing will be applied to all columns identified as currency columns in the data dictionary. A currency column is defined in the data dictionary with class CURRENCY.
Show Source
Nested Schema : keyCols
Type: array
Specify the columns to be used as the key based on the type of processing. (For example for COMPANY processing, which field in the table will have the company value.)
Show Source
Nested Schema : FSREvent
Type: object
An individual action to perform on a form.
Show Source
  • Allowed Values: [ "SetQBEValue", "SetControlValue", "SetRadioButton", "SetComboValue", "DoAction", "SetCheckboxValue", "SelectRow", "UnSelectRow", "UnSelectAllRows", "SelectAllRows", "ClickGridCell", "ClickGridColumnAggregate", "NextGrid" ]
    The command or action to perform.
  • The control to perform the action on. This a form level object. It is not applicable to individual grid rows.
  • GridAction
    Holds actions to perform on grid rows.
  • The value to use, if the action is to set a value.
Nested Schema : GridAction
Type: object
Holds actions to perform on grid rows.
Show Source
Nested Schema : gridRowInsertEvents
Type: array
The list of events for adding grid rows.
Show Source
Nested Schema : gridRowUpdateEvents
Type: array
The list of events for updating grid rows.
Show Source
Nested Schema : GridRowInsertEvent
Type: object
Contains actions to apply to a newly added grid row.
Show Source
Nested Schema : gridColumnEvents
Type: array
The actions to apply to the new grid row.
Show Source
Nested Schema : GridColumnEvent
Type: object
Action to be applied to a grid cell.
Show Source
  • The control id for the grid column. Pass only the column id here, a single number without separators. The grid must already be identified by including the gridID field at the root of GridAction.
  • Allowed Values: [ "SetGridCellValue", "SetGridComboValue", "ClickGridCell" ]
    The operation to perform on the grid cell.
  • The value to use in the operation.
Nested Schema : GridRowUpdateEvent
Type: object
The actions to apply to an existing grid row.
Show Source
Nested Schema : gridColumnEvents
Type: array
The actions to perform on the existing grid row.
Show Source
Nested Schema : condition
Type: array
The list of conditions in the having clause.
Show Source
Nested Schema : HavingCondition
Type: object
An individual having condition.
Show Source
Nested Schema : value
Type: array
The value to filter with in the having condition.
Show Source
Nested Schema : HavingValue
Type: object
The value to use within a having condition.
Show Source
Nested Schema : complexQuery
Type: array
The array of complex query fragments if this is a compex query.
Show Source
Nested Schema : condition
Type: array
The array of complex query fragments if this is a complex query.
Show Source
Nested Schema : ComplexQueryFragment
Type: object
The class used to join multiple queries into a complex query.
Show Source
Nested Schema : Condition
Type: object
A condition within a query.
Show Source
Nested Schema : value
Type: array
The value(s) used for data comparison based on the operation.
Show Source
Nested Schema : QueryValue
Type: object
The value used in a query comparison.
Show Source
Back to Top

Response

Supported Media Types

200 Response

Successful Execution

400 Response

Bad Request - Invalid JSON Input

403 Response

Authorization Failure

415 Response

Invalid Content-Type Header - Must use application/json

500 Response

Server Failed to Process Request
Body ()
Root Schema : ServiceErrorResponse
Type: object
Error Response
Show Source
Back to Top

Examples

Example Request VERSION2 Output

The following shows an example of a data request. The token value is from a prior call to the token request service to establish a session. The request includes a query and sorting order for three columns. 

curl -i -X POST -H "Content-Type:application/json" http://ais_server_url/jderest/dataservice -d
{  
  "token" : "0448slzeSZXyg2hHkcQNdbhhcYA9INqoVWydJSxVK8SWi8=MDE5MDA4MTQ3NTYwODg5MTA3NjAzNTA5Mk15RGV2aWNlMTQ3ODEyMDQyNDYxNQ==",
  "deviceName" : "MyDevice",
  "aliasNaming" : true,  
  "outputType" : "VERSION2",
  "targetName" : "F0101",
  "targetType" : "table",
  "dataServiceType" : "BROWSE",  
  "maxPageSize" : "10",
  "returnControlIDs" : "F0101.AN8|F0101.ALPH|F0101.AT1",
  "query" : {
    "autoFind" : true,
    "condition" : [ {
      "value" : [ {
        "content" : "7000",
        "specialValueId" : "LITERAL"
      } ],
      "controlId" : "F0101.AN8",
      "operator" : "GREATER"
    } ]
  },
  "aggregation" : {
    "orderBy" : [ {
      "column" : "F0101.AT1",
      "direction" : "ASC"
    }, {
      "column" : "F0101.ALPH",
      "direction" : "ASC"
    }, {
      "column" : "F0101.AN8",
      "direction" : "DESC"
    } ]
  }
}

Example Response VERSION2 Output

The following example shows the contents of the response body.


{
  "fs_DATABROWSE_F0101" : {
    "title" : "Data Browser - F0101 [Address Book Master]",
    "data" : {
      "gridData" : {
        "id" : 54,
        "titles" : {
          "F0101_AN8" : "Address Number",
          "F0101_ALPH" : "Alpha Name",
          "F0101_AT1" : "Sch Typ"
        },
        "columnInfo" : {
          "F0101_AN8" : {
            "id" : 51,
            "dataType" : 9,
            "bsvw" : true,
            "title" : "Address Number",
            "visible" : true,
            "longName" : "mnAddressNumber_51",
            "qbeEnabled" : true
          },
          "F0101_ALPH" : {
            "id" : 52,
            "dataType" : 2,
            "bsvw" : true,
            "title" : "Alpha Name",
            "visible" : true,
            "longName" : "sAlphaName_52",
            "qbeEnabled" : true
          },
          "F0101_AT1" : {
            "id" : 53,
            "dataType" : 2,
            "bsvw" : true,
            "title" : "Sch Typ",
            "visible" : true,
            "longName" : "sSchTyp_53",
            "qbeEnabled" : true
          }
        },
        "rowset" : [ {
          "rowIndex" : 0,
          "MOExist" : false,
          "F0101_AN8" : {
            "internalValue" : 10102,
            "value" : "10102"
          },
          "F0101_ALPH" : {
            "internalValue" : "Allcott, Melissa",
            "value" : "Allcott, Melissa"
          },
          "F0101_AT1" : {
            "internalValue" : "A",
            "value" : "A"
          }
        }, {
          "rowIndex" : 1,
          "MOExist" : false,
          "F0101_AN8" : {
            "internalValue" : 10108,
            "value" : "10108"
          },
          "F0101_ALPH" : {
            "internalValue" : "Bryant, Kelly",
            "value" : "Bryant, Kelly"
          },
          "F0101_AT1" : {
            "internalValue" : "A",
            "value" : "A"
          }
        }, {
          "rowIndex" : 2,
          "MOExist" : false,
          "F0101_AN8" : {
            "internalValue" : 10106,
            "value" : "10106"
          },
          "F0101_ALPH" : {
            "internalValue" : "Dylan, Devin",
            "value" : "Dylan, Devin"
          },
          "F0101_AT1" : {
            "internalValue" : "A",
            "value" : "A"
          }
        }, {
          "rowIndex" : 3,
          "MOExist" : false,
          "F0101_AN8" : {
            "internalValue" : 10105,
            "value" : "10105"
          },
          "F0101_ALPH" : {
            "internalValue" : "Eckles, Jocelyn",
            "value" : "Eckles, Jocelyn"
          },
          "F0101_AT1" : {
            "internalValue" : "A",
            "value" : "A"
          }
        }, {
          "rowIndex" : 4,
          "MOExist" : false,
          "F0101_AN8" : {
            "internalValue" : 10112,
            "value" : "10112"
          },
          "F0101_ALPH" : {
            "internalValue" : "Harris, Eric",
            "value" : "Harris, Eric"
          },
          "F0101_AT1" : {
            "internalValue" : "A",
            "value" : "A"
          }
        } ],
        "summary" : {
          "records" : 5,
          "moreRecords" : true
        }
      }
    },
    "errors" : [ ],
    "warnings" : [ ]
  },
  "stackId" : 2,
  "stateId" : 1,
  "rid" : "190f4c9e40ebbc11",
  "currentApp" : "DATABROWSE_F0101",
  "timeStamp" : "2016-11-02:21.04.53",
  "sysErrors" : [ ]
}

Example Request GRID_DATA Output

The following shows an example of a data request. The token value is from a prior call to the token request service to establish a session. The request includes a query and sorting order for three columns. 

curl -i -X POST -H "Content-Type:application/json" http://ais_server_url/jderest/dataservice -d
{  
  "token" : "0448slzeSZXyg2hHkcQNdbhhcYA9INqoVWydJSxVK8SWi8=MDE5MDA4MTQ3NTYwODg5MTA3NjAzNTA5Mk15RGV2aWNlMTQ3ODEyMDQyNDYxNQ==",
  "deviceName" : "MyDevice",
  "aliasNaming" : true,  
  "outputType" : "GRID_DATA",
  "targetName" : "F0101",
  "targetType" : "table",
  "dataServiceType" : "BROWSE",  
  "maxPageSize" : "10",
  "returnControlIDs" : "F0101.AN8|F0101.ALPH|F0101.AT1",
  "query" : {
    "autoFind" : true,
    "condition" : [ {
      "value" : [ {
        "content" : "7000",
        "specialValueId" : "LITERAL"
      } ],
      "controlId" : "F0101.AN8",
      "operator" : "GREATER"
    } ]
  },
  "aggregation" : {
    "orderBy" : [ {
      "column" : "F0101.AT1",
      "direction" : "ASC"
    }, {
      "column" : "F0101.ALPH",
      "direction" : "ASC"
    }, {
      "column" : "F0101.AN8",
      "direction" : "DESC"
    } ]
  }
}

Example Response GRID_DATA Output

The following example shows the contents of the response body for GRID_DATA output type.

{
  "fs_DATABROWSE_F0101" : {
    "title" : "Data Browser - F0101 [Address Book Master]",
    "data" : {
      "gridData" : {
        "columns" : {
          "F0101_AN8" : "Address Number",
          "F0101_ALPH" : "Alpha Name",
          "F0101_AT1" : "Sch Typ"
        },
        "rowset" : [ {
          "F0101_AT1" : "A",
          "F0101_AN8" : 10102,
          "F0101_ALPH" : "Allcott, Melissa"
        }, {
          "F0101_AT1" : "A",
          "F0101_AN8" : 10108,
          "F0101_ALPH" : "Bryant, Kelly"
        }, {
          "F0101_AT1" : "A",
          "F0101_AN8" : 10106,
          "F0101_ALPH" : "Dylan, Devin"
        }, {
          "F0101_AT1" : "A",
          "F0101_AN8" : 10105,
          "F0101_ALPH" : "Eckles, Jocelyn"
        }, {
          "F0101_AT1" : "A",
          "F0101_AN8" : 10112,
          "F0101_ALPH" : "Harris, Eric"
        } ],
        "summary" : {
          "records" : 5,
          "moreRecords" : true
        }
      }
    },
    "errors" : [ ],
    "warnings" : [ ]
  },
  "stackId" : 2,
  "stateId" : 1,
  "rid" : "ecdecdbd7f258cd6",
  "currentApp" : "DATABROWSE_F0101",
  "timeStamp" : "2016-11-02:21.07.07",
  "sysErrors" : [ ]
}

Example Aggrigation Request

The following shows an example of an aggregation data request. This requests the average salary (SAL) from F060116 (returned in descending order) and the record count, where the average salary is greater than 50000. The token value is from a prior call to the token request service to establish a session. 

curl -i -X POST -H "Content-Type:application/json" http://ais_server_url/jderest/dataservice -d
{  
  "token" : "044zvYQtpbBAd9os/GxkCDy9UmC+WJFGS38fx0KAmUve70=MDE5MDA4NTMwMTcyNDMwOTY5Mjk5NDY1Mk15RGV2aWNlMTQ3ODY0MDk1ODM1Mg==",
  "deviceName" : "MyDevice", 
  "targetName" : "F060116",
  "targetType" : "table",
  "dataServiceType" : "AGGREGATION",
  "aggregation" : {
    "aggregations" : [ {
      "column" : "F060116.SAL",
      "aggregation" : "AVG"
    }, {
      "column" : "*",
      "aggregation" : "COUNT"
    } ],
    "groupBy" : [ {
      "column" : "F060116.HMCU"
    } ],
    "orderBy" : [ {
      "column" : "F060116.SAL",
      "aggregation" : "AVG",
      "direction" : "DESC"
    }]
  },
  "having" : {
    "condition" : [ {
      "value" : [ {
        "content" : "50000",
        "specialValueId" : "LITERAL"
      } ],
      "controlId" : "F060116.SAL",
      "operator" : "GREATER",
      "aggregation" : "AVG"
    } ]
  }
}

Example Aggrigation Response

The following example shows the contents of the response body for an aggregation request.

{
  "ds_F060116" : {
    "output" : [ {
      "groupBy" : {
        "F060116.HMCU" : "          50"
      },
      "F060116.SAL_AVG" : 260000.0,
      "COUNT" : 1
    }, {
      "groupBy" : {
        "F060116.HMCU" : "    80180101"
      },
      "F060116.SAL_AVG" : 183600.0,
      "COUNT" : 5
    }, {
      "groupBy" : {
        "F060116.HMCU" : "          77"
      },
      "F060116.SAL_AVG" : 77878.0,
      "COUNT" : 1
    }, {
      "groupBy" : {
        "F060116.HMCU" : "         200"
      },
      "F060116.SAL_AVG" : 63866.67,
      "COUNT" : 12
    }, {
      "groupBy" : {
        "F060116.HMCU" : "         777"
      },
      "F060116.SAL_AVG" : 52778.65,
      "COUNT" : 33
    }, {
      "groupBy" : {
        "F060116.HMCU" : "        7071"
      },
      "F060116.SAL_AVG" : 51957.68,
      "COUNT" : 19
    } ]
  }
}
Back to Top