Java API:Record Handling

The Record Handling Java APIs provide the ability to get, add, update, delete and search for Custom Objects.

1 Identifying Objects and Records

Many of the Java API record handling calls require an <object> element identifier. To find the Object Type Identifier:

To find an object type identifier:

1. Click > Customization > Objects
2. In the Display Title column, find the object you're looking for.
3. The Object Name column has the identifier you need.

Alternatively:

1. Go to > Customization > Objects > {object}
2. Click Properties
3. The object name appears at the top of the page

Note: For objects that are not listed, see the System Objects page. The Object Name is typically used as the identifier, because it is human-readable for both System Objects and Custom Objects. Object ID can be used, as well, but it is human-readable only for System Objects.

When a record is added to the database, the platform assigns it a unique record identifier. The value of a record identifier is opaque and you normally do not care about it.

However, several Java API calls take a recordID parameter. Find a record identifier by requesting the record_id field with the searchRecords call.

2 Atomic Operations

Operations initiated by Java API calls are atomic. In other words, they are transaction safe: Either all of the work is done or none of the work is done. For example, if you add a record, then all operations triggered by the add must succeed for the add to occur. If any error occurs, the record is not added.

Note:

When adding and updating records, the field type determines the data format.

Learn more: Java API:Field Type Reference

2.1 addRecord

The addRecord Java API helps in performing the following operations:

• Adding a new record to an object.
• Creating a new platform user in AgileApps.

2.2 Adding a new record to an object

Syntax

<syntaxhighlight lang="java" enclose="div">

Result result = Functions.addRecord(String objectName, Parameters params); </syntaxhighlight>

Parameters

• objectName - The Object to add the record to.
• params:
  • The field-value pairs for the object you are adding.
  • Turn off rules that might otherwise be triggered as a result of this action:

<syntaxhighlight lang="java" enclose="div">

params.add(PLATFORM.PARAMS.RECORD.DO_NOT_EXEC_RULES,"1"); </syntaxhighlight>

• Pass file parameters in the current request to any subsequent API calls made in a data policy defined on this object:

<syntaxhighlight lang="java" enclose="div">

params.add(PLATFORM.PARAMS.RECORD.ENABLE_MULTIPART,"1"); </syntaxhighlight>

Return

Result object

Note: The Result object contains the record ID for the new record. Use Result.getID() to retrieve it.

2.2.1 Example #1: Add a Record to the Account Class

This example creates an instance of Parameters and adds name-value pairs to it. The code then calls addRecord, assigning the returned value to an instance of Result and calling Result.getCode() to assign the error code to a variable which is then conditionally checked to determine the code to execute. If the call was not successful, the code calls throwError to display an error dialog.

<syntaxhighlight lang="java" enclose="div">

Parameters params = Functions.getParametersInstance(); params.add("name", "Acme Solutions"); params.add("number", "GRG2323339"); Result result = Functions.addRecord("ACCOUNT", params); int resultCode = result.getCode(); if(resultCode < 0) {

   // Some error happened. 
   String msg = "Account could not be added";
   Logger.info(msg + ":\n" + result.getMessage(), "Account");  // Log details
   Functions.throwError(msg + ".");                            // Error message

} else {

   // Take other actions on successful addition
   // of the account

} </syntaxhighlight>

2.2.2 Example #2: Add a Record with a Multi Object Lookup field

This example add a record to an object that contains a Multi Object Lookup, where:

• Books is an object that contains a MultiObject Lookup field
• libraryBook is a Multi Object Lookup field that points to a particular book and the library it came from
• 9978946545 is the ID the library object that contains the book.
  (Object ID must be specified. Object name will not work.)
• 767645492 is the record ID of the book in that library

<syntaxhighlight lang="java" enclose="div">

Parameters params = Functions.getParametersInstance(); params.add("title", "A Good Book"); params.add("libraryBook", "9978946545:767645492");

                 //  {object_id}:{record_id}

