updateDatasource
Definition:
NDLDS<datasource name>.updateDatasource(ndlParams)
Description:
Writes the current record in the specified Datasource from memory to the physical database. If the database already contains a record with the current record's Primary Key, that record will be updated, otherwise a new record will be created in the database. If a new record is created, the Form's current record will be updated with the values stored in the created record, so that where a new Primary Key is generated by the database, the current record will be updated with the actual key value(s) and all Elements on the form which are input mapped to the specified Dataset will automatically be updated.
If you select UpdateDatasource as a predefined action in an Element's Event Property, the current record will be updated with values in all Elements on the form which are output mapped to the specified Dataset before the record is updated. If you are using updateDatasource in your own custom JavaScript, you will need to update the current record yourself (see Code Sample and Notes below).
-
You cannot use Imported Stored Procedures which return a result set with this function - in order to execute these you need to call loadDatasource or use the LoadDatasource action in an Event. Imported Stored Procedures which only perform inserts and updates and those used with Digitise Forms Datasources can be executed by a call to updateDatasource.
Parameters:
updateDatasource requires the following properties to be added to the default ndlParams object:
ndlParams.tablename - Required
Name of the Dataset.
ndlParams.errorHandler - Optional
Name of an error handler function to be called if the updateDatasource call fails and an HTTP error response is received. The function must be a JavaScript function located within the custom JavaScript for the form. If you omit this property, the function specified in the Form's Default Error Handler property will be used, by default this will be the onDatasourceError function. For more information, see Display Datasource Error Messages.
Note that this function will only action error handling if Display Error Messages is selected under the form properties.
ndlParams.requestParams - Required
This parameter should be set to be an empty string.
ndlParams.data - Required
This parameter should contain a copy of the records to be updated in the database. You can enter a copy of the current record using the getCurrent intrinsic function as shown in the code sample below.
ndlParams.onResponseReceived - Optional
Specifies the name of a JavaScript function to be run when updateDatasource has finished. This function will be run after updateDatasource has completed all its actions and hence, if a new record has been created, the current record will have been updated with the new Primary Key(s), if relevant, by the time this function runs.
If there are multiple records eligible for updating in the specified Dataset, onResponseReceived will be called after each individual record is updated. If you want to wait until all records have been updated use onSubmitComplete (see below) instead, although note that you can use both options if required.
ndlParams.onSubmitComplete - Optional
Specifies the name of a JavaScript function to be run when updateDatasource has finished. This function will be run after updateDatasource has completed all its actions and hence, if a new record has been created, the current record will have been updated with the new Primary Key(s), if relevant, by the time this function runs.
If there are multiple records eligible for updating in the specified Dataset, onSubmitComplete will only be called once a response has been received for each record. If you want to action your function after each individual record is updated, use onResponseRecieved (see above) instead, although note that you can use both options if required.
Returns:
nothing
Code Sample 1:
This example shows how to use updateDatasource without an onResponseReceived or onSubmitComplete function.
It shows the JavaScript statements which will appear in the JavaScript function generated from an Element's Event property where UpdateDatasource is assigned to the Event.
//initialise the requestParams field
ndlParams.requestParams = "";
//name the table
ndlParams.tablename = "MyDataset";
//name the error handler function - the name shouldn't be in quotes
ndlParams.errorHandler = functionname;
//read the values from the elements into the current record
NDLRuntime.prepareDatasource("MyDatasource","MyDataset");
//assign the current record to the ndlParams.data object
ndlParams.data = NDLDSMyDatasource.myDatasets.getCurrent();
//call updateDatasource - the response is asynchronous
NDLDSMyDatasource.updateDatasource(ndlParams);
Code Sample 2:
This example shows you how to perform two updateDatasource calls for a Digitise Forms or Imported Datasource using an onSubmitComplete function to nest the second download (see Notes below for additional information).
Note in the onSubmitComplete function, because this function is not generated from an Element's event property, you need to create an ndlParams object to pass to updateDatasource, but updateDatasource doesn't need any of the four properties specified in the default ndlParams object and so these properties are not added here.
Note also, that before calling updateDatasource, you need to update the current record with values from the form by calling NDLRuntime.prepareDatasource and assign the current record to the ndlParams.data property as shown in the code sample below.
function Page1_button1_clicked(ndlParams){
⁝
//initialise the requestParams field
ndlParams.requestParams = "";
//name the table
ndlParams.tablename = "MyDataset";
//name the error handler function - the name shouldn't be in quotes
ndlParams.errorHandler = functionname;
//read the values from the elements into the current record
NDLRuntime.prepareDatasource("MyDatasource","MyDataset");
// specify an onResponseReceived function - the name shouldn't be in quotes
ndlParams.onSubmitComplete = [onSubmitComplete];
//assign the current record to the ndlParams.data object
ndlParams.data = NDLDSMyDatasource.myDatasets.getCurrent();
//call updateDatasource - the response is asynchronous
NDLDSMyDatasource.updateDatasource(ndlParams);
⁝
}
function onSubmitComplete(responses){
// define an ndlParams object for the second updateDatasource call
var ndlParams2 = {};
//initialise the requestParams field
ndlParams.requestParams = "";
//name the table
ndlParams2.tablename = "AnotherDataset";
//name the error handler function - the name shouldn't be in quotes
ndlParams2.errorHandler = functionname2;
//read the values from the elements into the current record
NDLRuntime.prepareDatasource("MyDatasource2","AnotherDataset");
//assign the current record to the ndlParams.data object
ndlParams2.data = NDLDSMyDatasource2.anotherDatasets.getCurrent();
//call loadDatasource : the response is asynchronous, and the elements are updated with the values of the first record once the response has been received
NDLDSMyDatasource2.updateDatasource(ndlParams2);
}
Code Sample 3:
This sample shows you how to use updateDatasource to update Datasets which have multiple Foreign Key relationships and hence do not allow child records to be handled with the parent records. In this situation, you will need to update the parent record first and then insert the new Primary Key value from the parent record into the child record(s) before updating the child database table(s).
Note, because the onSubmitComplete functions are not generated from an Element's event property, you need to create a new ndlParams object for each function call which requires ndlParams. The properties added to these objects varies depending upon the requirements of the functions to which they will be passed.
var submitSuccessResponses = 0;
var expectedResponses = 0;
var mainRecId = -1;
function Page1_button1_clicked(ndlParams){
//this function handles the click event on a custom submit button (using a standard Button Element)
//it submits the parent record, which needs to be submitted first so that the generated Primary Key (PK) value
//can be inserted in to the Foreign Key(FK)columns of the child records
submitSuccessResponses = 0; //count the success responses to determine whether to display success page
form1.page1.$$ndl.setSubmitted(); //ensure that validation takes place
if(form1.page1.$$ndl.isValid){
//read the data into the Datasets so we can count the expected number of responses
NDLRuntime.prepareDatasource("MyDatasource","MyParentDataset"); //reads data from the Elements into the parent Dataset
NDLRuntime.prepareDatasource("MyDataSource","MyChild1Dataset"); //reads data from the Elements into the first child Dataset
NDLRuntime.prepareDatasource("MyDataSource","MyChild2Dataset"); //reads data from the Elements into the second child Dataset
expectedResponses = NDLDSMyDatasource.myChild1Datasets.updates.length
+MyDatasource.myChild2Datasets.updates.length
//set up an ndlParams object (data in standard ndlParams not required for updating parent record)
var ndlUpdateParams = {};
ndlParams.requestParams = ""; //initialise the requestParams field
ndlUpdateParams.tablename = "MyParentDataset"; //datasetname
ndlUpdateParams.onResponseReceived = onResponseReceived;
ndlUpdateParams.onSubmitComplete = [onMainRecordSubmitSuccess]; //the callback function to be called when the parent record has been successfully submitted (see below)
NDLDSMyDatasource.updateDatasource(ndlUpdateParams); //perform the parent update
}
}
function onMainRecordSubmitSuccess(submitResponses){
//this function is called when the parent record (MyParentDataset) has been successfully submitted
//it receives the value of the PK generated by the database and updates all the child records with it before submitting them
//here the child records are submitted concurrently - we are assuming there are no dependencies between them
if(submitResponses.responses.length > 0 && submitResponses.responses[0].status < 300){
mainRecId = submitResponses.recordId;
form1.controller.$$ndl.clientLogService.logEvent(form1.controller.$$ndl.sessionId, "SubmitComplete for Main record", "Form1", "Success", "",0 ) ;
//Child1
//set up the ndlParams for the first Child Dataset update
var ndlParamsChild1 = {};
ndlParamsChild1.requestParams = ""; //initialise the requestParams field
ndlParamsChild1.tablename = "MyChild1Dataset"; //datasetname
ndlParamsChild1.onResponseReceived = onResponseReceived;
ndlParamsChild1.onSubmitComplete = [onSubmitCompleteReceived]; //called when the child record submission succeeds
for(i = 0; i < MyDatasource.myChild1Datasets.updates.length; i++){ //insert the parent record PK value as the FK in each child
NDLDSMyDatasource.myChild1Datasets.updates[i].myChild1FKColumn = mainRecId;
}
NDLDSMyDatasource.updateDatasource(ndlParamsChild1); //perform the update
//Child2
//set up the ndlParams for the second Child Dataset update
var ndlParamsChild2 = {};
ndlParamsChild2.requestParams = ""; //initialise the requestParams field
ndlParamsChild2.tablename = "MyChild2Dataset"; //datasetname
ndlParamsChild2.onResponseReceived = onResponseReceived;
ndlParamsChild2.onSubmitComplete = onSubmitCompleteReceived; //called when the child record submission succeeds
for(i = 0; i <MyDatasource.myChild2Datasets.updates.length; i++){ //insert the parent record PK value as the FK in each child
NDLDSMYDatasource.myChild2Datasets.updates[i].myChild2FKColumn = mainRecId;
}
NDLDSMyDatasource.updateDatasource(ndlParamsChild2); //perform the update
}
}
function onSubmitCompleteReceived(submitResponses2){
//this function is called each time one of the above updates completes, regardless of whether it succeeds or not
//it counts the successful submissions and is used to navigate to the success page when all expected responses have been received and were successful
console.log("onSubmitCompleteReceived received response count: " + submitResponses2.responses.length + " of expected total : " + expectedResponses);
if(submitResponses2.responses.length > 0){
for(var i = 0; i < submitResponses2.responses.length; i++){
if(submitResponses2.responses[i].status >= 400) {
var ndlParams = {
"hash": window.location.hash,
"form": form1,
};
NDLLibrary.moveToPage(ndlParams,"pg_Failure");
break;
}
else{
submitSuccessResponses++;
}
}
}
else{ //assume success - if an error occurs onDatasourceError will be called automatically, if Display Error Messages selected in Form properties
submitSuccessResponses++;
}
if(submitSuccessResponses >= expectedResponses){
var ndlParams = {
"hash": window.location.hash,
"form": form1,
};
form1.controller.$$ndl.submittedRecordId = mainRecId;
NDLLibrary.moveToPage(ndlParams,"pg_Success");
}
}
function onResponseReceived(responses){
if(responses.length > 0){
console.log("onResponseReceived received " + responses.length + " responses.")
for(var i = 0; i < responses.length; i++){
console.log("onResponseReceived response#" + i + ": status " + responses[i].status);
}
}
}
Notes:
-
updateDatasource should only be used if you want to update a record without submitting the form.
-
For Recordset Elements all rows in the Recordset will be updated as separate records.
If you have created a Digitise Forms Datasource using New Datasource from Form where the form contains at least one Recordset, updating the form Dataset will, by default, automatically update the Recordset Dataset(s) as well; you don't need to call updateDatasource separately for each Recordset Dataset, unless you have disabled this option in the form Dataset's properties.
-
You can set data values for any fields in the Dataset which don't have output mappings from Elements on the form immediately before calling prepareDatasource, e.g.:
ndlParams.tablename = "MyDataset";
NDLDSMyDatasource.myDatasets.getCurrent().col1 = getRandomIntInclusive(1,10);
NDLDSMyDatasource.myDatasets.getCurrent().col7 = "Sample string";
NDLRuntime.prepareDatasource("MyDatasource", "MyDataset");
ndlParams.data = NDLDSMyDatasource.myDatasets.getCurrent();
NDLDSMyDatasource.updateDatasource(ndlParams);
You can add values to fields which do have output mappings from Elements on the form as well, but these will be overwritten with the current values in the Elements by prepareDatasource.
Note that column names must be converted to a suitable format for use within JavaScript - any characters which are not A-Z, a-z, 0-9 or '_' should be replaced with '_', and the first character of the name should be converted to lower case. The Code Name column under the Datasource Mappings tab shows the name as it should be used here. The names are case-sensitive.
Remember, you can only write to Datasets which have a Primary Key and you must have enabled write access in the Dataset's properties by selecting the Allow Write Access property.
-
If a Dataset has a Primary Key with a data type of int and its Identity property set to True or a data type of uniqueidentifier (GUID), Forms Studio will automatically assign a value to the Primary Key field. For Primary Key(s) which have other data types, you will need to manually enter a suitable value into the Primary Key field(s), either within your custom JavaScript or via an output mapping from a suitable Element on the form, before you update the physical database. When you publish your form, Forms Studio will warn you if there are any Datasets which won't automatically have their Primary Keys generated.
Digitise Forms Datasources automatically have their Primary Key set to be a GUID, so this is generally only a potential issue with Imported Datasources. You can check a Primary Key's data type by opening the appropriate Dataset from the Datasources tab in the Project Explorer.
-
If you are updating Datasets which include one or more Foreign Keys, refer to the
-
If you have Datasource error reporting enabled (see ndlParams.errorHandler property above) and an error occurs, the error message will be displayed and any onResponseReceived or onSubmitComplete function specified will also be run but there is no guarantee that the error will be displayed before the function runs. Therefore, you will need to ensure that your function is written to allow for this situation should it arise.
-
The data upload is asynchronous, which means that the Script doesn't wait for it to complete before continuing and any code following the updateDatasource will be executed whilst the data upload is taking place.
Any actions that require the data upload to be completed before they are performed can be coded using the onResponseReceived and/or onSubmitComplete properties, which allow you to specify a second function to be run after each record has been uploaded or when the data upload has finished, respectively.
If you want to make more than one call to updateDatasource within the same function, you can either include multiple calls within the same function or use the onSubmitComplete functions to nest calls. Which you choose depends upon whether you need one data upload to finish before performing a second. Where the logic requires one upload to have finished before a second upload starts, you can include the second updateDatasource within the onSubmitComplete function for the first in order to be sure that the first completes before the second starts. Where it doesn't matter whether the first call has finished before a subsequent call starts, you can include both calls to updateDatasource in the same function.
The parameter passed into the onResponseReceived function, will be an array of HTTP response objects containing the HTTP response(s) from the upload. It is likely that there will in fact only be one response contained in this array. For example, the following code is take from the onResponseReceived function onResponseReceived from Code Sample 3 above:
function onResponseReceived(responses){
if(responses.length > 0){
console.log("onResponseReceived received " + responses.length + " responses."
⁝
The parameter passed into the onSubmitComplete function, will be an array of HTTP response objects which is a member of the parameter object. For example, the following code is take from the onSubmitComplete function onMainRecordSubmitSuccess from Code Sample 3 above:
function onMainRecordSubmitSuccess(submitResponses){
if(submitResponses.responses.length > 0 && submitResponses.responses[0].status < 300){
⁝
If you want to perform multiple calls to updateDatasource, whether within a single function or nested using an onSubmitComplete function, each call will need to have a separate version of the ndlParams object. Code Samples 2 and 3 above, provides examples of using nested updateDatasource calls and creating ndlParam objects. The principle is the same if you include both calls within a single function rather than nesting them.
-
You can also include any additional custom parameters you might want to pass into your onResponseReceived or onSubmitComplete functions by adding them to the ndlParams object along with the other properties, e.g.:
ndlParams.tablename = "MyDataset";
ndlParams.errorHandler = functionname;
// specify an onResponseReceived function - the name shouldn't be in quotes
ndlParams.onResponseReceived = myFunction;
//pass through customer name
ndlParams.custName = form1.page1.ndltextbox1.text;
NDLRuntime.prepareDatasource("MyDatasource","MyDataset");
ndlParams.data = NDLDSMyDatasource.myDatasets.getCurrent();
NDLDSMyDatasource.updateDatasource(ndlParams);
When specifying custom parameters in this way, you must avoid using reserved words or parameter names which are already in use. For a list of typical reserved words see: https://www.w3schools.com/js/js_reserved.asp.
-
Remember when calling updateDatasource and getCurrent that you can find the names you need to use in your custom JavaScript to refer to the Datasource and Dataset from the Datasource and Datasets's Code Name properties and for Dataset column names from the Code Name column under the Mappings tab when the dataset is open in the Form Design workspace.
Using as Predefined Action in Event:
When assigned to an Event as a predefined action, this function doesn’t require any editing.
If you want to update more than one Dataset within a single Event, you will need to configure the first Update Datasource action and then convert it to a JavaScript function from the Event Builder pop-up and then add the second into the JavaScript function manually, using the code samples above as a guide. You can add the second call to updateDatasource into the same function or use the onResponseReceived parameter to run it in a separate function - see Notes above for more details.
See also: