AgileApps Support Wiki

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

From AgileApps Support Wiki
Revision as of 19:47, 28 December 2011 by imported>Aeric (Created page with "This sample JSP page illustrates achieves several goals: :* :<syntaxhighlight lang="javascript" enclose="div"> <!-- Create a View from a SQL Query --> <% String query = "SE…")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

This sample JSP page illustrates achieves several goals: 

<!-- Create a View from a SQL Query -->
<%
    String query = "SELECT id, customer_name AS Name FROM Demo_Customers ORDER BY Name";
%>

<!-- 
   To display this page by itself, independent of the GUI:
      1. Activate the "Short URLs" solution in the code below,
         and deactivate the "showTabInIframe" solution.
      2. Add this page to the platform as "DemoCustomers.jsp".
      3. Visit it using https:{platform}/networking/pages/DemoCustomers.jsp
  
   To display this page as a "Web Tab" within the GUI:
      1. Leave the "showTabInIframe" solution activated in the code below.
      2. Add this page to the platform as "DemoCustomers.jsp".
      3. Go to Designer > Presentation > Web Tabs
      4. Click [New Web Tab]
      5. Settings:
           * Title: Demo Customers (custom)
           * Name: demoCustomersWebTab
           * Web Tab Type: Page
           * Pages:/DemoCustomers.jsp
      6. Click [Ok].
         The page now appears as an entry in the application workspace.
         When you click that entry, the page opens as a tab in the platform GUI.
-->
<html>
<head>
<title>Demo Customers</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<style type="text/css">
/* Not Used in this Example */
.body1 {
        background-color: #DCDEEE;
        font-family: Arial, Helvetica, sans-serif;
        font-size: 12pt;
        line-height: 24pt;
        color: #336699;
}

.a1 {
        color: #3366CC;
        text-decoration: none;
}

.form1 {
        background-color: #CCCC99;
        padding: 6px;
}

.header1 {
        font-family: Arial, Helvetica, sans-serif;
        font-size: 12pt;
        background-color: #006666;
        color: #DCDCDC;
        font-weight: bold;
}
/* End of Unused Styles */

.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%" class="title1">Customer</td>
      <td width="33%" class="title1">&nbsp;</td>
      <td width="33%" align="right" valign="middle" class="title1"></td>      
    </tr>      
<% 
  Result result = Functions.execSQL(query);
  if(result != null) {
    String service_url = "https://" 
       + com.platform.api.utility.ServiceConfiguration.getServiceDomainURL();
    ParametersIterator iter = result.getIterator();
    while(iter.hasNext()) {
       Parameters row = iter.next();
       String recordID = row.get("id");
       String name = row.get("Name");
       
       /* THE SIMPLEST SOLUTION -  A SHORT URL.
        * Use this solution when the current page is displayed by itself, 
        * independent of the platform GUI.
        */
       //String link = service_url + "/networking/record/Demo_Customers/" 
       //            + name_or_id;     
            // Use record "name" if the Record Identifier is defined as 
            // a unique index. Otherwise use "recordID".

       /* 
        * USE THIS SOLUTION WHEN THE CURRENT PAGE IS DISPLAYED AS A WEB TAB
        * WITHIN THE PLATFORM. (The short URL for a record goes to a full GUI 
        * page. That's the behavior you want when following a link in an email. 
        * But in the platform, a new tab opens with the whole GUI nested inside.)
        *
        * This solution requires:
        *   a) The object ID.
        *        - Visit https://{domain}/networking/rest/object/{objectName}
        *        - Use the content of the <id> tag
        *   b) The record ID returned by the SQL query
        * 
        * Link Pattern: 
        *   javascript:top.showTabInIframe(uniquekey, tempTitle, url, displayTitle);
        */
       String objectID = "3e7da19991d64b4a86cd92f040d86fd4";
       String tempTitle = "Loading...";
       String url = "Service?t=498&id=" + recordID
                  + "&object_id=" + objectID + "&a=view&policyaction=view";
       Boolean displayTitle = true;      
       String link = "javascript:top.showTabInIframe('" +recordID+ "','" 
                   +tempTitle+ "','" +url+ "'," +displayTitle+ ");";
%>
    <tr>
        <td class="td1"><a href="<%=link%>"><%=name%></a></td>
        <td class="td2"><!--Link: <%=link%> --></td>
        <td class="td2"><!-- ... --></td>
    </tr>
<%
    }
  }
%>
  </table>
</body>
</html>
Retrieved from "https://agileappscloud.info/aawiki-src/index.php?title=HowTo:Use_a_SQL_Query_to_List_Records_in_a_Custom_Page&oldid=15966"