Scripting

If you need to do more advanced things with the data, small scripts make almost everything possible. Moreover, it is not as if you have to learn everything about programming, since the entire architecture is already in place. You only need to focus on resolving a single value at a time, and it is very clear from the architecture both if, and exactly where a script has been created to resolve or validate a specific value.

What you can do

Among the many things you can do with scripts are:

Clean dirty values (e.g. numbers packed together with text or signs)
Validate if required fields have values
Validate if numbers are within a required range
Validate whether file paths exist and contain required files
Create compound values from several fields
Convert & translate values from lookup data sources
Summarize values
Count values
…and lots more

Script Execution Architecture

Previously we have seen how data can be processed with the value map. A script-field is actually part of the value map.
An Input-source is processed in the following way:

The entire Input-data source is iterated 1 row at a time.
For each Input-row, the entire value-map is iterated 1 map entry at a time.
Depending on what has been entered into the From and To fields of the value-map, it may result in a Name and Value for the Output-data source.
After this the derived Value will be written to the derived Name column in the Output-source.

If any script code has been created in the Script-field, a .Net method will be created automatically containing the script code. This method is called right after step 3 above and right before step 4. This makes it possible for the script code to test and change both Value and Name. This gives the script full control over exactly what Value to write and into which Column Name in the Output-data source.
The script is intentionally focused on one Name and Value at a time, so it is easy to see exactly where any script has been implemented and what it affects.
The script is .Net C# and you will have access to use some (not all) of the .Net library classes and methods, e.g. to check if a System.IO.File.Exists( filePath) and similar. However, remember to keep the task at hand simple and focused, and do not go crazy in .Net code  - just because you can!

The Script Method

The script code you write is automatically compiled into a method and executed exactly at the step in the value-map where you have located it. You do not know or need to know the name of the method; you can only control the content of the method.
When the method (i.e. your script code) is executed, it will have access to a few Properties with data about the current map-entry and results. Furthermore it will have access to a pre-compiled library of scripting methods that should make some tasks easier to do. You are very welcome to contribute with ideas and wishes to new library methods that you think will be useful (e.g. if you find yourself writing the same 3 lines of code every time).

Get Started Fast - Example

Below are a few examples of scripts to get you started quickly.

Example 1: Cleaning Dirty Number values

Assuming we receive an Excel-sheet that has a column containing dirty number values, that e.g. contain units in the same cell as the number (which means it is not really numbers). The values in the column might look something like this; "125.75 mm"
The following simple script will quickly solve this problem:

var num = ParseNumber( Value );
if( num.Value.HasValue )
Value = num.Value;

ParseNumber is one of the Scripting library methods, which is described in detail later.
Assuming we would also like to get the Unit from the above example into a separate Output-column, we can easily do this on a separate map entry with the following script:

Value = ParseNumber( Value ).Post;

Example 2: Validation of a required value

Sometimes values are not supplied, but you want to make sure they are. This can easily be done with the following script.

Validate.FailItem( !HasValue, "Fuel-field requires a value" );

Example 3: Validation of whether a number value is within a required range

Sometimes values cannot just have any value, but e.g. needs to be within a certain range. This can easily be done with the following script.

You will notice that there is an extra check in the beginning of the script. This is because if there was no Value the Convert.ToDouble() method would fail. If the value does not only need to be within a certain range, but also need to have a value, you can extend the script with a validation of this, in the same way as example 2.

More Examples

You will find more examples with the description of each Scripting Library method below.

Script Properties

Your script will have access to the following Properties that it can use.

string P.From	The exact content of the From field, as it appears in the map
string P.To	The exact content of the To field, as it appears in the map
object Value	The Value that will be written to the Output-source
string Name	The Name of the column in the Output-source that the Value will be written to
bool HasValue	Returns true if a Value has already been derived (e.g. by the standard value map process or it has been set earlier in your script).
object OutValue	The value in the Output-row (read-only). May exist from a previous iteration.
bool HasOutValue	Returns true if a value already exists in the Output-row, e.g. form a previous iteration.
long P.InRowNumber	A counter giving the number of the current Input-row
P.Validity	Get or Set the Validity of the current Value map-entry. Can be set to one of the following values:
	Validity.Pass
	Validity.Warn	A warning will be added to the $LOG
	Validity.FailItem	The row will be excluded and a notice written to the $LOG
	Validity.FailAll	All processing will abort after current Command and a notice will be written to the $LOG
P.Message	Set any message to inform of the result of a Validation that does not Pass.

External libraries/DLLs

If you miss some standard .Net libraries or would like to use a third parts library, it is possible to add external DLL file to the Plugin Management under Settings.

The Added DLL file must be .Net framework 4.8 compatible and cannot contain Namespaces "Pim." or "Perfion.". If a third parts library use other libraries (which is not already loaded) it is also necessary to add the missing DLL files.
To include the added library in the script code use a @using{ } secton in the top of the script like:

The example ActionDLL:

1 What you can do
2 Script Execution Architecture
3 The Script Method
4 Get Started Fast - Example
5 Script Properties
6 External libraries/DLLs

Script Library Methods & Properties

In addition to a lot of standard .Net classes and methods, below you will find a list of methods and properties that have been added to solve some common scripting operations.