Proper design pattern for adding a record through a form

[edwardhamlin]

Newbie question (another).

I would like guidance on the best way to insert a new record, allow the user to fill in new values, and then post the changes.

I have a very simple table that lists geographic (wine) subregions, and next to it a form that allows the user to either modify the subregion name of an existing record or add a new one (in response to an Add... button click on the form). So, I am using that form for two purposes.

What I've done so far, which kind of works:

Created an insert query that creates a new, essentially blank record in the Subregion database table, a null Subregion Name;

Added a Success handler to the insert query that clears the data entry form via the .clear method (doesn't work - nor does .reset);

Created a button click handler on that Add button that fires the insert query.

What happens is:

User clicks Add;
Insert query fires and creates a new record with just the PK id (good);

Data entry form is NOT cleared (bad) and table component is not refreshed to show the new record (acceptable);

Manual entry, typing over the value that was there based on the last-selected table row, updates that value's record rather than the newly created record (very bad).

I think what I need to do is somehow fetch the id created by the insert query, refresh the table, and use that id to navigate to the new row, which should synchronize the form data.

• Is that the right way to do it? (Skeptical...)

• How can I derive the id of the row just created?

[pyrrho]

Hello @edwardhamlin! I have some surface level thoughts about your design but I think what you are trying to achieve is doable with a few properly chained events.

Since you are using a form to enter data to your table, there should be some built-in options to help you manage submissions through the form component (like the clear on submit option). The table, I assume, has the Primary Key set to be the ID of the data in the row. This will allow you to utilize the changeSetArray/Object to process changes to your data table.

There is a automated form builder that uses your table data to link entries, which can have their initial values set to the current selected row (or new row data if adding entries).

The submission button features on the form should be driving the Update/Create actions based on whether or not the information is being edited or a new entry is being added. Maybe there needs to be an option to Update vs Create in the form itself?

In either case, the success event handlers for these operations should be responsible for refreshing the table which should in turn update/clear the form values.

You may need a supplementary query to fetch the new row data (select max(id) from table?)

I am not seeing the full picture of the interaction as well, so if you have some screenshots of the form/table relationship queries that may help refine any future answers.

[edwardhamlin]

Thank you for this thorough response. I guess one fundamental question, implied by your discussion of Update/Create behavior, is this: should I first perform the insert, then try to refresh the query and resync the table to the new record so that my data entry form reflects it? Or if I know it's a new record (e.g., the user has clicked an Add button), should I present a form unlinked to a datasource (if that's allowed), then fire the insert query with its contents when the Submit is done? This is where I'm kind of stuck. Resyncing to a newly-created record is a key issue - select max(id) from table would be ok in a single-user environment, but not with multiple concurrent users, I think.

Screenshots below...open to all thoughts and suggestions!

The app with Region and linked Subregion tables, and the data entry form for Subregion

The Update query

The Insert query

[lamh_bytecode.io]

Hi @edwardhamlin,
From a loosely understand the use case. You want to select the new record after inserted and table component's data refreshed. You can capture the form's data into a variable if your form reset after submit, then find the index after the data refreshed to select on the table. The variable can then be cleared after index is found. Rinse and repeat for new insert.

[edwardhamlin]

Thank you. The part I don't know how to do is "find the index after the data refreshed." If the PK is created automatically when the insert is done, I can't explicitly set that value, and I don't see how to query the database to discover it. If I had that new key value I could refresh the table component and select that row and all would be good, but how do I get it?

[lamh_bytecode.io]

I see your dilemma,
Is the input value of sub-region allows to be duplicated with existing value? if duplicate is not allow, you can use the input value to find the index.

Alternate solution would requires you to use manual SQL instead of GUI. This is where you insert and return the newly ID in the same SQL. There are tools such as (PostgreSQL)

Stored Procedure like this example: postgresql - Invoke Stored Procedure and Return ID - Stack Overflow

Function: postgresql - RETURN value directly from INSERT with RETURNING clause - Database Administrators Stack Exchange

[matth]

@edwardhamlin

Data entry form is NOT cleared (bad)

Is it possible that your using button type "default" instead of "submit"?

@edwardhamlin

I think what I need to do is somehow fetch the id created by the insert query, refresh the table, and use that id to navigate to the new row, which should synchronize the form data.

The response of insertRegion should contain the inserted id. insertRegion.data.result[0].id you might have to do some sanity checks on that data before you use it.

