Date: Thu, 28 Mar 2024 08:35:55 -0500 (CDT) Message-ID: <1551663071.34503.1711632955952@olcflv1.outside.agileassets.com> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_34502_1857515106.1711632955942" ------=_Part_34502_1857515106.1711632955942 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
Complex data joins can be exposed from the AgileAssets system us= ing 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 O= R REPLACE FORCE VIEW ROUTE_ID_VIEW ("ROUTE_ID") AS=20 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 = =3D null; var xhr =3D new XMLHttpRequest(); xhr.withCredentials =3D true; xhr.addEventListener("readystatechange", function () { if (this.readyState =3D=3D=3D 4) { console.log(this.responseText); } }); xhr.open("GET", "https://akdot.agileassets.com/AMS_AK_DEV/rest/v1/lookup/vi= ew/ROUTE_ID_VIEW?q=3D{%22page%22:{%22size%22:1000,%22number%22:1}}"); xhr.setRequestHeader("Authorization", "Bearer $2a$12$YT4WGAVMaSP09.qisYMp6O= P/VpAUtynPQUqIRtTm9dU6A3lfWMKRW"); xhr.setRequestHeader("Cache-Control", "no-cache"); xhr.send(data);
The "Q" Object Reference
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.
Example of such object:
{
"projection":["WO=
RK_ORDER_ID","STATUS"],
"filter":[[<=
span style=3D"color: rgb(78,154,6);">"STATUS","=3D",4], ["OWNER_ID"=
span>,"IN","1317,56"]=
span>, "AND"],
"sort":{"START_DATE":"DESC"<=
span style=3D"color: rgb(114,159,207);">},
"page":{=
span>"size":20,&n=
bsp;"number":2}
}
All fields of object are optional and have the following meaning:
In the following example we request all work orders, and with the "q=
" object limit response to return only open work orders (Status =3D 4). The=
response contains only two columns, sorted by work order start date and re=
turns second page where there are 20 rows per page:
<baseUrl>/workOrder?q=3D{"filter":=
[["STATUS","=3D",4]],"projection":["WORK_ORDER_ID","STATUS"],"sort":{"START=
_DATE":"DESC"},"page":{"size":20,"number":2}}
Note: When combining the proje=
ction with the sort & filter features, columns referenced in the sort a=
nd filter parameters must be included in the pro=
jection parameter.
N=
ote: When using a default q filter a default of 200 maxim=
um records are returned. Use the page field to s=
et the "size" to a high value (number of records to return) and set "number=
" to 1.