Automatically change a value when a record detail is "opened"

[kgeorge]

Good Day,

I am hoping someone can help me with this.

Is there a way to cause a field to automatically change when a record
detail is "opened".

I have a table into which records are inserted directly
to the DB. They are inserted with a field called job_status that has a value of
"NEW". I would like to have that job_status to automatically change to "Bot" when the record
is accessed the first time. Additionally, at the same time I would like a date field
filled with today's date BUT I don't want the date to change once it is set.

I would appreciate if someone could show me how to code this and where to put it.

Thanks a million!

[jsetzer]

I would like to have that job_status to automatically change to "Bot" when the record
is accessed the first time.

Unfortunately, there is no TABLENAME_before_view($selectedID, &$data, ...)-hook which I've already suggested to Ahmed several years ago.

This is what I usuallly do:

Alternative (1)
Update records in TABLENAME_init() hook:

Code: Select all

$sql = "UPDATE `tablename` SET `status`=100 WHERE status IS NULL";
sql($sql, $err);

In your case:

Code: Select all

$sql = "UPDATE `jobs` SET `job_status`='Bot', `your_date_column`=now() WHERE status='NEW'";
sql($sql, $err);

Advantage
Very easy to implement and to change. Full control.

Disadvantage
I know this will take place whenever any user visits table view or detail view of TABLENAME, but I accept minimal performance loss as there are only very few new records, if any at all.

Notes

• If you cannot rely on status='NEW', consider adding another column like is_initialized (default: NULL), then $sql = "UPDATE `tablename` SET `status`=100, `is_initialized`=1 WHERE is_initialized IS NULL";
• If records get inserted from outside AppGini, remember to set_record_owner() or users may not see those.

Alternative (2)
Create a PHP-script which does the job an call it periodically (cron) or after insert in the (external) data-creating app.

Alternative (3)
Create a database trigger on after insert, see https://dev.mysql.com/doc/refman/8.4/en/triggers.html for MySQL.

Alternative (4)
In hooks/TABLENAME-dv.js use JavasScript to change jQuery('input[name="myField"]').val('Bot'); after finding that this field's value equals 'NEW'. Initializing your "date"-field with today's date will be more difficult, I guess, because by default AppGini uses three different <select> controls for year, month, date. As I am not doing such stuff, perhaps you can search this forum or someone else can join in.

For sure there are more ways to handle such situations. I really prefer Alternative (1) over the years due to transparency, simplicity, (immediate) testibility and maintainability.. Hope this helps.

[kgeorge]

I got a support reply from Ahmed that said to use tablename_dv hook and directed me to this:

tablename_dv()

Called when a user requests to view the detail view (before displaying the detail view). If you open the generated hooks/tablename.php file in a text editor (where tablename is the name of the concerned table), you can see this function defined as follows:


