Bulk Update selected rows with value

[Blochergroup]

I am trying to select multiple rows, press a button which then prompts me for a value. After I have entered the value, this value should be saved back to a specific column in the table.

So in this example I would like to change the "Estimated Sales" to 30. So I would want to select both rows, the Button should prompt me for a value and after I entered 30 and clicked save, the value of "Estimated Sales" should change to 30

This is a similar open Issue:
#5666

[ConorWebb96]

Hey @Blochergroup,

I believe in this instance you could use automations for this along with the automation app action.

I've created a simple demo below to help guide you on how to do this.

1. Go to your automations area
2. Add a new automation, name it whatever you want. (In my case I called it Bulk Update)

3. Select App action as the automation type and click the save button. 4. Add in 2 different fields which will be used later. One being the rows for the select rows array that you will pass to the automation. The other is up to you. In my case I went with the name newFieldValue. 5. Add a new automation step and select the `Update Row` option and then click save. 6. Click the add looping button. 7. Select string for the input type 8. Use the field created previously here for the binding/value 9. Select the table you wish to run this automation on. 10. Target the value you wish to change and set it to the `newFieldValue` created earlier on in the fields area. Additionally, add `loop.currentItem` to the row id so the automation knows which row to update.

11. That finishes up everything on the automation, next head over to the design section.
12. Add a button, then add the run automation action to this when its clicked.
13. Add a input field used to send the value you want the fields to change to.

14. Configure the run automation action similar to the below. 15. Publish the app and that should be it working.

Demo Below

I hope this helps!

[ConorWebb96]

My bad I didn't realise I forgot to supply the screenshot for the input!

In the below screenshot, you should be able to see the binding for the input when attaching it in the automation action area.

I hope this helps, let me know if this helps sort the issue.

[Blochergroup]

Thank you for the Help @ConorWebb96 !
It seems like I had mixed up a text input with a number field.
I fixed that and I can see the Value I did input in that field in the Automation History. So far so good!

I still have one error with the "Update Row" Action

"UID can't be blank".
UID is the primary Key of my MS SQL table but I don't understand the error as we use the row ID to update the row and the UID shouldn't be necessary, right?

[sklaffa]

I also have the very same issue. I'm trying to do the very same thing, that is, bulk update a field in multiple rows. It looks like the "update row" action always tries to update all the fields of the row, even if there is no need to change their values. So, if some of these fields is actually a foreign key to a row of another table, the update fails because that field is required. In my opinion, the expected behaviour of "update row" should be something like "just update the fields for which you were given a value, and let the others alone".

[ConorWebb96]

Hey @Blochergroup,

Apologies I figured out the issue you were having and forgot to post the answer to this for you. Basically your MsSQL scehma has its own validation on this field. Therefore, we need to pass the existing values back in the automation. This does mean you will have to change the automation slightly from what I demoed previously. The changes required are below;

1. Add a query rows step before the update row step
2. Select the table for the rows you wish to query.

3. Click the define filters button, and customise it similar to the below 4. Updated your looping type from string to array and passing in the `{{ step.1.rows }}` from the previous step 5. Then filling the the values of the required fields using the syntax `{{ loop.currentItem.nameOfField }}` 6. Publish the changes and run the automation

I hope this helps resolve your issue!

[sklaffa]

Hello, for me it still does not work. Here are the screenshots:

STEP 1: configure the automation

STEP 2: configure the filter of the "query rows" action

STEP 3: configure the looping

STEP 4: configure the "update row" action

RESULT: checking the "automation history" it can be seen that the list of selected rows contains "rogue" characters (they seem to be the UFT-8 codes for the square brackets), thus forcing the query to fail and return no rows for the subsequent loop:

EDIT: I noticed that in my case the "selected_rows" trigger field was set as "text" type instead of "array". I tried to switch to "array" and tested again, but looking at the automation history the result is exactly the same, and the string of selected rows still contains the unexpected characters.

How can I solve this?

[andz-bb]

