[Automation] Possible bug: cannot update table after querying it

[MrT3chGuy]

Hi,

I've done more testing and I think I found a bug. Here is how to reproduce:

1. Create 2 tables in internal DB
2. Each table should have an auto increment Id and any number of other columns
3. Input one row to each table
4. Create an automation trigger on Row Created (it can be any table)
5. Add an automation step to query the second table (limit output to 1)
6. Add an automation step to update the table from the previous step
7. The above step will fail when putting {{ steps.Query rows.rows.0.id }} in Row ID

Below is the original post:

I have a simple app set up. I have 2 tables "signups" and "discountCodes".

signups schema:

discountCodes schema:

The idea is when there is a new sign up, I query the discountCodes table and get a discount code that hasn't been assigned. Then, I update the new signup row's discountCode with the fetched code from the discountCodes table. Everything works until here. Next, I want to update the discountCodes table and set the discountCode to Assigned (boolean value). This last step fails with "Row does not exist". Am I missing something?

Budibase version: 3.2.32 (self hosted)
Thanks in advance!

[mikesealey]

Hey @MrT3chGuy - I think there's some confusion here between the row_id and the auto_id.

Head to the Data tab, and right-click on the row you plan to update with this automation. You'll find that the row_id actually looks something like ro_ta_b1ea879f4d794e37b6597bf011ef1fe5_ff1516c4339343d2850d4e04b6efcce2

We can also see this in filtering, where we have the auto-incrementing column that I named auto_id (I think yours is named id, but we also have the option for _id)

Whenever you're planning to update a row, you'll need the _id, rather that it's auto_id. I think looking at your screenshots, the fix might simply be to prepend an underscore to "id" in your handlebar bindings, where you use something like {{ steps.Query rows.rows.0.id }} it should become {{ steps.Query rows.rows.0._id }}

[MrT3chGuy]

Hi @mikesealey, thanks for taking a look. So, I did actually try the _id earlier, but it produced weird results. It marked two rows as Assigned. And if there was only one row, it will create a blank row and mark it as Assigned. See screenshots. I'm also confused about using _id because I saw an issue/discussion that mentioned we should create our own ids and use them (I'll try to find it and link it).

Before running automation:

After running automation:

Only one row available (it creates a new one):

Automation results:

[mikesealey]

In this image you're using the step in a loop, but the value you pass in should use the Loop Outputs bindings. try {{ loop outputs.row._id

Also bear in mind that if your passing in more than one row, you will loop over those rows to update them.

Looking at what you're doing here though, you may be better off using a Row Action. Here's the documentation on Row Actions and here's an answer I gave to a discussion where Row Actions were part of the solution.

[MrT3chGuy]

Hi @mikesealey, sorry for the late reply. I'm not using the step in a loop though, am I? I didn't enable the loop. Regarding Row Action, I don't think I can update another table with it. For example, when a new row in the Users table is created, I want to query the Discounts table and get a code that's not assigned. Assign the code to the new user. Then, set that code as assigned. It's a bit of back and forth between the two tables

[mikesealey]

The response from "Query Rows" will be an array - even if it has no rows in it. This means the next step should be a looping-step. If you want it to only run on the first element, set it to limit: 1

Any "Query Rows" step is capable of querying any table table.

You can trigger the automation on Row Created, and you'll have access to the Trigger Row.

You can then use a Query Rows step to query your Discounts table, using the filters to find a unassigned discount codes. Use a looping step and set it to run only once.

You can then use an Update row to match these two things together, and another Update Row step to set the code as assigned.

1. Your automation should trigger on Row Created in the Users table
2. Query your Discounts table with filters set to Assigned === false (or similar)
3. In an Update Row, using the _id of the trigger row you can assign the Discount code to the user
4. In another Update Row step you can update your Discounts table using the looping functionality to loop over the Query Rows response that you got from point 2, again making sure to only loop once.