Adaptive Planning Functions

Test Connection Function

Context Parameter

The Test Connection is provided with 1 parameter, 'context'.

Method	Parameters	Return Type	Description
getDataSource()		ai.dataSource	Returns the active data source.
getCalendar()		ai.calendar	Returns the Adaptive Planning Calendar

Example

function testConnection(context) {
    // Step 1: Create a https request to send to Earthquake USGS API
    var url = 'https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_hour.atom';
    var method = 'GET';
    var body = ``;
    var headers = {};
    // Step 2: Send request and receive response
    try{
    
        var response = ai.https.request(url, method, body, headers);
        ai.log.logInfo("Test Connection Successful");
       }
    catch(e)
    {
    
        ai.log.logInfo("Test Connection Failed");
    }
    // Step 3: Interrogate response to see if it was successful. Return true or false depending on the result.
    if (response.getHttpCode() == '200') {
        var body = response.getBody();
        return true;
    } else {
        return false;
    }
}

Import Structure Function

Context Parameter

The Import Structure function is provided with 1 parameter, 'context'.

Method	Parameters	Return Type	Description
getDataSource()		ai.dataSource	Returns the active data source.
getProgressManager()		ai.progressManager	Returns an object that can be used to update the Import Structure task percentage complete value.
getStructureBuilder()		ai.structure.structureBuilder	Returns an object that is used to create the data source structure - tables, columns, parameters etc.
getCalendar()		ai.calendar	Returns the Adaptive Planning Calendar

Example

function importStructure(context) {
    //getDataSource
    var dataSource=context.getDataSource();
    // getStructureBuilder
    var builder = context.getStructureBuilder();
    //getCalendar
    var calendar= context.getCalendar();
  }

ai.progressManager

The ai.progressManager object allows a user to manually set the percentage complete of the task that is running their Import Structure script. Script execution accounts for 70% of the overall task percentage complete. Therefore, progress values defined via the ai.progressManager are scaled to represent the progress of the overall task. Example: Setting the ai.progressManager percentage complete to 100% would equate to 70% of the running task, 50% would equate to 35% of the running task and so on.

Functions

Method	Parameters	Return Type	Description
getProgress()		Number	Returns the percentage complete of the script as defined be the user. If the user has not previously set the percentage complete, this function would return 0.
setProgress( value )	value: Number Value to set script percentage complete to		Sets the script percentage complete. This works as follows: Only numeric values are accepted such as 0, 10, 22.5 100, 110. Values greater than 100 are treated as 100. Values less than the current script progress are ignored. Example: Call setProgress(50) followed by setProgress(40). This results in the script percentage complete to 50%. All other input types for 'value' will have no effect on script progress,

Example

function importStructure(context) {    
    ..........
 
 
    var progressManager = context.getProgressManager();
 
 
    // Set script progress % to 10
    progressManager.setProgress(10);
 
 
    // Increase script progress % to 50
    progressManager.setProgress(50.0);
 
 
    // Set progress to 100 by using value greater than 100
    progressManager.setProgress(230);
    progressManager.getProgress(); // returns 100
 
 
    // Increase progress by 10% based on current progress
    progressManager.setProgress(progressManager.getProgress() + 10);
 
 
 
 
    ..........
    return builder;
 }

ai.structure.structureBuilder

Functions

Method	Parameters	Returns	Description
addTable( tableId )	tableId : String Unique Id of the table	ai.structure.editableTable	Creates and returns a new table for the data source.

Example

Provides the ability to define the tables and column structure that will be populated by data.

function importStructure(context) {
........
//getStructureBuilder
    var builder = context.getStructureBuilder();
//Example addTable( tableId )-"Sample"
    var table = builder.addTable("Sample");
....... 
}

ai.structure.editableTable

Functions

Method	Parameters	Returns	Description
setDisplayName( displayName )	displayName : String Value to set the table's display name property to.	ai.structure.editableTable	Sets the display name of the table.
addColumn( columnId )	columnId : String RemoteId of the column	ai.structure.editableColumn	Creates and returns a new child column of the table.
addRequiredParameter( parameterId )	parameterId : String Id of the parameter	ai.structure.editableParameter	Creates and returns a new required parameter for the table. An assignable parameter must be set as the value of a Required Parameter when this table is used as part of ImportData or ImportStructure.

ai.structure.editableColumn

Functions

