DataLoad Scripting Functions

Syntax

function SetDynamicCols($dynamic, $grid=MAIN)

Examples

SetDynamicCols(true); //Turns on dynamic cols in main spreadsheet
SetDynamicCols(false, FTR); //Turns off fixed cols in footer spreadsheet

Description

Call SetDynamicCols to turn dynamic columns on/off in the specified spreadsheet. $dynamic is true or false and $grid is HDR, MAIN or FTR to specify the Header, Main or Footer spreadsheets respectively. If the spreadsheet parameter is omitted then this defaults to MAIN.

Spreadsheets normally have a dynamic number of columns, that is there is not a fixed number of columns and new columns are always available when required. DataLoad may have either a dynamic or fixed number of columns and this function between each behaviour. In dynamic mode, users can insert & delete columns and the spreadsheets automatically grow & shrink. This function has no effect Forms Playback loads where the column count is always fixed.

Syntax

function InsertRow($position, $count=1)

Examples

InsertRow(1, 5); //Inserts 5 rows at row 1
InsertRow(3); //Inserts 1 row at row 3

Description

Call InsertRow to insert rows in the main spreadsheet. $position is the row where the new row(s) will be inserted and $count is the number of rows to insert. If $count is omitted then this defaults to 1. The header and footer spreadsheets always have 1 row and additional rows cannot be inserted.

Syntax

function InsertCol($position, $count=1, $grid=MAIN)

Example

InsertCol(5, 2, HDR); //Inserts 2 cols at col 5 in the Header spreadsheet

Description

Call InsertCol to insert columns in one of the spreadsheets. $position is the column where the new col(s) will be inserted and $count is the number of cols to insert. If $count is omitted then this defaults to 1. $grid is HDR, MAIN or FTR to specify the Header, Main or Footer spreadsheets respectively. If this parameter is omitted then it defaults to MAIN.

Syntax

function DeleteRow($position, $count=1)

Examples

DeleteRow(10, 2); //Delete 2 rows from row 10 in the main spreadsheet
DeleteRow(2); //Delete row 2

Description

Call DeleteRow to delete rows from the main spreadsheet. $position is the row where the delete will start and $count is the number of rows to delete. If $count is omitted then this defaults to 1. Rows cannot be deleted from the Header and Footer spreadsheets.

Syntax

function DeleteCol($position, $count=1, $grid=MAIN)

Examples

DeleteCol(5, 2, FTR); #Delete 2 columns from column 5 in the footer spreadsheet
DeleteCol(7); #Delete column 7 in the main spreadsheet

Description

Call DeleteCol to delete columns from the specified spreadsheet. $position is the column where the delete will start and $count is the number of rows to delete. If $count is omitted then this defaults to 1. $grid is HDR, MAIN or FTR to specify the Header, Main or Footer spreadsheets respectively. If this parameter is omitted then it defaults to MAIN.

Syntax

function ClearCol($position, $count=1, $grid=MAIN)

Example

ClearCol(1, 3, HDR); #Clears the first 3 columns in the header spreadsheet

Description

Call ClearCol to clear values from columns in the specified spreadsheet. $position is the column where the clear will start and $count is the number of columns to clear. If $count is omitted then this defaults to 1. $grid is HDR, MAIN or FTR to specify the Header, Main or Footer spreadsheets respectively. If this parameter is omitted then it defaults to MAIN.

Syntax

function ClearRow($position, $count=1, $grid=MAIN)

Example

ClearRow(5); #Clears the fifth row in the main spreadsheet

Description

Call ClearRow to clear values from rows in the specified spreadsheet. $position is the row where the clear will start and $count is the number of rows to clear. If $count is omitted then this defaults to 1. $grid is HDR, MAIN or FTR to specify the Header, Main or Footer spreadsheets respectively. If this parameter is omitted then it defaults to MAIN.

Syntax

function ClearGrid($grid=MAIN)

Examples

ClearGrid(FTR); #Clears the footer spreadsheet
ClearGrid(); #Clears the main spreadsheet

