Replicate Cloud Platform Data Using the appRules® Data Replication Activity

As companies move applications to the cloud, there is an increasing need to make data available locally for many reasons.  IT organizations want to store the data locally for backup purposes, reporting and data analysis using familiar and latest tools.

appRules includes a very powerful workflow activity that is specifically designed to replicate data in a SQL database.  The ReplicateData activity is available in most modules of appRules.  It is designed to allow you to replicate data by simply configuring properties.   You can elect to to replicate all entities or to replicate only a selected list of entities.

Figure 1 shows a simple but very powerful workflow that can be used to replicate Dynamics CRM data in Microsoft SQL Server.  The process is the same for replicating Salesforce, SugarCRM, NetSuite, Eloqua, HubSpot, Marketo, SAP and other data sources.

Figure 1: ReplicateDynamicsCRMData

Accept the default entity configurations or make changes:

Select Entities:

Configure the properties below and you are ready to go.

Conclusion

The ReplicateData activity is a powerful tool for replicating SaaS data in SQL Databases.   It includes configurable options for managing every aspect of the process.

You can download appRules with sample projects at: http://www.appstrategy.com/appRulesTrial

appRules® Project Gallery : Supplier Evaluations


Note:
The Supplier Evaluations Project uses three decision tables for scoring and rating suppliers.
ExecutionPlan property for decision tables is set to OnDemand so the decision tables can be initialized once and executed repeatedly in the While loop for each supplier.
Scoring is done using the PerformScoring activity (GetSupplierScore).

To download appRules: http://www.appstrategy.com/appRulesTrial

Extend Data Integration & Business Rules Projects Using Custom Functions

We are constantly mentioning the fact that appRules is the most powerful no-code engine for composing integration and business rules solutions.  But that does not mean that appRules does not have the capability to integrate code in all forms when you need it.  To the contrary, appRules includes easy-to-use facilities throughout the engine to extend your workflows using custom functions and expressions.

 

The App Object

Since we are talking code, we must discuss the App object which is the most widely used object in appRules.

App is automatically added and scoped to the main (top) Sequence when you create a new project in appRules.

Expressions

You can access the methods and properties of the App object directly.  The designer will accept conditions directly from the App object. This allows you to enter condition expressions such as App.Result, App.IsActionResultValid(), etc. while composing your workflow.

Below is an example of using a method from the App object in the If activity:

Custom Functions

The Custom Function Editor is used for adding custom functions to appRules projects.  Code can be added using either C# or VB.  As in expressions, App exposes the methods and properties of the workflow to your function.   The Custom Function Editor also supports intellisence for browsing the methods and objects.

Unlike typical development environments, you do not have to worry about compiling your code and referencing the assembly.  Once validated, appRules takes care of compiling the code as needed.

Below is the appRules Custom Function Editor window:

Conclusion

Long story short, appRules – (the no-code engine) supports serious code when you need it.  It uses familiar languages and objects giving you full access to .NET.   This allows you to reuse existing code in your organization thereby saving money and completing projects much faster.

You can download appRules with sample projects at: http://www.appstrategy.com/appRulesTrial

Using appRules® to Implement Round Robin Lead and Incident Assignments in Dynamics CRM

Assigning leads and incidents to users using a formula that works for your company is a common requirement for most sales, marketing and customer service departments.

While most CRM and customer service solutions provide a rudimentary implementation of lead and incident assignment, they do not offer support for the advanced schemes that most companies require.

appRules includes several workflow activities that can be used to implement a variety of advanced assignment schemes. In this post, we will explore how you can implement round robin lead assignments in Microsoft Dynamics CRM without writing code.

Using the basic workflow activities in appRules, the project below shows how you can implement a round robin lead assignment solution for Dynamics CRM.

Initialization

The activities below are executed once to initialize the sources and targets used in the project.

# Activity Description
1 PreloadDynamicsCRMUsers This activity is the InitializeDynamicsCrmSource activity configured to load selected records from the SystemUser entity.
2 LeadSource This activity is the InitializeDynamicsCrmSource activity configured to fetch selected records from the Lead entity one at a time. These are the records to be assigned in a round robin fashion.
3 LeadTarget This activity is the InitializeDynamicsCrmTarget activity configured for updating records in the Lead entity.

 

 

Record Iteration

The activities below are executed in a loop to assign the leads in a round robin fashion. (Looping is implemented using the While activity with a simple App.Result condition.)

