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
Example 1:
$where[] = "GERICHTE.NAME LIKE '%suppe%'";
$from[] = "GERICHTE";
$order[] = "Gerichte.NAME";
$select[] = "Gerichte.NAME";
This example takes into account only those data records of the ‘Dishes’ table that have the characters’ Soup ‘in the ‘Dish Name field.
Please note that “order” only works in combination with “select”!
Advanced Filter through filter settings using the “$gsr” parameter (see functional description “get_gresult”)
Example 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;
In this example, the following variables have to be replaced with appropriate values.
- tableIdGerichte: ID of the ‘Dishes’ 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
- $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 .
Example 3:
$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);
Example 4:
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;
}
In this example, two queries are generated and then manually combined into a new query:
- The first query contains all customers who inserted an email address
- The second query contains all customers who inserted a website