Adding date("Y-m-d H:i:s") or CurrentUserId() (v9)

[fbachofner]

In virtually every MySQL table I have the following fields:

Record_Last_Modified (timestamp)
Record_Modified_By (int)
Record_Added (timestamp)
Record_Added_By (int)

MySQL helps a little in that the FIRST timestamp in a table is automatically updated on record edits (even without a trigger), so the Record_Last_Modified field is always correct.

Here are the remaining issues and challenges:

1. I would like to NOT display these fields in the Edit and Add pages -- ONLY on LIST and VIEW pages if possible. [Alternatively, no user edits possible on edit and add pages.]

2. This seems to cause problems when adding or editing records, because these fields are then apparently not added to the application logic (i.e. when NOT checked "on" for the Add and Edit pages). Adding "date("Y-m-d H:i:s")" or "CurrentUserId()" to the default value simply does NOT work.

3. Using Auto-Update_Value is NOT appropriate for Record_Added and Record_Added_By fields because they should NOT update during an EDIT.

4. Furthermore, using Auto-Update_Value for Record_Modified_By (where it is appropriate to fire for each edit) doesn't work -- probably because the field is not displayed on the edit page (see #2 above).

Presumed solution:

It seems I should be able to add some code to "Server Events; Table-Specific; Add/Copy Page; Page_Load" or similar, but nothing I do results in anything but errors.

Webmaster's response at
http://www.hkvforums.com/viewtopic.php? ... y+add+user
seems partially on point, but I am still lost.

Any help would be truly appreciated.

[mobhar]

fbachofner wrote:

Here are the remaining issues and challenges:

1. I would like to NOT display these fields in the Edit and Add pages --
   ONLY on LIST and VIEW pages if possible. [Alternatively, no user edits
   possible on edit and add pages.]

Simply define the CurrentUserName() or CurrentUserID() and ew_CurrentDateTime() functions from "Auto-Update Value" under the Fields setup of the fields. This will hide the fields in the "Edit" page. Meanwhile, for the "Add" page, simply enter those functions in the "Add" -> "Default Value" column also from Fields setup.

1. This seems to cause problems when adding or editing records, because
   these fields are then apparently not added to the application logic (i.e.
   when NOT checked "on" for the Add and Edit pages). Adding
   "date("Y-m-d H:i:s")" or "CurrentUserId()" to
   the default value simply does NOT work.

See the answer of question 1 above.

1. Using Auto-Update_Value is NOT appropriate for Record_Added and
   Record_Added_By fields because they should NOT update during an EDIT.

See the answer of question 1 above.

1. Furthermore, using Auto-Update_Value for Record_Modified_By (where it
   is appropriate to fire for each edit) doesn't work -- probably because
   the field is not displayed on the edit page (see #2 above).

It should work. I often doing this successfully.

[danielc]

mobhar wrote:
fbachofner wrote:

Here are the remaining issues and challenges:

1. I would like to NOT display these fields in the Edit and Add pages --
   ONLY on LIST and VIEW pages if possible. [Alternatively, no user edits
   possible on edit and add pages.]

• If you do not want the field to display in Edit and Add pages, you can uncheck the field at Edit Page [Edit]/Add Page [Add]. Read Field setup at help file.

1. This seems to cause problems when adding or editing records, because
   these fields are then apparently not added to the application logic (i.e.
   when NOT checked "on" for the Add and Edit pages). Adding
   "date("Y-m-d H:i:s")" or "CurrentUserId()" to
   the default value simply does NOT work.

1. Using Auto-Update_Value is NOT appropriate for Record_Added and
   Record_Added_By fields because they should NOT update during an EDIT.

• If you do not want to use Auto-Update value, you can add your value in Row_Inserting server event:
  $rsnew["YourField"] = ...;

[fbachofner]

Thanks danielc

So here's what I tried:

// Row Inserting event
function Row_Inserting($rsold, &$rsnew) {
// Enter your code here
$rsnew["Record_Modified_By"] = CurrentUserId();
$rsnew["Record_Added"] = date("Y-m-d H:i:s");
$rsnew["Record_Added_By"] = CurrentUserId();

// To cancel, set return value to FALSE
return TRUE;

}

the error message I get when I try to add a new record is

"Failed to execute SQL. Error: Column count doesn't match value count at row 1"

Any ideas?

[mobhar]

Error: Column count doesn't match value count at row 1" means that you are trying to insert a record that contains several fields but the destination table indicates that the number of fields of the inserted record does not match with the number of fields in the destination table.

Simply insert this code:
$rsnew["Record_Last_Modified"] = date("Y-m-d H:i:s");

Before this:
$rsnew["Record_Modified_By"] = CurrentUserId();

[fbachofner]

Hi mobhar

mobhar wrote:
Simply insert this code:
$rsnew["Record_Last_Modified"] = date("Y-m-d H:i:s");

Before this:
$rsnew["Record_Modified_By"] = CurrentUserId();

So, I did that [I also removed the code relating to $rsnew["Record_Added_By"] = CurrentUserId(); to eliminate another point of complexity].

SAME ERROR MESSAGE: "Failed to execute SQL. Error: Column count doesn't match value count at row 1"

Also, I checked whether having the fields checked "on" in the add page makes a difference. On or Off, same error message.

Any other ideas? Thanks.

[fbachofner]

I just discovered the "code repository" and some samples there.

It seems to imply the correct way to do something like this is such:

$MyResult = ew_Execute("INSERT INTO MyTable (Field1, Field2, Field3) VALUES (Value1, Value2, Value3)");

This, of course, is quite different from what was proposed as a solution here on the forum . . .

[fbachofner]

I have added MySQL triggers to automatically populate the Record_Added field on each table.

This leaves me with only one issue:

how to add CurrentUserId() to Record_Added_By field

1. WITHOUT showing the field (or allowing edits) on Add or Edit page
2. WITHOUT using the "Auto-Update Value" feature (the field should NOT update on edit).

I hope this will make a solution much easier as most of the logic is now handled by MySQL.

[mobhar]

It seems that you have excluded some fields from "Fields" setup under the "Add" column. Make sure you have included the field(s), especially those which are mandatory.

[fbachofner]

fbachofner wrote:
This leaves me with only one issue:
how to add CurrentUserId() to Record_Added_By field

D'Oh!! Thanks to HKVStore tech support I now know that CurrentUserID() is case sensitive.

Please note that in this entire thread we have been using "CurrentUserId()" . . . but the D needs to be upper case like this: CurrentUserID() !

Perhaps MORE embarrassing, in one table's DDL (the one in which I was testing, naturally) I had accidentally spelled a field name "Record_Modified-By" (Modified HYPHEN By) instead of "Record_Modified_By" (Modified UNDERSCORE By).

Meanwhile, my custom code in PHPMaker had it "correct" (referencing the field with an underscore) because that's how I always type it . . . except when I defined my test table! Obviously this mismatch in the field reference caused a significant issue.

Thank you all for your help!