Link

The following examples show how to apply the advanced filter for Links.

When creating a link to an existing data record from another table, all data records from the other table are listed as options. With advanced filters, the quantity of the listed data records can be reduced. Only data records which meet the set conditions of the advanced filter will be listed for selection.

In the following examples a data record of the ‘PATIENTS’ table should be linked to a data record of the ‘DISHES’ table:

Advanced Filter through setting the variables, corresponding to the SQL clause “$where []” and “$from []”

Available filters:

• $select
• $from
• $where
• $join
• $order

$where[] = "GERICHTE.NAME LIKE '%suppe%'";
$from[] = "GERICHTE";
$order[] = "Gerichte.NAME";
$select[] = "Gerichte.NAME";

Advanced Filter through filter settings using the “$gsr” parameter (see functional description “get_gresult”)

$gsr[$tableIdGerichte][$fieldIdAnzahlZutaten][$laufendeFilterNr]=10;
$gsr[$tableIdGerichte][$fieldIdAnzahlZutaten]['num'][$laufendeFilterNr]=5;
 
$gsr[$tableIdGerichte][$fieldIdName][$laufendeFilterNr+1]='suppe';
$gsr[$tableIdGerichte][$fieldIdName]['txt'][$laufendeFilterNr+1]=1;
 
$gsr[$tableIdGerichte][$fieldIdName][$laufendeFilterNr+2]='eintopf';
$gsr[$tableIdGerichte][$fieldIdName]['txt'][$laufendeFilterNr+2]=1;
$gsr[$tableIdGerichte][$fieldIdName]['andor'][$laufendeFilterNr+2] = 2;

$gsr[$tableIdGerichte][$fieldIdAnzahlZutaten][$laufendeFilterNr]=10;
$gsr[$tableIdGerichte][$fieldIdAnzahlZutaten]['num'][$laufendeFilterNr]=5;
 
$gsr[$tableIdGerichte][$fieldIdName][$laufendeFilterNr+1]='suppe';
$gsr[$tableIdGerichte][$fieldIdName]['txt'][$laufendeFilterNr+1]=1;
 
$gsr[$tableIdGerichte][$fieldIdName][$laufendeFilterNr+2]='eintopf';
$gsr[$tableIdGerichte][$fieldIdName]['txt'][$laufendeFilterNr+2]=1;
$gsr[$tableIdGerichte][$fieldIdName]['andor'][$laufendeFilterNr+2] = 2;
 
$myOnlyField[$tableIDPatienten] = array($fieldIdEinschraenkung);
$myResult = get_gresult($tableIDPatienten, 1, null, null, null, $myOnlyField, $ID, null, null);
$gsr[$tableIdGerichte][$fieldIdBeschreibung][$laufendeFilterNr+3]=$myResult[$tableIDPatienten][fieldIdEinschraenkung][0];
$gsr[$tableIdGerichte][$fieldIdBeschreibung]['txt'][$laufendeFilterNr+3]=1;

In this example, the following variables have to be replaced with appropriate values.

• $tableIdGerichte: ID of the’Dishes’ table
• $tableIDPatienten: ID of the ‘Patient’ table
• $fieldIdAnzahlZutaten: Field ID of the ‘Number of ingredients’ field in the ‘Dishes’ table
• $fieldIdName: Field ID of the ‘Dish name‘ field from the ‘Dishes’ table
• $fieldIdBeschreibung: Field ID of the ‘Description’ field in the ‘Dishes’ table
• $fieldIdEinschraenkung: Field ID of the ‘Restriction’ field in the ‘Patients’ table
• $laufendeFilterNr: Index (freely selectable) , with multiple filters starting from 0 and ascending.

When creating the link, only data records of the ‘Dishes’ table are suggested / listed, where

• the number of ingredients is 10 or more
• the characters ‘Soup’ or ‘Stew’ are found in the name
• the characters from the ‘Restriction’ field of the ‘Patients’ table are in the description field

Advanced filter by using your own function

return myExt_FilterExample($gtabid,$utyp,$filter,$gsr,$verkn,$onlyfield,$null,$extension);

function myExt_FilterExample($gtabid,$utyp,$filter,$gsr,$verkn,$onlyfield,$null,$extension){
    // get first query
    $verkn[relext] = '
	$where[] = "kunden.email is not null";
    ';   
    $query1 =  get_sqlquery($gtabid,$utyp,$filter,$gsr,$verkn,$onlyfield,$null,$extension);

    // get second query
    $verkn[relext] = '
	$where[] = "kunden.web is not null";
    ';
    $query2 =  get_sqlquery($gtabid,$utyp,$filter,$gsr,$verkn,$onlyfield);
   
    // filter 'order by'-clause out of sql query
    $query1['sql'] = explode('ORDER BY',$query1['sql']);
    $query1['sql'] = $query1['sql'][0];
    $query2['sql'] = explode('ORDER BY',$query2['sql']);
    $query2['sql'] = $query2['sql'][0];
   
    // union first and second query
    $query['sql'] = $query1['sql'].' UNION '.$query2['sql'];
    $query['count'] = $query1['count'].' UNION '.$query2['count'];
   
    return $query;
}