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:

 

                        image178.jpg

 

 

  Property   Description
DataSource Select the datasource to be used In this Target for writing SQL Server records.
image179.jpg
Follow the on-screen instructions to select the Connector Type, Connection Option and other values.  
HighPerformance Option (available in Salesforce and Dynamics365/CRM targets) By default the BulkLoadOption property is set to Disabled.
Click the property button to enable high performance/bulk processing.  Bulk processing is discussed in detail in the next section.  

 


 

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:

 

image180.jpg 

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.

 

                                image181.jpg

 

                       

 

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.

 

                                       image183.jpg

 

                         

 

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. 

 

                                        image184.jpg

 

                       

 

 

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.

   delete_record.jpg

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:

     delete_record_optionaldetails.jpg

 

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.

 

                                       

 

image186.jpg 

 

Property Description
Datasource Click to property button to configure or select the data source and other connection values.  
StoredProcedureType Select the type of stored procedure indicating the type of result returned.  
StoredProcedure The name of the stored procedure  
StoredProcedureFields This collection property specifies the data fields in the stored procedure. Only the field name and field type is required when defining the field.  The field name must match the column name used in the stored procedure.   image187.jpg  
StoredProcedureParams If the stored procedure uses parameters, specify them with this collection property.   image188.jpg  
AutoLoad Use FirstRecord to read the first record from the stored procedure or AllRecords to read all records.  
TestRunParameters Design time property to get stored procedure parameter values.  


 

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