i'm kinda thinking this might be a syntax issue. ('{"product_a", "product_d"}') would normally define an array, with { } denoting the typeof array and not ( ) as might be expected. if so, this might be right(?):
IN ({ `{{ lineItemsInvoiceIDs.value }}` })
if it doesn't work and you end up going the guess/check route with different formatting, you'll want to be careful with 3 curly brackets in a row. just make sure the ones that designate JS code are surrounded by a space since { {{ js_code }} } can parse differently than {{ { js_code } }} and {{{ js_code }}}.
I've tried loads of variations on the formatting... same issues. but if I add them as hardcoded in the same format works fine. leading me to believe it may be a retool issue?
seems like it isn't recognizing lineItemsInvoiceIDs.value as an array... or the invoice_id column type isn't the same as the lineItemsInvoiceIDs.value type? maybe try casting it to ARRAY?
I just noticed you're using mssql which i think has different types than postgresql, it might not even have ARRAY (you'd use VARCHAR/TEXT or JSON instead i guess). i'm barely treading water with postgresql, it's even worse with mssql so hopefully a db person will chip in here