Result result = Functions.addRecord("Books", params); int resultCode = result.getCode(); if(resultCode < 0) {

   // Some error happened. 
   String msg = "Book record could not be added";
   Logger.info(msg + ":\n" + result.getMessage(), "Library");  // Log details
   Functions.throwError(msg + ".");                            // Error message

} else {

   // Successful add. Take other actions, as needed.

}</syntaxhighlight>

2.3 updateRecord

Syntax

<syntaxhighlight lang="java" enclose="div">

Result result = Functions.updateRecord(String objectName, String recordID, Parameters params); </syntaxhighlight>

Parameters

objectName

The identifier of the object

recordID

The identifier of the record to update.

• Use the searchRecords API to retrieve the record ID
• An error is returned if the record is not found.

params

• The field-value pairs for the object.
• Turn off rules that might otherwise be triggered as a result of this action:

<syntaxhighlight lang="java" enclose="div">

params.add(PLATFORM.PARAMS.RECORD.DO_NOT_EXEC_RULES,"1"); </syntaxhighlight>

• Pass file parameters in the current request to any subsequent API calls made in a data policy defined on this object:

<syntaxhighlight lang="java" enclose="div">

params.add(PLATFORM.PARAMS.RECORD.ENABLE_MULTIPART,"1"); </syntaxhighlight>

Return

Result object

2.3.1 Example #1: Update an Account Record

This example creates an instance of Parameters and adds name-values pairs to it. The code then calls updateRecord, assigning the returned value to an instance of Result and calling Result.getCode() to assign the error code to a variable which is then conditionally checked to determine the code to execute. If the call was not successful, the code calls throwError to display an error dialog.

<syntaxhighlight lang="java" enclose="div">

Parameters params = Functions.getParametersInstance(); String accountID = ""; // Some logic to populate accountID variable. params.add("name", "Acme Solutions"); params.add("number", "GRG2323339"); Result result = Functions.updateRecord("ACCOUNT", accountID, params); int resultCode = result.getCode(); if(resultCode < 0) {

   // Some error happened.
   String msg = "Account could not be updated";
   Logger.info(msg + ":\n" + result.getMessage(), "Accounts"); // Log details
   Functions.throwError(msg + ".");                            // Error message

} else {

   // Take other actions after successful addition of the account.

} </syntaxhighlight>

2.3.2 Example #2: Update a Record with a Multi Object Lookup field

This example uses the same fields and record IDs as those shown when adding a record with a multiobject lookup field.

<syntaxhighlight lang="java" enclose="div">

Parameters params = Functions.getParametersInstance(); String bookID = "117645552" params.add("title", "Another Good Book"); params.add("libraryBook", "9978946545:767645492");

                     //  {object_id}:{record_id}

Result result = Functions.updateRecord("Books", bookID, params); int resultCode = result.getCode(); if(resultCode < 0) {

   // Some error happened.
   String msg = "Book record could not be updated";
   Logger.info(msg + ":\n" + result.getMessage(), "Library"); // Log details
   Functions.throwError(msg + ".");                           // Error message

} else {

   // Successful update. Take other actions, as needed.

} </syntaxhighlight>

2.4 getRecord

Get a record, with specified fields.

Syntax

<syntaxhighlight lang="java" enclose="div">

Result result = Functions.getRecord(String objectName, String fields, String recordID

                 {, Parameters params} );

</syntaxhighlight>

Parameters

objectName

An Object Identifier

fields

The fields to retrieve.

• A comma-separated list of field names (Use the REST API:field Resource to get a complete list of fields.)
• The asterisk ("*") wildcard specifies all fields.
• For related-record fields in a Composite Object, specify the alias of the related object and the field name, where the alias is defined in the Object Relationships:

alias[.alias...].* or alias[.alias...].field

When fetched, an alias becomes a virtual "field" that contains an array of records from the related object, where each record contains the specified field(s).

• alias.* specifies all fields in the aliased object.

recordID

A Record Id.

params

An optional com.platform.api.Parameters object. Use it to specify the Retrieve Record Permissions Parameter, in order to find out if the user has update or delete permissions on the record.

Return

Result object. If the return code is greater than zero, use the getParameters() method to get a Parameters object with the record's fields. (See the Java Code Samples for multiple examples of the getRecord API.)

