Filter Results for a ListView

App Building
1

I'm so close. I have a ListView inside a GridView.

image
image1455×865 96.3 KB

I have tried what seems like everything to get the data for the ListView filtered to only include the appropriate values for the appropriate years. (You can see that each list is identical.)

get_all_years is a query that gets all of the distinct years from my data, and is the datasource for the GridView.
get_all_series is a query that gets all of the distincy series from my data, which includes a "year" property.

I was hoping to call get_all_series once, and then have the option to _.filter() the records for each ListView based on the year used by the parent GridView, but this seems to be eluding me.

Anyone have any advice?

2

I think using

get_all_series.data.filter(x => x.year === item.year)

should achieve what you're trying to do.

What I've found helpful is having all the data in one single query and have your cards nested within the years. So you could have something like

SELECT year, ARRAY_AGG(name) AS cards
FROM your_table
GROUP BY year

(don't forget to add formatDataAsArray (data) within the query transformer)

And so in your parent list view you can use get_all_info.data and your child list view item.cards

Hope that makes sense

3

This is exactly what I tried, but it didn't work. Retool thinks .filter() isn't a function.
image

Even though it recognizes .data as an array.
image

I'll take a look at modifying my query.

4

Hi @jeffblankenburg! Can you try wrapping get_all_sets_by_year.data with formatDataAsArray -- e.g. formatDataAsArray(get_all_sets_by_year.data).filter(() => ...)

This should verify that your data is an array before you filter it.

5

That did it! It's weird to me that I have to cast it as an array when it should already be an array, but I'll take the win! Thank you so much!

image
image1277×796 57.3 KB

6

Anna could you take a look at mine? Grid View Sorting Data by Container

7

Hi @MiguelOrtiz,
Learning retool through trying to replicate code provided and here come a cropper for some reason.
Using the Array_Aggr as below

SELECT p.id, p.name, ARRAY_AGG(c.name) AS children
FROM "parentGuardianA" as p
join "childA" as c on c."parentID" = p.id
group by p.id, p.name

returning
return formatDataAsArray(data);

When I populate the gridview using {{item.name}} and the list within using {{item.children}}, the list is showing all children for each parent but if I just output the data as below, it is fine. What am I missing?

image
image1138×284 27.3 KB

8

Hi @Kotin_karwak,

Apologies if it took me a while to get back to you. So I just made a quick example. My query is the following (and it is transformed using return formatDataAsArray (data) ):

SELECT
c.id,
c.company_name,
ARRAY_AGG(DISTINCT jsonb_build_object('id', con.id, 'first_name', con.first_name, 'last_name', con.last_name, 'company_id', con.company_id)) AS contacts
FROM ps1.company c
JOIN ps1.contacts con on con.company_id = c.id
group by c.id,c.company_name

It returns this:

image

And so in my first listView data source I have the query as data source. In the nested list view my data source is {{item.contacts}}

image
image1664×470 37.5 KB

And so the text components are:

image

Hope that helps!

2 Likes
9

Excellent, now I follow this right. Been able to implement that scenario.

image
image1027×311 20.2 KB

1 Like
10

I've searched for an answer to this problem myself for quite some time and so glad I stumbled across this! Thank you Sir.

1 Like