Script to dynamically generate URLs for hyperlinks in Pivot Table rows

Hello! I need help modifying a widget-level script that we've used for years to create clickable hyperlinks in our Pivot tables.

First, a bit of background. My company doesn't use Sisense directly, instead we use matter management software called TeamConnect, which has a reporting component called "TeamConnect Business Intelligence" or TCBI. This is just a rebranded form of Sisense; in our case, it's specifically Sisense v8.2.1 (Windows). It's important to keep this in mind, since ours is an older version of Sisense, so certain features may not be available to us, or certain scripts may not work in our environment. Also, we do not have direct access to the backend admin area for TCBI/Sisense (only the vendor behind TeamConnect does), so ideally I'd like to avoid solutions that involve modifying or creating new columns/tables in the eCube. If this is the only solution then we'll work with the vendor, if need be, to modify things in the eCube, but this potentially costs us so we try to avoid it unless necessary.

Anyway, we've long been using the following script to create hyperlinks in our pivot tables:

widget.on('ready', function(se, ev)
{ prism.createDrillDown(widget, "<LinkedColumnName>", "<PrimaryKey>", “<ObjectID>", "_<location>", "<TeamConnectURL>"); }
);

"LinkedColumnName" is the name of the column that we want to be the hyperlink. "PrimaryKey" is the unique identifier for the record we want to link to, and is a required part of the URL we're constructing. "ObjectID" indicates the type of record being linked to, and is also required for the URL to work properly.

Here's a specific example of a script we'd use:

widget.on('ready', function(se, ev) 
{ prism.createDrillDown(widget, "Dispute Name", "Dispute Primary Key", "DISP", "_blank", "https://southwest.teamconnect.com/SOUTHWEST"); }
);

Basically, this is saying "Make whatever is in the 'Dispute Name' column into a hyperlink, utilizing the code 'DISP' and whatever unique ID is under 'Dispute Primary Key'."

(Note: you'll notice it somehow inserts "/entityrecord/" between the base URL and the object code+PK. I'm not sure how it does that. Perhaps it is functionality that's unique to TCBI, or they're using a plugin of some kind?)

Anyway, this script has served us well for several years, but recently we ran into an issue with a new custom table we asked the vendor to create for us. It's a unioned table that has records from two different objects. This means some rows need to link to a "DISP" primary key, but others need to use a different object code: "TRAN". In our current script, the "DISP" part is hard-coded. This means if we use the script as-is then the hyperlinks it generates for TRAN-type records is incorrect.

We could change it to use "TRAN" instead but obviously then the DISP-based records won't work. We need a way for the script to dynamically switch between either "DISP" or "TRAN" depending on the record.

As you can see in the screen, we do have a column in that custom table that lists the object, called "Object Type". The problem is that the values are not an exact match for what the linking URL requires. They show the full name of the object ("Dispute", "Transaction") not it's shorter code ("DISP", "TRAN"). So we'd need some way of taking the LEFT of Object Type for the first four characters, or maybe just simple if/then logic (e.g. "If 'Dispute', then 'DISP'").

However, before we can even attempt that, we noticed a larger issue when we tried modifying the script to insert "Object Type".

widget.on('ready', function(se, ev) 
{ prism.createDrillDown(widget, "Matter Name", "Matter Primary Key", "Object Type", "_blank", "https://southwest.teamconnect.com/SOUTHWEST"); }
);

Unfortunately, this doesn't work. That third parameter is treated as a literal, rather than dynamically pulling the correct value, so it simply outputs the phrase "Object Type" into the URL for every row.

Is there a way we could make it pull the actual value from the column, like the other parameter is doing with the Primary Key? And if so, is there a way we can limit the value to the first four characters instead of the full name?

Alternatively: is there a different way of achieving this, perhaps with another script? We don't mind switching to something else as long as it achieves the same end goal (opening up hyperlinks in a new tab to the correct URLs).

Jake Raz

Posted 1 year ago·Last reply 1 year ago

15 comments

Jake Raz

OP1 year ago

Interestingly, the Mitratech support team for our matter management software (TeamConnect) came up with a different script. I think they're still refining it but we verified this also works.

widget.on("ready", function(widget, args) {
  
   var CUR_COL_FIND_CRITERIA = "[fidx=1]";
   var FORMAT_COL_FIND_CRITERIA = "[fidx=0]";
	var OBJECT_TYPE = "[fidx=2]";
	var url = 'https://southwest.teamconnect.com/SOUTHWEST/entityrecord/';
  
   //format a cell according to a condition on the current cell
   function formatCell(){
    if ($(this).siblings(FORMAT_COL_FIND_CRITERIA).attr('val') != undefined){
	   var x = $(this).siblings(FORMAT_COL_FIND_CRITERIA).attr('val');
	  var x1 = $(this).attr('val');
		var x2 = $(this).siblings(OBJECT_TYPE).attr('val');
		if (x2 === 'Dispute'){
			x3 = 'DISP';
		} else {
			x3 = 'TRAN';
		}
		
		var object_url = url+x2+'_';
	   
     $(this).siblings(FORMAT_COL_FIND_CRITERIA).html('<a rel="noreferrer" href="'+url+x3+'_'+x1+'" target="_blank">'+x+'</a>');
    }
   }

   //get all column cells and format other cells
   var cells = $('[id="' + widget.oid+ '"]').find('td' + CUR_COL_FIND_CRITERIA).each(formatCell);

 });

Jake Raz

OP1 year ago

Okay, took the time to slightly modify the alternate script that Mitratech provided for clarity's sake, adding comments and junk to help better explain what's-what. This works just as well as the other script.

// *************************************
// This script will change the indicated column into a hyperlink
// For example: use to make the Matter Name into a link
// You must have at least three columns displayed: the Primary Key, the Object Type, and a column to make into the hyperlink
// When clicked, the link will open a new tab to a specific record in TeamConnect
// This script is for Pivot and Table widgets

// History: 
// - 8/28/24 - Original script created by Sujay Kumar (TeamConnect/Mitratech Support)
// - 8/29/24 - Modified by Jake_Raz (SWA), formatted for clarity w/ clearer variable names & comments

// Start of script below --
// *************************************

widget.on("ready", function(widget, args) {
  
   // Define the positions of the relevant columns. 
   // Change the number of the fidx to indicate which column is which. Start counting from ZERO.
   // The first left-most column would be [fidx=0], the second column would be [fidx=1], and so on
   
   // The position of the column you want to be a link, e.g. the Matter Name or Matter Number
   var COLUMN_TO_MAKE_LINK_POSITION = "[fidx=3]";
   // The position of the column with the Primary Key of the record being linked to   
   var RECORD_PK_COLUMN_POSITION = "[fidx=0]";
   // The position of the column that says what kind of object the record is, e.g. Dispute or Transaction
   var OBJECT_TYPE_COLUMN_POSITION = "[fidx=1]"; 
	
   // This is the first part of TeamConnect's url format when linking to specific records
   var BASE_TC_URL = 'https://southwest.test.teamconnect.com/SOUTHWEST_TEST2/entityrecord/';
	
   function formatCell(){
      if ($(this).siblings(COLUMN_TO_MAKE_LINK_POSITION).attr('val') != undefined){
	   
	     // Grabbing the text of the cell we're making into a link 
		 var TEXT_OF_LINK = $(this).siblings(COLUMN_TO_MAKE_LINK_POSITION).attr('val');
	     // Grabbing the value of the Primary Key
		 var RECORD_PK_COLUMN_POSITION_VALUE = $(this).attr('val');
	     // Grabbing the Object Type
		 // Taking only the first four characters and making them UPPERCASE (link is case-sensitive)
		 var OBJECT_TYPE_VALUE = $(this).siblings(OBJECT_TYPE_COLUMN_POSITION).attr('val').substring(0, 4).toUpperCase();
 		
		// Constructing the hyperlink
         $(this).siblings(COLUMN_TO_MAKE_LINK_POSITION).html(
		     '<div class="p-value"><a rel="noreferrer" href="'+ // Hardcoded html, don't change this
			 BASE_TC_URL+
			 OBJECT_TYPE_VALUE+'_'+RECORD_PK_COLUMN_POSITION_VALUE+ // ex: "DISP_12345"
			 '" target="_blank">'+ // Ensures link opens in new tab
			 TEXT_OF_LINK+ // The original text in the column that's being turned into a hyperlink
			 '</a></div>' // Closing out html tags
		 );
    }
   }

   // Get all column cells and format other cells
   var cells = $('[id="' + widget.oid+ '"]').find('td' + RECORD_PK_COLUMN_POSITION).each(formatCell);
 });

Here's a condensed version without all the explanations:

widget.on("ready", function(widget, args) {
   var COLUMN_TO_MAKE_LINK_POSITION = "[fidx=3]";
   var RECORD_PK_COLUMN_POSITION = "[fidx=0]";
   var OBJECT_TYPE_COLUMN_POSITION = "[fidx=1]"; 

   var BASE_TC_URL = 'https://southwest.test.teamconnect.com/SOUTHWEST_TEST2/entityrecord/';
	
   function formatCell(){
      if ($(this).siblings(COLUMN_TO_MAKE_LINK_POSITION).attr('val') != undefined){
	   
		 var TEXT_OF_LINK = $(this).siblings(COLUMN_TO_MAKE_LINK_POSITION).attr('val');
		 var RECORD_PK_COLUMN_POSITION_VALUE = $(this).attr('val');
		 var OBJECT_TYPE_VALUE = $(this).siblings(OBJECT_TYPE_COLUMN_POSITION).attr('val').substring(0, 4).toUpperCase();
 		
         $(this).siblings(COLUMN_TO_MAKE_LINK_POSITION).html('<div class="p-value"><a rel="noreferrer" href="' + BASE_TC_URL + OBJECT_TYPE_VALUE + '_' + RECORD_PK_COLUMN_POSITION_VALUE + '" target="_blank">' + TEXT_OF_LINK + '</a></div>');
    }
   }

   var cells = $('[id="' + widget.oid+ '"]').find('td' + RECORD_PK_COLUMN_POSITION).each(formatCell);
 });

DRay

·1 year ago

Jake_Raz . If you are able to find a solution with rapidbisupport please let us know.

RAPID BI Support

·1 year ago

Hi Jake_Raz ,

I think this will require a pretty specific solution, given the older version of Sisense and already custom functions in play here.

As a starting point, I would look at a widget script something like:

widget.on('processresult', (w, args) => {
  
    console.log(args.result.$$rows)
    // [
    //    [                         // ROW 1 DATA
    //      { data: '', text: '' }, // COLUMN 1 DATA
    //      { data: '', text: '' }, // COLUMN 2 DATA
    //      { data: '', text: '' }, // COLUMN 3 DATA
    //    ],
    //    [                         // ROW 2 DATA
    //      { data: '', text: '' }, // COLUMN 1 DATA
    //      { data: '', text: '' },
    //      { data: '', text: '' },
    //    ],
    // ]

    let rowCount = args.result.$$rows.length

    for (let i = 0; i < rowCount; i++) {
        let row = args.result.$$rows[i]

        // set column 1 text to column 0 + column 2 text
        row[1].text = row[0].text + ' ' + row[2].text
        row[1].data = row[0].data + ' ' + row[2].data
    }

    // open the 'inspect' panel in browser console to see what is going on
    // under the hood (debugger below)
    // can use the console to check values - args, w, etc.
    debugger

})

I don't know if the data will look the same as the object commented above - but that will require some exploration. There is a simple example that makes the values / text of one cell related to the values in the adjacent cells.

I think we could get this done pretty quickly over a call if you wanted to take advantage of our free 30-minute consult: https://www.rapidbi.com.au/sisense-add-ons/Services/Free-30-Minute-Sisense-Consultation/

Let me know how you go?

Thanks,

Daniel

RAPID BI

daniel.ranisavljevic@rapidbi.com.au

RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons

Jake Raz

OP1 year ago

Hello! I tried copy/pasting that script into the widget. However, I'm not sure how to pull up the console logs to see the information (from the last comment before the debugger line, it says to "use the console to check values"). After pasting the script, saving, and refreshing, I also do not see anything different with the widget itself.

DRay

·1 year ago

Hi Jake_Raz,

rapidbisupport is referring to the browser console. https://balsamiq.com/support/faqs/browserconsole/

Jake Raz

OP1 year ago

Sorry, I should've specified: I'm aware of how to pull up the developer tools. However, once I did that, I wasn't sure where to find the values he mentioned. The console just shows various warnings or error message about how the page was rendered, I don't see the "args, w, etc." values anywhere.

It's set to "All Levels" and "Verbose" is checked

I also tried clicking around to the other tabs in that menu ("Sources", "Network", "Performance", etc) but couldn't find those values listed anywhere.

DRay

·1 year ago

HI Jake_Raz.

Thank you for the clarification. If you like I can work on getting you a technical resource to work with you. Daniel rapidbisupport also offered a free 30-minute consult, so that is another option for you. Let us know how you would like to proceed.

Jake Raz

OP1 year ago

I don't think we need a consult for their add-on. Even if it can solve our particular issue, it's relatively low-importance in the grand scheme of things, and I don't think my leadership would approve the cost, unfortunately.

DRay

·1 year ago

He did offer a free consultation. It's probably worth a try. Otherwise, I can work with your account team to find a Sisense technical resource to work with you.

RAPID BI Support

·1 year ago

Hi Jake_Raz ,

Not trying to sell you anything - just easier to resolve over a call.

I'd be keen to help support you in solving this particular problem.

The script is failing because there is no 'args.result.$$rows'. As suggested, the older Windows version must store this differently.

You can get the debugger up and then inspect what's in scope by simply doing a:

widget.on('processresult', (w, args) => {
  debugger
})

re: our motivations on Community:

RAPID BI exists to help companies get more from their data,
This isn't a sales play - we're looking to meet customers and better learn their use-cases, strategies and frustrations so that we can better fit the market,
We do this to show thought leadership/celebrate our partnership with Sisense - hoping that potential customers will see a post or solution by us and reach out.

We do have paid addons that we also market - but we're explicitly not allowed by community guidelines to shamelessly plug them (and rightly so) - unless they solve a very specific solution and are presented alongside other potential solutions.

I'm confident we can get to the bottom of what's happening here on a call - and my intention post call would be to document what we found out about tables in Sisense on Windows here afterwards.

TL;DR - i'm not going to try sell you an addon, I just want to help you solve this problem.

Let me know how you go?

Thanks,

Daniel

RAPID BI

daniel.ranisavljevic@rapidbi.com.au

RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons