Custom column with query

[ignacio]

Hi Community!

I have a special use-case and i dont know how to do it (if is possible).

This table is loaded by a CSV file with 2 columns (id_feed_client, url_competitor), the other columns are custom.

I need to get the host in "url_competitor" value for each value

var host = new URL (<url_competitor_value>).host

and with this value, execute a query that uses "host" in a where clause to get the id

select id from table where domain like '%host%';

and finally put this id value in each customigna cell, for example:

Is this possible?
Thanks!

[jocen]

Hi @ignacio, not sure exactly what you are trying to do here. By host, do you mean the website's base url and the id being the PK of that website in your table?

Regardless, I'm pretty sure you could do a JS Query to transform the data from the csv and attach the id_product and customigna in the data object of the JS Query before putting it on the table. With this complexity, I don't think going straight to custom column is a good idea.

[everett_smith]

Hey @ignacio, This is possible! Assuming that your CSV data is formatted as an array of objects, you can write a script in a JS Query to iterate through your data and trigger a separate query with the host value for each row and append that value to your dataset. You can pass the host value to the query you trigger with additionalScope. Here are a couple of examples of ways you could trigger these queries for each row in your data.

https://docs.retool.com/docs/scripting-retool#triggering-a-query-for-each-item-in-an-array

https://docs.retool.com/docs/scripting-retool#promises-and-async-queries

I hope this helps!