For a Composite Object relationship, the relationship alias becomes a "field" in the Parameters object. That field contains an ArrayList of embedded Parameters objects, one for each related record.

Composite Record Example

This example assumes that a relationship has been established between Cases and a Contacts object, where Contact records have an email field and a Lookup to Cases. With that relationship, multiple contacts can be associated with a case. (In this example, the name of the alias relationship is assumed to be "contacts", matching the name of the object.)

<syntaxhighlight lang="java" enclose="div">

// Get the status field and two composite fields for a specified case record. // Composite fields are identified as <alias>.<field1>, <alias>.<field2>, etc. // The returned structure contains an array of Parameters for the "alias", where // each Parameters item contains the fields from a related Contact. recordID = ...; Result result = Functions.getRecord("cases", "status,contacts.name,contacts.email", recordID); if (result != null && result.getCode() == 1) {

  Parameters params = result.getParameters();
  if (params != null)      
  {
      // Get the case status
      String caseStatus = params.get("status");
      
      // The "contacts" field contains the array of Parameters for associated Contacts.
      // Each entry in the array contains the fields for one record.
      // Here we get just the first entry from the array. (Loop to get them all.)
      ArrayList caseContacts = (ArrayList)params.getObject("contacts");
      HashMap contactParams = (HashMap)caseContacts.get(0); // Get first entry
      String name = (String)contactParams.get("name");      // Get the fields
      String email = (String)contactParams.get("email");  
   }

} </syntaxhighlight>

2.5 getRecordCount

Gets a count of records in an Object that match specified filtering criteria.

Syntax

<syntaxhighlight lang="java" enclose="div">

int count = Functions.getRecordCount(String objectName, String criteria); </syntaxhighlight>

Parameters

objectName

The object name or identifier

criteria

A filter expression that specifies records to select.

Returns

An integer containing a count of records that match the selection criteria.

2.6 changeOwnerShipInfo

Change the owner of a record.

Requirements

To perform this operation, you must either:

• Own the record,
• Be a System Administrator, or
• Have Change Ownership of my Team’s Records permission and be working on a record that is owned by someone on your Team.

Note: When adding a record with the addRecord method, it is only necessary to specify the owner in the requestParams:   requestParams.add("owner_id", "new user id");

Syntax

<syntaxhighlight lang="java" enclose="div">

Result result; result = Functions.changeOwnerShipInfo(String objectName, String recordId,

                                      String ownerId [, Boolean notifyEmail] );

</syntaxhighlight>

Parameters

objectName - The object name or identifier

recordId - The record identifier

ownerId - User ID of the new owner.

notifyEmail- An optional parameter. Set to true to send the new owner an email notification.

Returns

Result object

Throws

Exception

2.7 deleteRecord

Delete a record.

Syntax

<syntaxhighlight lang="java" enclose="div">

Result result = Functions.deleteRecord(String objectName, String recordID); </syntaxhighlight>

Parameters

objectName

The object name or identifier

recordID

The identifier of the record to delete.

params

• Turn off rules that might otherwise be triggered as a result of this action:

<syntaxhighlight lang="java" enclose="div">

params.add(PLATFORM.PARAMS.RECORD.DO_NOT_EXEC_RULES,"1"); </syntaxhighlight>

Return

Result object

Example

This example calls deleteRecord, assigning the returned value to an instance of Result and calling Result.getCode to assign the error code to a variable which is then conditionally checked to determine the code to execute. If the call was not successful, the code calls throwError to display an error dialog.

<syntaxhighlight lang="java" enclose="div">

String accountID = ""; // Some logic to populate accountID variable. Result result = Functions.deleteRecord("ACCOUNT", accountID); int resultCode = result.getCode(); if(resultCode < 0) {

   // Some error happened.
   String msg = "Account could not be deleted";
   Logger.info(msg + ":\n" + result.getMessage(), "Delete");  // Log details
   Functions.throwError(msg + ".");                     // Error message

} else {

   // Take other actions on successful addition
   // of the account.

} </syntaxhighlight>

2.8 deleteUser

Delete a user.

Syntax

<syntaxhighlight lang="java" enclose="div">

Result result = Functions.deleteUser(String userID); </syntaxhighlight>

Parameters

userID

The identifier of the user to delete.

Return

Result object

Example

This example calls deleteUser, assigning the returned value to an instance of Result and calling Result.getCode to assign the error code to a variable which is then conditionally checked to determine the code to execute. If the call was not successful, the code calls throwError to display an error dialog.

<syntaxhighlight lang="java" enclose="div">

String userID = ""; // Some logic to populate userID variable. Result result = Functions.deleteUser(userID); int resultCode = result.getCode(); if(resultCode < 0) {

   // Some error happened.
   String msg = "User could not be deleted";
   Logger.info(msg + ":\n" + result.getMessage(), "Delete");  // Log details
   Functions.throwError(msg + ".");                     // Error message

} else {

   // Take other actions on successful addition
   // of the user.

} </syntaxhighlight>

2.9 Retrieve Record Permissions Parameter

When retrieving or searching for records, it is sometimes useful to find out if the user has update or delete permissions. (For example, to determine whether or not to display a button for the purpose.)

To do that, you specify the PLATFORM.PARAMS.RECORD.RETRIEVE_RECORD_PERMISSIONS parameter, with a value of "1".

For example:

<syntaxhighlight lang="java" enclose="div">

recordID = ...; com.platform.api.Parameters params = Functions.getParametersInstance(); params.add("PLATFORM.PARAMS.RECORD.RETRIEVE_RECORD_PERMISSIONS" ,"1"); Result result = getRecord("profile", "id,profileName", recordID, params); </syntaxhighlight>

When specified, and when the field list contains the id field (needed to do an update or delete), a RecordPermissions bean containing the relevant permissions is returned in the Result object:

<syntaxhighlight lang="java" enclose="div">

Parameters resultParameters = result.getParameters(); RecordBean.RecordPermissions permissions =

  (RecordBean.RecordPermissions)resultParameters.getObject("PLATFORM.PARAMS.RECORD.RECORD_PERMISSIONS");

if (permissions != null) {

  Logger.info("Update=" + permissions.getUpdatePermission(), "Permissions");
  Logger.info("Delete=" + permissions.getDeletePermission(), "Permissions");

} </syntaxhighlight>

2.10 searchRecords

Search and retrieve the records for an object.

2.10.1 Syntax

<syntaxhighlight lang="java" enclose="div">

Result result; result = Functions.searchRecords (String objectOrView, String fields, String criteria

                                   {, Parameters params} );

result = Functions.searchRecords (String objectOrView, String fields, String criteria,

                                 String sortBy, String sortOrder, 
                                 String sortBy2, String sortOrder2, 
                                 int offset, int numberOfRows {, Parameters params} );

</syntaxhighlight>

Note: Functions.searchRecords checks for roles and application permissions of the logged in user before returning any result. There are multiple joins involved in the query formed which might duplicate the results. We recommend you to use Set interface (Java collection) to store the results.

2.10.2 Parameters

objectOrView

The object name, Object Type Identifier, or Database View ID.

fields

The fields to retrieve.

• A comma-separated list of field names (Use the REST API:field Resource to get a complete list of fields.)
• The asterisk ("*") wildcard specifies all fields.
• For related-record fields in a Composite Object, specify the alias of the related object and the field name, where the alias is defined in the Object Relationships:

alias[.alias...].* or alias[.alias...].field

When fetched, an alias becomes a virtual "field" that contains an array of records from the related object, where each record contains the specified field(s).

• For fields in a Database View, use the object alias for each field, in the form {alias}.{field}, where the aliases are defined in the specification of the Database View.

criteria

A filter expression that specifies records to select.

sortBy

Sort the search results on the specified field.

sortOrder

Specify if the sort order is ascending ("asc") or descending ("desc"). Not case sensitive. The default is ascending.

sortBy2

Do a secondary sort on the specified field.

sortOrder2

Specify if the sort order on the second level is ascending ("asc") or descending ("desc"). Not case sensitive. The default is ascending.

