How to Group Data in Table From API Responses
Application Type
Traditional Web

Hi All,

I need some idea around this. I have a received a lot of records from API and I have used a structure to map that data and display in a table. Now I have to group that data in the table based on one column Value i.e. if the column value is 'ABC' then all the rows containing the 'ABC' value will come under each other and they will be coloured GREY. Please note the rows will not be merged together , it's just they will come under each other.


Please help on this how to group this data based on column value.

mvp_badge
MVP

Hi Arpit,

I am not 100% sure on what you are trying to achieve.
Are you trying to sort the list you got based on column value?
If so have a look at the listSort action and sort by that column (Would be better to this on the API side, but since you are asking, I assumed this wasn't an option)

Then for the colouring of the rows you can just use an expression in the extended properties to set a class with the color

Hi Eric,

There is a field in the API responses based on the value of that field we have to group the records and change the row colour. If you see the snapshot, it is the last field of the table where you can see identical values are present for some the rows and this is just only one instance. So how do I group that and change the UI as the value to be assigned in extended properties will be dynamic.

group-by2.PNG

mvp_badge
MVP

Hi Arpit,

I don't think I am following you yet.

So if I understand correctly you want to group these together?

By grouping together, do you mean put them in the same row?

Or just have them below each other? (as they currently are on the image)
In the latter case please refer to my reply above using the listSort action

Or do you have a table with records. And then the API gives you a value and you need to "group" by that value?

Hi Eric, 

I want like this in the image i.e. under each other. What I am struggling at to provide any value in the extended properties. As you have shown, column_name="abc", I can't put any such value over there as there could be multiple records with different unique values. Like for "abc", there could be 3 records and for "def" there could be another 3 records.

I hope I am able to make you understand.

group-by.PNG

mvp_badge
MVP

Hi Arpit,

The group by can be done using the listsort action I mentioned.

As for the colouring
I assume you only want 1 set of rows grey? the column_name="abc" can be dynamic. "abc" can be a variable.
If you create a variable "highlightValue" on your page and then assign it the value that you want to highlight you should be golden.

Hi Eric, 

This isn't solving my issue actually. If you can provide any sample .oml file regarding my use case , will be helpful.

mvp_badge
MVP

Hi Arpit,

I am uncertain how it does not solve your use case. Maybe I am misunderstanding your use-case?
as what my solution does is:

the ListSort action sorts the list by the column where you want to "group" certain values. Meaning these values will be listed beneath each other.

And this assignment:

sets a class with which you can turn a row grey (you might need to write the CSS to do so, or change the class to background-neutral-2  or any of those).


You will need to Assign the value of the record you want to highligh to HighLightValue.

Could you please explain what part of your use case I missunderstood or did not solve?

Hi Eric,

Sorry for late reply. I am still stuck on this. Maybe I am not able to convey my use case properly.

So the thing is , I have used List Sort to sort the list records and I have to show the rows in grey color where one of my columns will be having identical values for those rows. The value can be different , so if I loop through the sorted list I need to compare the values of each record with other records in the list and then can show the changes in the UI. 

This is the Sorted List Records in a Table and the table is in a web block. The sorting has been done based on Agency seed and I have to color the rows where the agency seeds are same. The issue is I can't set any value like AccountTable.Current.AgencySeed="15317690" to identify the rows as the value will be different for different account number. I have to over ride the CSS classes in the page which contains this web block. I can understand the logic I need to apply but not sure how to implement the same. 


mvp_badge
MVP

Hi Arpit.

Another way to do would be to use javascript:

// Get all rows
var a = document.querySelectorAll('.TableRecords tr');

// Loop through all rows
[...a].forEach(function(e){
    
    // Get the data-check value of the current row
    b = e.dataset.check
    // Find all rows with the same data-check value
    c = document.querySelectorAll('tr[data-check='+b+']')
    // If more than 1 row (it self) is found change the background color
    if ( c.length > 1) {
        e.classList.add('background-neutral-3')
    }
});	

To make this work add a data-check attribute to the extended properties and set the value of data-check to the column you want to compare

you might need to add the following class to your css to make sure the whole row gets colored:

.TableRecords tr.background-neutral-3 > td {
    background-color: inherit;
}
	


Make sure the orange classes match


Hi Eric,

This solution is indeed helpful. I can see reaching closer to the solution. The last thing is I can see the row colors getting changed in service studio but not in the browser.

I have the table in web block and I have override the class in the screen level , used !important as well (although that's not suggested as part of best practices),unfortunately that's not reflecting as of now. Do you think I am missing something here. 

mvp_badge
MVP

Hi Arpit,


!important should not be needed.
Did you add the extra css code from my last reply?
In your browser, could you right click on a cell, click inspect and showme a screenshot like this?

Hi Eric,

From your snapshot, what I understood the "data-check" property is not getting validated for each of the row hence the class is not getting added.

I have added the JS snippet at the block level and assigned the data-check in the extended property as well. I added the CSS code at the screen level. I have added all the snapshots along with the view from browser as well. 


mvp_badge
MVP

Hi Arpit,

You are adding the data-check on the table
While it should be on the row as my example. Adding it to the row should fix things.

Also, !important really should not be needed here

Hi Eric,

Added it to the row level , not sure why the class is not getting to the row. Attached snapshots of the page level css and inspect tool snapshot.


mvp_badge
MVP

Hi Arpit,

Could you tell me what the DBACCT parameter is in yout webblock?


Then let's tweak the javascript a bit to make sure it runs after the dom is ready

document.addEventListener("DOMContentLoaded", function() {

// Get all rows
var a = document.querySelectorAll('.TableRecords tr');

// Loop through all rows
[...a].forEach(function(e){
    
    // Get the data-check value of the current row
    b = e.dataset.check
    // Find all rows with the same data-check value
    c = document.querySelectorAll('tr[data-check="'+b+'"]')
    // If more than 1 row (it self) is found change the background color
    if ( c.length > 1) {
        e.classList.add('background-neutral-3')
    }
});    
});

Hi Eric,

The DBACCT parameter is account number which we are receiving from a Legacy System through javascript, for that we already have a separate onload function. As you can see in the image , the table is sorted based on the INS score . The business logic is for a certain account number , the rows having same agency seeds value should be below each other in the table. The same is happening here for the first three rows , you can see a * in the account number of the first row means there are other rows with the same agency seeds in the table. So we have to show the other two rows (in this case the 2nd and 3rd row) in Grey color.

Ideally if there are n number of rows with same agency seeds (these all data are coming from API and bind to a local variable with type of list structure) , the 1st row account number will be having a * with it and the rest n-1 rows will be in grey color. So I have implemented the other things , the issue is with marking the n-1 rows in grey.

I hope I can make you understand my use case. Here we don't need to deal with the DBACCT parameter as of now.


mvp_badge
MVP

alright thank you.

Then the new Javascript should work

Hi Eric,

Added the new JS at the web block still no luck.




mvp_badge
MVP

I'm currently not behind my pc anymore. I'll send an OML tomorrow :) 

mvp_badge
MVP

As promised, the OML

Let me know if it works

PG_TraditionalWeb.oml

Hi Eric, 

Thanks for sharing the OML file still it didn't resolve my issue.


mvp_badge
MVP

Hi Arpit,

When running my OML. does it highlight the rows with Bob in the name?

Hi Eric,

Yes it is working . I am unable to understand why it's not happening in my case.

mvp_badge
MVP

Then carefully compare the differences (or share your oml)

Hi Eric,

Getting this Error in the Console.

mvp_badge
MVP

Perfect Arpit, thanks!
that is an easy fix :)
please change this line in the javascript

