Creating filter in Recordset Selecting

I have the following table:

Change_id
Title
Requester
Description
Aproval
Aproved_by
Notes

Multiple users are supposed to check each other work and the person who request a change, cannot approve it. So, I can’t use a filter under the “lookup table” section. I would like to apply the filter under the Recordset Selecting but it is not working as written.

Here is the code I am using:

function Recordset_Selecting(&$filter) {
if (CurrentPageID() == “edit”) {
AddFilter($filter, “Approved_by = '”.CurrentUserName().“'”);
}
}

Basically, the filter does not work and it show all users instead of the current user’s username only.

The usertable where the users come from is as follows:

Table Contacts

Here is the table structure:

Column Type Comment
EntID int(20)
ContID int(20) unsigned Auto Increment
First varchar(35)
MI varchar(3) NULL
Last varchar(35)
Position varchar(35) NULL
OfficeNo varchar(20)
Mobile varchar(20) NULL
Email varchar(50)
Profile text NULL
Username varchar(50)
Password varchar(500)
Group int(11) NULL

Here are some record examples (modified)

EntID ContID First MI Last Position OfficeNo Mobile Email Profile Username Password Group

1 6 Joshua P Hersho IT Security 555-555-5555 555-555-5556 elhersho@company.com hersho xxxxxxxxxxxxxxx -1
1 7 Noslen B Piedro IT Engineer 555-555-5557 555-555-5558 elhijo@company.com elhijo xxxxxxxxxxxxxxx -1
1 8 Agostin P Bobo IT Manager 555-555-5559 555-555-5550 elbobo@company.com bobol xxxxxxxxxxxxxxx -1

I think I have to use ContID somewhere as that is the key of the table for users but I don’t know how.

Help, as always, is appreciated!

No need to check Edit page condition for List page, so your code above should be:

function Recordset_Selecting(&$filter) {
AddFilter($filter, "ContID = ". CurrentUserID());
}

It will filter by current user id, and not by current username.

If you want to filter by current username, then simply put the following code in “Recordset_Selecting” server event under Contacts table (see the table schema in your first above):

AddFilter($filter, “username = '”.CurrentUserName().“'”);

Unfortunately it is not working. I can see all the usernames. I noticed that the actual field has a capital letter U and I changed the code accordingly but still it is not filtering. Here is the code again:

Under the recordset_selecting for the table contacts

function Recordset_Selecting(&$filter) {
AddFilter($filter, “Username = '”.CurrentUserName().“'”);
}

  1. Did you select “contacts” from “Use Existing Table” for login on site, or did you use another table for login?
  2. If you used table other than “contacts” for login page, then double check; is the username in your “users” table same with the username in your “contacts” table?

Number 1 is correct, I used the table “Contacts” to login.

If I am not mistaken, after analyzing your sample data above, the last field is “Group”, and its value for those 3 records are “-1”.

It seems this “Group” field is equal to “User Level” field, right? If so, and if you enable the “User Level Field (Integer)” feature under “Security” → “Advanced” → “User Levels” of your project, and choose “Group” in that setting, then it means “-1” = “Administrator”.

The conclusion is, why all the records are displayed (even you have added filter string in Recordset_Selecting server event), is because one of the user from those records has “User Level” = “-1”, too. He/she is Administrator, in this case. That’s why all records are displayed.

So, please double check again your data in “Contacts” table, and make sure the values in “Group” field is not equal to “-1”, if you want to filter the recordset by using “Recordset_Selecting” server event.

Or, at least, please create another User Level record other than Administrator, and assign it into another user.

Then, try to login using that ordinary user in order to make sure the filter in your “Recordset_Selecting” server event is working properly.

Although it seems to be working as the filter now works. When I am back at the list table I can no longer see the actual usernames that edited each record. All I can see if the user id which it ContID. The only username that shows is my own when I am logged in. Also when I edit a record, the id that was in the “Approved_by” field get again overwritten by blank.

I though creating a filter in the Recordset_Selecting will allow me to see the user that edited that record before while in edit mode but the filter gets applied and the “Approved_By” is blank. Of course, when I save the field the black overwrites the value that was previously store there.

I also used “Page_Rendered” event on the Edit page to make the field read only. But even when is read only, it still gets overwritten by the blank selection.

I hope what I have said here makes sense…

This is how I finally got it to work.

I added this to row updating:

function Row_Updating($rsold, &$rsnew) {
if ($rsold[“Approval”]==“2”) {
$rsnew[“Approved_by”]=$rsold[“Approved_by”];
}
return TRUE;
}

and I added this to page render:

function Page_Render() {
if ($this->Approval->CurrentValue == “2”) {
$this->Approved_by->ReadOnly = TRUE;
}
}

When a record was approved, anyone who came after to edit the same record for another reason could not change the approved_by because it it read only and it will always get the same value that was there before. I hope this can help others.

napiedra wrote:

This is how I finally got it to work.

I added this to row updating:

function Row_Updating($rsold, &$rsnew) {
if ($rsold[“Approval”]==“2”) {
$rsnew[“Approved_by”]=$rsold[“Approved_by”];
}
return TRUE;
}

and I added this to page render:

function Page_Render() {
if ($this->Approval->CurrentValue == “2”) {
$this->Approved_by->ReadOnly = TRUE;
}
}

When a record was approved, anyone who came after to edit the same record for another
reason could not change the approved_by because it it read only and it will always
get the same value that was there before. I hope this can help others.




I have used your code for my project! you saved me…
but how could I make it so that only 1 user can edit 1 time if the value changes from 0 to 1 if it changes to 1 and the same user cannot edit

@girllandrade,

Assuming that you want to prevent editing the entire record (and not just the field), put this in the Row_Rendering event of your table:

global $Security;
$Security->loadCurrentUserLevel($this->TableName);
if ($this->YOUR_FIELD_NAME->CurrentValue == ‘1’) $Security->SetCanEdit(false);

Don’t forget to change YOUR_FIELD_NAME to the actual field name.
Enjoy.