blend 365- quick start guide: 1. use ase: reation of …...cin: u72500tg2018ptc121667 uptrix...

CIN: U72500TG2018PTC121667

Uptrix Consulting Pvt.Ltd. +91 8008703185, www.uptrixconsulting.com

H.No.6-3-1090/A/9, Suite No.4B, Somajiguda, Hyderabad, Telangana, India, 500082

Blend 365- Quick Start Guide:

1. Use Case: Creation of an automation script to “Create” data in MS Dynamics from Excel.

Steps: 1. On the Home screen click on “Create Script”.

2. Select the data source as “Microsoft Excel” and the required Destination System. Click on

the “Connect” button to connect to the Destination System by providing your login

credentials.

http://www.uptrixconsulting.com/

CIN: U72500TG2018PTC121667



3. Please provide a name for the automation script and data file. In case you do not wish to

specify them here, please proceed by clicking on “Select Objects Arrow”.

4. Select the “Create” Operation to create data in Dynamics.


CIN: U72500TG2018PTC121667



5. Select the required Master Object and click on the “Mapper Arrow”.

6. In the Mapper screen select the object fields which are required to create data for the

Master Object.

7. Map the selected fields to the Excel Preview screen by clicking on the “Auto Map” button

and verify the field mapping. Save the Excel template and the Script.


CIN: U72500TG2018PTC121667



8. Fields can also be mapped manually, if required by using a drag and drop approach.

9. After mapping the required fields click on the “Workflow Arrow” at the bottom on the right.

10. Before starting the workflow, please ensure that the excel file contains data for review.

11. Provide the Reviewer(s) and Originator email IDs and click on “Start Workflow”.


CIN: U72500TG2018PTC121667



12. If you wish to skip the workflow, please proceed by clicking on the “Execute Arrow”.

13. Add the desired data to the Excel template, by opening the saved file by clicking on the

“Open in Excel” button on the Execute screen.

14. Validate or Execute the script by selecting the “Run Parameters” in the execution step.

Note: The Validate function does not create any entries in Dynamics. It only verifies data quality in the excel template. The Execute function creates the physical entries in Dynamics.

2. Use Case: Creation of an automation script to “Update” existing data in MS Dynamics from

Excel.



CIN: U72500TG2018PTC121667





credentials.


CIN: U72500TG2018PTC121667




specify them here, please proceed by clicking on the “Select Objects Arrow”.

4. Select the “Update” Operation to update data in Dynamics.


CIN: U72500TG2018PTC121667




6. In the Mapper screen select the object fields which are required to update data for the

Object.




CIN: U72500TG2018PTC121667




9. After mapping the required fields click on the “Workflow arrow” at the bottom on the right.

10. Before starting the workflow please ensure that the excel file contains data for review.



CIN: U72500TG2018PTC121667




13. Use a query to download the data by adding a “Filter”. (Please refer to the steps under Use

Case 11: “How to use a Filter to download the desired data “).

14. Edit the desired data in the Excel template, by opening the saved file by clicking on the

“Open in Excel” button.



3. Use Case: Creation of an automation script to “Create/Update” data in MS Dynamics from

Excel.



CIN: U72500TG2018PTC121667





credentials.


CIN: U72500TG2018PTC121667





4. Select the “Create/Update” Operation to create/update data in Dynamics.


CIN: U72500TG2018PTC121667




6. In the Mapper screen select the object fields which are required to create/update data for

the Object.




CIN: U72500TG2018PTC121667



8. Fields can also be mapped manually if required, by using a drag and drop approach.


10. Before starting the workflow please ensure that the excel file contains data for review.




CIN: U72500TG2018PTC121667



13. Use a query to download the data by adding a “Filter”. (Please refer to the steps under Use

Case 11: “How to use a Filter to download the desired data “).

16. Add new data or Edit the downloaded data, by opening the saved file by clicking on the

“Open in Excel” button.

14. Validate or Execute the script by selecting the “Run Parameters” in execution step.

15. If a record already exists in Dynamics, it will be updated based on the values in the Excel

template. If a record does not exist, then a new record will be created.

Note: The Validate function does not update any entries in Dynamics. It only verifies data quality in the excel template. The Execute function updates the physical entries in Dynamics.

