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