How to Run a Query when a Table Row is Updated

App Building
1

Hello Retool Community,

I'm working on a feature in my Retool application where I need to run a query after a row has been updated in a given table. Table name is messages (which gets populated from workflows) re-run a query to refresh the table whenever a row's team_id column matches a predefined application variable (team.value.id).

I'm currently running a query periodically but seems like a silly way of doing this.

Thank you in advance for your help!

2

I'm not totally sure I'm following. Where is the team_id being updated? It sounds like you just need to trigger the table source query/workflow in the success handler of the query that updates that value.

3

Thanks @kschirrmacher for your response!

team_id is in fact not being updated. I just need to run a query (getMessages) when a new row that matches the logged in user's team_id is met. I am wondering if there's a way to send a webhook to an app?

Thanks again

4

Ahh I see, makes more sense now.

It looks like workflows support webhook triggers. Trigger workflows with webhooks | Retool Docs

5

there's a few ways to run a query on that condition. it kinda depends on the query itself as to which is the best approach. can we see the query? or can you tell us what kind of Resource Query it is?

if you reference team_id from the table within the query you can do something like:
image

since you trigger the query manually on update you can ignore the Watched Inputs section. The disable query will make it so every time the query is triggered by an update if that evaluates to true it won't run the query, it'll return immediately.

6

I am only seeing the option to trigger other workflows with webhooks, not specific queries in apps. Am I missing something? If so, could you please share a screenshot? Thanks!

7

@bobthebear here's the query in my app:

screenshot_
screenshot_3248×2048 624 KB

And the actual query:

SELECT
  *
FROM
  messages
WHERE
  conversation_id = {{ conversation_id }}
  AND team_id = {{team_id}}
ORDER BY
  created_at DESC

Thanks in advance for your help! I have a feeling this is the most elegant solution, but looking forward to hearing from you.

8

click on the Advanced tab for that query and enter this:
image

now, anytime conversationsTable.selectedRow.id changes it will trigger this query. if the selected row id is not equal to the team id, disable the query and don't run it... if they are equal, we run the query.

9

Thanks for your help @bobthebear, greatly appreciate it. I'm running into 2 issues now:

  1. The Disable query section appears to be checking for a selectedRow.id value (which is a conversation id) to match a team_id value, is that intentional?
  2. Watched inputs appears to only have 2 options populate and not much beyond that. I'm not familiar with this part of Retool, do you know how to get {{conversationsTable.selectedRow.id}} to populate there?

Thanks again!

10

what are the 2 options you have available for Watched inputs?

the comparison wasn't intentional. i guess you'd use {{ messages.selectedRow.team_id !== team.value.id[0] }}? since you only want the query to trigger when the selected row team_id matches the variable team id... i forgot the selected row team_id comes from your messages table.