How to insert/update automatically some values, while letting users modify others?

unibocconi-ltcc

Description of problem:
Hi, we have an Editor license.

I have a table that has some columns referred to who and when inserted/modified that row ("who" is the user logged, "when" is a timestamp). Is it there a way to "calculate" those values in the backend (or in the frontend via some hidden columns)?

These columns have an auditing purpose, so I cannot trust any user input.

rf1234

Here is a little excerpt from my Editor code:

Field::inst( 'ctr.update_time' )->set( false ),
Field::inst( 'ctr.updater_id' ) ->set(Field::SET_BOTH)
                                ->setValue( $_SESSION['id'] ),
Field::inst( 'ctr.creator_id' ) ->set(Field::SET_CREATE)
                                ->setValue( $_SESSION['id'] ),
Field::inst( 'ctr.updater_id AS ctr.updaterName' )->set( false )
    ->getFormatter( function($val, $data, $opts) {
        return getUserName($val);
    }),                     
Field::inst( 'ctr.creator_id AS ctr.creatorName' )->set( false )
    ->getFormatter( function($val, $data, $opts) {
        return getUserName($val);
    }),

I want to read the update_time ("when") but I don't want to set it with Editor because the database does that for me automatically. (field definition contains "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")

I want to set updater_id ("who") for create and edit.

I want to set creator_id ("who") only for create.

$_SESSION['id']: id of the user logged in.

In addition I pass updater_id and creator_id into a getFormatter to return the user's full name. But that is optional of course.

Done!

unibocconi-ltcc

Thanks @rf1234, this is exactly what I was looking for. On the client side, what do you do with the updaterName, creatorName, update_time? Do you show them as read-only or do you completely hide them?

rf1234

I don't show them in Editor because they aren't editable. But of course you can show them in the data table or have "update_time" as a hidden column for ordering purposes only. Up to you!

Here is an example in which I am not using "update_time" at all on the client side but render the column using a custom renderer that uses updaterName and creatorName. I make sure those fields are called the same in all of my dozens of data tables.

{  data: "vat_question.update_time",
    render: function (data, type, row) {
        return renderUpdaterCreator(row.vat_question, true);
    }
}
..............
function renderUpdaterCreator(tableRow, withName) {
    var str = '';
    if (lang == 'de') {
        str = moment(tableRow.update_time).format('DD.MM.YYYY, HH:mm [Uhr]');
        if ( withName ) {
            str += ( ', ' + tableRow.updaterName + ';<br>Erfasser: ' + tableRow.creatorName );
        }
    } else {
        str = moment(tableRow.update_time).format('DD/MM/YYYY, @ hh:mm A');
        if ( withName ) {
            str += ( ', ' + tableRow.updaterName + ';<br>Creator: ' + tableRow.creatorName );
        }
    }
    return str;
}

Here are the rendered fields in both of my user languages: