cancel
Showing results for 
Search instead for 
Did you mean: 

Script to dynamically generate URLs for hyperlinks in Pivot Table rows

Jake_Raz
10 - ETL
10 - ETL

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'."

Jake_Raz_1-1723816059863.png

Jake_Raz_0-1723815701411.png

(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.

Jake_Raz_3-1723817401996.png

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.

Jake_Raz_4-1723818208176.png

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).

2 ACCEPTED SOLUTIONS

Thanks for your help so far! I had to modify it a bit to get the correct URL to generate (taking only the first four characters of the object and making them all caps since the URL is case-sensitive), and for the hyperlink itself to generate with the correct properties (noreferrer and target=_blank to open in a new tab), but now it seems to be working as expected.

 

function getValueFromCell(tableBody, rowNumber, columnNumber) {
    let row = tableBody.find(`tr:nth-child(${rowNumber})`)
    let cell = row.find(`td:nth-child(${columnNumber})`)
    let cellVal = (widget.type == 'pivot') ? $(cell).attr('val') : cell.html()
    return cellVal
}

function setValueToCell(tableBody, rowNumber, columnNumber, valueToSet) {
    let row = tableBody.find(`tr:nth-child(${rowNumber})`)
    let cell = row.find(`td:nth-child(${columnNumber})`)
    if (widget.type == 'pivot') { 
		cell.find('span').html(valueToSet)
		return
	}
	cell.html(valueToSet)
}

widget.on('ready', () => {
	let widgetElem = (widget.inFullScreenMode) ? $('.widget-body') : $(`[widgetid="${widget.oid}"]`) 
    let tableBody = widgetElem.find('tbody')
	let rowCount = widget.type == 'pivot' ? tableBody.find('tr').length / 2 : tableBody.find('tr').length + 1
	
	for (let rowIndex = 1; rowIndex < rowCount + 1; rowIndex++) {
	  // we can configure the target column and desired string here
	  // we set target column as 2 - meaning we'll overwrite the value of the cell in the second column.
	  let targetColumn = 2
	  
	  // we can then gather some information from adjacent cells
	  // current value gets the value from the currently processed row in column 2
	  let currentValue = getValueFromCell(tableBody, rowIndex, 2)
      // current value gets the value from the currently processed row in column 1	  
	  let valueFromFirstColumn = getValueFromCell(tableBody, rowIndex, 1)
	  // current value gets the value from the currently processed row in column 3
	  let valueFromThirdColumn = getValueFromCell(tableBody, rowIndex, 3)
		
	  // Extract only the first 4 characters and convert them to uppercase
	  let shortenedValueFromThirdColumn = valueFromThirdColumn.substring(0, 4).toUpperCase();

	  // formulate the string to write to the cell, in this case constructing a hyperlink
	  let string = `<a rel="noreferrer" href="https://southwest.teamconnect.com/SOUTHWEST/entityrecord/${shortenedValueFromThirdColumn}_${valueFromFirstColumn}" target="_blank">${currentValue} 'HELLO'</a>`;
	  
	  // set the value to the cell
	  setValueToCell(tableBody, rowIndex, targetColumn, string)
	}
})

 

 

This generates the correct URL for each object type (note how it dynamically swaps between DISP and TRAN)

Jake_Raz_0-1724422233384.png

Jake_Raz_1-1724422261803.png

However, for some reason it wasn't applying to every row in the table. In my original example, the last row was left off:

Jake_Raz_2-1724422318687.png

Another example with more rows in the view, the last three rows are left off:

Jake_Raz_3-1724422658551.png

It's always the same rows, even if I re-sorted:

Jake_Raz_4-1724422717835.png

I'm guessed it was some issue with how the script is looping through the rows. I don't know javascript well enough to try to modify it myself, so I tried asking ChatGPT for help (using a specific "Sisense Helper" persona thing). It suggested a few changes, which I implemented and it seemed to fix the issue. Here's the revised script I'm using:

 