c = document.querySelectorAll('tr[data-check='+b+']')

to this

c = document.querySelectorAll('tr[data-check="'+b+'"]')

Hi Eric,

That's working correctly. Although I would like to know few things, currently this is highlighting all the rows and I have to exclude the first row from highlighting . What tweak we need to do in the JS ?

Another thing I have to do the same highlighting thing in another place using a List widget where I won't be using the web block. Also I have show a different color of separator widget under the highlighted rows. Can you please give me some idea how I can achieve that ? 

mvp_badge
MVP

Hi Arpit,
I changed the code to:

1. Not highlight the first record of each group
2. Be usable on any type of list/table. Just give add

HighlightDupplicants 

to the classes of the list/table element. For a list make sure to wrap the record in an element and give it the data-check attribute.

document.addEventListener("DOMContentLoaded", function() {
    // In case of multiple lists get them all
    var t = document.querySelectorAll('.HighlightDupplicants');
    // loop through lists
    [...t].forEach(function(el){
        var d = false, bold = ""
        // Get all datacheck rows
        var a = el.querySelectorAll('[data-check]');

        // Loop through all rows
        [...a].forEach(function(e){
            // Get the data-check value of the current row
            b = e.dataset.check
            // Find all rows with the same data-check value
            c = el.querySelectorAll('[data-check="'+b+'"]')
            // If more than 1 row (it self) is found change the background color
            b == bold? d = d: d = false;
            bold = b; 
            if ( c.length > 1) {
                d? e.classList.add('background-neutral-3'): d = true;
            } else {
                d = false
            }
        });  
    });
});    

