Heure de dernière modification d'un enregistrement

Bonjour,

Est ce que quelqu’un sait comment retrouver l’heure de dernière modification d’un enregistrement, sachant qu’aucun champ n’a été prévu pour ça?

Si le journal est actif, c’est le seul moyen qui me vienne à l’idée…

Malheureusement, il ne l’est pas.

Merci quand même pour le dérangement.

Bon week end.

Je dirais que c’est anormal ;-). Quand on a un fichier de données qui s’endommage définitivement, la restitution automatique proposée par 4D est vraiment bien fichue. Un utilisateur correctement formé est capable de remonter une base, avec ça.

Pour le “traçage” d’enregistrements, ça n’a strictement aucun intérêt de faire ce qu’on voit trop souvent : dans toutes les tables, un champ création, un champ modification. D’emblée on élimine la possibilité de savoir ce qui se passe entre les deux. Ensuite il faut que ce soit date ET heure, sinon la chronologie est floue à la journée. Si on veut savoir “qui”, re-ajout de champs. Enfin, le jour où on doit résoudre un problème, on découvre que ça ne répond en rien à ce problème.

Par exemple, un montant ou date de pièce comptable qu’on retrouve changé alors qu’on pensait l’avoir “blindé”. Le dernier utilisateur n’a fait que modifier un commentaire, il n’a touché ni la date ni le montant : le traçage est incapable de résoudre le problème.

Autre exemple, la suppression : comment lit-on le champ “derniereModification” d’un enregistrement qui n’existe plus ? Réponse : on remonte des fichiers de données plus anciens en espérant tomber sur le bon :grimacing:

C’est plus efficace d’avoir une table “historique” où l’on note ce qui importe pour le métier : montant ou date de pièce comptable qui change, pièce comptable supprimée. Et c’est beaucoup plus rapide à implémenter.

Bonjour Arnaud,

Je suis bien d’accord avec tout cela, sauf que c’est un concours de circonstances qui a fait qu’il n’y avait plus de Journal ni même de sauvegarde automatique.

Dans ce cas précis, les opérations ont eu lieu hier et une personne a par erreur cliqué sur un bouton, modifiant automatiquement une série d’enregistrements.
Le client voudrait savoir ceux qui ont été modifiés, sachant qu’il connait le premier de la liste.

Ce n’est pas très simple mais…

Hello Xavi,
If you don’t need to access the create and mod info using SQL here is a scheme I’ve been using for tracking create/modify dates and users since v15. It requires a single object field instead of 4 typed fields. This code is from a v17 project.

  //  meta_update (ptr)
  // $1: pointer to object field of a table
  // ------------------
  // Purpose: utility for updating the meta info - usually from a trigger
C_POINTER($1)