function getValueFromCell(tableBody, rowNumber, columnNumber) {
    let row = tableBody.find(`tr:nth-child(${rowNumber})`)
    let cell = row.find(`td:nth-child(${columnNumber})`)
    let cellVal = (widget.type == 'pivot') ? $(cell).attr('val') : cell.html()
    return cellVal
}

function setValueToCell(tableBody, rowNumber, columnNumber, valueToSet) {
    let row = tableBody.find(`tr:nth-child(${rowNumber})`)
    let cell = row.find(`td:nth-child(${columnNumber})`)
    if (widget.type == 'pivot') { 
		cell.find('span').html(valueToSet)
		return
	}
	cell.html(valueToSet)
}

widget.on('ready', () => {
	let widgetElem = (widget.inFullScreenMode) ? $('.widget-body') : $(`[widgetid="${widget.oid}"]`);
    let tableBody = widgetElem.find('tbody');
	
    // Find all the relevant rows in the pivot table
    let rows = tableBody.find('tr');
    
    // Loop through each row
	for (let rowIndex = 0; rowIndex < rows.length; rowIndex++) {
	  // we can configure the target column and desired string here
	  // we set target column as 2 - meaning we'll overwrite the value of the cell in the second column.
	  let targetColumn = 2;
	  
	  // we can then gather some information from adjacent cells
	  // current value gets the value from the currently processed row in column 2
	  let currentValue = getValueFromCell(tableBody, rowIndex + 1, 2);
      // current value gets the value from the currently processed row in column 1	  
	  let valueFromFirstColumn = getValueFromCell(tableBody, rowIndex + 1, 1);
	  // current value gets the value from the currently processed row in column 3
	  let valueFromThirdColumn = getValueFromCell(tableBody, rowIndex + 1, 3);
	  
	  // Extract only the first 4 characters and convert them to uppercase
	  let shortenedValueFromThirdColumn = valueFromThirdColumn.substring(0, 4).toUpperCase();

	  // formulate the string to write to the cell
	  let string = `<a rel="noreferrer" href="https://southwest.teamconnect.com/SOUTHWEST/entityrecord/${shortenedValueFromThirdColumn}_${valueFromFirstColumn}" target="_blank">${currentValue} 'HELLO'</a>`;
	  
	  // set the value to the cell
	  setValueToCell(tableBody, rowIndex + 1, targetColumn, string);
	}
});

 

This seems to do the trick!

Jake_Raz_5-1724424330616.png

Here's ChatGPT's summary of the changes...

It sounds like the issue is related to the loop logic and how the rows are being counted and processed. Since you're working with a pivot table, it's possible that some hidden rows or subtotals are affecting the loop count, causing certain rows to be skipped.

Let’s revise the logic for determining the row count and iterating through the rows. I’ll provide an improved version that ensures all rows are processed.

Here’s an updated approach: [code I adapted, see above - JR]

Key Changes:

  1. Counting Rows Properly: The code now retrieves all rows using let rows = tableBody.find('tr');. This ensures that all rows in the table are included in the loop.
  2. Adjusted Loop Indexing: We’re now looping directly through rows.length instead of calculating rowCount based on row splitting or assumptions. This approach handles cases where there might be hidden rows or subtotals.
  3. Row Index Adjustment: The loop uses rowIndex + 1 when fetching the cell values, as the getValueFromCell function may be 1-based while the loop is 0-based.

Why This Works:

  • By targeting all rows and using direct row indexing, this approach minimizes the chances of skipped rows due to hidden elements or miscalculated row counts.

Let me know if it appears to be making any mistakes in its logic...like I said, this appears to work for now, but I'm not sure if this approach might cause problems for us later in other ways.

View solution in original post

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);
 });

 

 

View solution in original post

15 REPLIES 15

rapidbisupport
11 - Data Pipeline
11 - Data Pipeline

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