Description

Call ClearGrid to clear all values from the specified spreadsheet. $grid is HDR, MAIN or FTR to specify the Header, Main or Footer spreadsheets respectively. If this parameter is omitted then it defaults to MAIN.

Syntax

function GetLastRow($grid=MAIN)

Example

$rows=GetLastRow();
MessageBox("There are " . $rows . " in the main spreadsheet");

Description

Call GetLastRow to get the row number of the row that contains the last data value in the specified spreadsheet. This function is typically used to enable the developer to iterate through all data rows in the spreadsheet. $grid is HDR, MAIN or FTR to specify the Header, Main or Footer spreadsheets respectively. If this parameter is omitted then it defaults to MAIN.

Syntax

function GetLastCol($grid=MAIN)

Example

$cols=GetLastCol();
MessageBox("There are " . $cols . " in the main spreadsheet");

Description

Call GetLastCol to get the column number of the column that contains the last data value in the specified spreadsheet. This function is typically used to enable the developer to iterate through all columns in the spreadsheet. $grid is HDR, MAIN or FTR to specify the Header, Main or Footer spreadsheets respectively. If this parameter is omitted then it defaults to MAIN.

Syntax

function GetRowCount($grid=MAIN)

Example

$rows=GetRowCount();

Description

Call GetRowCount to get the total number of rows in the specified spreadsheet. $grid is HDR, MAIN or FTR to specify the Header, Main or Footer spreadsheets respectively. If this parameter is omitted then it defaults to MAIN.

Syntax

function GetColCount($grid=MAIN)

Example

$cols=GetColCount();

Description

Call GetColCount to get the total number of columns in the specified spreadsheet. $grid is HDR, MAIN or FTR to specify the Header, Main or Footer spreadsheets respectively. If this parameter is omitted then it defaults to MAIN.

Syntax

function SetRowCount($count)

Example

SetRowCount(100);

Description

Call SetRowCount to set the total number of rows in the main spreadsheet. $count is new number of rows in the spreadsheet.

Syntax

function SetColCount($count, $grid=MAIN)

Example

SetColCount(50, FTR); //Set the Footer spreadsheet to have 50 columns

Description

Call SetColCount to set the total number of columns in the specified spreadsheet. $count is the new number columns in the spreadsheet. $grid is HDR, MAIN or FTR to specify the Header, Main or Footer spreadsheets respectively. If this parameter is omitted then it defaults to MAIN.

Syntax

function SetBlankRows($count)

Example

SetBlankRows(250);

Description

Call SetBlankRows to set the number of blank rows maintained after the last data row in the main spreadsheet. $count is number of blank rows in the spreadsheet. By default this is 100 and that should normally not need to be changed. This function has no effect where the number of rows is fixed rather than dynamic (see SetDynamicRows).

Syntax

function SetBlankCols($count, $grid=MAIN)

Example

SetBlankCols(250, HDR);

Description

Call SetBlankCols to set the number of blank columns maintained after the last data columns in the specified spreadsheet. $count is number of blank columns in the spreadsheet. By default this is 100 and that should normally not need to be changed. $grid is HDR, MAIN or FTR to specify the Header, Main or Footer spreadsheets respectively. If this parameter is omitted then it defaults to MAIN. This function has no effect where the number of columns is fixed rather than dynamic (see SetDynamicCols).

Syntax

function GetBlankRows()

Example

$blankrows=GetBlankRows();

Description

GetBlankRows returns the number of blank rows maintained after the last data row in the main spreadsheet. This function only works on the main spreadsheet because the Header and Footer spreadsheets are fixed at 1 row each.

Syntax

function GetBlankCols($grid=MAIN)

Example

$blankcols=GetBlankCols();

Description

GetBlankCols returns the number of blank columns maintained after the last data columns in the specified spreadsheet. $grid is HDR, MAIN or FTR to specify the Header, Main or Footer spreadsheets respectively. If this parameter is omitted then it defaults to MAIN.

Syntax

function SetHourglass($status=false)

