Script to dynamically generate URLs for hyperlinks in Pivot Table rows

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)

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

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

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

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!

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.

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