Writing Data
Your projects will be writing data to files, databases, SAAS, and other targets. appRules includes activities for writing data in a variety of data sources.
InitializeTarget Activity
To write data to an appRules target, you will need to initialize the Target of the data, select the connector to use and define other properties using the InitializeTarget activity.
For example, to write data to Microsoft SQL Server, drag and drop the InitializeTarget activity from the SQL Server toolbox on to the designer and configure the properties as follows:
Configuring the High Performance Options – Bulk Load
You can set the BulkLoadOptions property of the InitializeTarget activity to optimize writing of data. When the related property button is pressed, the following window is displayed:
Follow the on-screen instructions and the field descriptions below to set the BulkLoadOption.
Bulk Load Option
Select the option to use from the list below:
Option | Description |
Disabled | This is the default setting. Optimized settings are not utilized when writing records to the target. |
Enabled | Enables basic optimization for writing records. This is the most commonly used option for optimizing performance. |
EnabledForStaging | This option saves the records in the RecordStaging table from where you can employ multiple projects utilizing multi-threading to process the records. You can also use this option for testing purposes – when you do not want to write directly to the target. |
Include
Use the checkboxes to select the type of transactions to include as part of high performance processing – Deletes, Inserts, Updates.
If you want to use bulk for Inserts and Updates in the same project, you need to define two targets for the same datasource entity/table.
Cache Size / Number of Threads
Set the cache size and number of threads to use for processing the records. You can use the default values or test other values depending on your system configuration (CPU, memory, etc.). By caching the records and using multiple threads, appRules optimizes the writing of records to the target.
PostExecutionActions
You can specify whether to cache the records in a preloaded table (set a lookuppreloaded source in the project and select it here), optionally select an insert/update activity (set in the project) and execute a customfunction or a child workflow for each batch completion.
Setting Data Field values for the Active Record
Before writing records in the target data source, the values of the data fields of the active record must be set.
The table below shows the activities that can be used to set the data field values of the active record:
Activity | Module | Notes |
MapAndSetFields | Supported in every modules such as SQL Server, Dynamics CRM etc. | Any number of MapAndSetFields activities can be used to set data field values. Field mapping was described in detail in the previous chapter. |
SetValues | Base Module | Any number of SetValues activities can be used to set data field values |
ClearRecord | Record Module | Clears the active record |
CopyRecord | Record Module | Copies field values from another Source or Target record to the active record |
TransformValues | Base Module | Transforms data field values in the active record |
MapAndSaveRecords | Base Module | Use the MapAndSaveRecords activity for bulk imports. This activity takes a source and a target and automatically reads all the records into the target. |
Inserting Records
Once the data field values in the active record have been set, you can save the new record in the target by using the InsertRecord activity.
For example, to insert record into SQL Server, drag and drop the InsertRecord activity from the SQL Server toolbox activities on to the designer.
Select the Target for inserting the record. All the field values that have been set or mapped to the current record for the selected target will be added to the data source.
To retrieve the key of the newly created record, check the RetrieveNewRecordKey box. The key value will be made part of the target record.
You can also define optional details for including more field values to save and also for logging detailed errors associated with adding the new record. To define optional details, click the OptionalDetails property button. The following window will be displayed:
Enter the property values as follows:
Property | Description |
IgnoreDataField | Click to Collection property button to define the fields that must be ignored in the current target record and not be saved in the data source. |
DataFieldValues | Click to Collection property button to define the additional fields and values to be saved in the data source. |
TargetClearRecord | Check this box to clear the target record before setting the values using DataFieldValues. |
CaptureRecordErrorDetails | When set to true, the value of the active record in the selected Source will be available if an error occurs during the execution of the activity (in association with the following properties). |
ErrorLogSource | When specified, the value of the active record in the selected Source will be written to the log if an error occurs during the execution of the activity. |
ErrorLogValues | When specified, the values in the collection will be written to the log if an error occurs during the execution of the activity. |
Updating Records
Existing records can be updated using the UpdateRecord activity. For example, to update a Salesforce record, drag and drop the UpdateRecord activity from the Salesforce activities tool box into the designer.
Select the Target for updating the record. You must also select whether to update the record using a PrimaryKey or a CompositeKey. All the field values that have been set or mapped to the current record for the selected target will be updated in the data source.
You can also define optional details for including more field values to save and also for logging detailed errors associated with adding the new record. To define optional details, click the OptionalDetails property button. The following window will be displayed:
Enter the property values as follows:
Property | Description |
IgnoreDataField | Click to Collection property button to define the fields that must be ignored in the current target record and not be saved in the data source. |
DataFieldValues | Click to Collection property button to define the additional fields and values to be saved in the data source. |
TargetClearRecord | Check this box to clear the target record before setting the values using DataFieldValues. |
CaptureRecordErrorDetails | When set to true, the value of the active record in the selected Source will be available if an error occurs during the execution of the activity (in association with the following properties). |
ErrorLogSource | When specified, the value of the active record in the selected Source will be written to the log if an error occurs during the execution of the activity. |
ErrorLogValues | When specified, the values in the collection will be written to the log if an error occurs during the execution of the activity. |
The UpsertRecord Activity
The UpsertRecord activity combines the InsertRecord and UpdateRecord activities. For example, to use the UpsertRecord activity from the Salesforce module, drag and drop the UpsertRecord activity on to the designer.
At run time, if the record exists, it is updated. If the record does not exist at run time, it is inserted into the Salesforce entity.
Select the Target for updating the record. You must also select whether to update the record using a PrimaryKey or a CompositeKey. All the field values that have been set or mapped to the current record for the selected target will be updated in the data source.
If the target record includes the salesforce key, you can set the AutoLookup property to True and the record will be Inserted or Updated depending on the key.
You can also define optional details for including more field values to save and also for logging detailed errors associated with updating the record. To define optional details, click the OptionalDetails property button. The following window will be displayed:
Enter the property values as follows:
Property | Description |
IgnoreDataField | Click to Collection property button to define the fields that must be ignored in the current target record and not be saved in the data source. |
DataFieldValues | Click to Collection property button to define the additional fields and values to be saved in the data source. |
TargetClearRecord | Check this box to clear the target record before setting the values using DataFieldValues. |
CaptureRecordErrorDetails | When set to true, the value of the active record in the selected Source will be available if an error occurs during the execution of the activity (in association with the following properties). |
ErrorLogSource | When specified, the value of the active record in the selected Source will be written to the log if an error occurs during the execution of the activity. |
ErrorLogValues | When specified, the values in the collection will be written to the log if an error occurs during the execution of the activity. |
Deleting Records
The DeleteRecord activity is used for deleting records from a target. For example, to delete a record from SQL Server, drag and drop the DeleteRecord activity on to the designer. To delete a record, the record key is required.
At run time, if the record exists, it is deleted.
Select the Target for updating the record. You must also select whether to delete the record using a PrimaryKey or a CompositeKey (RecordKeyType). If you use a composite key, define the fields/values used in the CompositeKey collection.
You can also define optional details for logging errors associated with deleting the record. To define optional details, click the OptionalDetails property button. The following window will be displayed:
Enter the property values as follows:
Property | Description |
ErrorLogSource | When specified, the value of the active record in the selected Source will be written to the log if an error occurs during the execution of the activity. |
ErrorLogValues | When specified, the values in the collection will be written to the log if an error occurs during the execution of the activity. |
Executing Stored Procedures
appRules supports the execution of stored procedures. For database engines that support stored procedures, appRules includes the ExecuteStoredProcedure activity for executing stored procedures. If the stored procedure returns records, the records and the fields they contain can be read from the database and utilized in appRules just like any other records and fields.
ExecuteStoredProcedure Activity
For example, to execute a stored procedure in SQL Server, drag and drop the ExecuteStoredProcedure activity from the SQL Server Toolbox on to the designer and configure the properties.
RecordCount | Design time property to get the record count if the stored procedure returns records. |
Records | Design time property to view the records if the stored procedure returns records. |
TestRunParameters | Design time property to test the stored procedure with parameters values |