Passing lists into ActionKit query reports
egj actionkit , actionkit reports
Suppose you want to build an ActionKit report where the end user inputs a list. In some of these cases ActionKit is magical enough to know what you want to do and make it work without any effort, e.g. when you're querying for user_ids or mailing_ids. But suppose we want a simple report to pull data on multiple members by email address instead of by ID:
select email, first_name, last_name, address1, address2, city, state, region, zip, postal, country from core_user where email in [USER_SUPPLIED_LIST_OF_EMAILS]
Doing this the obvious way doesn't seem to work properly:
select email, first_name, last_name, address1, address2, city, state, region, zip, postal, country from core_user where email in {{ emails }}
This fails because ActionKit treats the entire comma-separated list as a single string, so it ends up running an unhelpful SQL query where email in 'first@example.com,second@example.com'.
Other variations, on both the input and the query, also fail:
- Asking staff to input a list doesn't help, because that too gets treated as a string:
where email in "('first@example.com', 'second@example.com')" - Adjusting the query to "where email in ( {{ emails }} )" doesn't help either, because... yup, we're still getting a string on the inside:
where email in ( "'first@example.com', 'second@example.com'" )
OK -- so what about parsing the string in the query using template code?
select email, first_name, last_name, address1, address2, city, state, region, zip, postal, country from core_user where email in {{ emails | split:',' }}
Well... now we get an entirely different type of failure -- instead of requiring an input parameter named "emails", ActionKit decides we're asking users for an input parameter named "emails | split:','"!
There are two ways of making this work.
The first is to have your staff users pass in a semicolon-separated or newline-separated list of emails, instead of comma-separated:
This doesn't require any special logic in the query report -- just use that simple version we tried originally. This syntax is documented in ActionKit under Reports: Entering Input Parameters -- it's described there as a way to pass lists with a mix of letters and numbers for built-in reports, but it works just as well for all lists of strings, and for custom reports.
If you prefer something less magical, or if you need more complex template logic to parse the user inputs (or need user inputs with semicolons or newlines that aren't control characters) there's another, more roundabout, but explicit solution.
Just create a dashboard report to take the user's raw input parameter, parse it out into a list, and pass it in to the query report.
For the query report, we'll again use that simple version we tried originally:
select email, first_name, last_name, address1, address2, city, state, region, zip, postal, country from core_user where email in {{ emails }}
Then, for the dashboard report, make sure you're forcing user input with the {% required_parameter %} tag:
{% required_parameter "emails" %}
{% report "my_query_report" with emails|split:"," as emails %}
This does exactly what we want!