Pass a repeatable value to a query / transformer / whatever

[MFP]

I'm trying to do something that should be extremely simple, because it seems to be the very purpose of any app ...
But I find it extremely convoluted, and while there seem to be several topics about this, I did not get around this.

Suppose I have a ListView.
2 questions :

1. I need the data source to be a filtered query. So far was only able to get to it via a transformer. This seems an overkill to me, I thing I get it wrong and there has to be a smarter way ...
   

   

2. I want each row to either

• display the results of a query
• or filter an existing query
  with a value in the row as input ...
  I could not find my way around passing variables to a query (and even less to a transformer for filtering an existing one), and despite the several mentions of "additionalScope", I am too green around this to figure it out.
  
  

I have tried as per documentation :

but no game

Thanks for any clue.

[MiguelOrtiz]

Hi @MFP,

For number 1) you can apply the filter directly within the ListView Data Source. Click on FX right next to the field and you can write {{formatDataAsArray(getComptes.data.filter(compte => compte.compte_aggregation === ''))}}

By doing this you don't have to use the transformer. Another advise is to apply formatDataAsArray () directly within the query's transform section, i.e. formatDataAsArray (data).

I'm not sure I understand your question number 2, but you can actually do the same. If the information you want to display in each row is on another query, then you can use the FIND function. e.g. {{query1.data.find (x => x.id === item.value)YOUR_COLUMN}}

I don't think additional scope is what you're searching for. You should focus on either your data structure or how to reference the data already available. E.g. is the row you have highlighted a nested value? Could it include more than one line? If yes then you may need to create an array within your array. If no, then you should be able to reference it with {{item.your_column}}.

Hope this helps! ListViews are tricky, but once you get the hang of them they can do wonders. If this is still not helpful, it would be useful if you could provide a view of your data structure by clicking on your query / status and send a screenshot of how your data is structured and where do you want each data to be shown.

[MFP]

Hi Miguel,
thanks for coming back to me.

1. I just copied/pasted you code, but as I saw in many posts, Retool complains about filter not being a function ...
   

   

2. Here is an example.
   I have a table 'locataires' and a listview displaying from query
   

   

Within the 'locataires' table, I have a foreign key to table 'comptes'

Each locataire has its 'compte' record (but 'comptes' table is used for other purposes as well)

Now what I would like to do is to display the corresponding value of 'montant_ouverture' from table Compte in my 'locataires' listview (red circle).

Regards.
MF

[MiguelOrtiz]

Hi @MFP,

Thanks for the additional context.

With regards to question 1):

So, in my experience the error "... filter is not a function" pops up whenever the data is being applied to is not an array. We tried addressing this with the formatDataAsArray helper, but something else seems amiss. Two questions on this:

1. Would you mind clicking on your getComptes' state and send a screenshot of how your data is structured?
2. Did you try applying formatDataAsArray() within the transformer section in your query?

With regards to number 2):

I think the easiest way is to make a join in your getLocataires query, i.e.

SELECT
locataires.*
comptes.montant_ouverture
FROM locataires
LEFT JOIN comptes on comptes.id = locataires.compte
ORDER BY actif desc, nom_1 asc

As such, within your listView you will be able to reference your new field with item.montant_ouverture.

Hope that makes sense.

[MFP]

Hi Miguel,

Thanks very much for your insight !

1. Here is the getCompte State
   

   

2. I did, and got around with (there was just the parenthesis I got wrong in the beginning, but this works :
   

   

Since I'm a Bubble refugee, I have a surface knowledge of SQL, and I was not aware LEFT JOIN existed ... Thanks for providing a working code (just a coma missing on second line ), you saved me hours of hassle.