Page Comparison

Data from database file

In this section, we will populate the Grid using a database file. In the illustration below, we are using an item master file named ITM001P for the database file property.  The file name can be qualified with a library; however, if the library is omitted, the session’s library list will be used to find the database file.

To add multiple files¹, right-click on the "database file" property, click "Add Another Database File", and specify each file:

Image Added

When using multiple files, a join relationship must be setup. Click on the button in the "database join" property to get the join interface:

Image Added

Drag the related columns from one table to the next in order to join between tables. Click on the blue square to change the join type–inner, left outer, right outer.

Next, you must specify the “database fields” property, which will be used to determine the database fields from which the data will be populated.  You can type the field names separated by commas or choose them from a dialog. Click on the button circled in green to display the database fields dialog for the database file you specified.

...

Note: Only SELECT statements are allowed in the “custom sql” property.

Filtering and Sorting Limitations with Custom SQL

...

function transform(responseText) {
  
  var parser = new DOMParser();
  var xml = parser.parseFromString(responseText, "text/xml");  
  var root = xml.documentElement;
  var transformed = {};
  transformed["totalRecs"] = root.getAttribute("total");
  var results = transformed["results"] = [];
  var rows = root.childNodes;
  for (var rowNum = 0; rowNum < rows.length; rowNum++) {
    
    var row = rows[rowNum];
    var cols = row.childNodes;
    var obj = {};
    for (var colNum = 0; colNum < cols.length; colNum++) {
      
      var col = cols[colNum];
      obj[col.nodeName] = (col.childNodes && col.childNodes.length == 1) ? col.firstChild.nodeValue : "";
      
    }
    results.push(obj);
    
  }
  
  return transformed;
    
} 

Filtering with Computed Columns

Both data-from-database-file and Custom-SQL type grids allow developers to specify SQL expressions as columns, resulting in computed columns. If the grid has the Filter feature enabled, then these computed columns must use an alias; otherwise, filtering will fail on the computed column.

¹ Database-driven joins with multiple files is available with Version 6 Fix Pack 10.0.