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.
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
@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]"
User | Count |
---|---|
18 | |
14 | |
13 | |
9 | |
8 |