# Activity Description
1 AssignLead This activity is the Assign activity configured to assign the lead to the preloaded User.
2 GetNextUserFromList This activity is the GetNextPreloadedDynamicsCrmRecord configured to get the next User record from PreloadDynamicsCrmUsers.

Note: The EndOfListAction property is set to RestartList. This is what implements the round robin scheme since it will continue cycling through the preloaded users.

3 GetNextLeadRecord This activity is the GetNextDynamicsCrmRecord configured to get the next lead record.

 

By configuring only six out-of-the-box activities, we are able to compose a solution for implementing round robin lead assignment for Microsoft Dynamics CRM. This solution can be run on premise or in the cloud.

You can download this project at: http://www.appstrategy.com/appRulesTrial

Using appRules to Read and Process Stored Procedure Records

appRules Studio includes workflow activites for reading records from a stored procedure.  By utilizing the stored procedure related activities, the stored procedure records and the fields they contain can be read from the database and utilized in appRules just like any other records and fields.

Once a stored procedure record is read into an appRules project, the record and its fields can be used in Mappings, Custom Functions, Conditions, and other modules and facilities.

In this post, we will walk through a simple workflow example for reading records from a stored proceedure and viewing the field values using the ViewRuntimeRecords  activity.

Below is the sample workflow for reading records from a SQL Server Northwinds sample database stored procedure:

 

The table below shows the key workflow activities used in composing the workflow:

 

# Activity Type Notes
1 initializeProject Use the default values
2 ExecuteMssqlStoredProcedure

(ExecuteSqlServerStoredProcedure)

This activity is used to define a Source to select the records to be processed.

 

  • Configure the DataSource property to select the data source to be used for connecting to the database.

 

  • Set the StoredProcedure property by selecting it from the list of stored procedures in the database.

 

  • Define the fields in the stored procedure record by adding an entry for each field in the StoredProcedureFields collection. 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.

 

  • If your stored procedure uses parameters, you can define them by adding an entry for each parameter in the StoredProcedureParams collection.

 

  • Set the AutoLoad property to FirstRecord to read the first record from the stored procedure once the activity is initialized.

 

3 While Use a While activity to control the flow of the workflow.

 

  • Configure the Condition by using the App.ActionResultIsValid flag to check the last action performed by the activity before the While activity.

 

  • Add a Sequence activity to the Body of the While activity. This Sequence will contain activities that will read and display the next record from the stored procedure.

 

4 ViewRuntimeRecords Use the default values to display the fields read from the stored procedure.

 

5 GetNextMssqlRecord

(GetNextSqlServerRecord)

Reads the next record from the stored procedure.
6 CloseMssqlSource

(CloseSqlServerSource)

Closes the connection to the database
7 CloseProject Closes the project, logs statistics and frees resources
 

 

 

Below are the properties of the ExecuteSqlServerStoredProcedure activity:

 

This is an example of how you can use appRules workflow activities to read records and associated columns from a stored procedure.

You can download appRules Studio including this sample project at: http://www.appstrategy.com/appRulesTrial

Importing Very Large Delimited and Fixed-Length Files into On-Premise & SaaS Platforms Using appRules

Splitfile Activity

In this post, we will take a deep dive into the appRules SplitFile workflow activity. SplitFile is part of the appConnector.DelimitedFiles and appConnector.FixedLength file modules.

SplitFile can be used to split a file into multiple files  based on number of files or number of records that you specify.  In addition to splitting the file, the SplitFIle activity also allows you to specify a workflow to be executed once each file is created.  This feature used in conjunction with the High Performance options of the Microsoft Dynamics CRM Target activity is what allows you to compose and deploy workflows that deliver the best bulk load performance.

To illustrate the usage of the Split file activity to split and load large CSV and Fixed Length files into Microsoft Dynamics CRM, we will do a walkthrough of two sample workflows.   Note:  The process is the same for importing data into Salesforce, NetSuite, Oracle or other platforms.

In the first workflow (CreateChildCSVFiles), we will use a single SplitFile activity (along with the InitializeProject activity) to split a very large CSV file into smaller files.  After each file is created, a new instance of another workflow will be launched to process the records in the newly created child file.  Ofcourse you can add more activites to your workflow to suit your needs.

In the second workflow (ProcessChildCSVFile), we will show a sample workflow that is launched by the CreateChildSCVFiles after each child CSV file is created.  ProcessChildCSVFile is where the work is done to import the CSV records into Dynamics CRM.

 

