Complex data joins can be exposed from the AgileAssets system using database views and the REST API.
The following examples shows the process.
First create the view in the database.
e.g. Create a view ROUTE_ID_VIEW
CREATE OR REPLACE FORCE VIEW ROUTE_ID_VIEW ("ROUTE_ID") AS SELECT DISTINCT ROUTE_ID FROM SETUP_NETWORK_LINES ORDER BY ROUTE_ID; |
The following JSON is returned which is the values from the database view
[ { "ORACLEROWNUM": 1, "ROUTE_ID": 74523 }, { "ORACLEROWNUM": 2, "ROUTE_ID": 74524 }, { "ORACLEROWNUM": 3, "ROUTE_ID": 74525 }, { .... |
Note: By default only the first 200 records are returned.
var data = null; var xhr = new XMLHttpRequest(); xhr.withCredentials = true; xhr.addEventListener("readystatechange", function () { if (this.readyState === 4) { console.log(this.responseText); } }); xhr.open("GET", "https://akdot.agileassets.com/AMS_AK_DEV/rest/v1/lookup/view/ROUTE_ID_VIEW?q={%22page%22:{%22size%22:1000,%22number%22:1}}"); xhr.setRequestHeader("Authorization", "Bearer $2a$12$YT4WGAVMaSP09.qisYMp6OP/VpAUtynPQUqIRtTm9dU6A3lfWMKRW"); xhr.setRequestHeader("Cache-Control", "no-cache"); xhr.send(data); |
The "Q" object is optional object of JSONObject type that can be passed as @QueryParameter into many API calls. Purpose of this object is to format response of API response.
In the following example we request all work orders, and with the "q" object limit response to return only open work orders (Status = 4). The response contains only two columns, sorted by work order start date and returns second page where there are 20 rows per page: |