Method	Parameters	Returns	Description
setDisplayName( value )	value : String Value to set the columns' displayName property to	ai.structure.editableColumn	Sets the display name of the column.
setDisplayOrder( value )	value : String Value to set the columns' displayOrder property to	ai.structure.editableColumn	Sets the display order of the column.
setIncludeByDefault( value )	value : Boolean Value to set the columns IsEnabled property to	ai.structure.editableColumn	Sets the column to be enabled for data import.
setIncludeInKey( value )	value : Boolean Value to set the columns' includeInKey property to	ai.structure.editableColumn	Indicates whether the column is part of the table key.
setMandatoryForImports( value )	value : Boolean Value to indicate if this column is enabled for data imports by mandatory	ai.structure.editableColumn	Sets the column to be enabled for data import by mandatory.
setNonFilterable( value )	value : Boolean Value to set the columns' nonFilterable property to	ai.structure.editableColumn	Indicates if the column can be included in a remote query.
setRemoteColumnType( value )	value : String Value to set the columns' remoteColumnType property to	ai.structure.editableColumn	Sets the remote data type of the column e.g. 'string', 'integer' etc
setTextColumnType( length )	length : Integer Max length of the values this column can hold	ai.structure.editableColumn	Makes the column a text column.
setIntegerColumnType()		ai.structure.editableColumn	Makes the column an integer column.
setFloatColumnType()		ai.structure.editableColumn	Makes the column a double precision column.
setBooleanColumnType()		ai.structure.editableColumn	Makes the column a boolean column.
setDateTimeColumnType()		ai.structure.editableColumn	Makes the column a date time column.

ai.structure.editableParameter

Functions

Method	Parameters	Returns	Description
setDisplayName( value )	value : String Value to set the parameters' displayName property to		Sets the display name of the parameter.
setForDataImport( value )	value : Boolean Value indicating whether the parameter will be accessible in a ImportData script		Enables parameter for data import.
setTypePeriodRange()			Changes the type of the parameter to a Period Range

Example

function importStructure(context) {
...............
    //addColumn
    var valueColumn= table.addColumn('Value');
    //setDisplayName
    valueColumn.setDisplayName('Value');
    // Set the data type for the column - Can be Integer, Float, DateTime or Boolean values
    valueColumn.setFloatColumnType();
    //setDisplayOrder
    valueColumn.setDisplayOrder('0');
    //setIncludeByDefault
    valueColumn.setIncludeByDefault(true);
    //setRemoteColumnType
    valueColumn.setRemoteColumnType('float');
    // setIncludeInKey
    valueColumn.setIncludeInKey(false);
    //setMandatoryForImports
     valueColumn.setMandatoryForImports(false);
..............
}

Import Data Function

Context Parameter

The Import Data function is provided with 1 parameter, 'context'.

Method	Parameters	Return Type	Description
getRowset() Generates API deprecation warning in the logs after support enables getRowset(ColumnIds) at your request with the 2021R1 release. The getRowset() method will continue to function but ignore any parameters.		ai.rowset	Returns the Rowset object that rows are added to. The Rowset defines the Table, Columns and MaxRows.
getRowset([columnIds]) Open a case in the Workday Customer Center to enable the " custom cloud services reads staging table by specified columns " feature flag.	[Column Ids]	ai.rowset	Returns the Rowset object that rows are added to, in your specified column order. The Rowset defines the Table, Columns and MaxRows.
getDataSource()		ai.dataSource	Returns the active data source.
getCalendar()		ai.calendar	Returns the Adaptive Planning Calendar

Example

This example assumes that the rowset contains an Integer column and a Text column in order.

function importData(context) {
...........
   var rowset = context.getRowset();
..........
    var rowset= context.getRowset([ 'NAME', 'ID']);
}

ai.rowset

The Rowset object is the mechanism by which 'rows' inside a users' script are transported into the Integration stack. A Rowset models the shape (column names and their data types, maxRows and tableId) of the table that a user is running either their PreviewData or ImportData script against.

Functions

Method	Parameters	Return Type	Description
addRow( cells )	cells : Array<Primitive Types> Array of values for each column, in order		Adds an array of cell values as a row to the Rowset. Cell values must be either a valid String (can be null), Number (Finite Integer or Decimal), Boolean or Date object. Each cell value type must match its corresponding column data type in the order defined by getColumnNames. The number of cell values must match the number of columns defined.
getTableId()		String	Returns the table id of the table being previewed.
getMaxRows()		Integer	Returns the maximum number of rows allowed in this Rowset. 0 means unknown, as used by ImportData.
getColumns()		Array<ai.dataSource.table.column>	Returns the table columns selected for the preview.
setSmartParsingEnabled( val )	val : Boolean Value indicating whether to turn this feature on or off		If possible, enabling this setting will parse each string cell value into the target columns required data type automatically during the addRow() function. This saves a user having to lookup a target columns' data type and doing manual parsing if the data type of the value for the current cell does not match the target columns' data type. This is especially useful when dealing with 3rd party API's that return all row values as strings via JSON or XML, and each cell value can be successfully parsed into Integer, Float, Boolean or DateTime values.
getSmartParsingEnabled()		Boolean	Returns true if this setting is enabled. Defaults to false.