Example

SetHourglass(true);
...
#Do some long running actions here
...
SetHourglass(false);

Description

Call SetHourglass to control whether or not the mouse pointer is an hourglass. If the $status parameter is true the cursor is changed to be an hourglass but if this parameter is false then the cursor is changed back to normal. If no parameter is provided then the cursor is set to its default, i.e. not an hourglass. It is recommended that the cursor is changed to be an hourglass during any operations that may take some time to complete.

Syntax

function ShowConsole()

Example

ShowConsole();

Description

Call ShowConsole to open the DataLoad load progress console window.

Syntax

function HideConsole()

Example

HideConsole();

Description

Call HideConsole to hide the DataLoad load progress console window.

Syntax

function SetConsoleData($ColsLoaded=0, $ColCount=0, $RowsLoaded=0, $RowCount=0, $ErrorCount=0, $Caption="")

Examples

SetConsoleData(5, 10, 59, 523, 0); #Set all statistics
SetConsoleData(7, 15, 143, 1000); #Set the col & row statistics but leave errors defaulted to 0
SetConsoleData(); #Reset all statistics

Description

Call SetConsoleData to set the load statistics in the progress window. All parameters are optional and default to 0 (numbers) or blank (strings) if they are omitted, thus if the function is called without any parameters then all statistics are reset. The following parameters can be provided:

$ColsLoaded - The number of columns processed in this row
$ColCount - The total number of columns to process (typically equal to GetLastCol())
$RowsLoaded - The number of rows processed
$RowCount - The total number of rows (typically equal to GetLastRow())
$ErrorCount - The number of errors that have occurred.
$Console - The caption to put at the top of the console window

Syntax

function ClearConsole($Console=ALL)

Examples

ClearConsole(); #Clear both message areas
ClearConsole(INFO); #Clear the top area
ClearConsole(ERROR); #Clear the bottom area

Description

Call ClearConsole to clear some or all of the messages in the progress window. The progress window contains two message areas, one for general information and one for error messages, although it is up to the script developer as to how these areas are actually used. $Console is set to INFO to clear information messages, ERROR to clear error messages and ALL to clear both types of message.

Syntax

function SendMsg($data, $type=INFO)

Examples

SendMsg("Load completed successfully", INFO);
SendMsg("Failed to save record ", ERROR);
SendMsg("Load completed"); #Message is diaplayed in the INFO area

Description

Call SendMsg to add a message to the console. The console contains two message areas, one for general information and one for error messages, although it is up to the script developer how these areas are actually used. The text message to be displayed is supplied in the $data parameter. $type is an optional parameter that controls where the message is displayed. It should be set to INFO for information messages, ERROR for error messages and ALL if the message should be shown on the information & error message areas. Messages of type ERROR are also added to the console beneath the PHP code editor on the Script tab in DataLoad. If the $type parameter is not used then it defaults to INFO.

Syntax

MessageBox($text="Error: Message text not defined!", $caption="", $flags=MB_OK)

Example

$response=MessageBox("Do you wish to continue?", "" , MB_YESNO);

MessageBox("Acme Vendor Load Complete")

Description

Presents a message box to the DataLoad user. Processing does not continue until the user has pressed one of the message box buttons. Parameters and return value are the same as for the Windows MessageBox API, i.e.:

$text - The value of the message. Long messages are automatically wrapped in the message box.
$caption - The value of caption that appears in the title bar of the dialog box. Captions don't wrap and a long caption results in a wide message box. If caption is blank then the DataLoad product name is used.
$flags - Specifies what buttons appear on the message box and the possible return values. These values can be combined to obtain the desired effect. Valid value are:

MB_ABORTRETRYIGNORE - The message box contains three push buttons: Abort, Retry, and Ignore.
MB_OK - The message box contains one push button: OK. This is the default.
MB_OKCANCEL - The message box contains two push buttons: OK and Cancel.
MB_RETRYCANCEL - The message box contains two push buttons: Retry and Cancel.
MB_YESNO - The message box contains two push buttons: Yes and No.
MB_YESNOCANCEL - The message box contains three push buttons: Yes, No, and Cancel.

