Continuing the discussion from Retool App of the Week!:
I think I finally wrapped this app and I am very proud of it. Tried a bunch of different techniques to find the ones that finally worked best - I think I was pushing Retool's limits a bit with this one. It is a port of an internal tracking app I built using ExtJS some years back. If I was doing it newly in Retool it would probably look different but I am pretty used to the UX. It has 4 related datasets, 2 subtables and a subsubtable, (coming from an Azure Postgres instance) and all are fully CRUDed.
The two main datasets are displayed in a table as well as a form which is fully editable.
The next/prev button set moves the table selection forward or backward which automatically populates the form elements by setting their Default Values.
The main table is filterable - locally. No trips to the server.
This drop down offers different views and each is a different query to the database. Most views are relational and are too complex to be handled locally. All subsequent CRUD actions and local filtering are performed against the loaded view.
Some more data:
All records of all 4 datasets are loaded into their table and then all filtering is done locally using setFilter(). This is much faster than multiple round trips to the database as you move from record to record. Obviously updates, inserts and deletes require two trips to the server, one to perform the appropriate action and one to requery the current view.
When a record is inserted, I find that new record in the table and select it so it is visible in the form. Without a table.onLoad event it gets kinda hacky to do since there is no good way to determine when the table has reloaded. For updates, I leave the selection alone and it seems to work. For inserts I wait until the table.data.id.length is different than it was and then find the new row and select it.
Managing full CRUD on 4 linked tables along with some deterministic row selection makes the reactive flow very unpredictable. Queries were firing multiple times in the wrong order and it was not pretty. So I set nearly all queries to only run when triggered. Used no onSuccess queries and handled everything by brute force.
Maybe not pretty but here is my delete query:
const deleteSourceRow = async () => {
let recordCount=tblSource.data.sourceid.length
await qrySourceDelete.trigger() // Delete the record from Source table
await jsSetView.trigger({additionalScope: {skipSelect: true}}) // run the proper select query based on the current view
let loopIterations = 0
let newRecordCount
// Wait for the table to be updated. We should get one additional record with the insert.
do {
await new Promise(resolve => setTimeout(resolve, 1000))
newRecordCount = tblSource.data.sourceid.length
loopIterations += 1
} while ((recordCount === newRecordCount && newRecordCount > 0) && loopIterations < 20)
await jsSetSourceSelection.trigger({additionalScope: {sourceid: 0, assumeSameID: false}}) // Select the updated row if needed
await new Promise(resolve => setTimeout(resolve, 500)) // Wait a heartbeat
await jsSourceRowSelect.trigger() // trigger subtable setups
}
deleteSourceRow()
I've got one for update and delete - a little micromanagement in a sea of reactivity!
And for good measure, here is the app it is replacing:
