Custom Functions

The custom functions feature of Form Logic allows for the writing of custom JavaScript functions for advanced functionality that cannot be configured with the standard settings. These include things like custom conditions, custom form control, web service access, among many other things. 

This article describes some of the commonly required functions and code to perform many different tasks.

Key Concepts

JavaScript

Standard JavaScript web calls can access all of the elements on a TracerPlus form. Each element has a control ID that maps to an HTML ID attribute on the form. The HTML ID differs slightly from the Control ID that is shown in the form designer within TracerPlus Desktop. The difference is that the HTML ID contains a session index prefix before the control. 

Example: A text box's Control ID in TracerPlus Desktop is '12', and its in the second session. Then the HTML DOM ID when using Form Logic is '01-12' to access this text box. 

To access this element via standard JavaScript, one could use

const pTextBoxControl = document.getElementById('01-12');

All standard JavaScript functions and properties can then be used on this control, such as style, className etc.

SQL

Within a Custom Function TracerPlus session data can be manipulated using custom SQL queries and statements. This allows for advanced modification of data either during an insert operation, or when selecting data to display to the user. Each session within TracerPlus has its own database table. The table and fields are named based on the session and field index (1 based). For example, The table for the 4th session in TracerPlus is Session4. This is regardless of what the session name is configured to in TracerPlus Desktop. The same goes for the fields. They are named Field{x} where {x} is replaced with the field number, i.e. the second field in the session is Field2.

Field Tokens

Within a custom function field tokens can be used to represent the current value in a TracerPlus form field. These tokens are formatted as [*field_index*], where the field_index is the field (0 based) that contains the value to be replaced. These tokens can be used anywhere within the custom function.

Common Functions and Properties

Get a field value:

const sFieldValue = document.ActiveSession.getFieldByIndex(5).getFormControlValue();
   OR
const sFieldValue = '[*5*]'; // Using a field token.

Set a field value:

document.ActiveSession.getFieldByIndex(3).setFormControlValue('Test Value');
   OR
document.ActiveSession.getFieldByIndex(3).setFormControlValue('[*2*]'); // Field token.

Click a button on the form:

handleFormButtonClicked('02-25'); // Where 02-25 is the HTML ID of the button.

Insert an array of data into a cached grid:

const aArrayOfRowsToInsert = [{Field1: "Wahoo"},{Field2: "Yahoo"}];

var nGridCount = document.ActiveSession.EntryGridControls.length;

// Loop through all the grid controls of this session 
// and find the one that we want to insert into by the 'documentId'.
for (var i = 0; i < nGridCount; i++) {
    var pGrid = document.ActiveSession.EntryGridControls[i];
    if (!pGrid)
        continue;
    if (pGrid.documentId != '00-11') // Change this to the ID that is required in the project.
        continue;
        
    pGrid.CachedGridData = []; // Init the cached grid data array.

    for (var j = 0; j < aArrayOfRowsToInsert.length; j++){
        pGrid.CachedGridData.push({id:j+1,values:{Field1:aArrayOfRowsForInsert[j].Field1, Field2:aArrayOfRowsForInsert[j].Field2}}); // Add to the cached grid data array.
    }

    // We need to call refreshGridData_Cached so that the 
    // grid is refreshed with our newly inserted rows.
    pGrid.refreshGridData_Cached();
}

To insert data into a TracerPlus table using custom SQL within the custom function:

var sInsertSQL = "INSERT INTO Session1 (Field1, Field2) VALUES ('Value1', 'Value2')";
var pInsertJson = [{sql : sInsertSQL}];
  
doDBExecuteSql(pInsertJson, function() {
    alert('Success!')
});

Selecting data from a TracerPlus session using the TracerPlus DB plugin. The select result is added to an array:

var aRecords = [];
var handleDataSelected = function (pSelectResult){
    var pRowset = pSelectResult.rowset;
    var nResultCount = pRowset.length;

    for (j = 0; j < nResultCount; j++) {
        var pRow = pRowset[j];
        if (pRow) {
            var pRowToAdd = {data:[], where:[]}
            var nFieldReturnLen = pRow.length;
            for (var p = 0; p< nFieldReturnLen; p++){
                var sFieldName = "field" + (p+1);
                if (pRow[p].value){
                    pRowToAdd.data.push({name: sFieldName, value: pRow[p].value.escapeSpecialChars(), field_index: p, blob:"0"});
                }
            }
            aRecords.unshift(pRowToAdd); // Insert before the new record.
        }
    }
}

// Build the request to select 3 fields from Session 21 (0 based), where Field1 = Field token 0
var pSelectReq = [{table: {sessionindex: 20, create: false}},
                  {fields:[]},
                  {where: [{"name":"Field1","value":"[*0*]","operator":"=" }], type: "0"},
                  {limits : {limit:-1, offset:0}},
                  {sort : []}];

pSelectReq[1].fields.push({name:"Field1", value:"",f_idx:0 });
pSelectReq[1].fields.push({name:"Field2", value:"",f_idx:1 });
pSelectReq[1].fields.push({name:"Field4", value:"",f_idx:3 });
        
doDBSelect(pSelectReq, handleDataSelected, null);

Refresh the record count on a form - *This is useful if data is submitted via Custom SQL which bypasses the typical form refresh flow after a record is submitted:

document.ActiveSession.getSessionRecordCount();