Limiting dynamic input query results to only certain products

Question

I'm in the middle of automating a workflow to run off sql server tables instead of xlsx. I basically need to get the contract values for certain contracts in the trailing twelve months (TTM). TTM has ~80k-100k records, but I only need info on ~300 total records. What I was doing previously is getting those contract numbers from the filter tool, using a crosstab to get a comma separated list, building a query with the formula tool, then feeding that into a dynamic input tool. Worked fine locally but it's having issues on the gallery..."Parse Error at char(246): Unknown variable "Concat__Contracts" (Expression #3) (ToolId: 943)". I don't know what the hang is since this runs error free in Designer.

Is there a better way to do this that might be more "gallery friendly"? I'm thinking of pulling in the full TTM result set and filtering within the workflow, but I need <1% of those records and would like to keep this as performant as possible.

ScottB1 · Answer

@geraldo

Turns out it was a simple error on my part - On the gallery I was selecting a date that contained no data, but in the workflow running locally had a hardcoded earlier date. Works fine with the proper dates, probably a sign I need better (or any) error handling. The query itself looks like this, didn't include it earlier as it had connection info.

"select [Contract Name], sum(contract
from server.db
where [Contract Name] in"
+ "(" + [Concat__Contracts] + ") " +
"and [Sale Date] < '" + [end_date] + "' and [Sale Date] >= '" + [start_date] + "'" +

" group by [Contract Name], [Product Code]"

geraldo · Answer

@ScottB1

exactly where the error is giving you did not send the print with it open.
is the query formula. there is a variable with the name Concat__Contracts and it is what is causing the problem, send a print with the open query formula to verify