HowTo:Use a SQL Query to List Records in a Custom Page
From AgileApps Support Wiki
For: Developers See more: |
This sample JSP page uses a SQL query to create a list of Customer object records.
To achieve that goal:
- This page uses SQL Syntax in the Java execSQL API to issue the query.
(It could also have used the REST execSQL Resource.) - It uses Platform URLs to access record details.
- This page uses SQL Syntax in the Java execSQL API to issue the query.
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:
- Go to
> Developer Resources > Pages
- Click [New Page]
- For the "title", give the page a valid name. For example: CustomOrderList.jsp
- Include header files.
- Copy in the sample code below.
- 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:
- Visit the page in the browser at https://{yourDomain}/networking/pages/{pagename}.jsp
- The page appears by itself, without any platform context.
To make the page available as a tab in the GUI:
- Go to
> Developer Resources > Web Tabs
- Click [New Web Tab]
- 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
- Set Web Tab Type to Page
- Select the page you created.
- Click [Save]
The Web Tab is created. - Log out and the log back in to see the new Web Tab.