The return value specifies the button pressed by the user, which will be one of the following:

IDOK - The user chose the OK button.
IDCANCEL - The user chose the Cancel button.
IDABORT - The user chose the Abort button.
IDRETRY - The user chose the Retry button.
IDIGNORE - The user chose the Ignore button.
IDYES - The user chose the Yes button.
IDNO - The user chose the No button.

Syntax

function InputQuery($ACaption, $APrompt, &$Value)

Example

$Value="";
$Result=InputQuery("Acme Vendor Load", "Enter the vendor ID", $Value);

Description

Use InputQuery to display an input dialog box where the user can enter a value. $ACaption is the dialog box caption, $APrompt is the text that prompts the user to enter a value and $Value contains the initial value (if one is wanted). The value entered by the user is returned in $Value, thus this parameter must always be supplied even if no initial value is required, is $Value is initially empty.

InputQuery returns true if the user chooses OK, and false if the user chooses Cancel or presses the Esc key.

If a default value should be used when the user cancels out of the dialog, use InputBox instead.

Syntax

function InputBox($ACaption, $APrompt, $ADefault)

Example

$Result="";
while($Result=="")
  InputBox("Acme Vendor Load", "Enter the vendor ID", "");

Description

Use InputBox to display an input dialog box where the user can enter a value. $ACaption is the dialog box caption, $APrompt is the text that prompts the user to enter a value and $ADefault is the value that appears in the edit box when the dialog box first appears.

If the user chooses the Cancel button, InputBox returns the default value. If the user chooses the OK button, InputBox returns the value in the edit box.

Use the InputBox function when there is a default value that should be used when the user chooses the Cancel button (or presses Esc) to exit the dialog. If the application needs to know whether the user chooses OK or Cancel, use the InputQuery function instead.

Syntax

function GetRowData($position, $grid=MAIN, &$values=NULL)

Example

$rowarray=GetRowData(1) #Get first row of main spreadsheet

Description

Call GetRowData to obtain an array containing all values from a row in the specified spreadsheet. $position is the row for which data will be returned and $grid is the spreadsheet, which must be HDR, MAIN or FTR. $grid is an optional parameter and if it is omitted it will default to MAIN. GetRowData returns an array containing the row values unless an array is provided via the optional parameter $values. In that case $values is populated with the row data and the existing key names will be presered.The $values array can be used when an existing array should be updated with the row data values.

Syntax

function GetColData($position, $grid=MAIN, &$values=NULL)

Example

$colarray=GetColData(1) #Get first column of main spreadsheet

Description

Call GetColData to obtain an array containing all values from a column in the specified spreadsheet. $position is the column for which data will be returned and $grid is the spreadsheet, which must be HDR, MAIN or FTR. $grid is an optional parameter and if it is omitted it will default to MAIN. GetColData returns an array containing the column values unless an array is provided via the optional parameter $values. In that case $values is populated with the column data and the existing key names will be presered.The $values array can be used when an existing array should be updated with the column data values.

Syntax

function GetCellData($col, $row, $grid=MAIN)

Example

$value=GetCellData($col, $row, $grid=MAIN)

Description

Call GetCellData to obtain the value of the cell in column $col and row $row of the spreadsheet identified in $grid. $grid is HDR, MAIN or FTR to specify the Header, Main or Footer spreadsheets respectively. If this parameter is omitted then it defaults to MAIN.

Syntax

function SetRowData($data, $position=-1, $grid=MAIN)

Example

SetRowData($myarray) #Add contents of $myarray to end of main spreadsheet

Description

Call SetRowData to update an entire spreadsheet row with the contents of an array. The array is supplied via the $data parameter and it will replace the data in row $position of the spreadsheet identified in the $grid parameter. $grid is HDR, MAIN or FTR to specify the Header, Main or Footer spreadsheets respectively. $position and $grid are both optional. If position is omitted then the data is added to a new row after the current last row. If $grid is not used then the main spreadsheet is updated.

