2. PHPRunner General questions
  3. Logical delete
This topic is locked

Logical delete

 		9/16/2008 6:03:01 AM
PHPRunner General questions
M
 mattd1 author

Hi all, newbie Matt here.
I've searched and can't find anything for this but I am sure it's not new - if you can point me to the relevant thread it'd help me a lot!
Just in case it is a new topic ...
I have a table of users who are linked (FK) to lots of other things in the system. When a user leaves employment I want to retain the fact that they were here, and did X, Y, Z etc.
However, I want to prevent them from being selected in lookups. I still want them displayed (e.g. in audit "created by" fields etc in pages and in reports), I just don't want them selectable for any new stuff.
So, I plan to have a "deleted" tinyint on my table, defaults to 0, then on Delete I would update this to 1, rather than actually deleting.
Then in any place where users can be selected, e.g. assigning actions to them in a workflow (Add Action), I want the Lookup to be WHERE deleted=0.
Does this seem sensible?
Also, in edit pages, should I (how?) also in the Lookup code allow WHERE (deleted=0 OR (deleted<>0 and userid=[$?????])) - where [$?????] is the current value held in the FK field, so that the current ("deleted") user shows up and can be left as is?
Any help/pointers gratefully received!
Thanks.
Matt
PS. I expect to apply this principle to lots of other reference data tables in the system, like country, currency, role ... etc.
J
 		Jane 9/16/2008

Hi,
to replace delete action with update use Before delete event on the Events tab.

Here is a sample:

global $conn;

$strUpdate = "update TableName set deleted=1 where ".$where;

db_exec($strUpdate,$conn);

return false;


I'm not sure that I understand you second question.

Do you want to add where clause to the lookup query?
M
 		mattd1 author 9/16/2008

Thank you Jane.
Yes, I want to add a WHERE clause, however it's really the notation for it that I'm wondering about.
This is easy: WHERE deleted=0;
What I would like is (only a little) more complicated:
Let's say it's a lookup to the User table, from the Edit Action form, linking ActionUserID=UserID, and showing UserName. The current user stored in the field is ActionUserID=23, Name=Jane, deleted=1.
I want to include only WHERE deleted=0 OR ActionUserID=[23] (i.e. the current value stored in the edited record). How do I refer to "the current value stored" in my query string for the where clause? Or is it necessary on loading the edit form to populate a session variable with the current value, then say:
WHERE deleted=0 OR ActionUserID=$_SESSION["tmpActionUserID"]
I can see this is an obvious workaround, however, I hope you'll tell me I don't need to do this and some variable is already available to substitute in.
Thanks again Jane.
J
 		Jane 9/17/2008

Hi,
I suppose you need to add where clause on the "Edit as" settings dialog on the Visual Editor tab.

Here is a sample:

"deleted=0 OR ActionUserID=".$_SESSION["tmpActionUserID"]



Then fill $_SESSION["tmpActionUserID"] variable in the Edit page: Before processevent on the Events tab.

Here is a sample:

global $conn;

if (@$_REQUEST["editid1"]

{

$str = "select UserID from TableName where RecordID=".$_REQUEST["editid1"];

$rs = db_queyr($str,$conn);

$data = db_fetch_array($rs);

$_SESSION["tmpActionUserID"] = $data["UserID"];

}
M
 		mattd1 author 9/17/2008

Jane, thanks. Perhaps I am being dim, or my long explanations are confusing.
(1) It seems wasteful to have to execute a query to get the value of the ID of the current record.

(2) It surely must be already stored somewhere, since it's been SELECTed for display on the form from the basic underlying query for the page?
So, would it be in, like, $values["ActionID"] or something and I could refer to this in my WHERE clause?
Sorry to be a pain Jane.
Thanks again for your help.
Matt
J
 		Jane 9/17/2008

Matt,
Unfortunately there is no such variable for lookup query.