[email protected]

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

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
Community Team Member
Community Team Member

Hi @Jake_Raz,

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

David Raynor (DRay)

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.

Jake_Raz_0-1724086526553.png

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

Jake_Raz_1-1724086563062.png

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
Community Team Member
Community Team Member

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.

David Raynor (DRay)

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
Community Team Member
Community Team Member

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.

David Raynor (DRay)

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

[email protected]

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

Ah, okay, thanks for the clarification, good to know. I'll use the link you posted in a prior comment to schedule a call. 

Hi @Jake_Raz ,

Seems that your instance is leveraging some custom method (prism.createDrillDown) to produce urls based on certain columns and set them to target columns on pivot and table widgets. Furthermore, looks like some of this string is hardcoded into the function.

Based on the code you provided, I have the following widget script that is a bit more complex, but should allow you to generate the url you need:

function getValueFromCell(tableBody, rowNumber, columnNumber) {
    let row = tableBody.find(`tr:nth-child(${rowNumber})`)
    let cell = row.find(`td:nth-child(${columnNumber})`)
    let cellVal = (widget.type == 'pivot') ? $(cell).attr('val') : cell.html()
    return cellVal
}

function setValueToCell(tableBody, rowNumber, columnNumber, valueToSet) {
    let row = tableBody.find(`tr:nth-child(${rowNumber})`)
    let cell = row.find(`td:nth-child(${columnNumber})`)
    if (widget.type == 'pivot') { 
		cell.find('span').html(valueToSet)
		return
	}
	cell.html(valueToSet)
}

widget.on('domready', () => {
	let widgetElem = (widget.inFullScreenMode) ? $('.widget-body') : $(`[widgetid="${widget.oid}"]`) 
    let tableBody = widgetElem.find('tbody')
	let rowCount = widget.type == 'pivot' ? tableBody.find('tr').length / 2 : tableBody.find('tr').length + 1
	
	for (let rowIndex = 1; rowIndex < rowCount + 1; rowIndex++) {
	  // we can configure the target column and desired string here
	  // we set target column as 2 - meaning we'll overwrite the value of the cell in the second column.
	  let targetColumn = 2
	  
	  // we can then gather some information from adjacent cells
	  // current value gets the value from the currently processed row in column 2
	  let currentValue = getValueFromCell(tableBody, rowIndex, 2)
      // current value gets the value from the currently processed row in column 1	  
	  let valueFromFirstColumn = getValueFromCell(tableBody, rowIndex, 1)
	  // current value gets the value from the currently processed row in column 3
	  let valueFromThirdColumn = getValueFromCell(tableBody, rowIndex, 3)
	  
	  // formulate the string to write to the cell
	  let string = `<a href="https://${valueFromFirstColumn}_${valueFromThirdColumn}">${currentValue} 'HELLO'</a>`
	  
	  // set the value to the cell
	  setValueToCell(tableBody, rowIndex, targetColumn, string)
	}
})

Note that this should work only with the Windows Sisense, and uses a heap of jQuery to:

  • find the table, row and cells in the rows,
  • gather the value of a cell given a row and column index,
  • change the html in a cell given a row, column and some string to change it to.

This one uses the 'domready' event, but in the case of pivot you can also use the 'ready' event. 'domready' is only required if you're using a table.

It'll require some modification from your end - getting the values from the columns you need, and using those values to generate the string you'll then eventually pass to the setValueToCell method.

I've wrapped this up in a for loop that should iterate for rows in the table (so that you can modify the value of the cell in each row).

Feel free to reach out again if you need further assistance with implementing this, as it's more complex than the script you shared to me - i'd be happy to jump on another early call.

Lastly, some resources:

https://sisense.dev/guides/customJs/jsApiRef/widgetClass/

Let me know how you go?

Thanks,

Daniel

RAPID BI

[email protected]

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

 