Syntax

function SetColData($data, $position=-1, $grid=MAIN)

Examples

SetColData($myarray, 5, FTR) #Update column 5 in footer spreadsheet
SetColData($myarray) #Insert data in new column after current last column in main spreadsheet

Description

Call SetColData to update an entire spreadsheet column with the contents of an array. The array is supplied via the $data parameter and it will replace the data in column $position of the spreadsheet identified in the $grid parameter. $grid is HDR, MAIN or FTR to specify the Header, Main or Footer spreadsheets respectively. $position and $grid are both optional. If position is omitted then the data is added to a new column after the current last column. If $grid is not used then the main spreadsheet is updated.

Syntax

function SetCellData($col, $row, $value, $grid=MAIN)

Example

$value=GetCellData($col, $row, $grid=MAIN)

Description

Call SetCellData to set the contents of the cell specified in column $col and row $row of the spreadsheet identified in $grid to be $value. $grid is HDR, MAIN or FTR to specify the Header, Main or Footer spreadsheets respectively. If this parameter is omitted then it defaults to MAIN.

Syntax

function GetDBConn()

Example

$oraconn=new Oracle();

//Prompt the user for the connection details and update $OraDBConn
$OraDBConn=GetDBConn();

//Populate $connstr with the connection details
if($OraDBConn->usetns)
  $connstr=$OraDBConn->alias;
else
  $connstr=$OraDBConn->host . ":" . $OraDBConn->port . "/" . $OraDBConn->sid;

Description

When this function is called the user is shown a dialog box where they enter Oracle database connection details, that is the database username, password and connection string. The connection string can be a tnsnames alias or the server, sid and port details. If the latter is used then PHP will connect using Oracle EZConnect.

If the user clicks OK an object of class OraDBConnClass is created and returned by the function. If Cancel is pressed false is returned. The class OraDBConnClass is defined as:

class OraDBConnClass
{
  var $usetns, #True if user opted to use TNS alias, otherwise false
  $alias, #TNS alias, if TNS is used
  $sid, #Database SID if EZConnect is used instead of TNS
  $host, #Server hostname or IP address if EZConnect is used instead of TNS
  $port, #Server port if EZConnect is used instead of TNS
  $username, #Username
  $password, #Password
  $ebs, #Is the Oracle E-Business Suite flag ticked?
  $gwuser, #Oracle E-Business Suite gateway user
  $gwpassword, #Oracle E-Business Suite gateway password
  $server_id; #Oracle E-Business Suite server ID
}

Syntax

function ShowGrid($grid)

Example

ShowGrid(HDR);

Description

Call ShowGrid to show the header or footer grids when they are currently hidden. The $grid parameter specifies the spreadsheet to show and must be HDR or FTR for the Header and Footer spreadsheets respectively. The main spreadsheet cannot be hidden hence this function has no effect if used with that spreadsheet.

Syntax

function HideGrid($grid)

Example

HideGrid(FTR);

Description

Call HideGrid to hide the header or footer grids when they are currently visible. The $grid parameter specifies the spreadsheet to hide and must be HDR or FTR for the Header and Footer spreadsheets respectively. The main spreadsheet cannot be hidden hence this function has no effect if used with that spreadsheet.

Syntax

function CancelMacro()

Example

CancelMacro();

Description

CancelMacro is used to stop a macro load from running after the load has been started by the user but before the macro has actually started. This function is only effective when used in the OnStartLoad() event handler. It can be used to cancel the macro when an issue is found that means the load should not continue, e.g. data validation breaches.

Syntax

function GetLoadScope()

Example

GetLoadScope();

Description

Call GetLoadScope to display the load scope form where the user will enter load parameters, for example the the row numbers to be loaded. If macro loads are disabled (in the DataLoad Options window) then the fields specific to Macro loads are disabled in this window. If the load is a macro load and GetLoadScope is called before it would normally be shown, the load scope window will not be shown when normally displayed by DataLoad and instead the results from when it was run by the PHP script will be used.