offset

The maximum number of records that you can retrieve in a single call to searchRecords is 5,000. If you need to retrieve more than 5,000 records, you must set up a loop where you do paging by incrementing the offset parameter.

For example, if you need to retrieve 25,000 records, you need to set the offset parameter to zero (0) and the numberOfRows parameter to 5,000 on the first pass to get records 1-5000. Then, on the second pass, you set offset to 1 to get records 5001-10,000, and so on. In other words, you multiply offset by numberOfRows and add one to determine the number of the first record retrieved at each pass.

numberOfRows

The maximum number of records to return

params

An optional com.platform.api.Parameters object. Use it to specify the Retrieve Record Permissions Parameter, in order to find out if the user has update or delete permissions on the records.

alias.* specifies all fields in the aliased object.

2.10.3 Returns

Result object. If the return code is greater than zero, use the Result_Class#getIterator method to cycle through the list of Parameters objects it contains, one per record.

2.10.4 Learn More

• Filter Expressions in JAVA APIs

2.10.5 Examples

2.10.5.1 Example: Basic Search

This example calls searchRecords with three parameters, assigning the returned value to an instance of Result and calling Result.getCode to assign the error code to a variable which is then conditionally checked to determine the code to execute.

If the search was not successful, the code calls throwError to display an error dialog.

If the search was successful and returned records, the code creates an instance of ParametersIterator. In a while loop, the code calls ParametersIterator.next to get an instance of Parameters from which it then extracts the name and number keys.

<syntaxhighlight lang="java5" enclose="div">

String accountID = ""; // Some logic to populate accountID variable. Result result = Functions.searchRecords("ACCOUNT", "record_id,name,number",

   "name contains 'Acme'");

int resultCode = result.getCode(); if (resultCode < 0) {

   // Some error happened.
   String msg = "Account could not be retrieved";
   Logger.info(msg + ":\n" + result.getMessage(), "Accounts"); // Log details
   Functions.throwError(msg + ".");                            // Error message

} else if (resultCode == 0) {

  // No records found. Take action according to your business logic

} else {

 //Records retrieved successfully
 ParametersIterator iterator = result.getIterator();
 while(iterator.hasNext())
 {
   Parameters params = iterator.next();
   String accountID = params.get("record_id");
   String accountName = params.get("name");
   String number = params.get("number");
   // Take action according to your business logic
 }

} </syntaxhighlight>

2.10.5.2 Example: Sorted Search

This example calls searchRecords with nine parameters, assigning the returned value to an instance of Result and calling Result.getCode to assign the error code to a variable which is then conditionally checked to determine the code to execute:

• If the search was not successful, the code calls throwError to display an error dialog.

• If the search was successful and returned records, the code creates an instance of ParametersIterator. In a while loop, the code calls ParametersIterator.next method to get an instance of Parameters from which it then extracts the name and number keys.

<syntaxhighlight lang="java5" enclose="div">

// Next searchRecords call will sort the results, first by city // (ascending) and then by number (descending). It will return // first 200 records matching the criteria.

Result result = Functions.searchRecords("ACCOUNT", "record_id,city,name,number", "name contains 'California'", "city", "asc", "number", "desc", 0, 200); int resultCode = result.getCode(); if(resultCode < 0) {

   // Some error happened.
   String msg = "Account could not be retrieved";
   Logger.info(msg + ":\n" + result.getMessage(), "Accounts"); // Log details
   Functions.throwError(msg + ".");                            // Error message

} else if(resultCode == 0) {

 // No records found
 // Take action according to your business logic

} else {

  //Records retrieved successfully
  ParametersIterator iterator = result.getIterator();
  while(iterator.hasNext())
  {
     Parameters params = iterator.next();
     String accountID = params.get("record_id");
     String accountName = params.get("name");
     String number = params.get("number");
     // Take action according to your business logic.
  }

} </syntaxhighlight>

2.10.5.3 Example: Multi-Page Search

This search retrieves 5,000 records at a time, with each set of records in a separate "page".

<syntaxhighlight lang="java5" enclose="div">

