Self-timestamping table

[patient_but_frustrated]

Hi All.

I am attempting to create a table that can be directly edited and that will then update the data table whose contents it is displaying. I need the following criteria to be met:

1. Must update upon closure of each amended cell.
2. Must update the 'update' timestamp for the record that has been updated automatically

That's all I want, how hard can it be?

Here's some stuff that works, but not together...

Snagupdate updates the timestamp happily whenever it is run from the 'run' button.

Snagupdate2 happily updates the contents of amended cells, writing the data to the database.

The table change handler is supposed to triger both of the above, but even though both queries run without incident the timestamp is unaffected.

My guess is that this is all about asynchonicity, but I am hoping that I can fix this very modest function expectation without a degree in Java...

I'm happy to be humiliated, 'cos that means there's an easy answer that I haven't thought of.

[ScottR]

In the second query snagupdate2 - you are not passing in {{moment()}}; why?

[patient_but_frustrated]

I'm not aware that I can combine record Updates with moment() and have it update the timestamp in the 'modified' field of the record whose cell has a new value.

If there's a syntax for that I'll be really grateful.

[patient_but_frustrated]

Bear in mind that the table change handlers trigger on each cell value change...

[ScottR]

Is there a reason you are running the Bulk update when the changeset is already run when the cells are edited?

[patient_but_frustrated]

Didn't realise that it was. Will do some digging on Monday and get back to you.

[patient_but_frustrated]

To be clear, are you saying that snagUpdate2 is redundant?

[ScottR]

Yes, it would seem so

[patient_but_frustrated]

Will let you know how it goes on Monday.

[patient_but_frustrated]

So, I have deleted snagUpdate2 and the table does not update. I think this is because, as I mentioned above, I want the table to update automatically when the cell contents change. I do not want the operator to have to click an 'update' button.

For this reason it is necessary to run an event handler that triggers when the contents of a cell change, and the only command that seems to be available is bulkUpdate.

As I stated earlier, sangUpdate2 works perfectly for this purpose. When navigating away from the edited cell, or when executing a carriage return within the cell, the change is written to the data table.

snagUpdate also works to push moment() into the 'amended' field of the record. Both of these queries do what they should do, except when they share the same trigger. I have tried using two change handlers, one for each fuction. I have also tried chaining them together, having one triggered by the 'on success' function of the other. Neither of these approaches works. In all cases the updated cell value is written back to the data table, but the 'amended' timestamp is not.

I will continue to poke about but I am running out of ideas...

[patient_but_frustrated]

Have just reinstated both queries and chained one to the other. Both ran successfully but sangUpdate2 that contains the bulkUpdate command takes 0.9s to run, and snagUpdate that performs the timestamp update takes 1.0s to run. I presume that this means that the timestamp update 'misses the boat' and is not performed.

This makes no sense to me as the timestamp update is a simple field update that works just fine in isolation. The only reason why this scenario fails has to be because both operations are triggered by the same event and the timestamp component loses the 'processor race'.

[patient_but_frustrated]

Tried this:

UPDATE snagging
SET
snag = {{ tableLegacy2.recordUpdates[0].snag }},
priority = {{ tableLegacy2.recordUpdates[0].priority }},
modified = {{ moment() }}
WHERE id = {{ tableLegacy2.recordUpdates[0].id }};

but get this:

relation "snagging" does not exist

[patient_but_frustrated]

Having tidied up a couple of ambiguous column titles, and sorted out an upper-case table name, this code finally works (big whoop):

UPDATE snagging
SET snag = {{ tableLegacy2.recordUpdates[0].snag }},
priority = {{ tableLegacy2.recordUpdates[0].priority }},
modified = {{moment()}}
WHERE snag_id = {{ tableLegacy2.recordUpdates[0].snag_id }};