function tablename_dv($selectedID, $memberInfo, &$html, &$args) {


Could I use what you wrote above:

Code: Select all

 $sql = "UPDATE `jobs` SET `job_status`='Bot', `your_date_column`=now() WHERE status='NEW'";
sql($sql, $err); 

to accomplish this?

Thanks!!!

[jsetzer]

Perhaps I have misunderstood PHP code or missed something: From my experiences in the past, form's data will not be reloaded AFTER TABLENAME_dv-hook. But I may be wrong. in TABLENAME_dml.php I cannot see any reload after hook-call. This may work for lazy loaded fields (like lookups).

Could I use what you wrote above:

Without having tested, using my script call will affect all related records, yes, but I guess, on first load of this record it will still show the data BEFORE update. Probable reason: this record has been loaded BEFORE your script call and will NOT be reloaded AFTER your script call.

Just test it and please report back your findings. You need to carefully test it with newly created records. I doubt this will work on very first display of a new record.

[jsetzer]

I just did a small test:

Just added a "counter" field, generated app, reloaded, all values are NULL by default:

chrome_S7pUlpmE7j.png (9.86 KiB) Viewed 12592 times

Then I added the update-SQL-command in file hooks/modules.php, function modules_dv(...):

Code: Select all

function modules_dv($selectedID, $memberInfo, &$html, &$args) {
  if ($selectedID)
    sql("UPDATE modules SET counter=ifnull(counter,0)+1 WHERE id='{$selectedID}'", $err);
}

Then I selected one record which opens the details view:

chrome_XW8vwGVNvE.png (11.17 KiB) Viewed 12592 times

As you can see, counter is still showing NULL (not 1), while, at the same time, database already holds the updated value 1:

chrome_o2LAc4hq6d.png (25.54 KiB) Viewed 12592 times

This is exactly what I expected and what I experienced in the past.

After reload:

chrome_3lKedzopNY.png (1.02 KiB) Viewed 12591 times

Showing 1 although we already have 2 in database.

So, when reloading the same record, this will repeat over and over: AppGini loads value from database, which is 1 now, then updates database (which results in 2), then displays 1.



Summary

• Database changes in TABLENAME_dv hook will not affect the currently displayed record ($selectedID).
• I still recommend one of the alternatives given above. I prefer the TABLENAME_init-hook-alternative.
• I still think a TABLENAME_before_view($selectedID, &$data, ...)-hook would help us in many scenarios.

Just my 2 cents.

[kgeorge]

Good day,

First let me say thank you very much for your time in investigating this. I must admit, my skill level to work in the guts of this is limited.

Regarding your test, let me see if I totally understand what you found.

You said you added update-SQL-command in file hooks/modules.php, function modules_dv(...)
In the above does modules.php / modules_dv = my table name?

Code: Select all

 function modules_dv($selectedID, $memberInfo, &$html, &$args) {
  if ($selectedID)
    sql("UPDATE modules SET counter=ifnull(counter,0)+1 WHERE id='{$selectedID}'", $err);
}

When the record was opened, the counter field showed null, BUT the DB had been updated to 1. Is that correct? Then after you refreshed the record the counter field displayed 1.

Assuming I understand what you found correctly:

- If I have a python program that scans the DB every 5 minutes looking for a job_status of "Bot", will the program will pick up the record because the DB has the updated value even though it does not display - is that correct?

- if I have a summary count box for "Bot on the homepage will it count the record that was changed by the SQL Query?

If I use:

Code: Select all

$sql = "UPDATE `jobs` SET `job_status`='Bot', `campaign-start-date`=now() WHERE status='NEW'";
sql($sql, $err); 

the date would only change if the status starts out as NEW?


My apologies for sounding like I am stumbling thru this but I am trying make sure I have a complete grasp of what I am doing. Thanks again for all of your help!!!

[pbottcher]

Hi,

there would be another option that you might what to try if you need to update your record in the DV, and have it displayed.

Add to the _dv hook the code like:

Code: Select all

if ($selectedID) {
	$tn='TABLENAME'  // your tablename
	if (!isset($_SESSION[$tn])) {
		$_SESSION[$tn]=1;
		$sql="YOUR SQL STATEMENT";
		sql($sql,$eo);
			$html=TABLENAME_form($selectedID);  // your Tablename	
	}
	else {
		unset($_SESSION[$tn]);
	}
}

Like this the DV should show the correct information.

[jsetzer]

You said you added update-SQL-command in file [...]

Did you try recommended alternative 1?

I still recommend fully working alternative 1: update new records in TABLENAME_init()-function. This will keep database AND detail view display AND table view display in sync, also external data readers will fetch identical info. Easy to implement, easy to change. This solution works fine.

Pure _dv alternative will not work accurately.

If perfomance doesn't matter, pbottcher's solution should also work but I think this should require about double loading time.

Why not try?

[kgeorge]

Thanks to all for the help!

Jsetzer, as for your recommendation of Alternative 1, if I run that query in the jobs_init, wouldn't that update ALL of the "New" records at once? Each record needs to stay NEW until a user opens it. I'm a novice so I may be missing something.

[kgeorge]

Hi,

there would be another option that you might what to try if you need to update your record in the DV, and have it displayed.

Add to the _dv hook the code like:

Code: Select all

if ($selectedID) {
	$tn='TABLENAME'  // your tablename
	if (!isset($_SESSION[$tn])) {
		$_SESSION[$tn]=1;
		$sql="YOUR SQL STATEMENT";
		sql($sql,$eo);
			$html=TABLENAME_form($selectedID);  // your Tablename	
	}
	else {
		unset($_SESSION[$tn]);
	}
}

Like this the DV should show the correct information.

Thanks - are you saying use the code above and replace "YOUR SQL STATEMENT" with my Update query? Does _form go after my table name? I have not seen the _form before.

[jsetzer]

There are multiple ways for both scenarios (a) while opening (b) after opened the first time.

If you need data change on first opening (before first display) and you don't care fabout longer loading time, you should go pbottcher's way.

[pbottcher]

Hi,

yes indeed, the _form is an internal AppGini function that you need to call.
For the double loading time, that is indeed correct, but as it happens on the server side, I would not think that this will play a significant role, unless you have a very complex form.

Did you try it?

[kgeorge]

No I have not yet but am planning to. I will post my results when I do!

Thanks for all of the help!