Select query in workflow with NOT IN or != ANY not giving expected results

App Building
,
1

I have a list of ids from a Jobber query in a a block in a workflow. In another block I want to compare this list of ids with a retool db table scheduled_dispatch_routes and list the row in the table that are not present in the Jobber list of ids.

As you see in the screenshot jobberVisits displays the list of ids from Jobber. In the blocks excludedJobberVisitsVariable and excludedJobberVisitsVariable2 I have tried to get a list of rows not in the Jobber list. However, the result is all of the rows in the table with the dispatchDate.

I have hardcoded the list and it works fine, so I am assuming my syntax for the variable is off. Any ideas?

chrome_fmrvbYQ8Ub
chrome_fmrvbYQ8Ub1648×433 102 KB

2

In the run history of the workflow I have what looks like 2 identical evaluatedExpression between the hardcoded version and the variable. Is there a difference? If not, what else could this be?

rawExpression from excludedJobberVisitsVariable

SELECT jobber_visit_id FROM scheduled_dispatch_routes WHERE visit_date = {{ startTrigger.data.dispatchDate }} AND jobber_visit_id NOT IN ( {{ [jobberVisitsLimited.data] }} );

evaluatedExpression
SELECT jobber_visit_id FROM scheduled_dispatch_routes WHERE visit_date = 2025-01-19 AND jobber_visit_id NOT IN ( 'Z2lkOi8vSm9iYmVyL1Zpc2l0LzE2MTQ4Nzk2MDQ=','Z2lkOi8vSm9iYmVyL1Zpc2l0LzE2MTQ4Nzk2MDU=', 'Z2lkOi8vSm9iYmVyL1Zpc2l0LzE2MTQ4Nzk2MDY=', 'Z2lkOi8vSm9iYmVyL1Zpc2l0LzE2MTQ4Nzk2MDc=' );

rawExpression from excludedJobberVisitsHardcoded

SELECT jobber_visit_id FROM scheduled_dispatch_routes WHERE visit_date = {{startTrigger.data.dispatchDate }} AND jobber_visit_id NOT IN ( 'Z2lkOi8vSm9iYmVyL1Zpc2l0LzE2MTQ4Nzk2MDQ=', 'Z2lkOi8vSm9iYmVyL1Zpc2l0LzE2MTQ4Nzk2MDU=', 'Z2lkOi8vSm9iYmVyL1Zpc2l0LzE2MTQ4Nzk2MDY=', 'Z2lkOi8vSm9iYmVyL1Zpc2l0LzE2MTQ4Nzk2MDc=' );

evaluatedExpression
SELECT jobber_visit_id FROM scheduled_dispatch_routes WHERE visit_date = 2025-01-19 AND jobber_visit_id NOT IN ( 'Z2lkOi8vSm9iYmVyL1Zpc2l0LzE2MTQ4Nzk2MDQ=', 'Z2lkOi8vSm9iYmVyL1Zpc2l0LzE2MTQ4Nzk2MDU=', 'Z2lkOi8vSm9iYmVyL1Zpc2l0LzE2MTQ4Nzk2MDY=', 'Z2lkOi8vSm9iYmVyL1Zpc2l0LzE2MTQ4Nzk2MDc=' );
3

You're correct, I don't think you will be able to use an "in ()" statement dynamically like that. It's going to wrap whatever you have there as a string.

Try something like this

SELECT *
FROM scheduled_dispatch_routes
visit_date = {{startTrigger.data.dispatchDate }} AND jobber_visit_id != ALL ({{ arrayValue.value }});

4

I tried and still incorrect results. Block jobberVisitsLimited:

const fourVisits = "'Z2lkOi8vSm9iYmVyL1Zpc2l0LzE2MTQ4Nzk2MDQ=','Z2lkOi8vSm9iYmVyL1Zpc2l0LzE2MTQ4Nzk2MDU=', 'Z2lkOi8vSm9iYmVyL1Zpc2l0LzE2MTQ4Nzk2MDY=', 'Z2lkOi8vSm9iYmVyL1Zpc2l0LzE2MTQ4Nzk2MDc='"

return fourVisits

Query block:

SELECT jobber_visit_id
FROM scheduled_dispatch_routes
WHERE visit_date = {{startTrigger.data.dispatchDate }}
AND jobber_visit_id != ALL ({{ [jobberVisitsLimited.data] }});

What am I missing?

5

An array, that’s a string