Thanks for your help so far! I had to modify it a bit to get the correct URL to generate (taking only the first four characters of the object and making them all caps since the URL is case-sensitive), and for the hyperlink itself to generate with the correct properties (noreferrer and target=_blank to open in a new tab), but now it seems to be working as expected.

 

function getValueFromCell(tableBody, rowNumber, columnNumber) {
    let row = tableBody.find(`tr:nth-child(${rowNumber})`)
    let cell = row.find(`td:nth-child(${columnNumber})`)
    let cellVal = (widget.type == 'pivot') ? $(cell).attr('val') : cell.html()
    return cellVal
}

function setValueToCell(tableBody, rowNumber, columnNumber, valueToSet) {
    let row = tableBody.find(`tr:nth-child(${rowNumber})`)
    let cell = row.find(`td:nth-child(${columnNumber})`)
    if (widget.type == 'pivot') { 
		cell.find('span').html(valueToSet)
		return
	}
	cell.html(valueToSet)
}

widget.on('ready', () => {
	let widgetElem = (widget.inFullScreenMode) ? $('.widget-body') : $(`[widgetid="${widget.oid}"]`) 
    let tableBody = widgetElem.find('tbody')
	let rowCount = widget.type == 'pivot' ? tableBody.find('tr').length / 2 : tableBody.find('tr').length + 1
	
	for (let rowIndex = 1; rowIndex < rowCount + 1; rowIndex++) {
	  // we can configure the target column and desired string here
	  // we set target column as 2 - meaning we'll overwrite the value of the cell in the second column.
	  let targetColumn = 2
	  
	  // we can then gather some information from adjacent cells
	  // current value gets the value from the currently processed row in column 2
	  let currentValue = getValueFromCell(tableBody, rowIndex, 2)
      // current value gets the value from the currently processed row in column 1	  
	  let valueFromFirstColumn = getValueFromCell(tableBody, rowIndex, 1)
	  // current value gets the value from the currently processed row in column 3
	  let valueFromThirdColumn = getValueFromCell(tableBody, rowIndex, 3)
		
	  // Extract only the first 4 characters and convert them to uppercase
	  let shortenedValueFromThirdColumn = valueFromThirdColumn.substring(0, 4).toUpperCase();

	  // formulate the string to write to the cell, in this case constructing a hyperlink
	  let string = `<a rel="noreferrer" href="https://southwest.teamconnect.com/SOUTHWEST/entityrecord/${shortenedValueFromThirdColumn}_${valueFromFirstColumn}" target="_blank">${currentValue} 'HELLO'</a>`;
	  
	  // set the value to the cell
	  setValueToCell(tableBody, rowIndex, targetColumn, string)
	}
})

 

 

This generates the correct URL for each object type (note how it dynamically swaps between DISP and TRAN)

Jake_Raz_0-1724422233384.png

Jake_Raz_1-1724422261803.png

However, for some reason it wasn't applying to every row in the table. In my original example, the last row was left off:

Jake_Raz_2-1724422318687.png

Another example with more rows in the view, the last three rows are left off:

Jake_Raz_3-1724422658551.png

It's always the same rows, even if I re-sorted:

Jake_Raz_4-1724422717835.png

I'm guessed it was some issue with how the script is looping through the rows. I don't know javascript well enough to try to modify it myself, so I tried asking ChatGPT for help (using a specific "Sisense Helper" persona thing). It suggested a few changes, which I implemented and it seemed to fix the issue. Here's the revised script I'm using:

 

function getValueFromCell(tableBody, rowNumber, columnNumber) {
    let row = tableBody.find(`tr:nth-child(${rowNumber})`)
    let cell = row.find(`td:nth-child(${columnNumber})`)
    let cellVal = (widget.type == 'pivot') ? $(cell).attr('val') : cell.html()
    return cellVal
}

