How to Archive Deleted Records?

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
mohamed
Posts: 29
Joined: 2020-04-19 16:18

How to Archive Deleted Records?

Post by mohamed » 2020-09-17 17:55

Hello,

as newbie to AppGini and SQL ..

I would like to know How to save/archive Deleted records into a "Archive" table using AppGini ?
THANK YOU...
AppGini Pro 5.92 -
Calendar - Search Page Maker - Summary Reports - Mass Update - bizzworxx AppGini Helper JabaScript Library - bizzworxx Inline Detail-View - udemy course

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 1091
Joined: 2018-04-01 10:12

Re: How to Archive Deleted Records?

Post by pböttcher » 2020-09-18 19:08

Hi ,

you can use the before delete hook and save your record to an archive table.

mohamed
Posts: 29
Joined: 2020-04-19 16:18

Re: How to Archive Deleted Records?

Post by mohamed » 2020-09-19 11:28

Hello,
Thank you ...

A tough tip :), but will try to search the net for some possible codes, that I can review and try
THANK YOU...
AppGini Pro 5.92 -
Calendar - Search Page Maker - Summary Reports - Mass Update - bizzworxx AppGini Helper JabaScript Library - bizzworxx Inline Detail-View - udemy course

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 662
Joined: 2019-05-21 22:42
Contact:

Re: How to Archive Deleted Records?

Post by onoehring » 2020-09-22 16:16

Hi Mohamed,

this should actually be quite easy.
As pbötcher said - just intercept the delete using the hooks.
ag_root/hooks/tablename.php
function ...before_delete

Before the delete will be executed this function will be run. You can simply copy the current record (which ID you get in the variable $selectedID):
Read the record with $selectedID from your table
Write the record to your backup table
return true in the function to actually continue the deletion process.

You might also use the Audit Log (see my footer: viewtopic.php?f=4&t=1369&p=10407). This can log deletions as well (and give you some extra feature).

Olaf
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 815
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: How to Archive Deleted Records?

Post by jsetzer » 2020-09-22 16:40

The ideas and concepts named before will definitely work well.

A different approach which does not require additional, cloned tables is "soft delete" concept:

Add a column "is_deleted" (int, default=0, checkbox) and perhaps "deleted_on" and "deleted_by". Then, instead of deleting a record, just set "is_deleted" flag to 1 and cancel delete.

The "deleted" records will not be deleted but just marked as deleted. They will stay in the same db and table. History and references will stay consistent (integrity).

You can use filters, for example in TABLENAME_init function, to filter out "deleted" records (having is_deleted=1).

Or you can use permissions in membership system to hide those "deleted" records for non-admin users.

You can even use some jquery + CSS to show soft-deleted records for example in red or striped through.



Soft-delete is not better but different. As the other concepts, also soft-delete has advantages and disadvantages. All concepts require additional coding.
Kind regards,
<js />

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 815
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: How to Archive Deleted Records?

Post by jsetzer » 2020-09-22 16:56

Forgot to say: the "soft-delete" concept can be used with an "is_archived" column (instead of "is_deleted"). Filter all records where is_archived = 1 in _init() function. Those marked records will stay in db and integrity with related records (lookups) will be kept.

But if you need extra archive tables, as your question was about, you should follow the answers above.

Just wanted to describe a different approach than cloning into a different table and deleting the original record in case someone else stumbles upon this thread.
Kind regards,
<js />

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 662
Joined: 2019-05-21 22:42
Contact:

Re: How to Archive Deleted Records?

Post by onoehring » 2020-09-22 17:18

Hi,

good idea Jan. I want to add, that columns "deleted_on" and "deleted_by" are enough. You do not need an extra column "is_deleted". This would be redundant: A record is deleted, if it holds values in the "deleted_on" and "deleted_by" columns.

A quick reminder: Be sure, you follow privacy rules. You might not be legally allowed to keep deleted records - or you are forced to do so for a specific time.

Olaf
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 815
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: How to Archive Deleted Records?

Post by jsetzer » 2020-09-22 18:14

1[+2] columns vs. 1 column

Yes, from technical point of view you are completely right, Olaf. Thanks for emphasizing.

The primary idea of soft-delete is the is_deleted flag. As mentioned ("perhaps"), deleted_by and/or deleted_on are optional for some extra information. Of course, if you have at least one of them, others may become redundant from technical perspective. You can filter by "deleted_on IS NOT NULL" as well as on "is_deleted=1" (or vice versa).

However, I am used to add "is_xxxxx" flags for simple checkbox in UI and simple SQL queries plus some extra information in columns xxxxx_on and xxxxx_by. So, having all three columns may not be smallest possible solution but very practical over the years from different points of view.

Privacy rules

That's a good and important point, Olaf! The privacy rules or legal restrictions for deleting or keeping records are the same for all archive-concepts. Legal restrictions are not bound to technical implementation.
Kind regards,
<js />

mohamed
Posts: 29
Joined: 2020-04-19 16:18

Re: How to Archive Deleted Records?

Post by mohamed » 2020-09-29 09:54

Hello onoehring & jsetzer,
Thanks for the details. Will try the suggestions once I am done with testing the other features I am intending to use in AppGini.
THANK YOU...
AppGini Pro 5.92 -
Calendar - Search Page Maker - Summary Reports - Mass Update - bizzworxx AppGini Helper JabaScript Library - bizzworxx Inline Detail-View - udemy course

Post Reply