Data Broker Server Script (Transform) for Calendar component

[User367757]

The Calendar component for Now Experience Framework / UI Builder is a great building block for quickly creating a useful workspace experience for your business. It doesn't come with a default means to bind table data to it tho - so I wanted to provide one here.

I suggest you check out the video on UI Builder Data Resources by @Brad Tilton first. He will show you how to make an entry in sys_ux_data_broker_transform table and then add the necessary execute ACL on that record. These actions happen outside UI Builder, but allow you to wire up data to your components inside UI Builder.

I wanted to use the Calendar component to display an extension table of planned_task. It seemed like a small ask: put my planned tasks on a calendar for me so that I can see what's scheduled for my team.

I added the Calendar to a new page in my workspace experience and took note of the sample data in it. I phoned a friend for help and he told me to use the "Look Up Records" data source. It was simple to configure my source table, add a filter query and select the return fields. I did all this and then noted that the output format isn't the same as the events sample data for the calendar. A bummer, but that's what transforms are for.

The question next for me was: Do I really need to invent this wheel? Has nobody already done this? Well, happily for you, the answer is: YES, I've already done this. And also YES: You can have my code to get you started.

Here's what to do

1. Make a new Data Broker Server Script (sys_ux_data_broker_transform) and name it something like "Events from Planned Tasks Transform".
   
   
2. Take the sys_id of your new entry and create a new Access Control (sys_security_acl) for 'execute' of type ux_data_broker so that it can be accessed.
   
   
3. Returning to your Data Broker Server Script, set the properties value as follows:
   

   [
   {
     "name": "data",
     "label": "data",
     "description": "Planned Task GraphQL Data Result",
     "readOnly": false,
     "fieldType": "json",
     "valueType": "object",
     "mandatory": true
   }
   ]​

4. Use this script value to get you started. It will generate an array of objects with the minimum required properties to populate the calendar. It is necessary to use the UTC value for times if you plan to let users select their TimeZone on the calendar.
   

   function transform(input){
   	var returnArray = [];
   	var records = input.data;
   	
   	for (var i in records){
   		var myObj = {};
   		myObj.title = records[i]._row_data.displayValue;
   		myObj.id = records[i]._row_data.uniqueValue;
   		myObj.description = records[i].short_description.displayValue;
   		
   		var startUTC = new GlideDateTime(records[i].start_date.value);
   		myObj.start = startUTC.getNumericValue();
   		
   		var endUTC = new GlideDateTime(records[i].end_date.value);
   		myObj.end = endUTC.getNumericValue();
   
   		returnArray.push(myObj);
   	}
   	
   	return returnArray;
   }

5. Save the Data Broker Server Script and return to UI Builder.
   
   
6. Add the Look Up Records data broker to your page with the Calendar component. Query for Planned Tasks (or any other table, provided you update the script to match) and verify the output is working in the preview pane.
   
   
7. Add the Data Broker Server Script to your page, and bind the input on it to the result of your Look Up Records data resource. Verify the output in the preview pane.
   
   
8. Configure the Calendar component's events property to the output of your Data Broker Server Script. Verify the outcome outside of UI Builder as it is much easier to navigate to the correct date range to see the Planned Tasks / Events.

I hope this simple sample helps you along the way. I'm still quite new to Now Experience Framework, but I welcome your questions and comments.

[Grant Hulbert]

Thanks, this is soooo helpful! Would never have figured it out without your magic incantations...

[Ina]

Hi,

Thanks for writing this, it was helpful in understanding how the different pieces are put together. 

I have a few questions. I didn't realize I could bind my output from a Look Up Record to a transform script (step 7), how do I go about doing that? 

My issue is the content tree, which I am trying to add the knowledge categories to. I am a bit confused as to what is the best way to achieve this. In my understanding there are a few options. It could probably be done using GraphQL, a transform, or a combination of transform and Data Lookup Record (or GraphQL?) like you did here. Maybe also options I am not thinking of. 

The output should be something like the one below. 

[
  {
    "id": "parent1",
    "label": "Parent 1",
    "children": [
      {
        "id": "child11",
        "label": "Child 11"
      },
      {
        "id": "child12",
        "label": "Child 12"
      }
    ]
  },
  {
    "id": "parent2",
    "label": "Parent 2",
    "children": [
      {
        "id": "child21",
        "label": "Child 21"
      },
      {
        "id": "child22",
        "label": "Child 22"
      }
    ]
  }
]

I have been able to create something similar to my example above using this script below (transform). Obviously my children are hard coded in this example. 

function transform(input) {

    var child = {};
    child.id = 'Child 1';
    child.label = 'child1';

    var child2 = {};
    child2.id = 'Child 2';
    child2.label = 'child2';

    var categoriesArray = [];
    var i = 0;
    var categories = new GlideRecord('kb_category');
    categories.addActiveQuery();
    categories.addQuery('parent_id', '387b2552ff0131009b20ffffffffffdf');
    categories.query();

    while (categories.next()) {
        var categoriesObject = {};
        categoriesObject.id = categories.getValue('value');
        categoriesObject.label = categories.getValue('label');
        categoriesObject.children = [child, child2];
        categoriesArray[i] = categoriesObject;
        i += 1;
    }


    return categoriesArray;
}

