Comparing Data in Tables with data from Queries


I've been stumped on this for a while and could do with some help...

I have a query which retrieves data via SQL and a table which is populated by loading in some data from an Excel spreadsheet. It's a very simple table, with one column.

What I'd like to do is compare the data thats loaded into the table with that which is coming through on the query. I'm transforming the data from the query into an array so it is more workable.

I have some javascript to do the JSON comparison but the issue i'm having is the data format in the table is not matching the data thats coming from the query. If I load the two into the JSON viewer, it shows that I can see double quotes around the values from the query but not on the ones from the table... which means my comparison script is completely failing :frowning:


Can anyone suggest a way of quickly transforming this data so either the quotes are removed from one side or added to another, just so my comparison will run?


quick and dirty you can coerce it to a text field by adding '' or you can coerce to an integer with parseInt ie => parseInt(x.MFCode)) => x.MFCode+'')

Ideally you'd do this before loading them into the tables - comparing arrays of objects can be tricky, there's some built in functions in the Lodash library that may be useful

1 Like

Thanks @dcartlidge - I did something similar in the end based on a stackoverflow post I found, quick n dirty was just fine... yours also worked well :slight_smile:


let filtered = uploadedMFCodes.filter(({MFCode}) => !keys.includes(""+MFCode+""))

For context...

let uploadedMFCodes = [{MFCode : 5016109},{MFCode : 456}];

let existingMFCodes = tfmDataToArray.value;

// Create an array of unique identifier for the objects in existingMFCodes
let keys = Object.keys(existingMFCodes.reduce((a, {MFCode}) => Object.assign(a, {[MFCode] : undefined})));
let keys2 = Object.values(uploadedMFCodes);

// Filter in those elements which are missing from the above array
let filtered = uploadedMFCodes.filter(({MFCode}) => !keys.includes(""+MFCode+""))