Creating a calculated field to search and highlight text like a text dictionary

[Jodie]

Hi

So chatGPT suggested using a calculated field - unfortunately the option doesn't work in Caspio results page where its required. This might be too complicated for a calculated field and maybe requires JS but I'm struggling to get my head around the key concepts needed so that I can find how to create the solution. 

What I'm trying to do is similar to how text dictionary works.  My users have already entered text into my tables. In this datapage, I want to compare the text they've provided against a table with problematic words. If their feedback contains any of the words, I want the words to be highlighted, the alternative phrasing or action to be provided, and for the user to then edit and submit their final feedback.

The concept sounded easy in my head - but I can't figure out where to start.

Any help would be appreciated

 

[Jodie]

Ok so I've had a crack at starting this off and have a very basic version using JS and arrays rather than a look up. It's very basic and only returns matched items into a div at the moment but its a start that I can work with. I'll need to learn more about arrays or find a way to search against my table to look up and return with the matched field the alternative phrasing or suggestion. I'm guessing the highlighting/formatting option would return the position of the term as well and length so the word/phrase can be highlighted but that's a little further advanced.

This is the html block I've added that will return all the found terms: <div id="matchedWordsContainer"></div> 

And JS at bottom is this:

<script>

var gendered = ["female", "woman", "girl", "lady", "need", "to", "add", "all", "my", "fields!!!!!"];

var userInput = document.getElementById("EditRecordskillpassport_mgevidence5").value;

var isMatch = false;
gendered.forEach(function(gendered) {
  if (userInput.toLowerCase().includes(gendered.toLowerCase())) {
    isMatch = true;
  }
});

var matchedWords = [];
gendered.forEach(function(gendered) {
  if (userInput.toLowerCase().includes(gendered.toLowerCase())) {
    matchedWords.push(gendered);
  }
});

var matchedWordsContainer = document.getElementById("matchedWordsContainer");
if (matchedWords.length > 0) {
  matchedWordsContainer.textContent = "Matched words: " + matchedWords.join(", ");
} else {
  matchedWordsContainer.textContent = "No match found.";
}

</script>

 

[Volomeister]

Hello @Jodie

Do you want not only the existing text to be checked against the list of keywords but also checking the text that is being inputted on the fly?

The workaround can be summarized as follows:

1. You create a virtual field as drop-down that will use a source look-up table with keywords you wish to check against.
2. JavaScript will be required to loop through all report records, and check if any keywords from drop-down match any words in the text of the record. If there is a match, JavaScript will wrap such matches into <span> tag that will have a certain background color to highlight the text.
3. If you want to show some tool tip when hovering over the highlighted text, this also can be done with JavaScript or CSS
 

[Volomeister]

Hi @Jodie

I was playing around with this idea and created a draft of what it might look like:

https://c7eku786.caspio.com/dp/7f80b0005c7ad71ac0bd4946a9d0
 

Text in the second column is getting checked against keywords, and matches are highlighted.

The keyword lookup table screenshot attached

Is it something you are looking to create?

[Jodie]

Hi @Volomeister

That looks amazing and is what I'm trying to do (love the choice of red highlighting!). The even better would be an explanation or alternative like tooltip etc. 

[Volomeister]

Hi @Jodie

Here is the JS code snippet you can add to the footer on "Configure Results Page Fields" on the tabular report

<script>
const dataFieldColumnNumberArr = [2]
const keywordDropDownName = 'Virtual1'

const addKeywordsToArr = () => {
document.KeyWordsArr = []
document.querySelectorAll(`#cbParam${keywordDropDownName} option`).forEach(option=>{
document.KeyWordsArr.push(option.getAttribute('value').trim().toLowerCase())
})
}
const highlightCellKeywords = (HTMLTargetCell) => {
let initHTML = HTMLTargetCell.outerHTML
document.KeyWordsArr.forEach( keyword => {
let regex = new RegExp(keyword, "ig")
initHTML = initHTML.replace(regex, `<span class="target-keyword" data="Change this word">${keyword}</span>`)
})
HTMLTargetCell.outerHTML = initHTML
}

const highlightAllKeywords = () => {
for (let i=0;i<dataFieldColumnNumberArr.length;i++) {
document.querySelectorAll(`.cbResultSetTable tr td:nth-child(${dataFieldColumnNumberArr[i]})`).forEach(cell=>{
highlightCellKeywords(cell)
})
}
}
const hideVirtual = () => {
document.querySelector(`#cbParam${keywordDropDownName}`).style.display = 'none'
}
const main = () => {
hideVirtual ()
if(document.KeyWordsArr == undefined) {
addKeywordsToArr()
highlightAllKeywords()
}
}

document.addEventListener('DataPageReady', main)

</script>

Where dataFieldColumnNumberArr variable contains the comma-separated column number(s) of the data columns on the tabular report page where keywords must be highlighted. 
In the example above, keywords will be highlighted in the second column.
If you need to highlight keywords, say in the 2nd and 3rd column, the code snippet will look as follows:
const dataFieldColumnNumberArr = [2, 3]

keywordDropDownName variable must contain the name of the virtual field that is drop-down based on keywords lookup table.

The following CSS code snippet should be added to the header of the search form (if you have a search form in your tabular report) or to the header of the "Configure Results Page Fields" screen if you do not have a search form.
 

<style>
.target-keyword {
background-color: red;
}
.target-keyword:hover {
cursor: pointer;
}
.target-keyword:hover::before {
 content: attr(data);
 display: block;
 position: absolute;
 top: 100%;
 left: -7px;
 right: -7px;
 padding: 15px;
 background-color: #000;
}
</style>