function setValueToCell(tableBody, rowNumber, columnNumber, valueToSet) {
    let row = tableBody.find(`tr:nth-child(${rowNumber})`)
    let cell = row.find(`td:nth-child(${columnNumber})`)
    if (widget.type == 'pivot') { 
		cell.find('span').html(valueToSet)
		return
	}
	cell.html(valueToSet)
}

widget.on('ready', () => {
	let widgetElem = (widget.inFullScreenMode) ? $('.widget-body') : $(`[widgetid="${widget.oid}"]`);
    let tableBody = widgetElem.find('tbody');
	
    // Find all the relevant rows in the pivot table
    let rows = tableBody.find('tr');
    
    // Loop through each row
	for (let rowIndex = 0; rowIndex < rows.length; rowIndex++) {
	  // we can configure the target column and desired string here
	  // we set target column as 2 - meaning we'll overwrite the value of the cell in the second column.
	  let targetColumn = 2;
	  
	  // we can then gather some information from adjacent cells
	  // current value gets the value from the currently processed row in column 2
	  let currentValue = getValueFromCell(tableBody, rowIndex + 1, 2);
      // current value gets the value from the currently processed row in column 1	  
	  let valueFromFirstColumn = getValueFromCell(tableBody, rowIndex + 1, 1);
	  // current value gets the value from the currently processed row in column 3
	  let valueFromThirdColumn = getValueFromCell(tableBody, rowIndex + 1, 3);
	  
	  // Extract only the first 4 characters and convert them to uppercase
	  let shortenedValueFromThirdColumn = valueFromThirdColumn.substring(0, 4).toUpperCase();

	  // formulate the string to write to the cell
	  let string = `<a rel="noreferrer" href="https://southwest.teamconnect.com/SOUTHWEST/entityrecord/${shortenedValueFromThirdColumn}_${valueFromFirstColumn}" target="_blank">${currentValue} 'HELLO'</a>`;
	  
	  // set the value to the cell
	  setValueToCell(tableBody, rowIndex + 1, targetColumn, string);
	}
});

 

This seems to do the trick!

Jake_Raz_5-1724424330616.png

Here's ChatGPT's summary of the changes...

It sounds like the issue is related to the loop logic and how the rows are being counted and processed. Since you're working with a pivot table, it's possible that some hidden rows or subtotals are affecting the loop count, causing certain rows to be skipped.

Let’s revise the logic for determining the row count and iterating through the rows. I’ll provide an improved version that ensures all rows are processed.

Here’s an updated approach: [code I adapted, see above - JR]

Key Changes:

  1. Counting Rows Properly: The code now retrieves all rows using let rows = tableBody.find('tr');. This ensures that all rows in the table are included in the loop.
  2. Adjusted Loop Indexing: We’re now looping directly through rows.length instead of calculating rowCount based on row splitting or assumptions. This approach handles cases where there might be hidden rows or subtotals.
  3. Row Index Adjustment: The loop uses rowIndex + 1 when fetching the cell values, as the getValueFromCell function may be 1-based while the loop is 0-based.

Why This Works:

  • By targeting all rows and using direct row indexing, this approach minimizes the chances of skipped rows due to hidden elements or miscalculated row counts.

Let me know if it appears to be making any mistakes in its logic...like I said, this appears to work for now, but I'm not sure if this approach might cause problems for us later in other ways.

Hey @Jake_Raz ,

Looks good!

There was some weirdness around the count of rows. It seemed different from pivot to table controls, so glad you were able to get to the bottom of it.

let rows = tableBody.find('tr');

I found for some weird reason that pivots were doubling this count and tables were not, so I had some logic around this to catch it.

I don't think this will cause issues because you will always be searching for the 'rows' in the 'tableBody' which relate to the specific widget - it shouldn't spill over or cause any issues across any other widgets.

Nice work!

Thanks,

Daniel

RAPID BI

[email protected]

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

DRay
Community Team Member
Community Team Member

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

David Raynor (DRay)

Jake_Raz
10 - ETL
10 - ETL

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);

 });

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);
 });