Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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.

Image RemovedImage Added

Note: Make sure that the library in which the database file exists is included within your session’s library list. If the library does not exist, you will get a message that looks like this:

  Image RemovedImage Added

Otherwise, you will get a dialog with the available database field names to pick from. Select the appropriate fields from the list:

  Image RemovedImage Added

 

When you click OK, you will be presented with a dialog asking you whether if you want to use the field descriptions as column headings or provide your own column headings. Click OK to use the database field descriptions.Image Removedupdate the grid columns based on the fields that you selected. If you click OK, the grid column headings will be updated.

Image Added

The column headings should now appear on your Grid as in the screenshot below.

Image RemovedImage Added

Save your work and exit Design mode to see the data populated in the Grid as in the screenshot below.

  Image RemovedImage Added

Additionally, you can sort the data on certain columns by specifying the database fields that determine the order of the records in the “order by” property. See below.
Image RemovedImage Added

This is the final result after the data was ordered by Sales Person and Quantity Available.

Image RemovedImage Added

Data from a custom SQL statement

...

SELECT ITMNR, ITMDS, ITMSP, ITMAVL FROM ITM001P

Image RemovedImage Added

Save your work and exit Design Mode. Now the Grid should be populated with the same data as shown in the previous example (Data from database file). Additionally, we can also add an ORDER BY command to our SQL statement to order the columns by Sales Person and Quantity Available as shown previously. Now, the SQL statement becomes:

...

Save the work and exit Design Mode to see the results. You should get the same result as before. Furthermore, we can add a WHERE clause to the SQL statement to return the items for which the Quantity Available is 25 15 or more:

SELECT ITMNR, ITMDS, ITMSP, ITMAVL FROM ITM001P WHERE ITMAVL >= 25 15 ORDER BY ITMSP, ITMAVL
Image RemovedImage Added

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

...

From the properties dialog window, find the “data url” property and type in the URL of the program that you wish to call to send the JSON data as shown in the screenshot below:
Image RemovedImage Added

The “data url” property sets the URL to a program or web service that returns the Grid’s columns definition and data in JSON format.

...

The property can be set like this:

Image RemovedImage Added

Code Block
title"transform()" Function Code
linenumberstrue
languagejavascript
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;
    
} 

...