Add Record Creator to New Record

[pmjd]

Hello,

DB Version V.4.5-2021.06.12.00, Files Version V.4.5-2021.06.14.00

I'm looking to add to new records fields to record who created them

Code: Select all

sri_creator

and when

Code: Select all

sri_created

. Also to add when they are edited by who

Code: Select all

sri_updater

and when.

Code: Select all

sri_updated

I can create the when by modifying the SQL record for Created On to be a timestamp on creation. Not sure how too add the user name for who created the record? I guess it will be something to do with hash cookies but not sure how to achieve it.

I've been trying to impliment Janusz's fantastic solution on record simulataneous record blocking to add that feature for who and when a record was updated but each time I try to add the code the Save button no longer works.

I've tried adding the code (adjusting field names as required) as in the video (CheckEditStatus procedure, JS in form, and php in BS section) but to no avail.

I tried recreating the spices demo from the code on demo.nubuilder.cloud but no luck.

I downloaded the nutestCloud_timerecording.ver.2.2.sql file but it doesn't seem to have the CheckEditStatus procedure and nothing in the BS section? But this demo still manages to capture who created/edited the record and when.

I've also tried exporting the working spices table and forms from nutestCloud_timerecording.ver.2.2.sql into my own db, it no longer works

So a bit confused as to where to start. Have tried a good few things and a bit stuck, and suggestions would be most welcome.

Thanks,
Paul

[kev1n]

Hi Paul,

Does this help?
https://wiki.nubuilder.cloud/ ... g_Activity

[pmjd]

Hi Kevin,

Thanks for that, will have a look. It will be good for loggin purposes but I do really need to add someting to stop simulataneous record editing as well, so had hoped to impliment Janusz's solution.

Has anything changed in nuBuilder that would stop his solution working? I have the most up to date version, the version on Janusz's is an older version from March. I only ask because it works on his version but exporting and adding to mine and it no longer works.

Thanks,
Paul

[kev1n]

Can you post the link to Janusz's solution?

[pmjd]

The forum topic is here
https://forums.nubuilder.cloud/viewtopic. ... ing#p23524

The sql file was from this post
https://forums.nubuilder.cloud/viewtopic. ... 889#p23495

If I import the sql file the solution works, but it doesn't contain the Procedure and BS event code that is in the test.nubuilder.cloud site and youtube video

If I try to clone the form and associated table structure from this file into my own db it no longer works as the save button is inoperable.

[kev1n]

Are there any error message?

If you encounter an issue, be sure to check the developer console for errors by clicking the `F12` key on most browsers.
Also open nuDebug results (CTRL+SHIFT+D) and check your (Apache) server logs.

