Hi! @khill-fbmc's approach of breaking this query into two is definitely the right way to go! I'm just here to help clarify the "Keep variable references inside the query in sync with your app" and the run behavior of setValue.
setValue is in fact asynchronous. We can verify with the following:
A variable with a default value:
And some JS to run:
Without await, we log "Hello World" twice because setValue is still running in the background by the time the console.log from line 3 runs:
However, if we add await to setValue:
We wait for the async function call to finish running before line 3 runs:
The "Keep variable references inside the query in sync with your app" setting is what is allowing this demonstration on Retool:
This setting is off by default to improve performance by running with cached values. Queries that use setValue and reference the new value should have this enabled.