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

Query or Aggregate Tables and Views v1

post

/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
  • AggregationInfo
    Provide information about the requested aggregation.
  • Specify whether alias names should be used for field names. A value of true prevents Data Dictionary overrides from changing the field names.
  • Specify whether cache should be used if available for the transaction.
  • A flag to indicate if this is a batch data request (used for data requests only).
  • Override the default server Cache Time To Live in milliseconds.
  • Allowed Values: [ "BROWSE", "COUNT", "AGGREGATION" ]
    The type of data service request to execute.
  • An identifier for the device making the service call. If not provided, the requesting IP address is used.
  • Specify the environment to use for login. If not specified, the default environment configured for the AIS Server will be used.
  • Allowed Values: [ "true", "false" ]
    Indicates that the Find button should be pressed.
  • If cache is enabled, this tells the request to ignore the cache and execute the request.
  • formActions
    An array of actions to be performed in a specified order on a particular form.
  • Allowed Values: [ "true", "false" ]
    Request only a demo version of the form to view all the fields without actual data.
  • Having
    The details of the having clause to be applied to an aggregation request.
  • Specify whether timing information should be included in the response. The timing information shows how long the formservice/dataservice ran and the total processing time.
  • The HTML (JAS) Server to use for login. If not specified, the default HTML Server configured for the AIS Server will be used.
  • The maximum number of records that should be returned. The default value is 100. A value of 'No Max' will return all records.
  • Allowed Values: [ "<blank>", "VERSION1", "GRID_DATA", "VERSION2" ]
    The format of the output. The original (VERSION1) format is used by default. Other valid values are GRID_DATA and VERSION2.
  • The JD Edwards EnterpriseOne password, which is required if other authentication methods are not being used.
  • Used for logging in with psToken. Primarily used by the HTML Server to establish a session with the AIS Server.
  • Query
    The query portion of a data service or form service request.
  • The object name of a saved query to be executed on the form.
  • The returnControlIDs string is bar delimited, without a starting or ending bar. Form fields are just a single value. Grid fields are indicated with a grid id, followed by a bracketed list of grid columns. Subform fields are indicated with subform id underscore field id. For example: 33|34|17[24,26,28]|50_45|50_53|50_9[35,39,41]
  • The role to use for login. If not specified, the default role configured for the AIS Server will be used.
  • Specify whether to delete the data currently stored in the cache for the request. This does not execute the request. This forces the next execution to fetch from the database.
  • Specify whether action controls such as buttons, row, and form exits should be included in the response.
  • The name of the table or business view for the request.
  • Allowed Values: [ "table", "view" ]
    The object type of the target.
  • The AIS token, which is required if other authentication methods are not being used.
  • JD Edwards EnterpriseOne username, which is required if other authentication methods are not being used.
  • A list of variable names to correspond to the returnControlIDs. Used by Orchestrator only.
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
  • The value to be compared if it's a literal, or the value to be used in conjunction with the special value operation.
  • Allowed Values: [ "LITERAL", "LOGIN_USER", "TODAY", "TODAY_PLUS_DAY", "TODAY_MINUS_DAY", "TODAY_PLUS_MONTH", "TODAY_MINUS_MONTH", "TODAY_PLUS_YEAR", "TODAY_MINUS_YEAR" ]
    The special value id (see allowed values). Specify LITERAL if using the constant value only.
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