Adding a direct link to a topic in a data explorer query

[SStrong (Sophie Strong)]

We currently have some automated data explorer scripts being sent to senior stakeholders.

There is currently one new topic appearing stating “this one doesn’t have a response, someone needs to action this.”

They would like a direct link to the topic in question to answer

Current data explorer has the Topic Title and the ID, so you can search for it, however wondering if there is a way to add a direct link to a topic in this way?

Thanks

[Moin]

I think you can create the URL as described in Allow topic links in reports to be clickable - #2 by JammyDodger. If you don’t have the slug, you could just add, for example, “-” as Discourse relies on the topic ID.

[Lhc_fl (Linca)]

Hi @SStrong

I guess you could get the links by naming the required columns topic_id or post_id. Here is a screenshot:

[SStrong (Sophie Strong)]

Why and how does that work? It does work but I’m a bit confused as to why

[JammyDodger]

The data explorer has a selection of magic aliases to make the results more interactive. Formatting Data Explorer Table Results

A lot of them work automatically, depending on what table you use. For instance, pulling topic_id from the posts table will make it an interactive link to the topic - but to make the id field in the topics table do the same thing you’d need to add the alias (t.id AS topic_id). Same for the users and posts table as well:

@JammyDodger

For reports viewed ‘onsite’ you can alias the id from the topic (or post, etc) tables and use the data explorer magic to turn it into a usable link:

SELECT 
    id AS topic_id
FROM topics

These don’t carry though if you export the results though, and you’ll only get the base id. For that, you can do as moin suggested and build the topic link and alias it as an URL:

@JammyDodger

You can also construct an URL and alias it as SOMETHING_url to turn it into a hyperlink. eg:

SELECT 
  'https://meta.discourse.org/t/' || slug || '/' || id AS topic_url
FROM topics

[SStrong (Sophie Strong)]

Thanks, will read through the extra documentation at a later stage but much appreciated

[SStrong (Sophie Strong)]

Ok, so this 100% works just in Data explorer, however…

When a user receives an automated email using the Automation plugin, the only way the user can see this functionality is by going directly to the Data explorer report.

I.e. these links to the topics don’t come through in the body of the email when using the Automation tool. Is this how it should work?

[JammyDodger]

I think for the scheduled data explorer automation you would need to use the second method and build the URL.

[SStrong (Sophie Strong)]

Thanks, with a bit of tweaking this worked, thanks (sorry not a dev so a lot of these learnings are new, hence the questions)

[JammyDodger]

That’s okay. The chances are that if you have questions others will as well, so it all adds to the knowledge pile.