Adaptive Functions
Test Connection Function
The Test Connection function is used to determine whether Integration can connect to an external system. Test Connection function must return true or false indicating the result.
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 Calendar |
Example
function testConnection(context) { // Step 1: Create a https request to send to Intacct API var url = 'https://www.intacct.com/ia/xml/xmlgw.phtml'; var method = 'POST'; var body = '<request><control><senderid>adaptiveplanning</senderid><password>Bts9DrRkeZ</password><controlid>SomeRandomThingToMatchToResponse</controlid><dtdversion>3.0</dtdversion></control><operation><authentication><login><userid>Julia</userid><companyid>companyplanning-DEV</companyid><password>Welcome1</password></login></authentication></operation></request>'; var headers = { 'Content-Type': 'x-intacct-xml-request' }; // Step 2: Send request and receive response var response = ai.https.request(url, method, body, headers); // Step 3: Interrogate response to see if it was successful. Return true or false depending on the result. // Check that http communication was successful if (response.getHttpCode() == '200') { var body = response.getBody(); // Parse body and look for a success flag. Body could be xml or json depending on the use case. // ******* parsing logic here ******* return true; } else { return false; }
Import Structure Function
The Import Structure function is used to create/update the data source's schema (i.e tables and columns) defined by an external system. The Import Structure function must return an ai.structure.structureBuilder object.
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 Calendar |
Example
// JavaScript Example function importStructure(context) { // Step 1: Get the structure builder. var builder = context.getStructureBuilder(); // Step 2: Create and configure tables and columns that represent objects in the external system. In this example, I only created a sample of the GLACCOUNT table columns. var table = builder.addTable('GLACCOUNT'); table.setDisplayName('GLACCOUNT'); var recordColumn = table.addColumn('RECORDNO'); recordColumn.setIntegerColumnType(); recordColumn.setDisplayName('RECORDNO'); recordColumn.setDisplayOrder('0'); recordColumn.setIncludeByDefault(true); recordColumn.setRemoteColumnType('integer'); var accountNoColumn = table.addColumn('ACCOUNTNO'); accountNoColumn.setTextColumnType(96); accountNoColumn.setDisplayName('ACCOUNTNO'); accountNoColumn.setDisplayOrder('1'); accountNoColumn.setIncludeByDefault(true); accountNoColumn.setRemoteColumnType('string'); var titleColumn = table.addColumn('TITLE'); titleColumn.setTextColumnType(80); titleColumn.setDisplayName('TITLE'); titleColumn.setDisplayOrder('2'); titleColumn.setIncludeByDefault(true); titleColumn.setRemoteColumnType('string'); var accountTypeColumn = table.addColumn('ACCOUNTTYPE'); accountTypeColumn.setTextColumnType(2048); accountTypeColumn.setDisplayName('ACCOUNTTYPE'); accountTypeColumn.setDisplayOrder('3'); accountTypeColumn.setIncludeByDefault(true); accountTypeColumn.setRemoteColumnType('string'); }
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. For 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 ) |
|
|
Sets the script percentage complete. This works as follows:
|
Example
// JavaScript 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 ) |
|
ai.structure.editableTable |
Creates and returns a new table for the data source. |
Examples
Provides the ability to define the tables and column structure that will be populated by data.
function importStructure(context) { var builder = context.getStructureBuilder(); var table = builder.addTable('InvoiceTable'); table.setDisplayName('Invoices'); var amountColumn = table.addColumn('Amount'); amountColumn.setIntegerColumnType(); amountColumn.setDisplayName('AmountCol'); amountColumn.setDisplayOrder('2'); amountColumn.setIncludeByDefault(); amountColumn.setIncludeInKey(); var textColumn = table.addColumn('TextValue'); textColumn.setTextColumnType(99); textColumn.setDisplayName('TextColumn'); textColumn.setDisplayOrder('4'); textColumn.setRemoteColumnType('string'); var booleanColumn = table.addColumn('IsTrue2'); booleanColumn.setBooleanColumnType(); booleanColumn.setDisplayName('BooleanCol'); booleanColumn.setDisplayOrder('3'); booleanColumn.setMandatoryForImports(); booleanColumn.setRemoteColumnType('bit'); var invoiceDateColumn = table.addColumn('InvoiceDate'); invoiceDateColumn.setDateTimeColumnType(); invoiceDateColumn.setDisplayName('InvoiceDateColumn'); invoiceDateColumn.setDisplayOrder('1'); invoiceDateColumn.setIncludeByDefault(true); var parameter = table.addParameter('Prompt for Records'); parameter.setDisplayName('Records to Import'); parameter.setForDataImport(true); parameter.setForStructureImport(true); parameter.setTypeInteger(); }
ai.structure.editableTable
Functions
Method |
Parameters |
Returns |
Description |
---|---|---|---|
setDisplayName( displayName ) |
displayName : String
|
ai.structure.editableTable |
Sets the display name of the table. |
addColumn( columnId ) |
columnId : String
|
ai.structure.editableColumn |
Creates and returns a new child column of the table. |
addRequiredParameter( parameterId ) |
parameterId : String
|
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 ) |
|
ai.structure.editableColumn | Sets the display name of the column. |
setDisplayOrder( value ) |
|
ai.structure.editableColumn | Sets the display order of the column. |
setIncludeByDefault( value ) |
|
ai.structure.editableColumn | Sets the column to be enabled for data import. |
setIncludeInKey( value ) |
|
ai.structure.editableColumn | Indicates whether the column is part of the table key. |
setMandatoryForImports( value ) |
|
ai.structure.editableColumn | Sets the column to be enabled for data import by mandatory. |
setNonFilterable( value ) |
|
ai.structure.editableColumn | Indicates if the column can be included in a remote query. |
setRemoteColumnType( value ) |
|
ai.structure.editableColumn | Sets the remote data type of the column e.g. 'string', 'integer' etc |
setTextColumnType( length ) |
|
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 ) |
|
Sets the display name of the parameter. | |
setForDataImport( value ) |
|
Enables parameter for data import. | |
setTypePeriodRange() | Changes the type of the parameter to a Period Range |
Import Data Function
The Import Data function is used to bring rows from an external source and import them into Integration staging tables. The mechanism for importing rows is the ai.rowset object, which is provided via the 'context' parameter passed into the Import Data function.
Context Parameter
The Import Data function is provided with 1 parameter, 'context'.
Method |
Parameters |
Return Type |
Description |
---|---|---|---|
getRowset() |
ai.rowset |
Returns the Rowset object that rows are added to. The Rowset defines the Table, Columns and MaxRows. |
|
getDataSource() |
ai.dataSource |
Returns the active data source. |
|
getCalendar() |
ai.calendar |
Returns the Adaptive Calendar |
Example
This example assumes that the rowset contains an Integer column and a Text column in order.
// JavaScript Example function importData(context) { var rowset = context.getRowset(); // Step 1: Get the data via https or a static source // In this example, I will create some fake data that matches the known column data types var rows = []; for (var i = 0; i < 2000; i++) { var row = [i, 'hello' + i]; rows.push(row); } // Step 2: Add each row to the rowset for (var j = 0; j < rows.length; j++) { rowset.addRow(rows[i]); } }
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>
|
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 ) |
|
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. |
Preview Data Function
The Preview Data function is used to query an external system for rows of data for the specified table and columns. It is limited by the max rows the external system allows a user to see during a preview in that system.
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 Calendar |
Example
Provides the ability to preview data stored in the cloud source.
function previewData(context) { // Step 1: Make use of passed in contextual information. Here, I am simply assigning tableId, maxRows and columnNames to variables to be used elsewhere in this script. var rowset = context.getRowset(); var tableId = rowset.getTableId(); var maxRows = rowset.getMaxRows(); var columnNames = rowset.getColumnNames(); // Step 2: Create a https request that will return data - in this case, Intacct will return JSON instead of XML. var url = 'https://www.intacct.com/ia/xml/xmlgw.phtml'; var method = 'POST'; var body = '<request><control><senderid>adaptiveplanning</senderid><password>Bts9DrRkeZ</password><controlid>SomethingToMapRequestToResponse</controlid><dtdversion>3.0</dtdversion></control><operation><authentication><login><userid>Julia</userid><companyid>companyplanning-DEV</companyid><password>Welcome1</password></login></authentication><content><function controlid="SomethingToMapRequestToResponse"><readByQuery><object>' + tableId + '</object><fields>' + columnNames.join(",") + '</fields><query></query><pagesize>' + maxRows + '</pagesize><returnFormat>json</returnFormat></readByQuery> </function></content></operation></request>'; var headers = { 'Content-Type': 'x-intacct-xml-request' } // Step 3: Send https request and receive response. Normally you would want to check that the response contains a success message first before looking at the rows. var response = ai.https.request(url, method, body, headers); // Step 4: Since we requested JSON as the response format, we need to parse the https response body into a JSON object for ease of use. var rows = JSON.parse(response.getBody()); // Step 5: Process each row to extract the cell values for each column and add them as an array to the rowset in the expected column order. // In this case, each 'row' is represented as a single JSON object, where each property is the name of the column and each value of each property is the cell value for the row's column. for(var i = 0; i < rows.length; i++) { var cells = []; var row = rows[i]; for(var key in row){ cells.push(row[key]); } rowset.addRow(cells); } }