function importData(context) {
    var dataSource = context.getDataSource();
    var settings = dataSource.getSettings();
    var rowset = context.getRowset();
    
    //Set and Get SmartParsingEnabled() value
    rowset.setSmartParsingEnabled(true);
    ai.log.logInfo(getSmartParsingEnabled()); //true
    
    //Set a Start date and End date in the Designer Settings tab with the name "Period Range"
    var startDateString = dataSource.getSetting("Period Range").getValue().getFromPeriodStartDateTime().substring(0, 10);
    var endDateString = dataSource.getSetting("Period Range").getValue().getToPeriodEndDateTime().substring(0, 10);
    
    //get columns
    var columns = rowset.getColumns();

    for (var i = 0; i < dataSample.rows.length; i++) {
        var colName;
        var cells = [];
        for (var j = 0; j < columns.length; j++) {
            var colValue;
            var re = /\\\-/g;
            if ((new Date(dataSample.rows[i]["Effective Date"]) >= new Date(startDateString)) && (new Date(dataSample.rows[i]["Effective Date"]) < new Date(endDateString))) {
                colName = columns[j].getId();
                colValue = dataSample.rows[i][colName];
                cells.push(colValue.toString().replace(re, '.'));

            }
        }
        if (cells.length == columns.length) {
            //add rows
            rowset.addRow(cells);
        }
    }
}  

Preview Data Function

Context Parameter

The Preview Data Function is provided with 1 parameter, 'context'.

Method	Parameters	Return Type	Description
getDataSource()		ai.dataSource	Returns the active data source.
getRowset()		ai.rowset	Returns the Rowset object that rows are added to. The Rowset defines the Table, Columns and MaxRows.
getCalendar()		ai.calendar	Returns the Adaptive Planning Calendar

Example

Provides the ability to preview data stored in the cloud source.

function previewData(context) {

    var dataSource = context.getDataSource();
    var settings = dataSource.getSettings();
    var rowset = context.getRowset();
    
    //Set and Get SmartParsingEnabled() value
    rowset.setSmartParsingEnabled(true);
    ai.log.logInfo(getSmartParsingEnabled()); //true
    
    //Set a Start date and End date in the Designer Settings tab with the name "Period Range"
    var startDateString = dataSource.getSetting("Period Range").getValue().getFromPeriodStartDateTime().substring(0, 10);
    var endDateString = dataSource.getSetting("Period Range").getValue().getToPeriodEndDateTime().substring(0, 10);
    
    //get columns
    var columns = rowset.getColumns();

    for (var i = 0; i < dataSample.rows.length; i++) {
        var colName;
        var cells = [];
        for (var j = 0; j < columns.length; j++) {
            var colValue;
            var re = /\\\-/g;
            if ((new Date(dataSample.rows[i]["Effective Date"]) >= new Date(startDateString)) && (new Date(dataSample.rows[i]["Effective Date"]) < new Date(endDateString))) {
                colName = columns[j].getId();
                colValue = dataSample.rows[i][colName];
                cells.push(colValue.toString().replace(re, '.'));

            }
        }
        if (cells.length == columns.length) {
            //add rows
            rowset.addRow(cells);
        }
    }
}  

Combined Test Connection, Import, and Preview Data Example

//Consider a static JSON Data Sample set
dataSample = {
              rows: [
                        {"Value": 10, "Measure": "", "Test": "Palo Alto", "Effective Date": "2018-11-01"},
                        {"Value": 20, "Measure": " ", "Test": "Palo Alto", "Effective Date": "2018-11-01"},
                        {"Value": 30, "Measure": "  ", "Test": "Palo Alto", "Effective Date": "2018-11-01"},
                        {"Value": 40, "Measure": "   ", "Test": "Palo Alto", "Effective Date": "2018-11-01"},
                        {"Value": 50, "Measure": "1 ", "Test": "Pleasanton", "Effective Date": "2018-11-01"},
                        {"Value": 10, "Measure": "2  ", "Test": "Pleasanton", "Effective Date": "2018-12-01"},
                        {"Value": 20, "Measure": "3   ", "Test": "Pleasanton", "Effective Date": "2018-12-01"},
                        {"Value": 30, "Measure": "4    ", "Test": "Pleasanton", "Effective Date": "2018-12-01"},
                        {"Value": 40, "Measure": "5     ", "Test": "Pleasanton", "Effective Date": "2018-12-01"}
                    ]
               };
               
