Update multiple rows to table

[applicationuser]

Hello,

Need your help on how to design a form with multiple data rows update to a single table.

Data from budibase table displayed using repeater form. Based on the user selection criteria , repeater form can have at a minimum of 2 rows to 20 rows of data displayed. similar to a inspection/survey form with list of questions to answer & rating

Layout .
-->Form
----->Data Provider
---------> Repeater
------------->Field Group

is there an option to place save button outside of repeater and trigger update of all the rows selected from Data provider ?

prefer not to have Data provider limit to 1 and save each row, that would require user to go through ~200 rows and save each row.

Thanks,

[melohagan]

Hey @applicationuser

Part of the challenge is that the form fields within a repeater cannot have unique identifiers. Something noted here: #5944

This can be alleviated by repeating the form as well, so that your component tree looks as follows:

The tricky bit then is to aggregate the forms data outside the form component scope. The way I can think to do this is to add an Update State action to the On Change event of each form field (this could be a bit tedious if there are many form fields unfortunately).

Then you can update a JS dictionary to keep track of each form state, using the repeater row id as the index, e.g.

let formState = $("State.FormState");
const index = $("New Repeater.Sales.Auto ID")-1;

if (!formState) {
	formState = {};
}
if (!formState[index]) {
	formState[index] = {};
}
formState[index]["itemType"] = $("Field Value");
formState[index]["_id"] = $("New Repeater.Sales._id");

return formState;

Next we need a button that will trigger an automation. We need to do this because automations support looping over rows, whereas actions client side do not.

return $("State.FormState") ? Object.values($("State.FormState")) : [];

This JavaScript takes our dictionary and converts it to an array so that the automation can loop over it

Automation

I've only included some of the fields as an example

Here is the app export so you can take a closer look:
Multi Row Auto-export-1667303882919.tar.gz

[applicationuser]

Thank You @melohagan for putting this together and providing app export.

i'll work through with our form data and let you know if anything else needed. Thanks again!

[melohagan]

You're welcome! 😃

[gmentzik]

HI @melohagan ,
I am trying to create a similar form. Unfortunately I noticed that the onChange returns the old field value while building the json object at state variable. So something is wrong here. I am on version: 2.1.31

[melohagan]

Hey @gmentzik

Are you using the {{ Field Value }} binding?

[gmentzik]

No was using Form.Field...
formState[settingID]["pmiValue"] = $("PMIForm.Fields.pmiValue");
Changed to Field Value
formState[settingID]["pmiValue"] = $("Field Value");

Now it has new value! Thank You!

[gmentzik]

A few comments here as I am also trying to use looping to update/insert multiple rows but I am not very satisfied. Here are my comments:

1. Update works for both updating and creating row (if _id is missing). That simplifies implementation otherwise you would need to create two arrays (insert and update).
2. I was not able to test the automation from backend using Finish and test. I am getting an error that my id field in database that is set to Not Null should have a default value. When testing from Frontend row is stored/updated without issue.
3. Automation only works from published app which is a pity as in such cases preview is very valuable.
4. Failing insert/updates are not propagated to FE. So user does not know if updates were performed unless he refreshes screen or try to refresh provider to check by himself.
5. Hint. You may need to cleanup your state after submitting to automation.

[LMPexplorer]

Hi @melohagan,

I am using version : 2.19.4.
I followed your tuto to be able to update multiple rows in a table using update state/ automation.
I used it for two different tasks.
One is working but unfortunately, the other one doesn't seem to work. the field value part is working, but instead of updating an existing row, it create a new row, so my "_id" part doesn't seem to work. I have thoroughly followed ur instructions on both tasks so i don't understand why the second task isn't working.
Here is screenshot of the second task :

Do you find any error ?
Thank you

[LMPexplorer]

Hi @melohagan, thank you for your quick response. I have updated the app to the latest version. Unfortunately, it doesn't seem to fix my error. It still create a new row instead of updating an existing row. Do you find any misconduct that could fix my error ?

[LMPexplorer]

I have finally found my error. But i see that sometimes when a value is updated, the automation doesn't trigger. Could you explain to me how it works ? Is there a way to make sure that the automation trigger when a value is updated ? Thank you :)

[melohagan]

If you call the Trigger Automation action from the Design section, then it should always trigger the automation. Maybe you are missing some input data in those cases it doesn't work as you would expect?

[LMPexplorer]

Thank you for your quick response. I am using a repeater and the action is in the "on change" of the repeater so it should be working for all my fields. I will see what i am missing. Thank you for your help !

[phifogg]

Thanks so much for this tutorial! Works like charm!