Filter lookup field of a calendar report

PHPMaker General Discussion (PHPMaker)
1

Hi all:

I have a calendar report (v2026), in which one of the fields use a lookup table.

I need to filter this field depending on the user logged. The problem is that calendar report has no Lookup_Selecting event.

Any idea?

Thanks

4

Yoy may try to use the Page_Load event of the calendar report, call the getSqlSelectFields() to check the fields to be select, then modify it by the setSqlSelectFields(string $v) method. For example, you can use a subquery (varies with the logged in user) to get the looked up value of the field.

1 Like
5

Hi,

Thanks for the answer. I haven’t found a way of filtering the “projects” when I’m editing the event and show the projects from the select.
I’ve tried with:

  public function pageLoad(): void
    {
     if (CurrentUserLevel() = 4) {
     $idCode= AdjustSql(Profile()->get("idCode"));
     $subquery = "(SELECT descripction FROM projects
                  WHERE id_project = t0.project 
                  AND idCode= '" . $idCode. "') AS Filtered_Project";
    
    $select = $this->getSqlSelectFields(); 
    $newSelect = str_replace("`Project`", $subquery, $select); 
    log("New Select: " . $newSelect);
    $this->setSqlSelectFields($newSelect);
        }
    }

I’ve made some test with this approach:

public function pageLoad(): void
{
    $fieldName = "Project";
    $fieldProject = $this->{$fieldName} ?? $this->{strtolower($fieldName)} ?? null;

    if ($fieldProject && $fieldProject->Lookup) {
        $userLevel = CurrentUserLevel();
        $responsableCodigo = Profile()->get("codigo_responsable");
        $delegacion = Profile()->get("delegacion");
        $isDelegationResponsible = strpos("," . $userLevel . ",", ",4,") !== false;

        $baseFilter = "`estado` = 'Pedido'";

        if ($isDelegationResponsible) {
            $newFilter = $baseFilter . " AND `delegacion` = '" . AdjustSql($delegacion) . "'";
        } else {
            $newFilter = $baseFilter . " AND `codigo_responsable` = '" . AdjustSql($responsableCodigo) . "'";
        }

        $finalFilter = "(" . $newFilter . ")";
        $fieldProject->LookupFilter = $finalFilter;

        log("New filter applied: " . $fieldProject->LookupFilter);
    }
}

In the log I get the filter I want: “app.DEBUG: New filter applied: : (estado = 'Pedido' AND delegacion = 'M502')”

but in the Chrome network console, I get when I call the modal edit page: (number 5 is the real applied filter):

{page: "PlanificacionEdit", field: "Project", result: "OK",…}
field: "Project"
page: "PlanificacionEdit"
records: [{lf: "Estado Delega", df: "Estado Delega", df2: "22-0168-ABRE ORGIVA - VELEZ", df3: "", df4: ""},…]
result: "OK"
sql: "SELECT `id_project` AS `lf`, `id_project` AS `df`, `descripcion` AS `df2`, '' AS `df3`, '' AS `df4` FROM Projects ORDER BY `id_project` LIMIT 100"
totalRecordCount: 61124

Thanks

6

Your idea of using LookupFilter won't work because LookupFilter is used during lookup triggered from the client side. But you need to load the looked up value when you load the page.

In your first attempt, the AS Filtered_Project is wrong, you should use the original field name (i.e. to replace the original column, not to create a new column). Check the original $select in your code and make sure it contains the quoted `Project` so your code can replace successfully.