REST API Explorer - query joining two tables

[suprabha]

Hi Team,

We have to prepare a table API query joining two tables incident and journal_entry_field to fetch some incidents having entry in journal_entry_field by some specific user on particular date range.

I can able to get the same by database view but that would not work for me as the target system is not accessible to me this time and we can't create any view on same, we can fire the table query from browser/postman only to get expected result.

Can anybody help me on this please?

[Ankur Bawiskar]

Hi Suprabha,

you can join those 2 tables and then try using the database view in the API endpoint.

query looks fine

just test it once

Regards
Ankur

[Ankur Bawiskar]

@suprabha

Let me know if I have answered your question.

If so, please mark appropriate response as correct & helpful so that this thread can be closed and others can be benefited by this.

If not, please let us know if you need some more assistance.

Regards
Ankur

[mauricio_paloma]

Ankur was recommending a Scripted REST API. 

Avoid using the Table API and create your own end point using a Scripted REST API that can do the queries across multiple tables. 

[Ankur Bawiskar]

@suprabha

Let me know if I have answered your question.

If so, please mark appropriate response as correct & helpful so that this thread can be closed and others can be benefited by this.

If not, please let us know if you need some more assistance.

Regards
Ankur

[Hitoshi Ozawa]

Since the question doesn't yet seems to be fully answered to questioner's satisfaction, offering my version.

Create a Scripted REST API with Relative Path "/{user}" and query parameter "date".

The script to get all work notes on an incident is as follows:

(function process( /*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {

    var userName = request.pathParams.user;
    var startDate = request.queryParams.date;

    if (!startDate) {
        startDate = new GlideDateTime().getDisplayValue();
    }
    var records = [];

    var grSJF = new GlideRecord('sys_journal_field');

    grSJF.addEncodedQuery("'element=work_notes^name=incident^sys_created_onON" + startDate + "@javascript:gs.dateGenerate('" + startDate + "','start')@javascript:gs.dateGenerate('" + startDate + "','end')^sys_created_bySTARTSWITH" + userName);
    grSJF.query();
    while (grSJF.next()) {
        var grIncident = new GlideRecord('incident');
        grIncident.addQuery('sys_id', grSJF.element_id);
        grIncident.query();
        while (grIncident.next()) {
            records.push({
                'incident': {
                    'sys_id': grIncident.sys_id,
                    'number': grIncident.number,
                    'short_description': grIncident.short_description,
                    'sys_created_by': grIncident.sys_created_by,
                    'sys_created_on': grIncident.sys_created_on,
                    'work_notes': grSJF.value,
                    'assignment_group': grIncident.assignment_group,
                    'state': grIncident.state
                }
            });
        }
    }
    return records;
})(request, response);

Calling this API will result in a json message like the following. (note: I've named the scripted REST API "getjournalandincident" and the below query is querying on user name = "admin" and on date ="2020-10-08".

https://<servicenow instance>.service-now.com/api/<namespace>/getjournalandincident/admin?date=2020-10-08

{
  "result": [
    {
      "incident": {
        "sys_id": "a83820b58f723300e7e16c7827bdeed2",
        "number": "INC0010111",
        "short_description": "ATF : Test1",
        "sys_created_by": "admin",
        "sys_created_on": "2019-07-22 21:05:17",
        "work_notes": "Checklist item added: Check 2",
        "assignment_group": "",
        "state": "1"
      }
    },
    {
      "incident": {
        "sys_id": "c257f690dbb3101083212a9a4896190f",
        "number": "INC0010005",
        "short_description": "Stream file download",
        "sys_created_by": "admin",
        "sys_created_on": "2020-10-08 01:04:52",
        "work_notes": "Checklist item added: Check 2",
        "assignment_group": "",
        "state": "1"
      }
    }
  ]
}