Compare Column Data in Tables

[dcsearle]

Hi,

I am experimenting with how I might be able to compare column values of two tables.

My idea is that I would like to highlight cells where a value in table2 doesn't match what is held in table1.

Both tables have matching ID columns which is what I would use to "join" on, but the name, email and sales figures in table2 might differ from table1 and I'd like to highlight those.

Screenshot below hopefully shows what I mean.

I know I can achieve highlighting using the "background" property of each column, and this is where I'm thinking I could write the code which does the comparison - where the column in table2 compares itself to the column with the same "id" in table1.

Does anyone have any idea how to achieve this? Or suggest an alternative way of achieving the same?

Thanks in advance!
David

[ScottR]

@dcsearle
table 1 is on the left and table4 is on the right

I tried the following:
In a js query

for (i=0;i<table4.data.length;i++){
  if (table4.data[i].Amount != table1.data[i].Amount)
    table4.data[i].Amount.backgroundColor = '#FFFFE0'
  console.log(table4.data[i].Amount + ' table 4');
  console.log(table1.data[i].Amount + ' table 1');
}

which gives me:

So, it picks up the difference in the Amount in the two tables, but it is NOT setting the color... so I am still working on what property needs to be used.....backgroundColor is not working....

[dcsearle]

Thanks Scott, that seems like a good starting point!
Excuse my ignorance here as I've not had to insert custom JS into the IDE before, could you advise on how that is done?

Cheers,
David

[ScottR]

Bottom left panel

[dcsearle]

Hi @ScottR , had any more time to look at this idea or reached a dead-end?

Anyone else got any suggestions?

Cheers,
Dave

[ScottR]

Yes I tried to change the font itself to all CAPS and it works in the console but does NOT render in the table

here's the code I ran:

for (i=0;i<table4.data.length;i++){
  if (table4.data[i].Category != table1.data[i].Category){
    console.log(table4.data[i].Category.toUpperCase()  + ' table 4' + ' Row ' + i);
    } else {
    console.log(table1.data[i].Category + ' table 1' +  ' Row ' + i + ' same value as table 4' + ' Row ' + i); 
    }
}

[Kabirdas]

Hey all!

At the moment you can only set properties on components if they have specific methods allowing you to do so (check out this post for more detail).

Since you're trying to dynamically set the background you can use an inline transformer in the "Background" field to determine the color. How you actually write the JS can vary but here is one example that uses the self and i variables which are dynamically interpolated based on the cell - self represents the value of the cell and i represents the row index:

This checks to see if the value in the cell is equal to the value of the name column in the corresponding row of table1.

**Note** the exact syntax can vary here a bit depending on the structure of your table data. This example assumes your data is an array of row objects, however, if your data is an object of column arrays, as with SQL queries, you might want something like {{ self !== table1.data.name[i] ? '#ffaaaa' : 'white' }} or {{ self !== formatDataAsArray(table1.data)[i].name ? "#ffaaaa" : "white" }} instead.

Let us know if that works!

[ScottR]

Thanks @Kabirdas

• can confirm that works... can't believe I didn't think of it....
  @dcsearle - this looks good for you to use!

[dcsearle]

@Kabirdas , @ScottR - this looks like a winner... sorry to be a pain but i'm struggling with the transformer element of this so can you advise on what you did there? I guess I need to set something up which returns 'i' but not quite got my head around that!

[dcsearle]

Ignore me! Syntax error! Sorted! No need for transformer and this work perfectly! Thanks you guys

{{ self !== table1.data[i].name ? '#ffaaaa' : 'white' }}