Now experience - UI builder - Complex filter for data broker

[Aniko Hegedus]

Hi Everyone,

I’m just getting familiar with the Now Experience UI Builder and came across some problems that I didn’t find the answer to very easly. Since I managed to come up with some solutions, I decided to share it, maybe it’ll be useful for some of you. 
Please note that I’m an absolute newbee to UI Builder, so please be considerate when you see that something could have been solved much better. 🙂

Scenario
In a drop-down list display the users and their employee ids who have open „New email account” catalog request.

Problem
Combine data from two tables: sc_req_item and sys_user tables.
How I could phrase it for myself is that I needed a complex filter for the data source.

Solution

I created my own data source where I can use data from the reference fields.
I copied the „GlideRecord Collection Query” GraphQL Data Broker and made the changes in the script.

First I show you how the script would look like if I just wanted to query the sc_req_item table

(function buildQuery(query, inputs) {
    var newEmailCatItemSysId = gs.getProperty("xxxx.u_new.email_cat.item_sysid");
    var userQuery =
        "{\r\n" +
        "        GlideRecord_Query {\r\n" +
        "         sc_req_item(queryConditions: \"cat_item=" + newEmailCatItemSysId + "\") {\r\n" +
        "            _results {\r\n" +
        "               requested_for {\r\n" +
        "                   value\r\n" +
        "                   displayValue\r\n" +
        "                  }\r\n" +
        "               }\r\n" +
        "               cat_item {\r\n" +
        "                   displayValue\r\n" +
        "               }\r\n" +
        "            }\r\n" +
        "          }\r\n" +
        "        }\r\n" +
        "}";
    query.append(userQuery);
})(query, inputs);

This returns the requested_for user’s data and the cat item’s name.
However, in a dropdown list I need to have the requester’s employee number and sys_id for which normally you would write an additional Glide Query, but here you can use the „_reference” keyword.

(function buildQuery(query, inputs) {
    var newEmailCatItemSysId = gs.getProperty("x_489524_my_dict_0.u_new.email_cat.item_sysid");
    var userQuery =
        "{\r\n" +
        "        GlideRecord_Query {\r\n" +
        "         sc_req_item(queryConditions: \"cat_item=" + newEmailCatItemSysId + "\") {\r\n" +
        "            _results {\r\n" +
        "               requested_for {\r\n" +
        "                   value\r\n" +
        "                   displayValue\r\n" +
        "                  _reference {\r\n" +
        "                     sys_id {\r\n" +
        "                       value\r\n" +
        "                     }\r\n" +
        "                     employee_number {\r\n" +
        "                       displayValue\r\n" +
        "                     }\r\n" +
        "                  }\r\n" +
        "               }\r\n" +
        "               cat_item {\r\n" +
        "                   displayValue\r\n" +
        "               }\r\n" +
        "            }\r\n" +
        "          }\r\n" +
        "        }\r\n" +
        "}";
    query.append(userQuery);
})(query, inputs);

If we add this new data source to the UI builder the output will be the following

{
  "data": {
    "GlideRecord_Query": {
      "sc_req_item": {
        "_results": [
          {
            "requested_for": {
              "value": "sysid",
              "displayValue": "User 1",
              "_reference": {
                "employee_number": {
                  "displayValue": "1234"
                }
              }
            },
            "cat_item": {
              "displayValue": "New Email Account"
            }
          },
          {
            "requested_for": {
              "value": "sysId",
              "displayValue": "User 2",
              "_reference": {
                "employee_number": {
                  "displayValue": "121212"
                }
              }
            },
            "cat_item": {
              "displayValue": "New Email Account"
            }
          }
        ]
      }
    }
  }
}

So now we only need to add a dropdown component and script the property value

/**
 * @param {params} params
 * @param {api} params.api
 * @param {any} params.imports
 */
function evaluateProperty({api}) {

    var results = api.data.my_gliderecord_collection_query_1.output.data.GlideRecord_Query.sc_req_item._results;
    var listForDropdown = [];
    for (var i = 0; i < results.length; i++) {
        var obj = {
            "id": results[i].requested_for.value,
            "label": results[i].requested_for.displayValue + " - " + results[i].requested_for._reference.employee_number.displayValue
        };
        listForDropdown.push(obj);
    }

    return listForDropdown;
}

And this should give you the result of a dropdown menu with two names, the corresponding employee id and the sys_ids of these user as the values.

Please feel free to comment if you have any better solutions.
Good luck!
Aniko