Also use this new css instead of the old one

.HighlightDupplicants .highlighted,
.HighlightDupplicants .highlighted > td {
    /* set background color of highlighed element */
    background-color: var(--color-neutral-3);
    /* set underline color */
    border-bottom: 1px solid red;
}

Hi Eric,

This CSS class is not reflecting for List/Table for IE browser. Do we have to change anything for IE ? Kindly help.

mvp_badge
MVP

Hi Arpit, 

Did you add the css part to your css? 

And is IE a requirement? As Microsoft has dropped support for IE a while ago

Hi Eric,

Yes I have added the CSS and it's working in Chrome but the legacy application has IE browser in-built to it so we need the application IE compatible and Client is asking for that.

mvp_badge
MVP

Hi Arpit,

I hope you explained to your client that IE is no longer supported.

but as for the IE fix just use this javascript and you are golden:

document.addEventListener("DOMContentLoaded", function() {
    // In case of multiple lists get them all
    var t = document.querySelectorAll('.HighlightDupplicants');
    // loop through lists
    for (i = 0; i < t.length; i++){
        el = t[i]
    //[...t].forEach(function(el){
        var d = false, bold = ""
        // Get all datacheck rows
        var a = el.querySelectorAll('[data-check]');

        // Loop through all rows
        for (ii = 0; ii < a.length; ii++) {
            e = a[ii]
        //[...a].forEach(function(e){
            // Get the data-check value of the current row
            b = e.dataset.check
            // Find all rows with the same data-check value
            c = el.querySelectorAll('[data-check="'+b+'"]')
            // If more than 1 row (it self) is found change the background color
            b == bold? d = d: d = false;
            bold = b; 
            if ( c.length > 1) {
                d? e.classList.add('highlighted'): d = true;
            } else {
                d = false
            }
        //});  
        }
    //});
    }
});    

Hello Arpit,


I think I understand your problem. Your table is inside Web Block and you are trying to apply CSS in web block. First I would like to tell you that, web block has least priority while rendering the page so whatever you CSS you will apply there will get overwritten later(rendering seq: Web Block -> Module Theme ->Screen CSS). The easiest solutions to overcome this issue will be creating a new CSS class and calling them from property 'Style Classes' and also use '!important' with your style to not get overwritten later. If this will also won't work, just define that CSS in screen's CSS editor.


Hope it helps!

Sanjay

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.