hey @Blochergroup @sklaffa

I've come up with a new solution that should work well for you both. It's similar to Conor's method above but instead of querying the rows in the automation, I am passing an array of full row objects to the automation from the data provider. I am using a Postgres table to test this so it should work with most other external datasources.

In order to access that full row data I have made sure that the update button where I'm triggering the automation is nested within the data provider as shown here:

Here are the actions I have for the button:

Here is the custom JS I am using to get the value for rows:

return $("Dataprovider.Rows").filter(x => $("Table.Selected rows").includes(x._id));

This returns an array of the full row objects for the ids that are selected in the table.

Now that we are passing the full objects to the array we can reduce the complexity of the automation. Here are the updated automation steps:

For the trigger values I am making sure that rows is set to array type

Then I have an Update Row step which loops over the rows array on the trigger. I am using id rather than _id for my Row ID since this is a Postgres datasource.

Finally I have a Collect Data step at the end of my automation. This is not necessary for the basic functionality of this but is a nice bonus as it allows my automation to run synchronously. This means that the Refresh Data Provider action on the update button on my screen will only run after my automation has finished updating the table.

Here's the app in action. I hope this helps!

[Blochergroup]

@andz-bb thank you for your solution!
This is a interesting way to solve the problem. Your solution is working for me up until the actual SQL update.
In the Update Row step of the automation:

"success": false,
      "response": "Error: RequestError: Violation of PRIMARY KEY constraint 'PK__SKUSales__C5B196023CE24CF2'. Cannot insert duplicate key in object 'dbo.SKUSalesData'. The duplicate key value is *UID*

It seems like the Update Row Action is trying to create a new row instead of updating the existing rows?
Would be great to get the input of @sklaffa because he is also using Postgres. Maybe its just an error with MS SQL?

[andz-bb]

@Blochergroup could you send a copy of your DB schema and an app export to support@budibase.com please? It would be really useful to have a look on my end. There's a new feature in the latest version of Budibase (v2.8) that generates a schema for you which will hopefully make the process easier. Thanks

[Blochergroup]

@andz-bb just got it to work :)
Seems like I had a typo in the _id field step in the automations.

Thank you so much, that will save a lot of clicks & time in the future.

[sklaffa]

*** EDIT ***
Deleting the automation and reimplementing it from scratch did the trick. I don't know why the previous one didn't work, but the new one does. Thanks for the contributions and above all for the patience 👍

---

Hi All, sorry to reopen this thread after so long, but I didn't have the opportunity to test this solution earlier due to a variety of reasons (both professional and personal). So, long story short, it still does not work for me. But this time the problem is different in my case. Implementing the solution suggested by @andz-bb I managed to correctly pass the selected rows to the automation, but for some reason I can't understand, the loop block in the automation receives an empty input and exits with "no iterations".

This is how I modified my automation, as per @andz-bb instructions:

Here is the log of the automation execution as seen from the "manage" section of my app, as you can see the output of the trigger block correctly contains the array of the selected rows, but the input to the looping block is empty so no rows are actually updated:

I'm stuck at this point and I am unsure what to do next. Maybe I should delete and re-create the automation? The app is updated to the latest version (2.9.25), and the backend database is MariaDB (but considering the automation logs I don't think that the backend is relevant for the problem I'm experiencing).

[Jednadvacet]

Hello, I followed the automation update guide above and I succeeded, but I have problems with relationship typed columns.

My automation trigger updates boolean fields "Approved" from false to true, as intended. But updates relationship field "Workers" to empty as well, although the checkbox "Clear relationships if empty?" is not checked.

Seems to me like a bug. Can anyone work it around?

(PostgresDB, latest docker image)

[ConorWebb96]

Hey @Jednadvacet,

Have you tried re-passing the relationship values in that are already attached to the record?

If you could, would it be possible to write up a issue. I thought I had written this bug up but wasn't able to find it.

If you can't I'll try and get an example etc written up later today. Thanks for reminding me!

Thanks ahead of time!