This gives me the below output:

There is also a GraphQL Data Broker called "Get knowledge categories" in the scope "Knowledge Capabilities in UI Builder" which I could probably re-use, but I am stuck. 

I don't know exactly what I am asking, but some guidance in achieving this I guess. Am I on the right track?

[User367757]

Yes, it looks like you're on the right track. I have a sample data broker server script here that builds a slightly different model of the input data, but I think you can adapt it.

The big thing is to use smaller functions to build your objects.

function transform(input) {
    var modelArray = [];
    var modelData = input.broker;
    
	//gs.info(JSON.stringify(modelData));
	
    function block(label, type, value) {
        var out = {};
        var labelNew = gs.getMessage("{0}", label);
        out.label = labelNew;
        var outval = {};
        outval.type = type;
        outval.value = value;
        out.value = outval;
        return out;
    }

    function getDate(item) {
        if (undefined != item && item.displayValue != "" && item.displayValue != null) {
            var itemDate = new GlideDateTime();
            itemDate.setValue(item.displayValue);
			//attempt to bail on non-dates
			if (!itemDate.isValid())
				return null;
			
            item.displayValue = itemDate.getValue();
            return item;
        }
    }

    function addToModel(item, label) {
		
		//gs.info(JSON.stringify(item));
		
        if (undefined != item && item.displayValue != "" && item.displayValue != null) {
            modelArray.push(block(label, "string", item.displayValue));
        } else 
			modelArray.push(block(label, "string", "TBD"));
    }

	for (var f in modelData){
		if (f == '_row_data' || f == 'number' || f == 'short_description')
			continue;
		
		var field = modelData[f];
		
		if (!field)
			continue;
		
		var gdt = getDate(field);
		if (gdt)
			addToModel(gdt, field.label);
		else
			addToModel(field, field.label);
	}
	
    return modelArray;
}

Remember that you can gs.info (with JSON.stringify()) inside these and read that in the system logs to help you debug these broker scripts.

[User367757]

You had also asked how to bind the output of a lookup record(s) to your broker script. That looks like this:

[Ina]

Thank you for taking the time to reply! 

[Shashank35]

Can you make a Data Broker Server Script (Transform) for RestApi.I am new in Now Experience Framework 

I want when i clicked the Button in uib it Call the rest api and I used this server side code 

var demo_outbound_script_include = Class.create();
demo_outbound_script_include.prototype = {
 initialize: function() {},
 myFunction: function() {
 try {
 var r = new sn_ws.RESTMessageV2('global.demo_outbound', 'Default GET');

 //override authentication profile 
 //authentication type ='basic'/ 'oauth2'
 //r.setAuthenticationProfile(authentication type, profile name);

 //set a MID server name if one wants to run the message on MID
 //r.setMIDServer('MY_MID_SERVER');

 //if the message is configured to communicate through ECC queue, either
 //by setting a MID server or calling executeAsync, one needs to set skip_sensor
 //to true. Otherwise, one may get an intermittent error that the response body is null
 //r.setEccParameter('skip_sensor', true);

 var response = r.execute();
 var responseBody = response.getBody();
 var httpStatus = response.getStatusCode();
 return JSON.stringify(responseBody);
 } catch (ex) {
 var message = ex.message;
 }
 },
 type: 'demo_outbound_script_include'
};

[Community Alums]

Hi,

Thanks for this excellent post related to Calendar component. 

I followed all the steps you have. I am seeing the records returned in preview for Look Up Records and the GraphQL

I also bound Events in Calendar config with output of GraphQL

I am not seeing the events in calendar, below is my output. "start" and "end" are numeric values of Date field, not date/time field.

[

  {

    "id": "xxxxxx",

     "number": "yyyyyyyy",

     "start": 1641340800000, (this is for Jan 05 2022)

     "end": 1641340800000 (this is for Jan 05 2022)

  }

]

I want to show the "number" on Calendar for Jan 05 2022

What else I maybe missing in config of Calendar component?

[oleg7]

Hey, does anyone know if I can force calendar to reload events from the datasource? 

I added possibility to filter the events, but when I update the datasource, calendar component is not getting refreshed so I was looking for a way on how I can force it.

[prity]

Hi, were you able to auto-refresh the component based on the datasource update. I have the similar requirement.

[Ina]

My use case recently was needing to make an update to a record, and wanted the update to be displayed immediatly,. 

I am not sure if it's exactly what you need, but you could use this in a client script:

 api.data.name_of_your_datasource.refresh();

So for example you could bind a client script to a button (or whatever), and then use above sentence in your script, and the datasource will be updated together with the other things performed in your script. 

[kmohammed]

https://community.servicenow.com/community?id=community_question&sys_id=244c2857db794590ae8125091396195e

[darshanam_rp]

Hello, did you fix the issue.