CreateChildCSVFiles Workflow

Below is the CreateChildCSVFiles  workflow for splitting the large CSV file into multiple files.  After creating each file, the ProcessChildCSVFile  workflow is launched to process the records in it.

Figure 1 :  CreateChildCSVFiles workflow

 

 

The table below shows notes for the key properties of the workflow activities used in composing the workflow:

 

# Activity Type Notes
1 initializeProject Use the default values
2 SplitFile The SplitFile activity is used to split the file into smaller files and launch a new instance of the ProcessChildCSVFile workflow to process the records.

 

Configure the properties of this activity as follows:

 

  • Click the button to configure the SplitFileDefinition property to define the values required for the process. See figure 2 below for details.

 

3 CloseProject Closes the workflow, logs statistics and frees resources
 

Figure 2 : appRules SplitFile Activity Configuration window

Configure the values by following the instructions on the window

 

ProcessChildCSVFile Workflow

Below is the ProcessChildCSVFile workflow for processing the records in the files created by the main workflow after splitting the large CSV file into multiple files.  After creating each file, the ProcessChildCSVFile  workflow is launched to process the records in it.   By using this approach to launch and run mutiple processes,  the job is completed much faster.

 


Figure 3 :  ProcessChildCSVFile Workflow

The workflow must be configured to receive the workflow arguments to be passed to it by the master workflow CreateChildCSVFiles – (FilePath, FileNumber and RecordCount) .

Figure 4: Workflow arguments for ProcessChildCSVFile

The table below shows notes for the key properties of the workflow activities used in composing the ProcessChildCSVFile workflow:

 

# Activity Type Notes
1 initializeProject Use the default values
2 DynamicsCrmTarget

(InitializeDynamicsCrmTarget)

This activity is used to define a Target to be used for saving the CSV file records to the Microsoft Dynamics CRM data source.  You can add multiple Targets if your CSV file or requirements span more than one entity in Dynamics CRM.   Configure the properties of this activity as follows:

 

  • Click the button to configure the DataSource property to select the option for connecting to Dynamics CRM Online.

 

  • After configuring the DataSource property, select the PrimaryEntity from the dropdown list (account, contact, etc.).

 

  • Click the button to configure the HighPerformanceOption property. The default value for this property is set to Disabled. To utilize high performance option, you must select one of the other options to enable it.

 

3 CsvSourceFile

(InitializeDelimitedTargetFile)

This activity is used to define the Source CSV file to be imported into Dynamics CRM.  Configure the properties of this activity as follows:

 

  • Click the button to configure the DataFileDefinition property. This specifies the location of the file. Since the file name is passed as a workflow argument , select WorkflowArgument and select  the argument name (FileName) from the list.

 

  • Set the AutoLoad property to FirstRecord to read the first record from the file once the activity is initialized.

 

  • You can accept the default values for other properties or change them if necessary. These include Encoding, FieldEscapeChar, FieldQuotation etc.

 

4 While Use a While activity to control the flow of the workflow.

 

  • Configure the Condition by using the App.Results flag to check the last action performed by the activity before the While activity.

 

  • Add a Sequence activity to the Body of the While activity. This Sequence will contain activities that will read, map and save the CSV records in the Dynamics CRM entity.

 

 

5 MapAndSetCrmFields

(MapAndSetDynamicsCrmFields)

Use this activity to Map the CSV fields into the Dynamics CRM entity attributes

 

  • Select the CsvSourceFile activity as the Source.
  • Select the DynamicsCrmTarget activity as the Target
  • Click on the button to configure the FieldMaps property

 

 

6 InsertDynamicsCrmRecord

(InsertDynamicsCrmRecord)

Inserts the entity record into Dynamics CRM.

 

  • Select the DynamicsCrmTarget activity as the Target

 

7 GetNextCsvFileRecord Reads the next record from the CSV file
8 CloseDynamicsCrmTarget Closes the connection to Dynamics CRM
9 CloseCSVSourceFile Closes the CSV file
10 CloseProject Closes the workflow, logs statistics and frees resources
 

 

 

Conclusion

The appRules SplitFile activity is a simple and yet very powerful tool that can be used to tackle one of the more time consuming processes in data migration and integration.  Used in conjunction with other facilities in appRules, it offers unlimited options for processing CSV and Fixed Length files containing millions of records in a timely and cost-effective manner.

 

You can download appRules Studio including this sample project at: http://www.appstrategy.com/appRulesTrial