Lookup table filter

Adam · Post by **Adam** » Fri Dec 28, 2018 1:10 am

I've got a users table with a lookup into itself so that users can be put under other users for referral-tracking purposes.

However, I need to prevent users from being put under themselves, so I've been trying to filter out the current user record using its key (Profile_ID) which is a hidden field in the edit form. I've tried almost every variation of filter statement I can think of (even taken from the example in the help file) i.e.

(intval(CurrentPage()->Profile_ID->CurrentValue) > 0) ? "Profile_ID != " . strval(CurrentPage()->Profile_ID->CurrentValue) : ""

...but nothing works - either all users appear in the lookup, or none do. The only functional filter has been a static one:

"Profile_ID != 100004"

When the "Referrer" field is null in the DB, the details of the current user record (Profile_ID and Profile_Owner) are displayed as though they have been selected from the lookup.

Any thoughts?

arbei · Post by **arbei** » Fri Dec 28, 2018 9:37 am

Your lookup filter won't work in Add page as there is no current value for Profile_ID field yet. However, in Edit page, it should work, you can debug by printing out CurrentPage()->Profile_ID->CurrentValue.

mobhar · Post by **mobhar** » Fri Dec 28, 2018 10:48 am

If the value of "Profile_ID" field is actually current username, then you may simply use CurrentUserName() instead of CurrentPage()->Profile_ID->CurrentValue.

Adam · Post by **Adam** » Fri Dec 28, 2018 6:39 pm

Profile_ID = CurrentUserName() when the profile owner is logged in, but that's not the case when the site managers are editing records so I can't use CurrentUserName().

Records get added via the registration page, so this is only for edit pages ... and CurrentPage()->Profile_ID->CurrentValue prints the expected value from Row_Rendering(), so I don't understand why it's not working in the filter statement :(

...and I still can't understand why these text fields with lookups are all displaying the wrong details in list/view/edit pages!

If the field value is null then they should display nothing
If the field value is an invalid profile ID then they should display just the invalid profile ID
If the field value is a valid profile ID then they should display that profile ID and the name of that profile owner

...but it's not happening: in all cases, they display the profile ID and profile owner name of the current record :(

It's not even like there's something special in the field setups...

table is "user_profiles"
primary key is "Profile_ID" (int 11, auto inc)
lookup field is "Introducer" (int 11)
field type is "text"
edit tag settings:
...use lookup table
...force selection
lookup settings:
...table name is "user_profiles"
...link field is "Profile_ID"
...display fields are "Profile_ID" and "Profile_Owner"
...order by "Profile_ID" DESC
...filter - currently empty, but would be "Profile_ID != " . CurrentPage()->Profile_ID->CurrentValue if that was functioning

sangnandar · Post by **sangnandar** » Sat Dec 29, 2018 10:02 am

I don't get it.
You tied up Introducer to Profile_ID then set filter on Profile_ID->CurrentValue.
Being Profile_ID is PK, surely it return single value.

Adam · Post by **Adam** » Sat Dec 29, 2018 6:09 pm

@sangnandar ... not quite - the filter is "Profile_ID != " . CurrentPage()->Profile_ID->CurrentValue i.e. allow all except the current profile ID

I try and make this work and the problem seems to be that Profile_ID->CurrentValue hasn't been initialised with the current record data at the point that PHPM is trying to set the filter, so CurrentPage()->Profile_ID->CurrentValue has no value and that breaks the filter statement (and explains why a static filter works).

I created a v2018 project and copied over all of the server/client event code and everything works as it should. Planning to try and maintain both versions so I can switch back to v2019 whenever this problem finally gets resolved.

sangnandar · Post by **sangnandar** » Sat Dec 29, 2018 7:25 pm

How about moving the code to Lookup_Selecting() server event? I think it applied after Edit Tag filter.
Also, at worst, you can ExecuteScalar() the value if CurrentPage()->Profile_ID->CurrentValue is also not available.

Adam · Post by **Adam** » Sun Dec 30, 2018 11:59 pm

It's not ideal, but replacing "CurrentPage()->Profile_ID->CurrentValue" with "Get('Profile_ID', 0)" works in the current context.

I also discovered that all of the other lookup issues (displaying current record data instead of nothing / the looked-up record info) were resolved by creating a view of the users table and pointing the lookup at that. The ability for tables to do lookups into themselves (lookup filter using current value) has only broken in v2019. Apart from my particular application, it's nice to be able to show the details of someone's "manager" when record-level security is being implemented.

Adam · Post by **Adam** » Mon Jan 07, 2019 2:35 am

If I enable List access to the lookup table, then filtering on $this->Profile_ID->CurrentValue is working as expected, however, there are still issues.

Based on the help file, using ExecuteRows() in Lookup_Selecting() should mean that I can regain unrestricted lookup functionality without needing to grant List access to the lookup table (thereby retaining table security) so I quickly wrote this to test the theory:

$select = $fld->Lookup->UserSelect;
$where = str_replace('0=1', '', $fld->Lookup->CurrentFilter);
addFilter($where, $fld->Lookup->UserFilter);
$where = !empty($where) ? " WHERE {$where}" : '';
$order = !empty($UserOrderBy) ? " ORDER BY {$fld->Lookup->UserOrderBy}" : '';
$fld->Lookup->setOptions(ExecuteRows("{$select}{$where}{$order}"));

... but:

1) the filter statement defined in the project isn't passed through to Lookup_Selecting() with its parameters: $filter, $fld->Lookup->CurrentFilter and $fld->Lookup->UserFilter are all empty strings