When the user clicks "OK" the $LoadParams global variable is populated with the user's selections. This variable is an object of the LoadParamsClass class and these are defined as follows:

class LoadParamsClass
{
  var $FirstColHdr, #First column of Header grid to be loaded
  $FinalColHdr, #Final column of Header grid to be loaded
  $FirstRowMain, #First row of Main grid to be loaded
  $FinalRowMain, #Final row of Main grid to be loaded
  $FirstColMain, #First col of Main grid to be loaded
  $FinalColMain, #Final col of Main grid to be loaded
  $FirstColFtr, #First col of Footer grid to be loaded
  $FinalColFtr, #Final col of Footer grid to be loaded
  $StepMode, #True if user selected Step Mode load
  $UserOK; #True if user clicked OK, false if user cancelled the window
}

$LoadParams = new LoadParamsClass();

Syntax

function ListOfValues($Data, $Caption="Select Value", &$Value)

Example

$ListArray = array();
…
//Populate $ListArray here
…
if(ListOfValues($ListArray, "Choose a Responsibility", $SelectedValue))
{
…
//Value was selected so now $SelectedValue can be used
…
}
else
{
…
//LOV was cancelled so handle that here
…
}

Description

Call List of Values to present the user with a list from which they should choose a value. The function returns true if the user selects a value or false if they press Cancel. The function takes 3 parameters:

$Data - Array that contains the list of values to show the user
$Caption - Caption to display at the top of the list of values window
$Value - The value selected by the user is returned in this variable

Syntax

function BrowserOpen()

Example

BrowserOpen();

Description

Call BrowserOpen to open a new browser instance for use with Browser Control.

Syntax

function BrowserClose()

Example

BrowserClose();

Description

Call BrowserClose to close the browser when using Browser Control.

Syntax

function BrowserSource()

Example

$html=BrowserSource();

Description

Call BrowserSource to get the browser's current source code, which is typically HTML but could be any text downloaded by the browser.

Syntax

function BrowserNavigate($url)

Example

BrowserNavigate("http://www.dataload.com");

Description

Call BrowserNavigate to get the browser to open the page specified in the $url parameter. The function does not return until the browser operation is finished unless this behaviour is changed via the BrowserWaitOnBusy function.

Syntax

function BrowserBack()

Example

BrowserBack();

Description

Call BrowserBack to make the browser navigate one page back. The function does not return until the browser operation is finished unless this behaviour is changed via the BrowserWaitOnBusy function.

Syntax

function BrowserForward()

Example

BrowserForward();

Description

Call BrowserForward to make the browser navigate one page forwards. The function does not return until the browser operation is finished unless this behaviour is changed via the BrowserWaitOnBusy function.

Syntax

function BrowserHome()

Example

BrowserHome();

Description

Call BrowserHome to make the browser navigate to its home page. The function does not return until the browser operation is finished unless this behaviour is changed via the BrowserWaitOnBusy function.

Syntax

function BrowserShow()

Example

BrowserShow();

Description

Call BrowserShow to make the browser visible if it is currently hidden.

Syntax

function BrowserHide()

Example

BrowserHide();

Description

Call BrowserHide to hide the browser if it is currently visible.

Syntax

function BrowserRunning()

Example

if(!BrowserRunning())
  BrowserOpen();

Description

Call BrowserRunning to determine whether a browser instance is currently running when in Browser Control mode.

Syntax

function BrowserWaitOnBusy($wait=true)

Example

BrowserWaitOnBusy();

Description

Call BrowserWaitOnBusy to change whether DataLoad waits for the browser to be idle during Browser Control macro loads. By default this is always says to true and it is normally required that DataLoad waits for the browser to complete its current activity before making further requests. However, this behaviour can be turned off by calling BrowserWaitOnBusy with the $wait parameter set to false. If $wait is true or no parameter is supplied then the default waiting behaviour is enabled.

Syntax

function SetDebugStatus($status=false)

Example

SetDebugStatus(true);

Description