Custom Query - how to set Parameter Values to create tokens

asked on April 19, 2017

I am attempting to use the results of a Custom Query to send out a variety of emails using a Conditional Decision. The branches are using the results in the Status field of the data I extract to determine which path is valid, and the emails are using data from the First_Name and Email fields.

I have the query set up and I believe it is successfully pulling data from my db because when I run the test, it returns the correct # of rows.

My question is, how do I set up the parameters so that I can use the extracted data as tokens in my email process? I have scoured the help resources and tried all kinds of things in the "Values" column, but nothing seems to work - tokens are not created. The workflow runs successfully, but no emails are sent; it appears that this is because there is no data being passed through. (I do have data that should be meeting the requirements in my Conditional Decision area. The "Rows to Return" area in the Custom Query is set to "All rows"). Here is my workflow:

The For Each Row tool is set to "Get Rows From" = Custom Query Results
Here are the Conditions results after it runs:

And here is my query:

0 0

replied on April 19, 2017

More info .... I think my SQL might need tweaking. When I added a "Track Tokens" tool and looked at the result, it showed that the data in the first row of cells in my table was repeating over and over. It looks like there are 21 rows (which is the # of rows in my db) of data extracted, but each row is a repeat of what is in row #1 - ?? This query returns the correct records when I run it in Microsoft SQL Server Management Studio - perhaps it performs differently in the Custom Query tool?

0 0

View 1 previous reply

replied on April 19, 2017 • Show version history

This is probably just because you're tracking the wrong tokens (the Custom Query tokens show only the first row, you want to track the "current value" tokens inside For Each Row to see each iteration's values).

As for your query, it's not using any parameters, that's why it returns all rows. See the help file on how to specify parameters.

0 0

replied on April 20, 2017

I don't know what I had wrong in my first attempt, but I scratched the Custom Query and the For Each Row tools and recreated them and now I am getting the data I need. Turns out I don't need to set up the Parameters section of the Custom Query at all.

Just another question - if I use this process to send out in the neighborhood of 1200 - 1500 emails, do you see any issues with this? I'm concerned about whether there are volume limits and how long the process might take to get through that many emails. (Tests on small batches work fine, but I don't know if that will translate to the same results when I run this for over a thousand entries).

0 0

replied on April 20, 2017

Your query is a static query. You're specifying 5 columns to retrieve data from and no conditions to use to narrow down the results. A query with parameters would look like this:

(The WHERE condition specifies which column is used to filter results out. ODBC data sources don't allow for named parameters, they're all named "?" and replaced in the order they appear. Other data sources can have named parameters in the @ParamName format).

1200 emails are not a problem for Workflow. You would need to ask your IT people if they have any rate limits. They might be able to increase the limit specifically for Workflow's account.

You could also limit the search to 200 results or so, and run it on a schedule. If you tag the results as you process them and add that to your search criteria, they already processed documents won't appear in the search hits on the next run of the workflow.

0 0

replied on April 20, 2017

I actually changed my query as I kept adding more columns - so now it is this:

SELECT *
FROM [dbo].[TBL_AS_Notices]

So the parameters section would only be utilized if I wanted to filter the results ... which I don't in this case.

Good to know about the volume of emails being OK. I will check w/my IT folks and see if they know of any red flags. We do have limits if we email directly out of Google, which is what we use for all work correspondence. This limitation is one of many reasons I wanted to go to Workflow to handle the emails for this process.

Thanks for the help!

0 0

Question

Question

Custom Query - how to set Parameter Values to create tokens

Replies

Sign in to reply to this post.