Hey community! I'm building a workflow to automate auditing our inventory, and I have a SQL query that gives me random locations back, but theres a catch, we also have a table in BigQuery that contains locations of previous audits, so I want to check if the results from the PostgreSQL query contain any locations in the BigQuery table, and if there is, retry the PostgreSQL query, and keep doing this loop until a desired query is found.
The issue is that Retool doesn't allow me to have dependency cycles, so how can I approach this?
I would create the PostgreSQL query as a function, then use a while loop in a JavaScript block to repeatedly trigger the query until it doesn't match a value previously returned from your BigQuery locations. Something like
let foo = BigQuery.Data; // Array of location values from previous block
let newLoc;
do {
newLoc= getNewLoc(); // your PostgreSQL query as a function
console.log(newLoc) // If you want to see which locations where tried
} while (foo.includes(newLoc));
return newLoc