4. Use Case: Creation of an automation script to “Download” data from MS Dynamics into Excel



CIN: U72500TG2018PTC121667





credentials.


CIN: U72500TG2018PTC121667





4. Select the “Download” Operation to download data from Dynamics.


CIN: U72500TG2018PTC121667




6. In the Mapper screen select the object fields which are required to download data for the

Object.




CIN: U72500TG2018PTC121667



8. Fields can also be mapped manually if required, by using a drag and drop approach.

9. Use a query to download the data by adding a “Filter”. (Please refer to the steps under

Use Case 11: “How to use a Filter to download the desired data “).

10. Execute the script by clicking on the “Download” button in the execution step.

5. Use Case: Creation of an automation script to “Create” data in MS Dynamics from a CSV or a

TXT file



CIN: U72500TG2018PTC121667



2. Select the data source as “CSV and TXT” and the required Destination System. Click on the

“Connect” button to connect to the Destination System by providing your login credentials.


CIN: U72500TG2018PTC121667



3. Please provide a name for the automation script. In case you do not wish to specify it here,

please proceed by clicking on the “Select Objects Arrow”.



CIN: U72500TG2018PTC121667



5. Select the required Object and click on the “Select Source Files & Mapper” arrow.

6. Select the field delimiter from the dropdown and browse the CSV or TXT data source file

accordingly to proceed.


CIN: U72500TG2018PTC121667



7. In the Mapper screen select the “Source Fields” in the CSV or TXT file from the drop down and

map them to the corresponding object fields in Dynamics.

8. Verify the field mapping and “Save” the Script.

9. Execute the script by clicking on the “Upload” button in the execution step.

10. To verify the number of records uploaded, click on the “Log Viewer” in the left menu.


CIN: U72500TG2018PTC121667



11. If there are any errors shown in the log viewer, please refer to Use Case 14 for error handling.

6. Use Case: Creation of an automation script to “Create” data in MS Dynamics from SQL Server



CIN: U72500TG2018PTC121667



2. Select the data source as “SQL Server” and the required Destination System. Connect to

Dynamics by providing your login credentials.


CIN: U72500TG2018PTC121667



3. Please provide a name for the automation script. In case you do not wish to specify it here,

please proceed by clicking on the “Select Objects Arrow”.



CIN: U72500TG2018PTC121667



5. Select the required Object and click on the “Select Source Files & Mapper” arrow.

6. Click on “Connect” and select Microsoft SQL Server. Click on “Ok”.

7. Select the “Server Name” from the dropdown list and log on to the server by selecting “SQL

Server Authentication” or “Windows Authentication”.


CIN: U72500TG2018PTC121667



8. Select or Enter the database name, when working with a specific database. Click on the “Ok”

button.

9. Click on the “Read List” option to retrieve the list of tables.

10. Select the Tables in the drop down as per the objects selected and proceed.


CIN: U72500TG2018PTC121667



11. In the mapper screen select the “Source Fields” from the drop down and map them to the

corresponding object fields in Dynamics.

12. Verify the fields mapped and “Save” the Script.

13. Execute the script by clicking on the “Upload” button in the execution step.

14. Verify the number of records uploaded by clicking on the “Log Viewer” in the left menu.


CIN: U72500TG2018PTC121667



15. If there are any errors shown in the log viewer, please refer to Use Case 14 for error handling.

7. Use Case: Creation of an automation script to “Create” data in Dynamics 365 from Excel for a

Master Object and multiple Detail Objects.



CIN: U72500TG2018PTC121667



2. Select the data source as “Microsoft Excel” and the desired Destination System. Click on

the “Connect” button to connect to Destination System by providing your login

credentials.




CIN: U72500TG2018PTC121667



4. Select the “Create” Operation to upload data into Dynamics.

5. Select the required Master and Detail Objects and click on the “Mapper Arrow”.

6. In the Mapper screen select the Master Object fields and Details Object fields, which are

required to “Create” data for the Master and Detail Objects respectively.


CIN: U72500TG2018PTC121667




on the respective sheets of the Master and Detail objects.

8. Verify the field mapping. “Save” the Excel template and the Script.

9. After mapping the required fields click on the “Workflow arrow” at the bottom on the

right.


CIN: U72500TG2018PTC121667



