Lookup filter not working for a table (v2023)

[enfoque_traza]

In a project I have two tables (table_a // table_b) in which a drop-down field is configured as LOOKUP to a third table (table_c) filtering the records by a field common to all three (FAMILY, int 11).

Following the examples, I use this filter string:

(strval($this->FAMILY->CurrentValue) != "") ? "`FAMILY` = " . $this->FAMILY->CurrentValue : ""

The result is that in table_a, the dropdown is filtered and in table_b, it is not filtered and all the records of table_c are displayed. The fields have the same configuration (INT 11)

Any idea what could be happening?

[arbei]

Note that lookup filter is server side, there is no $this->FAMILY->CurrentValue yet in, for example, Add page.

[enfoque_traza]

Hi,

Thank you for your clarification.

Lookup filtering is currently working in add page of table a. Family is set by security settings (security field is family in this table).

The same configuration is set in table b (security setting, lookup table, filter) but the drop-down field related to the lookup table is not filtered.

That's what is strange.

Is there a way to filter this lookup query without using this server side script?

Thanks

[arbei]

enfoque_traza wrote:

Lookup filtering is currently working in add page of table a. Family is set by security settings (security field is family in this table).
The same configuration is set in table b (security setting, lookup table, filter) but the drop-down field related to the lookup table is not filtered.

If the settings are really the same, they'll work the same. There should be some difference in settings. Note that the field in table a works because it is the User ID field so it has field value (i.e. CurrentUserID()) even in Add page without initial data. It might not be exactly the same case in table b (e.g. it is enabled with Allow Sort/Search), you may want to review and compare the settings.

You can also use Lookup_Selecting server event to set lookup table filter.

[enfoque_traza]

I have tried with this lookup selecting event, but it is still not filtering

// Lookup Selecting event
function Lookup_Selecting($fld, &$filter)
{
  //  var_dump($fld->Name, $fld->Lookup, $filter); // Uncomment to view the filter
    // Enter your code here
    if ($fld->Name == "MyLookupField")
        $fld->Lookup->UserFilter = "FAMILIA = $this->FAMILIA->CurrentValue ";
    // Assume the field is of string type
}

Any suggestion?

[arbei]

1. arbei wrote:
   > Note that lookup filter is server side, there is no $this->FAMILY->CurrentValue yet in, for example, Add page.
   Your code will not work in Add page. You should check if $this->FAMILY->CurrentValue has value before setting the user filter.
2. You need to replace "MyLookupField" by your real lookup field name, e.g. "'FAMILY".

[enfoque_traza]

Hi,

Following your ideas, I finally used this lookup table event:

function Lookup_Selecting($fld, &$filter)
{
    // Enter your code here
$fam= CurrentUserInfo("familia");
if ($fld->Name == "NOMBRE")
$fld->Lookup->UserFilter = "FAMILIA = $fam";

} 

familia is also stored in the user table, so I set the filter using CurrentUserInfo().

I'm not used to deal with server events but finally it worked. Thank you!