Building Your First Action
- Morten Lindequist Køhler
About this example
Once the Actions installer has created the Actions features in your environment, you can now start configuring Actions to suit your needs. This demonstration is a common example that takes product data from two sources, a .xlsx and a .csv file, and merges them together before importing the unified data set into Perfion.
Create source files
You need two source files to build this example. You can build them by following instructions below.
Excel source file
Create the Excel source file like this:
Open a blank Excel-file
Copy the data below exactly as-is into the first sheet
Name the sheet DEMO
Save the file as Mappings.xlsx
COLOR INFO CUPS |
|
|
| |
from Jenny Marketing |
|
|
|
|
|
|
|
|
|
| Itemnumber | Color | Diameter | Volume |
|
|
|
|
|
| 9869031 | black | 8 cm | 0.3 l |
| 23242968 | red | 7 cm | 0.25 l |
CSV source file
Create the CSV source file like this:
Open a blank Notepad-file
Copy the data below exactly as-is into the file
Save the file as someCups.csv
SKU;Name;Teaser;Hierarchy;Date;Senseless;pic;ProductGroup
Action Mapping Cups;;;Coffee & Tea|Cups;;;;
9869031;Java Coffee Mug;This Java Coffee Mug is designed to add a splash of color and style to your home;;1/12/2018;whatever;C:\Perfion Data\actionMapping\pics\9869031.png;Action Mapping Cups
23242968;Stoneware Coffee Mug; Add a dash of color to your kitchen with this Stoneware Coffee Mug.;;1/12/2018;something;C:\Perfion Data\actionMapping\pics\23242968.png;Action Mapping CupsCreate Action
Start by clicking Actions in the main menu.
Next, right click in the empty Actions panel on the left and choose either Add New or Add New Virtual. Virtual Actions entries are generally used to group Actions of a similar nature but can also have action maps of their own. In this example a new (non-virtual) item is created named Hello World:
Add Data Sources to Action map
The next step is to add commands to our Action map that define our data sources. Our example will take product data from two sources, so we must define two data sources in the mapping using the SET command. Make sure to change paths to match where you have saved your source files:
Populating Result Sets
Next, we need to tell our Action map which Perfion features we want populated from each of our data sets. To do this, we use the commands SELECT.CSV and SELECT.EXCEL.
Set CSV data source
Starting with our CSV data source, create a new Virtual Item (so we can add subordinate actions for the feature mappings) in the "Action Map – From" using the CSV SOURCE in the From column.
The first entry in the CSV SOURCE field selection should populate the @CONTEXT variable and tell the Action map how to interpret the comma separated values. In this example you must set context like this: 1/;//,/dd-M-yyyy
Looking at our input source, someCups.csv, we see that the header row includes the following data elements, so we must create a mapping for each field from which we want data. If a field is specified in the header, but not referenced in the import mapping it will be ignored. In this example we want all fields except "Senseless".
SKU
Name
Teaser
Hierarchy
Date
Senseless
Pic
ProductGroup
Create the following mappings under CSV SOURCE. Make sure values in the To feature match existing features in your Perfion database:
TIP: Since right-clicking on CSV SOURCE and choosing Add New will create an entry at the TOP of the list rather than at the bottom of the list where you generally want it, right-click on the blank space in the Action Map – From panel and choose Add New. This will create new items at the bottom of all existing mappings. When you've finished creating all of your mappings, select and move the group into the CSV SOURCE folder and they'll be placed in the correct order.
Set Excel data source
Next, we need to get data from our Excel spreadsheet. Following the same steps as with the CSV source, we'll first create a virtual item named EXCEL SOURCE and map it to a new data source named "EXCEL RESULTS" that will hold all of our mappings and set the context for how to interpret the product data.
Again, when defining mappings, you must set the context for the mapping to interpret the selected data. In this example you must set context like this: DEMO/B:E/3/1
Add the rest of the desired fields from the spreadsheet as mappings to Perfion Features. Again, make sure values in the To feature match existing features in your Perfion database:
Notice that there are validation scripts attached to Itemnumber, Diameter and Volume. Double click on the Script field to open the larger text editor.
Add this text to the Script column for Itemnumber:
Value = Convert.ToString(V["ItemNumber"]);This script ensures that the Itemnumber will be stored as a String rather than a Number. This is needed in the next stage where we will join the sets together on Itemnumber and SKU (both mapped to “_value”), and these should have the same data type.
Add this text to the Script column for Diameter and Volume:
var num = ParseNumber( Value );
if( num.Value.HasValue )
Value = num.Value;
else
Value = DBNull.Value;This script ensures that only numbers, or null values, are written to these two Features.
Join Data Sets
Next, we need to join the two result sets together so we can import all of the desired data at one time. To do this, we use the command JOIN.LEFT.
This command takes all the rows of the data set named "CSV RESULTS" and merges all of its columns and data with data set named "EXCEL RESULTS", but in the latter only for the rows where the joint fields are equal. Providing a single, unified data source to be imported.
We must also specify the common denominators on which to join in both data sets, this is done by populating the @LeftKeyColumn and @RightKeyColumn variables. In this example you must set both on: _value.
Setting Import Targets
The last step is to set the Perfion features that will take data from the unified data source, EXCEL RESULTS, when the import is processed. In this example the data is imported into the feature named "Product". To do this, we use the command IMPORT.
Executing Actions
Now that the mappings are complete, it's time to execute the Action and import the test data.
To open the Action Execution window and run an action, click on the Action icon.
The Perfion Action system gives you two options for starting your actions:
Execute Start: Allows you to execute each action manually, so that you can observe the data being manipulated in real time. This option is most useful for error checking and when you're building the actions (i.e. debugging) and testing your data model.
Execute All: Will run the entire action without showing each step. This option is used most often after all of the import specifics have been worked out and you just want to run the action and get data imported.
Choose your preferred option and let the Action run its course.
When the Action reaches the last IMPORT step it will launch the standard "Run Import? Y/N" dialog box. In the background you can see what will be imported. Click Yes.
After the system processes the import, if there are no errors, the Execution Process button will show the "Move Next >>" button, to move to the next command after the IMPORT.
The execution is now complete, and you may close the Action Execution window.
Viewing the Results in Perfion
Now that the Importer has run successfully, it's time to look at the results of the work. Navigate to "Products" and Refresh the relevant product view. You will now see the product hierarchy that has been created by the import.
CONGRATULATIONS!! You have now completed your first Action map. The rest of this document is dedicated to the full command reference for the Actions tool, and more advanced examples of working with different data sources.