update and delete Append_Only Relations

usr338

Hello all,
Does anyone have any ideas on how to make a form to edit append-only relations?
What I mean by append-only relations are relations whose records are not edited or deleted and only the changed version of the record is inserted as new record, and these types of tables or relations usually include a date field to store the last change and a field to store the version number.
I tried using a before-update trigger and instead of updating a record, insert a new record with the new date and number in the table and returning the null or old value in the output of the trigger function, but with this method,after updating a record in the form, the input fields in the form are all blocked and the message of no access appears at the top of the page.( probably because Rei3 expect a record with old id and updated value of other fields not null record or old record!)
Does anyone have any idea how to achieve the desired result (inserting new record for update and delete)without using the buttons and manually executing the necessary commands for the operation and only using existing capabilities of Rei3?

gabriel

The best way to achieve this, afaik, is to block UPDATE actions on a relation via a relation policy, assigned to all or specific roles.

If you assign an empty set of IDs to a relation policy for UPDATE, you cannot alter any record of that relation. If you then create a record via form, after the save is executed, the form loads the newly created record in read only mode.

You´d still use a trigger to populate a datetime field and version counter - but other than that, the policy is enough. If you want your form to save and then immediately open a new record, you´d use a frontend function (after load event).

usr338

gabriel Hi
As you said by assigning empty set of IDs to relation policy for update , user with specified role in policy can not edit that relation.
but what i want is that user be able to update a record but instead of updating the record with updated values, a new one with user entered value, be inserted in table. (actually user don't know that what happens in behind)i always filter the relation to show the last version of each updated record.

gabriel

That is an interesting use case. Instead of returning a new record to edit, why not keep editing the same record and writing the changes to another or even same relation (with versioning and timestamps). For the user, nothing would change and you get all your versions.

If you really need to "edit" a record, which creates a new one, which then needs to be loaded, you would have to redirect the user to that new record after save:

User opens a record, makes changes and saves.
Backend trigger creates new record.
Form reloads initial record after save (default behaviour after save).
Form function (event: after load) calls a backend function to check if there is a newer version of this record. If yes, the ID is fetched. The form function then redirects (eg. opens the same form with the new record ID).

usr338

gabriel
Thank you Gabriel for solution, you said:

Instead of returning a new record to edit, why not keep editing the same record and writing the changes to another or even same relation (with versioning and timestamps).

actually because :
• Simplicity: It’s simpler to manage and query data from a single table.
• Consistency: All data, both current and historical, is in one place, which can make it easier to maintain consistency.
◦ Efficient Joins: Since all versions of an entity are in the same table, joining with other tables can be more efficient.
◦ Smaller Footprint: Less storage needed compared to separate tables.
and most important of all is that i want to hold referential integrity between data easily.each record in tables should refer to proper version of records in other tables.(updated record are new version of old record and old record in other tables should refer to old version)

unfortunately solution doesn't work for me! because fetching and returning the new record ID doesn't cause the redirect in in front-end! (may be i didn't understand how to do that)
by the way when i insert new record in update-trigger , i get an error in fornt-end(but record inserted correctly) :
An error occurred, Error: expected 1 row, got: 0
valueSetByRows http://localhost:11683/comps/form.js
get http://localhost:11683/comps/form.js
promise callbackget http://localhost:11683/comps/form.js
set http://localhost:11683/comps/form.js
promise callbackset http://localhost:11683/comps/form.js
VueJS 4
trigger http://localhost:11683/comps/button.js
VueJS 36

gabriel

I am sorry, but I think you misunderstood the approaches I´ve outlined.

In the first you would create a new record via INSERT/UPDATE trigger in another OR the same relation - it does not matter where. You would then still return the original record, which would continously be edited, while the updated version of this 1 record is continously written away on change - to the same relation if you prefer.

In the second approach, you would create a new record inside an INSERT trigger but do nothing else, then (when the form loads the original record), forward the user to the newly created record via frontend function.

In neither case, would you try to return a different record in the trigger function.

usr338

gabriel hi again,
so said:

In the second approach, you would create a new record inside an INSERT trigger but do nothing else

did you mean UPDATE trigger? because user will update current record and new one will inserted instead.

then (when the form loads the original record), forward the user to the newly created record via frontend function.

how to forward user to new record? do you mean opening a new record in form using instance.form_open function ?

gabriel

usr338
No, I meant INSERT trigger. In the second approach, the user always creates a new record. Then on save the INSERT trigger creates another record. The form loads the initially created record (as is default after save). You then execute a frontend function to redirect the user to the record that was created in the INSERT trigger. And yes, this would be app.form_open().

I would definitely prefer the 1st approach (updating the same record and writing copies of that record away) - but you asked for the solution to create new records instead. That is possible, though much more complicated, with the 2nd approach.

usr338

gabriel
Thank you for your time, actually i can not use 1st approach, because records should never updated and only new one that represent the changes should be created (append-only). by updating existing record other references to this record will point to changed (updated) record that is not appropriate.

There is still one unclear point, and that is, when the user selects a record through the list, for example, and the form is opened for editing, after pressing the save button, how is the insert trigger executed? because REI3 sends the update request !

gabriel

If you edit a record, there is no INSERT trigger. From your descriptions I assumed you only ever wanted to create new records, never edit existing ones. If you need to also edit existing records, you´d need a INSERT+UPDATE trigger. Which in both cases would create a new record.

Then the form (after save) reloads the newly created or updated, original record. After which you then forward the user to the record that was created in by the backend trigger via app.open_form().

usr338

gabriel Thank you Gabriel.I'll try the solution.

usr338

Hi,
I tried the 2st solution, only problem remains is that when user edit current record and press save. i forward user to new form to show inserted record (created by before update trigger) using app.form_open(), the old one still exist. i tried to close unwanted form using app.form_close() but it cause all opened form to be closed .
In other word i want only latest form that shows the inserted record in trigger,be opened.

gabriel

When you open the same form but with a different record, the form will be replaced - unless you specify that you want another form opened as pop-up or new tab.

app.form_open(FORM_ID_STRING, RECORD_ID_NUMBER, NEW_TAB_BOOL, POP_UP_BOOL, MAX_HEIGHT_NUMBER, MAX_WIDTH_NUMBER).

Make sure that both NEW_TAB_BOOL & POP_UP_BOOL are set to false.

usr338

gabriel Amazing! Thanks a lot Gabriel, you saved me from redesigning my relations 🙂
I always set POP_UP_BOOL to true (I thought that for the form to be opened as a popup, its value should be true. Even if the form is opened as a popup by its own parent.)

gabriel

Glad it worked out 🙂