int i; int numberOfRecords; int numberOfPages; int max_records_per_page = 5000;

//Ensure use of same criteria to do the search and to get the record count String criteria = "";

String objectName = "ACCOUNT"; String fields = "record_id,name"; String sortBy = ""; String sortOrder = ""; String sortBy2 = ""; String sortOrder2 = "";

numberOfRecords = Functions.getRecordCount(objectName, criteria); numberOfPages = numberOfRecords / max_records_per_page; if (numberOfRecords % max_records_per_page > 0) {

 // If the modulus operator says the division left a remainder, 
 // then do one more fetch for the remaining records.
 numberOfPages++;

}

for(i=0; i < numberOfPages; i++) {

   Result result = Functions.searchRecords (
       objectName, fields, criteria, 
       sortBy, sortOrder, sortBy2, sortOrder2,
       i, max_records_per_page); 
   int resultCode = result.getCode(); 
   if (resultCode < 0)
   {
       // Some error happened.
       String msg = "Account could not be retrieved";
       Logger.info(msg + ":\n" + result.getMessage(), "Accounts"); // Log details
       Functions.throwError(msg + ".");                            // Error message
   }
   else if (resultCode == 0)
   {
       // No records found. Take action according to your business logic
   }
   else
   { 
       // Records retrieved successfully 
       ParametersIterator iterator = result.getIterator(); 
       while(iterator.hasNext()) 
       { 
           params = iterator.next(); 
           String recordId = params.get("record_id"); 
           Logger.info(recordId, "Accounts"); 
       } 
   } 

} </syntaxhighlight>

2.10.5.4 Example: Searching Audit Logs

Audit Logs can be searched using the getRecord and searchRecord APIs

Learn more: Audit Log Fields

How it Works

Provide search criteria to search multiple audit log records and retrieve a list of audit log fields. The getRecord and searchRecords APIs are used in actions that invoke a Java Method.

Syntax

public static Result Functions.searchRecords(String objectId, String fields, String criteria)

objectId

log

fields

record_id, ownerid, type, operation, object, date_created, description, type_code

recordId

Audit log record Id

Return

Result object

Audit Log Example Based on Filtering Criteria

<syntaxhighlight lang="java5" enclose="div">

Result result = Functions.searchRecords("log", "record_id,type,operation,"+ "object,description", "(record_id starts with'65') and (description contains 'audit')");

int resultCode = result.getCode();

if(resultCode < 0) {

   // Some error happened.
   String msg = "Error during search";
   Logger.info(msg + ":\n" + result.getMessage(), "Accounts"); // Log details
   Functions.throwError(msg + ".");                            // Error message

} else if(resultCode == 0) {

  // No records found. Take action according 
  Functions.throwError("No results returned from search");

} else {

 //Records retrieved successfully
 ParametersIterator iterator = result.getIterator();
 while(iterator.hasNext())
 {
   Parameters params = iterator.next();
   String desc= params.get("description");
   String type= params.get("type");
   String obj = params.get("object");
   String record_id = params.get("record_id");
   String operation = params.get("operation");                   

   // Take action according to your business logic
 }

} </syntaxhighlight>

Learn more: Audit Log Fields

2.10.5.5 Example: Search Test

This example does a number of things:

• It defines a search method on the Orders object.
• It creates a test method to run that search, taking advantage of the [RunTests] button to execute the method.
  Learn more: Unit Test Framework
• It gets all fields in searchRecords and getRecord, using the "*" wildcard.
• It enumerates the names of the fields that are returned, putting them into the Debug Log.
  Note: Enumerating things in a JSP Page would produce a nicer result, without much additional effort. That page could then be named as a Web Tab, which would become available for use as a tab in the application.
• In addition data contained in an Order record, it uses the Java getRecord API and the data in the related_to_Customer lookup field to get data from the related Customer record.
  Note: The Java API Composite Object getRecord method could also be used to specify the customer name as a field to retrieve. But the "wildcard" specifier could not be used for field names, in that case.

<syntaxhighlight lang="java" enclose="div">

package com.platform.demo.test;

import com.platform.api.*;

public class SearchTest {