10. Before starting the workflow please ensure that excel file contains data for review.



13. Add the desired data to the Excel template, by opening the saved file by clicking on “Open

in Excel” button.



CIN: U72500TG2018PTC121667




8. Use Case: Creation of an automation script to “Update” existing data in MS Dynamics from

Excel for a Master Object and multiple Detail Objects.


2. Select the data source as “Microsoft Excel” and the required Destination System. Click on the



CIN: U72500TG2018PTC121667



3. Please provide a name for the automation script and data file. In case you do not wish to specify

them here, please proceed by clicking on the “Select Objects Arrow”.

4. Select the “Update” Operation to update existing data in Dynamics.


CIN: U72500TG2018PTC121667



5. Select the Master Object and Detail Objects. Click on the “Mapper Arrow”.

6. In the Mapper screen select the Master Object fields and Details Object fields, which are

required to “Update” data for the Master and Detail Objects respectively.


CIN: U72500TG2018PTC121667



7. Map the selected fields to the Excel Preview screen by clicking on the “Auto Map” button on

respective sheets of the Master and Detail Objects.

8. Verify the field mapping. “Save” the Excel template and the Script.






CIN: U72500TG2018PTC121667




14. Use a query to download the data by adding a “Filter”. (Please refer to the steps under Use Case

11: “How to use a Filter to download the desired data “).

15. Edit the desired data in the Excel template, by opening the saved file by clicking on the “Open in

Excel” button.



CIN: U72500TG2018PTC121667




template. If a record does not exist, then a new one will be created.


9. Use Case: Creation of an automation script to “Create/Update” data in MS Dynamics from

Excel for a Master Object and multiple Detail Objects.


2. Select the data source as “Microsoft Excel” and the desired Destination System. Click on



CIN: U72500TG2018PTC121667





4. Select the “Create/Update” Operation to create/update data in Dynamics.


CIN: U72500TG2018PTC121667



5. Select the Master Object and Detail Objects. Click on the “Next Arrow”.

6. In the Mapper screen select the Master Object fields and Detail Object fields, which are required

to “Create/Update” in Dynamics.


CIN: U72500TG2018PTC121667



7. Map the selected fields to the Excel Preview screen by clicking on the “Auto Map” button on

respective tabs of the Master Object and Detail Object sheets.


9. Verify the field mapping and Save the Excel template and the script.

10. After mapping the required fields click on “Workflow arrow” on the right at the bottom.




CIN: U72500TG2018PTC121667




