# Automation script Schedule PM with Data Explorer results not sending PMs
**URL:** https://meta.discourse.org/t/automation-script-schedule-pm-with-data-explorer-results-not-sending-pms/269135
**Category:** Bug
**Tags:** automation
**Created:** [June 21, 2023, 9:46am UTC](https://meta.discourse.org/t/automation-script-schedule-pm-with-data-explorer-results-not-sending-pms/269135 "2023-06-21T09:46:11Z")
**Posts on this page:** 13
**Page:** 1
### Author:  [@Christian\_Parker](https://meta.discourse.org/u/Christian_Parker)
#### Post date: [June 21, 2023, 9:46am UTC](https://meta.discourse.org/t/automation-script-schedule-pm-with-data-explorer-results-not-sending-pms/269135/1 "2023-06-21T09:46:11Z")
We use a Discourse hosted instance and make use of the provided Automation plugin with its range of scripts and triggers, however we are having issues receiving PM’s from the ‘Schedule PM with [data explorer](https://meta.discourse.org/t/32566?silent=true) results’ script.
It looks like the Automation trigger is working and the [Data explorer](https://meta.discourse.org/t/32566?silent=true) query is being run (this query does work and produces results when run manually), however we are not receiving any PM’s following this. I have tried with myself as the recipient and also the ‘Admin’ group but in both cases no PM’s are received.
Not sure if I’m missing something obvious here but any help would be appreciated.
---
### Author:  [@Canapin](https://meta.discourse.org/u/Canapin)
#### Post date: [June 21, 2023, 12:09pm UTC](https://meta.discourse.org/t/automation-script-schedule-pm-with-data-explorer-results-not-sending-pms/269135/3 "2023-06-21T12:09:39Z")
Hello Christian 👋
Do you see anything related in `/logs`?
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [June 21, 2023, 12:44pm UTC](https://meta.discourse.org/t/automation-script-schedule-pm-with-data-explorer-results-not-sending-pms/269135/4 "2023-06-21T12:44:26Z")
I think I’m seeing a similar thing:
The [data explorer](https://meta.discourse.org/t/32566?silent=true) query appears to have run:
But no PM is delivered.
I do see this error in the `/logs`:
```plaintext
Message (5 copies reported)
Job exception: undefined method `to_sym' for nil:NilClass
Backtrace
/var/www/discourse/plugins/discourse-data-explorer/lib/result_to_markdown.rb:20:in `block (2 levels) in convert'
/var/www/discourse/plugins/discourse-data-explorer/lib/result_to_markdown.rb:18:in `each'
/var/www/discourse/plugins/discourse-data-explorer/lib/result_to_markdown.rb:18:in `each_with_index'
/var/www/discourse/plugins/discourse-data-explorer/lib/result_to_markdown.rb:18:in `block in convert'
/var/www/discourse/plugins/discourse-data-explorer/lib/result_to_markdown.rb:15:in `each'
/var/www/discourse/plugins/discourse-data-explorer/lib/result_to_markdown.rb:15:in `convert'
/var/www/discourse/plugins/discourse-data-explorer/lib/report_generator.rb:20:in `generate'
/var/www/discourse/plugins/discourse-data-explorer/plugin.rb:117:in `block (4 levels) in activate!'
/var/www/discourse/plugins/discourse-automation/app/models/discourse_automation/automation.rb:83:in `trigger!'
/var/www/discourse/plugins/discourse-automation/app/jobs/scheduled/discourse_automation_tracker.rb:36:in `run_pending_automation'
```
I also get a ‘500’ error if I attempt to trigger the automation manually, and this (slightly different) error in the logs:
```plaintext
Message (4 copies reported)
NoMethodError (undefined method `to_sym' for nil:NilClass)
app/controllers/application_controller.rb:418:in `block in with_resolved_locale'
app/controllers/application_controller.rb:418:in `with_resolved_locale'
lib/middleware/omniauth_bypass_middleware.rb:74:in `call'
lib/content_security_policy/middleware.rb:12:in `call'
lib/middleware/anonymous_cache.rb:369:in `call'
config/initializers/100-quiet_logger.rb:20:in `call'
config/initializers/100-silence_logger.rb:29:in `call'
lib/middleware/enforce_hostname.rb:24:in `call'
lib/middleware/request_tracker.rb:228:in `call'
Backtrace
plugins/discourse-data-explorer/lib/result_to_markdown.rb:20:in `block (2 levels) in convert'
plugins/discourse-data-explorer/lib/result_to_markdown.rb:18:in `each'
plugins/discourse-data-explorer/lib/result_to_markdown.rb:18:in `each_with_index'
plugins/discourse-data-explorer/lib/result_to_markdown.rb:18:in `block in convert'
plugins/discourse-data-explorer/lib/result_to_markdown.rb:15:in `each'
plugins/discourse-data-explorer/lib/result_to_markdown.rb:15:in `convert'
plugins/discourse-data-explorer/lib/report_generator.rb:20:in `generate'
plugins/discourse-data-explorer/plugin.rb:117:in `block (4 levels) in activate!'
plugins/discourse-automation/app/models/discourse_automation/automation.rb:83:in `trigger!'
plugins/discourse-automation/app/controllers/discourse_automation/automations_controller.rb:10:in `trigger'
```
Let’s slide it over to #Contribute > Bug and see if we can get someone to take a look. 👍
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [June 21, 2023, 1:00pm UTC](https://meta.discourse.org/t/automation-script-schedule-pm-with-data-explorer-results-not-sending-pms/269135/9 "2023-06-21T13:00:17Z")
After a bit of further investigation, I think it may be connected to the type of query you’re trying to run. I’ve just tried with a very simple one and I have managed to get it to run and send a PM. Could you share what your query is?
---
### Author:  [@Christian\_Parker](https://meta.discourse.org/u/Christian_Parker)
#### Post date: [June 21, 2023, 1:38pm UTC](https://meta.discourse.org/t/automation-script-schedule-pm-with-data-explorer-results-not-sending-pms/269135/11 "2023-06-21T13:38:12Z")
Thanks for looking into this.
The query is not totally simple and looks like this:
```plaintext
WITH
ua AS (
SELECT target_topic_id, COUNT(id) FROM user_actions
WHERE action_type = 15
GROUP BY target_topic_id
)
SELECT
t.id,
t.title,
t.created_at,
t.last_posted_at,
t.views,
t.posts_count,
t.user_id,
t.last_post_user_id
FROM topics t
INNER JOIN users us ON us.id = t.user_id
LEFT JOIN ua ON ua.target_topic_id = t.id
WHERE t.deleted_at IS NULL
AND t.closed = false
AND t.archived = false
AND t.visible = true
AND ua.target_topic_id IS NULL
AND us.username_lower != 'system'
AND t.created_at > now() - INTERVAL '7' DAY
ORDER BY created_at DESC
```
As I say, it does run manually and produce results.
---
### Author:  [@Canapin](https://meta.discourse.org/u/Canapin)
#### Post date: [June 21, 2023, 1:40pm UTC](https://meta.discourse.org/t/automation-script-schedule-pm-with-data-explorer-results-not-sending-pms/269135/12 "2023-06-21T13:40:17Z")
> [@Christian\_Parker](#):
>
> As I say, it does run manually and produce results.
By manually, do you mean triggering the query from [data explorer](https://meta.discourse.org/t/32566?silent=true) or triggering the script from the automation plugin?
---
### Author:  [@Christian\_Parker](https://meta.discourse.org/u/Christian_Parker)
#### Post date: [June 21, 2023, 1:40pm UTC](https://meta.discourse.org/t/automation-script-schedule-pm-with-data-explorer-results-not-sending-pms/269135/13 "2023-06-21T13:40:37Z")
From [data explorer](https://meta.discourse.org/t/32566?silent=true).
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [June 21, 2023, 2:56pm UTC](https://meta.discourse.org/t/automation-script-schedule-pm-with-data-explorer-results-not-sending-pms/269135/16 "2023-06-21T14:56:56Z")
I think it’s something to do with the `user_id` style magic the [data explorer](https://meta.discourse.org/t/32566?silent=true) does when it converts them from bare ids to useable links. If I run your report as is it errors out just like you’re seeing, but if I remove both `t.user_id` and `t.last_post_user_id` from the SELECT it does work.
If I convert those into plain usernames it also seems to work properly through the automation:
```sql
WITH
ua AS (
SELECT target_topic_id, COUNT(id) FROM user_actions
WHERE action_type = 15
GROUP BY target_topic_id
)
SELECT
t.id,
t.title,
t.created_at,
t.last_posted_at,
t.views,
t.posts_count,
us.username,
u2.username
FROM topics t
INNER JOIN users us ON us.id = t.user_id
LEFT JOIN ua ON ua.target_topic_id = t.id
JOIN users u2 ON u2.id = t.last_post_user_id
WHERE t.deleted_at IS NULL
AND t.closed = false
AND t.archived = false
AND t.visible = true
AND ua.target_topic_id IS NULL
AND us.username_lower != 'system'
AND t.created_at > now() - INTERVAL '7' DAY
ORDER BY created_at DESC
```
Though we’ve also pinged someone more knowledgeable to have a look in more detail. 👍 🙂
* * *
Though separately, I’m not sure your query does what you want it to. Is it somehow Solution related, as I’m seeing a lot of PMs in my results?
---
### Author:  [@Christian\_Parker](https://meta.discourse.org/u/Christian_Parker)
#### Post date: [June 21, 2023, 3:44pm UTC](https://meta.discourse.org/t/automation-script-schedule-pm-with-data-explorer-results-not-sending-pms/269135/18 "2023-06-21T15:44:43Z")
Thanks, I’ll give it a try with changes to the user id.
I didn’t originally write this query so I’m yet to determine if it even gives us what we want anyway so i’ll probably re-write it.
---
### Author:  [@riking](https://meta.discourse.org/u/riking)
#### Post date: [June 22, 2023, 1:50am UTC](https://meta.discourse.org/t/automation-script-schedule-pm-with-data-explorer-results-not-sending-pms/269135/20 "2023-06-22T01:50:25Z")
> [@JammyDodger](#):
>
> I think it’s something to do with the `user_id` style magic the [data explorer](https://meta.discourse.org/t/32566) does when it converts them from bare ids to useable links. If I run your report as is it errors out just like you’re seeing, but if I remove both `t.user_id` and `t.last_post_user_id` from the SELECT it does work.
It’s this line: [https://github.com/discourse/discourse-data-explorer/blob/705753216cc632b4f6505d2000926ab3b73d8628/lib/result\_to\_markdown.rb#L20](https://github.com/discourse/discourse-data-explorer/blob/705753216cc632b4f6505d2000926ab3b73d8628/lib/result_to_markdown.rb#L20)
` related = relations.dig(colrender[col_index].to_sym) if col_index < colrender.size`
The `if` condition on the end is incorrect: `colrender` is supposed to be sparse, not packed. It will contain nulls if some but not all columns provide extra rendering data.
The correct check would probably be `unless colrender[col_index].nil?`
Also, this code appears to neglect the `url`, `reltime`, and `html` rendering types because they’re not ActiveRecord classes.
---
### Author:  [@Canapin](https://meta.discourse.org/u/Canapin)
#### Post date: [June 23, 2023, 1:04pm UTC](https://meta.discourse.org/t/automation-script-schedule-pm-with-data-explorer-results-not-sending-pms/269135/21 "2023-06-23T13:04:52Z")
As for me, it seems I can’t get a successful trigger of this automation script, whatever the [data explorer](https://meta.discourse.org/t/32566?silent=true) query is. For example:
```sql
SELECT username from users
LIMIT 10
```
Error 500 when I manually trigger from Automation:
```plaintext
Message (3 copies reported)
TypeError (Nil is not a valid JSON source.)
app/controllers/application_controller.rb:418:in `block in with_resolved_locale'
app/controllers/application_controller.rb:418:in `with_resolved_locale'
lib/middleware/omniauth_bypass_middleware.rb:74:in `call'
lib/content_security_policy/middleware.rb:12:in `call'
lib/middleware/anonymous_cache.rb:369:in `call'
config/initializers/100-quiet_logger.rb:20:in `call'
config/initializers/100-silence_logger.rb:29:in `call'
lib/middleware/enforce_hostname.rb:24:in `call'
lib/middleware/request_tracker.rb:228:in `call'
Backtrace
plugins/discourse-data-explorer/lib/report_generator.rb:42:in `parse'
plugins/discourse-data-explorer/lib/report_generator.rb:42:in `params_to_hash'
plugins/discourse-data-explorer/lib/report_generator.rb:15:in `generate'
plugins/discourse-data-explorer/plugin.rb:117:in `block (4 levels) in activate!'
plugins/discourse-automation/app/models/discourse_automation/automation.rb:83:in `trigger!'
plugins/discourse-automation/app/controllers/discourse_automation/automations_controller.rb:10:in `trigger'
actionpack (7.0.4.3) lib/action_controller/metal/basic_implicit_render.rb:6:in `send_action'
actionpack (7.0.4.3) lib/abstract_controller/base.rb:215:in `process_action'
actionpack (7.0.4.3) lib/action_controller/metal/rendering.rb:53:in `process_action'
actionpack (7.0.4.3) lib/abstract_controller/callbacks.rb:234:in `block in process_action'
Env
HTTP HOSTS: discourse.canapin.dev
```
---
### Author:  [@davidb](https://meta.discourse.org/u/davidb)
#### Post date: [July 4, 2023, 9:15am UTC](https://meta.discourse.org/t/automation-script-schedule-pm-with-data-explorer-results-not-sending-pms/269135/23 "2023-07-04T09:15:25Z")
These issues experienced should be fixed with this PR:
[https://github.com/discourse/discourse-data-explorer/commit/ee308c637cbcb5128dad2de89db3aaf16cbcb9bf](https://github.com/discourse/discourse-data-explorer/commit/ee308c637cbcb5128dad2de89db3aaf16cbcb9bf)
---
### Author:  [@davidb](https://meta.discourse.org/u/davidb)
#### Post date: [July 6, 2023, 9:26am UTC](https://meta.discourse.org/t/automation-script-schedule-pm-with-data-explorer-results-not-sending-pms/269135/24 "2023-07-06T09:26:10Z")
This topic was automatically closed after 2 days. New replies are no longer allowed.