I have the following app (see screen print).
What is the best way to persist the Households text area to a database line by line, (i.e. each line in the text box should be a row in a database table and also to re-load the text box from the database each time a new row in the campaign table is selected
I think a textbox is the wrong UI for this. Another table component linked to your Households DB table is simpler and less of an unusual solution. My philosophy - avoid unusual solutions where possible - they are harder to figure out and to get help on.
Using a Text component you have to manually create an array from the text box value to use in a Bulk Upsert query. Then you need to transform your Select results back into a string to put into your text box, You also, I assume, have to remove records that the user deletes. A text box UI is a bit easier to type in and such for the user if that is what you are after,
@bradlymathews The reason for a text box is because the users want to copy/paste the data from a text file. I agree that a table UI object would be more fitting but I believe impractical for adding e.g. 1k household codes.
Ooohh, I do see the use case then! Clever even.
I am guessing at your field and table names here.
You need to take the .value from the text box and split creating an array, but make it an object array: How to Split a String by Newline in JavaScript | bobbyhadz
Then you need to take the array and convert it into an object array with the key name being the column name for the household code: Javascript: Whats the best way Convert Array into Object? - Stack Overflow
Now you need to add your campaign_id to the object array. Now you have an object array that can be passed to a bulk insert query. Here are some tips on creating that array and using the query: How do I add multiple rows to table in PostgreSQL? you can find more tips on the forum.
Now to get the data back from the table into the text box, you are going to query the household table using {{tblCampaigns.selectedRow.data.campaign_id}} as a filter. Add a transformer that does a join, something like return {{data}}.household_ids.join("\r\n"); and then set the default value of your text box to the query data.
If you need to also do updates then use a bulk upsert instead of bulk insert. If you need to do deletes, well, that is a whole 'nother ball of wax.
