Hi! To preface I'm just learning everything about retool and know almost no javascript which might make this a challenge to explain to me, but hopefully there's a way!
I'm trying to create a tool that will allow a list of accounts visibility to a subset of data.
The subset of data is is just one data point, which is referenced by {{table1.selectedRow.data.id}}
the list of account IDs is from a CSV.
I've been able get the visibility added, and I can completely wipe the visibility, but I can't remove just some accounts.
Ideally I want to do what this posgresql query should do but doesn't:
delete from joor_web.accounts_collections
where collection_id = {{table1.selectedRow.data.id}}
and account_id = ANY({{fileInput1.parsedValue}})
I get syntax error at or near "["
I've also tried pulling the account_ids from a table that shows what's in the uploaded file but same issue.
I'm hoping the solution is simple! I'm attaching a screenshot of the tool for visual reference.
Hello, does this change works for you?
Im assuming your csv has a column like ID or similar as accound_id
so you need to get the array with the values
If doesnt work, could you share what is fileInput1.parsedValues returning?
Hello! thank you for your reply. The above give me a similar but slightly different syntax error:
syntax error at or near ","
The fileInput1.parsedValue returns what's in the screenshot below.
The file that is uploaded is just 1 column called retailer_id
It looks like it's pulling in the correct information.
and if I change the fileInput1.parsedValue to pull from the table called account_ids it gives the same error.
Adding "[]" between the curly brackets solves the error?
ANY( [ {{ fileInput1.parsedValue.flat().map(item => parseInt(item.ID)) }} ] )
Nope. Just goes back to syntax error at or near "[".
this what my csv returns, similar to yours
i get the array of the IDs
so i do
delete from test
where id = {{ table1.selectedRow.id }}
and s_id = ANY({{ fileInput1.parsedValue.flat().map((item) => item.ID) }})
try this one instead
ANY({{ fileInput1.parsedValue.flat().map((item) => item.retailer_id) }})
1 Like
That doesn't seem to work either. It just doesn't like the information from the .csv
Not sure if I mentioned I'm using postgresql, so maybe there just isn't a way to make this work and I need a different type of query?
if you do the following in a JS query, what you get?
console.log(JSON.stringify(fileInput1.parsedValue.flat().map((item) => parseInt(item.retailer_id))))
One thing that I think you might consider is that the postgres ANY clause expects an array, and in this case you're passing in a set of comma separated strings. The IN clause would accept the comma seperated list though.
ie
ANY (ARRAY[1, 2])
or
ANY ('{1, 2}')
vs
IN (1, 2)
Maybe this could be part of the issue?
Hello, there. Thank you very much for your help. I was able to get a different version to work for this. There was a partially completed query that I still don't fully understand how it works, but it wasn't doing anything. I added {{ fileInput1.parsedValue.flat().map(item => parseInt(item.retailer_id)) }} to variable 2 and now it works like I needed it to. Thank you SO much!!!!
1 Like
just thought I'd mention that if you would like help understanding the query feel free to post a reply with it in here (you can change var names and stuff if you want to protect things from public view) and either myself or someone here can help break it down for ya. 
3 Likes
