Lookup from table to another query

Queries and Resources
,
#1

Hi everyone. I'm a bit of a javascript noob so please help me out:

I have two queries from two different sources. let's say an api call from a CRM system, and a local database.
I've formatted my output from Query1 into a table. Query1 / table includes a column called 'ProductCode').

Query2 comes from a local db and has product details, including product code, and let's say 'Product Name'. The query has been run and the output available through the explorer in the left nav.

I'm an old SQL guy so what I'm trying to do is

SELECT query2."Product Name" FROM table1 LEFT JOIN query2 on table1.ProductCode = query2.ProductCode

I want to bring in the additional field(s) into the table as a custom field.

Struggling to get this working. I have tried putting this in query transformers, I've tried using javascript .find and .filter methods, in query transformers, normal transforms, in the calculation for manual fields, but can't find the right syntax etc.

This must be a common use case. Is there a best practice / standard pattern for this?

Thanks v m!

Dominic

#2

Ok I’ve answered my own question! Typically I’ve been working on this for days and shortly after giving up hope, and posting on here, I worked it out.

Very simple, obviously, just all about curly brackets etc.

Firstly: I included a transformer in my SQL query to apply formatDataAsArray for the whole query.

return formatDataAsArray(data)

Then, I added a custom column, with the following calculation:

{{Product_details.data.find(x => x['Product Code'] === currentRow['Product Code']).Product_id}}

This example returns the ‘Product_id’ field from the Product_details query, clearly if that item had a space in the name it would need a different syntax.

Anyway that solves a major problem for me. One of my main issues is that, if there is no match on the current row then the calculation shows an error / red so I had to make sure I had valid rows to test the syntax.

Hope that’s helpful to someone!

Dominic

3 Likes