Make sure that getUser() is declared. (It's in Setup -> Header)

BS:

Code: Select all

// for temporary storage who updated
$rid = '#RECORD_ID#';
if ($rid != - 1) {
    $table = "calculation"; // table name
    $on = "cal_updated_on"; //updated on
    $by = "cal_updated_by"; //updated by
    $q = nuProcedure('CheckEditStatus');
    eval($q);
    unset($q);
}
unset($rid);

Procedure:

Code: CheckEditStatus
Description: Check if record was modifed by someone else

Code: Select all

$id = $table . "_id"; //record ID field name
$s = nuHash();
// on the form clone the field *_updated_by na *_updated_by_temp
$r = $s["record_id"];
$uon2 = $s[$on]; // from the form - status as at record opening.
$uby2 = $s[$by . "_temp"]; // from the form - status as at record opening.
$t = "SELECT " . $on . ',' . $by . " FROM " . $table . " WHERE " . $id . "=" . '"' . $r . '"';
$x = nuRunQuery($t);
$y = db_fetch_object($x);
$uon1 = $y->$on; // from the DB - current status just before saving.
$uby1 = $y->$by; // from the DB - current status just before saving.
$m_nok = "You can not save!!! This record was updated in the meantime by:<br>
         " . $uby1 . "  at  " . $uon1 . "<br>
         You can copy your data to Notepad for example for temporary storage.<br>
         Now you must leave this record without saving. After You can open it again and check the content.";

$on = ($uon1 == $uon2) ? 'OK' : nuDisplayError($m_nok);


if ($on == 'OK') {
    $by = ($uby1 == $uby2) ? 'OK' : nuDisplayError($m_nok);
}

unset($table, $on, $by, $id, $s, $r, $uon2, $uby2, $t, $x, $y, $uon1, $uby1, $m_nok, $on, $by);

Form's custom code:

Code: Select all

function nuBeforeSave() {
	$('#cal_updated_by_temp').val($('#cal_updated_by').val()).change(); //temporary field to store last user who modified that record
	$('#cal_updated_by').val(getUser()).change();
}

function getUser() {
   var user = nuUserName();
   if (user === '') { user = "admin";}
   return user;
}

And there must be a text object cal_updated_by_temp on the form.

I'd probably do it without these separate columns (cal_updated_by, cal_updated_on) but rather use the nulog column for that purpose.

[pmjd]

Thanks Kevin, it partially works now. The getUser() function was missing from the Header.

I can now add a record but once added the record cannot be edited by anyone. It comes up with the warning that the record has been edited in the meantime and displays the last user and timestamp info from when the record was created.

So something is not evaluating correctly but not sure what it is.

nuDebug doesn't report anything, as I suppose the function is working as intended to stop the record being saved if edited but evaluating the wrong variables or in the wrong way. Also there was no information in from nuDebug in the original problem, the save button was coloured to indicate a change had occurred but would not work.

[kev1n]

the save button was coloured to indicate a change had occurred but would not work.

This happened because the function getUser() was not defined and a JavaScript error was output to the developer console:

Code: Select all

Uncaught ReferenceError: getUser is not defined
    at nuBeforeSave (<anonymous>:5:24)
    at nuUpdateData (nuajax.js?ts=20210701130651:671)
    at nuSaveAction (nuform.js?ts=20210701130651:3614)
    at HTMLInputElement.onclick (index.php)

I can now add a record but once added the record cannot be edited by anyone. It comes up with the warning that the record has been edited in the meantime and displays the last user and timestamp info from when the record was created.

I will try to replicate the issue on my side.

[kev1n]

I created a new code that makes use of the nulog column. No other additional db columns are required.

To set it up:

1. Create a nulog column in your table.
2. Add a new Text Object (Access: Hidden) to your form with the ID of the nulog column.
3. In BS, add this code. Normally no modifications are required, it's a generic code. Just change the error message string in nuDisplayError()

Code: Select all

if(! nuHasNoRecordID()){
    
    $tLog = tLogData();
    $tTime = $tLog[1];
    $tUser = $tLog[2];    

    $cLog = cLogData($tLog[0]);
    $pTime = $cLog[0];

    if ($pTime !== $tTime) {
        nuDisplayError("Saving not possible. This record has been modified by <b>$tUser</b> in the meantime.");
    }

}

// Get current nulog data
function cLogData($log) {
    $jd = json_decode(stripslashes(nuHash()[$log]));
    $edited = isset($jd->edited);
    return array($edited ? $jd->edited->time : '', $edited ? $jd->edited->user : '');
}

// Get table nulog data
function tLogData() {
    $fd = nuHash()['nuFORMdata'][0];
    $r = nuRunQuery("SELECT `".$fd->table."_nulog"."` FROM ".$fd->table." WHERE ".$fd->primary_key." = ?", array("#RECORD_ID#"));
    $o = db_fetch_row($r);
    $jd = json_decode($o[0]);

    $edited = isset($jd->edited);
    $userName = '';
    if ($edited) {
       $r = nuRunQuery("SELECT `sus_name` FROM `zzzzsys_user` WHERE `zzzzsys_user_id` =  ?", array($jd->edited->user));
       $userName = db_num_rows($r) == 1 ? db_fetch_row($r)[0] : 'admin';
    }
    
    return array($fd->table."_nulog", $edited ? $jd->edited->time : '', $edited ? $userName : '');
}

[pmjd]

Wow that's amazing, thank you very much Kev1n! Your solution is a lot simpler to impliment.

Is it possible to display the data from the nuLog on the form? It would be very useful to display who was the last person to update a record and when.