 public String searchOrders() throws Exception {
 
   Result result = Functions.searchRecords("Order", "*", "");
      //Toss the exception, if one occurs
   
   int resultCode = result.getCode();
   if (resultCode < 0)
   {
       // Some error happened.
       String msg = "Error during search";
       Logger.info(msg + ":\n" + result.getMessage(), "Search");  // Log details
       Functions.throwError(msg + ".");                           // Error message
       return("");
   }
   else if(resultCode == 0)
   {
       // No records found. Take action accordingly
       Functions.throwError("No results returned from search");
       return("");
   }
   else
   {
       //Records retrieved successfully
       ParametersIterator iterator = result.getIterator();
       boolean first_time = true;
       while(iterator.hasNext())
       {
           Parameters params = iterator.next();
           String num= params.get("order_number");
       
           // List the fields present in the params object
           if (first_time) {
               for (Object key : params.keySet() ) {
           	   Logger.info(""+key, "Search");
               }
               first_time = false;
           }
                   
           // Use the Lookup value in related_to_Customer to get customer info
           String customerID = params.get("related_to_Customer");
           Result customer = 
       	Functions.getRecord("Customer", "*", customerID);
           String company = customer.getParameters().get("customer_name");
       
           // Echo order info
           Logger.info("Order #" +  num + " for " + company, "Search");       
       }        
       //Result code is the #of records found.
       return ""+resultCode;
   }
 }   
 
 @TestMethod
 public void runSearchOrders() throws Exception {
    String record_count = searchOrders();
    RunTest.assertEquals(record_count, "6");
 }
 

} </syntaxhighlight>

2.11 execSQL

Execute a SQL query.

2.11.1 Syntax

<syntaxhighlight lang="java" enclose="div">

Result result = Functions.execSQL(String query); </syntaxhighlight>

2.11.2 Parameters

query

The SQL query to execute.

Learn more: SQL Syntax

2.11.3 Returns

Result object. If the return code is greater than zero, use the Result_Class#getIterator method to cycle through the list of Parameters objects it contains, one per record.

2.11.4 Sample Code

This sample retrieves a value from the most recent record that matches the specified criteria:

<syntaxhighlight lang="java" enclose="div">

try {

  String latest_value;
  String sql = 
     "SELECT some_field FROM MyObject " +
     "WHERE another_field = '" + someValue + "' " +
     "ORDER BY date_created DESC " +            
     "LIMIT 1";
  Result result = Functions.execSQL(sql);
  int resultCode = result.getCode();
  if (resultCode < 0)
  {
     // Error occurred
     String msg = "Sample: Error during SQL search";
     Logger.info("Sample:\n" + result.getMessage(), "SQL");
  }
  else if (resultCode > 0)
  {
     // A record was found. (Otherwise, resultCode == 0)                     
     ParametersIterator it = result.getIterator();
     Parameters params = it.next();  // Use a loop if Limit > 1       
     latest_value = params.get("some_field");
     Logger.info("Sample: latest value = " + latest_value, "SQL");        
  }

} catch (Exception e) {

  String msg = "Sample: Exception during SQL search";
  Logger.info("Sample:\n" + e.getMessage(), "SQL");        

} </syntaxhighlight>

2.11.5 Sample App

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

This sample uses the execSQL operation to populate a JSP page with a list of object records.

2.11.6 Learn More

• SQL Browser
• SQL Syntax
• SQL Functions

2.12 replaceTemplateVariables

Functions.replaceTemplateVariables(String text, String objectId, String recordId)

Finds and replaces Template Variables with new information

Syntax

Result result = Functions.replaceTemplateVariables(String text, String objectId, String recordId)

Parameters

text

Text string

objectID

The identifier of the object

recordID

The identifier of the record

Example

An action that executes Java code could contain the following lines to replace the $project.name Template Variable with a new Object Type Identifier (objectId) and Record Id (recordId).

<syntaxhighlight lang="java" enclose="div"> String text = "$project.name" String replaced_text = Functions.replaceTemplateVariables(text, "PROJECT", "123456") </syntaxhighlight>