Using Variable `i` from Custom List View in New SQL Query

Queries and Resources
, , ,
1

I am using a Custom List View, lv1, in Retool to populate a series of rows returned by a query, q1.

I have a nested Custom List view, lv2, within lv1, which I want to populate using a query q2 that uses a variable returned by the q1.

Example:

-- q1 (SQL): populates `lv1`
data = SELECT * FROM table_1;
return formatDataAsArray(data); // using a JS transformer

// lv1: sample text field
titleText = q1.data[i].title; // displays title correctly in `lv1` based on row `i`

-- q2_sql: populates `lv2` based on some variable `my_var` to be passed in
data = SELECT * FROM table_2 entry WHERE entry.var = {{ my_var }};

// q2_js: triggers `q2_sql` based on variable `i` from `q1` in `lv1`
let my_var = {{ q1.data[i].my_var }};

let data = await q2_sql.trigger({
  additionalScope: {
    my_var: my_var,
  },
});

return data;

However, based on this example, if I try to use q2_js.data.length to dynamically set the number of rows in lv2, then it says that the result from q2_js is undefined.

If I change the value of my_var from {{ q1.data[i].my_var }} to some hardcoded value 'xyz', then q2_js, q2_sql, and subsequently lv2, work just fine. So I suspect that for some reason I'm not able to propagate the variable i from q1 to q2 correctly.

I tried using the variable i in q2_sql directly without having a q2_js to trigger it at all:

-- q2 (SQL): populates `lv2`
SELECT * FROM table_2 entry WHERE entry.my_var = {{q1.data[i].my_var}};

but this does not work either, probably because of the way i is being derived.

2

Hi, welcome to the forums

So it sounds like you want a parent/child structure with your nested lists. That's perfectly possible but the i variable you're trying to use is only an internal property of that list so won't be available, as you've discovered.

What I would recommend is you have your 2 queries that select all the data (if the data volumes are not too large) and filter the child list based on the parent id of the main list or merge them using a SQL or transformer to form a single data set with parent/child relationship in the structure.

There's built in options for this using ri variables

2 Likes
3

Hey @shubham :slight_smile:

Thank you for the super helpful reply as always @dcartlidge!

This recent Community post might also be helpful:

http://community.retool.com/t/listview-component-value-in-rest-api/17531/14

2 Likes