If you set the "Primary Key" property on the table to your pk field.

Use table1.selectRow({mode:'key', key: id});

Also, if the pk is an auto-increment int and it works for ux, sort the table by pk descending and select row 0.

table1.selectRow({mode:'index', index:0});

[edwardhamlin]

I like the stored proc approach, and thanks so much for those references. Playing with it now - need to figure out how to retrieve the result of a SELECT that references the function, I think.

[edwardhamlin]

@matth

The response of insertRegion should contain the inserted id. insertRegion.data.result[0].id you might have to do some sanity checks on that data before you use it.

This sounds SO much simpler. So, I rewrote the logic behind the Add button to fire the insert query, then added a second handler to just display an info box with the resulting ID generated:

{{qrySubregionAdd.data.result[0].id}} (note that the actual name of the insert query is qrySubregionAdd)

The code editor for the submit handler gives me a green highlight for {{qrySubregionAdd.data}}, but won't complete the code or accept the syntax if I try to append the .result[0].id. Seems I can't extract the result object here. Does every query (even an INSERT query) return a result, or do I have to include RETURNING in the syntax etc.?

[matth]

I tested an insert on supabase and retoolDB both returned a response.

Here is a js query that calls an insert sql query. Just did this for console.log, works the other way as well.

// insertJs 
const f = form1.data;

const insertRsp = await insert.trigger({additionalScope:{form:f}});

// a couple different ways
console.log(insertRsp);
console.log(insertRsp.result[0].id);
console.log(insert.data.result[0].id);

EDIT
This seems to work as well

[edwardhamlin]

Matth, thank you sincerely for going to the trouble to do this. I’ll plug it in and take it for a spin as soon as I’m back at my desk.

All the best -
E

[Tess]

Hi there!

Hope it goes well! For Postgres, as @matth mentions, it does look like successful inserts should return queryName.data.result[0].primaryKey, which you can view in the Output of the query or in the State tab for the query after it has run:

For the form clearing, are you using something like this? It seems to be working for me. It's possible that the form is clearing briefly, but then the default values are getting re-evaluated and added back in right after being cleared.

For updating the table to show the new record, you have to re-trigger the select query on success of the insert query:

[edwardhamlin]

Thanks, Tess, and matth - I'm getting there slowly but surely, with your help. I'll post an update if I still have questions, but you've given me a good leg up on things. I can read that result value now, which seems like the key to the kingdom.

[haj]

I'm late to this conversation, but you might want to look at the query event handlers for success and failure. In these handlers, you can retrieve the query's result array. So, in the success event handler for the query addRow, I can get the primary key ("id") that was created when a row was added:

    console.log( addRow.data.result[0].id );

You may also find useful this tidbit. If you have a table and a form in an App (i.e., on a page of your app), and the form has table.selectedRow as its data source, if you table.clearSelection(), all the fields in the form will be cleared. Then, enter data for a new record and run an insert query with the form.data.

I can provide more details, if you like.

[edwardhamlin]

Thank you, haj, that tidbit is good to know, indeed.

So, I have this working now - all but one thing. I can do the update fine, and the insert query does exactly what it should. But the final step is to select the newly-added row in the associated table component so that my linked data entry form will enable the user to enter the new subregion name. The simple script I wrote to do this last step - which is attached to the Success handler of the insert query - just isn't moving the table row pointer. The new record appears in the table, but is not selected.

I'm using the primary key lookup mode in table.selectRow(), and have verified that the passed key is indeed the PK of the newly created row. The key column (id) is correctly set as the Primary Key of the table component. I can't figure out why in the world the table component row isn't being moved.

Here's the insert Success script:

—--
var newkey;

// grab the new PK from the insert query - value is correct upon runtime inspection
newkey = qrySubregionAdd.data.result[0].id;

// refresh the query underlying the table
qrySubregions.trigger();

// Table now correctly shows the newly created row at the bottom (alpha sorted), with a null value for Name and the new PK as id. Looks just right.

// Now, try to move to the new row using a PK lookup in the table component; PK value is confirmed correct at runtime

tblSubregions.selectRow({mode: "key",key: newkey });

// That doesn't work - now there is no row selected in the table, and selectedRowkey evaluates to NULL.

Drat. So close...
—--
Any insights welcome...once I solve this one I promise I'll shut up.

[haj]

I would first try adding an await before your trigger:

await qrySubregions.trigger();

[edwardhamlin]

That did the trick! Brilliant, thanks.