Case of 
	: (Test semaphore("suppressTriggers"))
	: ($1=Null)
	: (Type($1->)#Is object)  //  only valid for object fields
	Else 
		If ($1->=Null)
			$1->:=New object
		End if 
		
		C_OBJECT($o)
		$o:=$1->
		If ($o.meta=Null)
			$o.meta:=New object
		End if 
		
		Case of 
			: (Trigger event=On Saving New Record Event)
				$o.meta.created:=New object("name";Current user;"date";String(Current date;ISO date;Current time))
				
			: (Trigger event=On Saving Existing Record Event)
				If ($o.meta.modified=Null)
					$o.meta.modified:=New object
				End if 
				
				If ($o.meta.modified.n=Null)
					$o.meta.modified.n:=0  //  count of number of changes
				End if 
				
				$o.meta.modified.name:=Current user
				$o.meta.modified.date:=String(Current date;ISO date;Current time)
				$o.meta.modified.n:=$o.meta.modified.n+1
				
		End case 
End case 

To implement it you need to enable the trigger of the table. Paste in the method into the trigger with a pointer to the object field.

meta_update (->[Reg_Code]d)

The content of the object field looks like:

{
	"meta": {
		"created": {
			"name": "Designer",
			"date": "2020-05-24T13:14:50"
		},
		"modified": {
			"n": 4,
			"name": "Jxxx Hxxx",
			"date": "2020-05-27T16:58:19"
		}
	},
	"att": {},
	"sum": {},
	"last_date": "0000-00-00T00:00:00.000Z"
}

In this example the .d object field is used for other things as well. The meta method doesn’t affect anything else there. The modified.n property counts the number of times the record is modified.

I’ve tried a number of schemes for tracking create/mod data and this is the best one I’ve come across. Being in the trigger it catches any changes regardless of where those originate. I prefer this to putting the same code in methods outside of the trigger.

For situations where you may want to not run the updates (for example, importing records from another source and you want to set the create/mod infom or set it directly use the suppressTriggers semaphore.

1 Like

Hello Kirk.

Thank you very much for your answer.

I will try tomorrow and write to you about the result.

Best regards.

APPLI’XM

Xavier MAUCUER

Logiciels de Gestion

Tél: 06 77 72 99 19

74260 - LES GETS

Email: xavier.maucuer@orange.fr

appli-xm@orange.fr

Hello Kirk,

I will try your method later, because actually the version of the software is the 14.6 th, so without object field.

But, I will use your method when the software will be converted.

Thak you for you help.

Best regards.

APPLI’XM

Xavier MAUCUER

Logiciels de Gestion

Tél: 06 77 72 99 19

74260 - LES GETS

Email: xavier.maucuer@orange.fr

appli-xm@orange.fr

@MAUCUER.Xavier
Bof : avec une table [historique] pas besoin d’attendre la conversion, ça prendra 10 fois moins de temps et ça servira à quelque chose.

@Brooks.Kirk
(I translate, hoping it will be clearer than the translator)
I’m sorry I have a very intolerant opinion on this subject… I don’t like databases where “the record embeds its own history”. The story always starts with a customer who comes one day and says “I have a bunch of records that disappeared”, or “that invoice was send with that amount one month ago, and now it’s changed”.

1/ anywhere but not in records

  • each record is heavier, whatever the storage (4 fields, a uuid with encoded values, one object field…)
  • an object field is not a “small” field
  • you can hardly store more than created/modified
  • forget “deleted by” or “modified between created and last modified”

2/ generic is talkative and useless
From my experience, it’s it’s mostly uninteresting to keep trace of changes in a majority of fields (“comments” in a record [CUSTOMER], for example). What matters is the history of changes in some records/fields, depending on what the application handles. For example:

//trigger INVOICE
case of
:(Trigger event=On Saving New Record Event)
//log creator, amount, date
:(Trigger event=On Saving Existing Record Event)
//if amount or date are changed, log who, old/new amount, old/new date
:(Trigger event=On deleting Record Event)
//log who, when, amount, date, customer
end case

Most of the time, such specific code is very easy to write and maintain.

when we have to silence the generic code is that it talks too much… :wink:

Hi Arnaud,

I don’t disagree. It depends on what you are saving with the record and why. Including fields for created, modified dates, times and users is pretty common but all over the place in how it’s implemented. That is the primary purpose of the meta scheme I outlined. Note it’s not making a log of access, just keeping track of the last one.

Logging each access isn’t a good idea because of the points you make. A separate log table, that can be purged or exported, is a much better solution for that. Or logging to a disk file - as a few friends prefer.

I may not have been clear about the nature of the object field I referenced. In this case it’s used for a number of things one of which includes holding the meta data. The other properties are neither logs nor anything to do with access. It’s just convenient, for me, to use that object field for them. So there is no continual record bloat in my example. In this case those are some lists specific to the record, configuration, and so forth.

Using the semaphore came about when incorporating records from another source and there was the desire to have them retain their own creation date. Another use is when an update on old records is required. Maybe you want to keep the existing modified info? Maybe not. It’s nice to have a choice. Also note that the Test semaphore command doesn’t create a semaphore - just looks to see if there is one.

Finally a feature I like a lot about this scheme is because it’s consistent I can copy and paste the form object for displaying the create/modified data into any form where the table uses the scheme without having to specifically alter it for that table.

I may be overkill or stingy with the bytes, but in my experience, these fields most of the time did not helped me to solve “critical situations”, like values changed or deleted records and nobody-knows-who-or-when. If it’s in a huge table, performances will be affected. On the opposite, a log (I log to a table, when it’s old I export to a file and purge) is really helpful. The only concern here is to identify what is critical before it happens and to put it in the trigger. It does not prevent to consider that modifying any field must be logged.

If have a “historical” base where all tables start with PK, then 4 fields (created user+date, modified user+date), and a bunch of code and interface to query on; last month I asked users “do you use this?” and 100% reply was “never”.