14. Use a query to download the data by adding a “Filter”. (Please refer to the steps under Use case


15. Add new data or Edit the downloaded data, by opening the saved file by clicking on the “Open

in Excel” button.

16. Validate or Execute the script by selecting the “Run Parameters” in execution step.


CIN: U72500TG2018PTC121667




template. If a record does not exist, then a new one will be created.


10. Use Case: Creation of an automation script to “Download” data from MS Dynamics into Excel

for a Master Object and multiple Detail Objects.


2. Select the data source as “Microsoft Excel” along with the desired Destination System. Click on

the “Connect” button to connect to the Destination System by providing your login credentials.


CIN: U72500TG2018PTC121667





4. Select the Master Object and Detail Objects. Click on the “Mapper Arrow”.


CIN: U72500TG2018PTC121667



5. In the Mapper screen select the object fields, which are required for downloading the Master

and Detail Objects, in respective sheets.

6. Map the selected fields to the Excel Preview screen by clicking on the “Auto Map” button. Verify

the field mapping and Save the Excel Template and the Script.


CIN: U72500TG2018PTC121667



7. Use a query to download the data by adding a “Filter”. (Please refer to the steps under Use case


8. Execute the script to download the data by clicking on the “Download” button.

11. Use Case: How to use a Filter to download the desired data for the Update, Create/Update

and Download operations.



CIN: U72500TG2018PTC121667



2. Select the data source as “Microsoft Excel” and the required Destination System. Click on the



CIN: U72500TG2018PTC121667




them here, please proceed by clicking on the “Select Objects” Arrow.

4. Select the “Update or Create/Update or Download” Operation as required.


CIN: U72500TG2018PTC121667



5. Select the required Master Object and click on the “Mapper” Arrow.

6. In the Mapper screen select the object fields which are required.

7. Map the selected fields to the Excel Preview screen, by clicking on the “Auto Map” button and

verify field mapping. Save the Excel template and the Script.


CIN: U72500TG2018PTC121667




9. Proceed to the Execute screen by clicking on “Execute” on the left menu.

10. Provide the download limit as per the requirement and click on the “Filter” button for the

Condition Builder to open.

11. Select the relevant field on the left, on which the condition must be placed.


CIN: U72500TG2018PTC121667



12. Select the left brackets and the comparison character as per requirement. Then input the value

based on the comparison selected. Close the brackets.

13. Select And/Or operator If there are multiple conditions to be verified to download data.

14. Click on “Add” to add the condition.


CIN: U72500TG2018PTC121667



15. Select “Edit” to edit the condition and select “Delete” to delete the condition.

16. Once the conditions are added, click on check mark button to close the condition builder.

17. Select “Write Records” or “Clear Mapped Data”- To clear data in the excel before download or

“Append Data”- To append the downloaded data to the existing data in the excel or “Overwrite

Data”- To overwrite the existing data in the excel.

18. Click on the “Download” button to download the data.


CIN: U72500TG2018PTC121667



19. Once the data is downloaded save the Excel template.

12. Use Case: Scheduling scripts using Blend Scheduler.

Steps: 1. On the Home screen click on “Schedule script”.

2. Select the “Name of the connection” and “Notification Recipients”. Change the details under

“Script and Run Parameters” if required and click on “Next”.


CIN: U72500TG2018PTC121667



3. Provide the task “trigger” details such as, Day, Month, Year, Hours and Minutes for a One time

run or select any one of the other triggers based on the requirement. Also input the “Advance

Settings” and click on Finish.


CIN: U72500TG2018PTC121667



4. The “Scheduled task” can be we viewed in the “Scheduled Scripts” section under left menu bar.

5. Once the script file runs at the scheduled time, the logs can we viewed in the “Log Viewer”

under the left menu bar.


CIN: U72500TG2018PTC121667



Note: Users can also schedule the script from the execute screen by clicking on the Schedule button or from Command line.

Run schedule task from command line: Syntax: BlendCommand.exe [Scheduled Task name- Refer to the Task schedule for Task name] Ex: C:\Program Files (x86)\Uptrix\Blend365>BlendCommand.exe Blend_20191204-105659


CIN: U72500TG2018PTC121667



13. Use Case: Scheduling scripts using Command Prompt.

Steps: 1. Type in CMD or command prompt in Windows search.

2. Connect to your Dynamics system from your command line.

3. Provide the path of the script and data file.

4. Please refer to the below example:

C:\blend365> blendcommand.exe -logon_dyn 'oct19_hs' –api 'webapi' -run 'C:\Users\Admin\Documents\Blend\ScriptW\account_Create_XL.blend' -datasource 'C:\Users\Admin\Documents\Blend\DataW\account_Create.xlsx' -collection 'account' -startrow '2' -endrow '0' -logcolumn 'A' -validatecolumn 'B'


CIN: U72500TG2018PTC121667



Addition Information: Command line Parameter list 1.Dynamics Logon Name parameter

Syntax -logon_dyn 'logon name' Example -logon_dyn 'MyLogonName'

2. Run which script to run parameter Syntax -run 'script name'

-run 'C:\Users\Admin\Documents\Blend\ScriptW\account_Create_XL.blend' 3. Datasource Excel file path

Syntax -datasource 'Excel File Path' -datasource 'C:\Users\Admin\Documents\Blend\DataW\account_Create.xlsx'

4. Excel sheet name, or Csv file name or SQL Table name Syntax -collection 'NameOfSheethere' Example -collection 'account'

5 Start excel row, -startrow"StartRowNumberRowNumber" Syntax -startrow 'N' Example -startrow '2'

6. End excel row , zero means run all rows Syntax - endrow 'N' Example -endrow '0'

7. Log column name, Syntax -logcolumn 'NameOfColumn' Example -logcolumn 'A'

8 Validate Log column name -validatecolumn 'NameOfColumn' Example –validatecolumn 'B'

14. Use Case: How are errors for data migration tasks using SQL and CSV/TXT handled.

Steps: 1. Once the CSV and TXT/SQL script has been executed the number of records uploaded and errors

can be viewed from the “Log Viewer” on the left menu bar or from the execute screen.


CIN: U72500TG2018PTC121667



2. If there are any errors, an excel is file is auto generated and is located under C:\Users\(Users

Name)\Documents\Blend365\Data with the name (filename)_(CSV/SQL)_Err_(Date).xlsx.

Ex: contact_Create_CSV_Err_191120_154623

3. Browse the excel file and click on the “Open in Excel” button to check the error rows.


CIN: U72500TG2018PTC121667



4. Verify the data in Excel and make the required changes. Save the Excel.

5. Close the saved Excel.

6. Click on the “Run on errors” button.

7. Log column in the Excel shows the results of the run.

8. Repeat the process until the Excel file is error free.

15. Use Case: How to use Run Parameters to post data.

Steps: 1. There are four types of run parameters.

2. Upload Specified Rows: Uploads the specified number of rows as mentioned in the Start and

End Row.


CIN: U72500TG2018PTC121667



3. Upload Validated Rows: Uploads the rows which are validated. Data is validated by clicking on

the Validate button on the execute screen.

4. Run Error Rows Only: Uploads the rows which have errors.

5. Run Unprocessed Rows Only: Uploads the rows which have not been processed.

16. Use Case: How to use the Look up control for fields in mapper.

Steps: 1. Click on the “Look up” button in the mapper screen.

2. Select the available lookup fields of the object.


CIN: U72500TG2018PTC121667



3. Select Lookup query fields based on the requirement. These fields will appear in the Excel add-in

lookup.

4. Deselect the fields which are not required by selecting them on the left panel and click on the

“greater than(>)” arrow, to move them to right. Add them if required by click on the “less than

(<)” arrow button.

5. Once the fields are selected click on the “Preview Download” button to view a preview of the

data set.

6. Select the script fields on the left panel and click on the “greater than (>)” arrow to move them

to right panel.

7. Based on the selection of fields, the lookup will appear in the excel add-in. Click on the “Query”

button to query the lookup data.

8. When the lookup data is loaded in the lookup add-in, user can select any data row and click on

the “Copy to Sheet” button to copy the value field to the excel sheet from the preview.


CIN: U72500TG2018PTC121667



Note: Lookup needs to be configured in the automation script for it to be populated in the excel add-in.

17. Use Case: How to configure Rules in a script.

Steps: 1. Click on the “Rules Builder” button in the mapper ribbon.

2. Click on “New” to create a new rule.

3. Click on the “Add rule” button to add a new rule. Define the name of the rule and the conditions

under “Rule Definition”.


CIN: U72500TG2018PTC121667



Ex: if Name field length is greater than 0 then the Name needs to be changed to upper case. this.name.Length > 0 Then the action is this.name =this.name.ToUpper() We can also provide an Else Action based on the requirement.

4. Click on “Active” check box for the rule to get active.

5. Click on the “Ok” button to proceed.

6. Click on “Save” and close the Rule set.


CIN: U72500TG2018PTC121667



Please refer to the below table for more information on the operators, which can be used.

Operators Description

Add Used to add expressions.

Example: 2+2

BooleanAnd Represents a Boolean and expression, as used with if statements.

Example: val1 == val2&& val3 == val4

BooleanOr Represents a Boolean or expression.

Example: val1 == val2|| val3 == val4

Divide Provides the division operator.

Example: 10 / 2

GreaterThan Represents the greater than operator.

Example: x > 10

GreaterThanOrEqual Represents the greater than or equal to operator.


CIN: U72500TG2018PTC121667



Operators Description

Example: val >= 24

IdentityEquality The identity equality operator.

Example: val == anotherVal

IdentityInequality The identity inequality operator.

Example: val != anotherVal

LessThan Represents the less than operator.

Example: 5 < count

LessThanOrEqual Represents the less than or equal to operator.

Example: val <= 40

Modulus Allows the use of the modulus operator.

Example: (x%2)==0

Multiply The multiplication operator.

Example: 2x2

Subtract The subtraction operator.

Example: 40 - x

ValueEquality The value equality operator.

Example: val == 2

Please refer to the link below for few examples. https://flylib.com/books/en/1.504.1.55/1/


https://flylib.com/books/en/1.504.1.55/1/