//Table Definition
ExternalMetadataDefinition = {
    Tables: [{
        name: "dataSample",
        displayName: "Sample",
        columns: [{
            name: "Value",
            displayName: "Value",
            type: "float",
            includeByDefault: true
        }, {
            name: "Measure",
            displayName: "Measure",
            includeByDefault: true
        }, {
            name: "Test",
            displayName: "Test",
            includeByDefault: true
        }, {
            name: "Effective Date",
            displayName: "Date",
            type: "datetime",
            includeByDefault: true
        }]
    }]
};                              
//Since this example uses a static data source,testConnection() function can return true
function testConnection(context) {
    return true;
}

//Import Structure
function importStructure(context) {    
        var addColumn = function(table, column, order) 
        {
            var recordColumn = table.addColumn(column.name);
            recordColumn.setDisplayName(column.displayName);
            switch (column.type) 
            {
                case "text":
                    recordColumn.setTextColumnType(column.length);
                break;
                case "int":
                    recordColumn.setIntegerColumnType();
                break;
                case "float":
                    recordColumn.setFloatColumnType();
                break;
                case "boolean":
                    recordColumn.setBooleanColumnType();
                break;
                case "datetime":
                    recordColumn.setDateTimeColumnType();
                break;
                default:
                    recordColumn.setTextColumnType(200);
                break;
          }
            recordColumn.setDisplayOrder(order);
            recordColumn.setIncludeByDefault(column.includeByDefault || false);
            recordColumn.setIncludeInKey(column.includeInKey || false);
            recordColumn.setMandatoryForImports(column.mandatoryForImport || false);
            recordColumn.setRemoteColumnType(column.remoteColumnType || "String");
        };    
     var builder = context.getStructureBuilder();    
     for (var i = 0; i < ExternalMetadataDefinition.Tables.length; i++)
     {
        var externalTable = ExternalMetadataDefinition.Tables[i];
        var table = builder.addTable(externalTable.name);
        table.setDisplayName(externalTable.displayName);        
        for (var j = 0; j < externalTable.columns.length; j++) 
        {
            addColumn(table, externalTable.columns[j], j + 1);
        }
    }
}
function previewData(context) {
    //Since the data is static, previewData() function can invoke importData()
    importData(context);
}
function importData(context) {
    var dataSource = context.getDataSource();
    var settings = dataSource.getSettings();
    var rowset = context.getRowset();
    
    //Set and Get SmartParsingEnabled() value
    rowset.setSmartParsingEnabled(true);
    ai.log.logInfo(getSmartParsingEnabled()); //true
    
    //Set a Start date and End date in the Designer Settings tab with the name "Period Range"
    var startDateString = dataSource.getSetting("Period Range").getValue().getFromPeriodStartDateTime().substring(0, 10);
    var endDateString = dataSource.getSetting("Period Range").getValue().getToPeriodEndDateTime().substring(0, 10);
    
    //get columns
    var columns = rowset.getColumns();

    for (var i = 0; i < dataSample.rows.length; i++) {
        var colName;
        var cells = [];
        for (var j = 0; j < columns.length; j++) {
            var colValue;
            var re = /\\\-/g;
            if ((new Date(dataSample.rows[i]["Effective Date"]) >= new Date(startDateString)) && (new Date(dataSample.rows[i]["Effective Date"]) < new Date(endDateString))) {
                colName = columns[j].getId();
                colValue = dataSample.rows[i][colName];
                cells.push(colValue.toString().replace(re, '.'));

            }
        }
        if (cells.length == columns.length) {
            //add rows
            rowset.addRow(cells);
        }
    }
}  

Export Data Function (Custom Cloud Loader Only)

Context Parameter

The Export Data Function is provided with 1 parameter, 'context'.

Method	Parameters	Return Type	Description
createTableReader()	optional array of source table column names	TableReader	Returns the active data source. The column order is the same as the order provided in the parameter.

TableReader Object

Method	Parameters	Return Type	Description
readRow()		array of values	An array of values from the data source.

Example

// Manually invoke this method via 'Run manually'
function exportData(context) {
  // Step 1: Get the data
  var reader = context.createTableReader();

  // Step 2: Process and export the data
  var row = null;
  // If reader.readRow() returns null, then there are no more records.
  while ((row = reader.readRow()) !== null) {
    // Send the row to the external system or process it for a subsequent bulk update.
    ai.log.logInfo(JSON.stringify(row));
  }
}