Relational Features

Relational features - linking tables

Relational features in Woda work on the same server or across several servers using HTTP. This is what you can do:

• You can set the items of selection pull downs to data in another WODA table. Specify the field as LINKOPTION type and in the INTO field define database alias or path to its dataDir.
• Specifying a query into another WODA database as a picture of a field. The following function can be used in all data definition fields where string expressions are allowed:

&QRY($table, $search, $then, $sort, $format, $first, $max);

• Getting a value of a field from another WODA database. The following function can be used in all data definition fields where string expressions are allowed:

&FLD($table, $id, $fieldName);

• Getting raw data from another table in TAB format. The following function returns rows of data from another table. Only the fields listed in flds attribute (comma separated) will be returned.

&ROWS(table,flds,search,then,sort,format,first,max);

• See also functions
  • &ID();
  • &TODAY();
  • &QRY();
  • &GET();
  • &PUT();
  • &RELATED();
  • &DECODE();
  • &LINK();
  • &ROWS();
  • &FLD();
  • &KEY();
  • &FETCH();
  • &PIC();
  • &IDS();
  • &NEXT();
  • &URL();
  • &PUTN();

Most of these function expect a reference to another table as a parameter. If the application is cgi-bin/people with data in somewhere/people and the other application is cgi-bin/other with data in somewhere/data you do not need to do anything. Just call the other table 'other'. If, however, the other table is somewhere else on the same server or even on another server somewhere on the Internet, then a setting $WBB{'tables'} must be defined. [More information].

Example of setting up a relation between two tables

Suppose we have two tables - a table of professors and a table of classes. Each professor teaches 0 or more classes. In the definition of the classes table, we would include a field that would point to the class's professor like this:

in cgi/classes

$x='professor'; # ------------------------------   
$WBF{$x,srt}=$i--;   
$WBF{$x,'p'} = 'Professor teaching this class';   
$WBF{$x} = '1;';   
$WBF{$x,'type'} = 'LINKOPTION';   
$WBF{$x,'into'} = 'professors';   
$WBF{$x,'picture'} = '"<a href=/cgi/professors?$_>$_</a>"'; 

in the above example only the id code of the professors record will appear in the pulldown. If we modify the above into field as:

$WBF{$x,'into'} = '&ROWS("professors","_id,firstName,lastName")';

the pulldown will include first and last name of the professor.
If we further improve the picture field as:

$WBF{$x,'picture'} = ' 
 $xx = &FLD(professors,$_,lastName) . " " .&FLD(professors,$_,firstName; 
 "<a href=/cgi/professors?$_>$xx</a>"
 '; 

the fist and last name of the professor will also be nicely printed in the tabular output form.
Finally, in the professors record, we might like to see a list of classes he/she teaches:

in cgi/professors

 $x='classes'; # ------------------------------ 
 $WBF{$x,srt}=$i--; 
 $WBF{$x} = '1;'; 
 $WBF{$x,'type'} = 'COMPUTE'; 
 $WBF{$x,'picture'} = '"<a href=$SCRIPT/Search?search=$rec{_id}>Classes he/she teaches</a>"';

The above only displays a link to a query. The below modification displays the actual records:

$WBF{$x,'picture'} = '&QRY(“classes”,"+professor:$rec{\_id}");';

The above approach may not be too smooth, but it works. The &RELATED, &DECODE and &LINK functions make this even simpler.