Replacing " addFilter($where, $fld->Lookup->UserFilter); " with " if ($fld->Name == "ThisField") addFilter($where, "MyField = 'xyz'"); " at least gets around the issue until it's fixed properly.

2) Searching i.e. from a text field, no longer works ...the Ajax search is totally dead if the Lookup_Selecting() has retrieved the rows :(

3) AutoFill no longer works ...same issue as with searching

4) recursive lookups (into the same table) don't work (incorrect data is displayed)

To get correct functionality, you have to create a view of the current table and use the view as the child table of the lookup... functional but sloppy.

Post by **Webmaster** » Mon Jan 07, 2019 9:45 am

If you use set lookup options dynamically, disable "Use lookup cache" in Advanced Settings.

Adam · Post by **Adam** » Mon Jan 07, 2019 10:41 pm

I don't use the cache system so it was disabled already

Just updated to the new template and issue #4 has been resolved - thanks :)

Issues #2 & #3 also seem resolved by the same fix - will check more but looks good so far.

#1 still on-going - have submitted instructions to reproduce the problem in the demo project

Adam · Post by **Adam** » Tue Jan 08, 2019 11:09 pm

Okay... so the issue with #1 is that the $LookupFilter property in the Lookup class (phpfn15.php) has been made private.

EW's logic was that the filter is always applied, so there was no need to leave it public... except that when Lookup_Selecting() is used, the filter is always NOT applied because the lookup function is never called.

I've explained that it's a bit illogical making the SELECT and ORDER BY properties public without the WHERE and I've asked them to kindly make that change as I'm sure I'm not the only person this will affect over time.

For now, my options are to either modify the template (which I'm trying to avoid while updates are continuing) or to duplicate the project lookup filters in code within the relevant Lookup_Selecting() events.

I've chosen to add the code for now, but really hope they update the template as the code snippet I posted earlier will then allow "drop-in" reversion to pre-v2019 lookup behaviour without impacting the newer security requirements that support the API features... the best of both worlds :)

Support have confirmed that the $LookupFilter property will be exposed in the next build :-)

mobhar · Post by **mobhar** » Tue Feb 12, 2019 12:40 pm

This has already been resolved in v2019.0.7.

vilhenamix · Post by **vilhenamix** » Sun May 26, 2019 4:45 am

I use 2019.0.10
for me to solve, does a direct select in the table without passing through security filter....

function Lookup_Selecting($fld, &$filter) {
//var_dump($fld->Name, $fld->Lookup, $filter); // Uncomment to view the filter
// Enter your code here
if ($fld->Name == "cel_rede_id")
$fld->Lookup->setOptions(ExecuteRows("SELECT rede_id, rede_nome FROM tbl_rede")); // Use data from ExecuteRows
}