AgileApps Support Wiki

HowTo:Use a SQL Query to List Records in a Custom Page

From AgileApps Support Wiki

For:   Developers
Level: Intermediate
Time: 15 minutes

See more:
    ◾ HowTo Guides

This sample JSP page uses a SQL query to create a list of Customer object records.

To achieve that goal:

Preqequisites

This example assumes that there is an Orders object with fields:

  • order_num
  • customer
  • order_date
  • order_total

Creating the Page

To create this example:

  1. Go to GearIcon.png > Developer Resources > Pages
  2. Click [New Page]
  3. For the "title", give the page a valid name. For example: CustomOrderList.jsp
  4. Include header files.
  5. Copy in the sample code below.
  6. Save the page.
<!-- Create a View from a SQL Query -->

<html>
<head>
<title>Customers</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<style type="text/css">
.title1 {
        font-family: Arial, Helvetica, sans-serif;
        font-size: 24pt;
        background-color: #006666;
        color: #DCDCDC;
        padding: 10px;
}

.td1 {
        font-family: Arial, Helvetica, sans-serif;
        font-size: 18pt;
        background-color: #FFFFFF;
        border-color: #006666; 
        border-width: medium;
        color: #006666;
        padding-left: 18px;
}  

.td2 {
        font-family: Arial, Helvetica, sans-serif;
        font-size: 12pt;
        background-color: #FFFFFF;
        border-color: #006666; 
        border-width: medium;
        color: #006666;
        padding-left: 2px;
}            
</style>
</head>

<body>
  <table width="100%" border="0" cellspacing="0" cellpadding="0">
    <tr>
      <td width="33%" colspan="3" class="title1">Custom Orders</td>
    </tr>      
    <tr>
      <td width="20px"  class="td1"><u>Order#</u></td>
      <td width="120px" class="td1"><u>Customer</u></td>
      <td width="40px"  class="td1"><u>Date</u></td>
      <td width="40px"  class="td1"><u>Total</u></td>
    </tr>      
<% 
  // Issue the SQL Query
  String query = "SELECT customer, order_date, order_total FROM Orders ORDER BY customer";
  Result result = Functions.execSQL(query);
  if(result != null) {
    String service_url = com.platform.api.utility.ServiceConfiguration.getServiceDomainURL();
    String platform_url =  "https://" + service_url + "/networking";
    ParametersIterator iter = result.getIterator();
    while(iter.hasNext()) {
       Parameters row = iter.next();
       String recordID = row.get("id");
       String order_num = row.get("order_num");
       String customer = row.get("customer");
       String date = row.get("order_date");
       String total = row.get("order_total");
       
       /* Formulate links to the records
        *
        * The application ID and object ID are required to formulate the links.
        * Get them from the URL you see when you visit the object tab in the GUI,
        * or get them from the metadata displayed in the GUI, or from a REST API.
        * For example, this REST API returns object metadata:
        *   - Visit https://{{domain}}/networking/rest/object/{objectName}
        *   - Use the content of the <id> tag
        * 
        * Link Pattern:
        *    ../servicedesk/index.jsp?applicationId={appID}#_{objectID}/{recordID}
        */
       String appID = "348f933bf0dc4bfeb5a8e8ade2bb145e";
       String objectID = "9912b3648e8b4b919c23e00efcab8089";
       String link = platform_url 
                   + "/servicedesk/index.jsp?applicationId=" + appID
                   + "#_" + objectID + "/" + recordID;     

            // Use record "name" if the Record Identifier is defined as // ?-RECORD NAME ??
            // a unique index. Otherwise use "recordID".
%>
    <tr>
        <td class="td1"><a href="<%=link%>"><%=order_num%></a></td>
        <td class="td2"><%=customer%> --></td>
        <td class="td2"><%=date%> --></td>
        <td class="td2"><%=total%> --></td>
    </tr>
<%
    }
  }
%>
  </table>
</body>
</html>

Viewing the Page

To view the page directly:

The page appears by itself, without any platform context.

To make the page available as a tab in the GUI:

  1. Go to GearIcon.png > Developer Resources > Web Tabs
  2. Click [New Web Tab]
  3. Give the tab a display title and a name that can be used to access it from code.
    For example:
    • Display Title - Custom Orders
    • Name - custom_orders_page
  4. Set Web Tab Type to Page
  5. Select the page you created.
  6. Click [Save]
    The Web Tab is created.
  7. Log out and the log back in to see the new Web Tab.
Retrieved from "https://agileappscloud.info/aawiki-src/index.php?title=HowTo:Use_a_SQL_Query_to_List_Records_in_a_Custom_Page&oldid=16021"