Page Comparison

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 at 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:

1. The entire Input-data source is iterated 1 row at a time.

2. For each Input-row, the entire value-map is iterated 1 map entry at a time.

3. 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.

4. 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.

if( !HasValue ) return;
double val = Convert.ToDouble( Value );
Validate.FailItem( val > 8.0, "Cylinder value of '"val"' is out of range" );

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.

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:

@usings{
  System.Linq,
  System.Globalization,
  ActionDLL
}
List<string> resultFromExampleMethodList = ThirdPartsLibraryClass.ExampleMethod( "Input1", "Input2" );

var["$FirstInList"] = resultFromExampleMethodList.FirstOrDefault();

string swedishNumberString = "1 234 567,2";
double parsedNumber = 0;
double.TryParse( swedishNumberString, NumberStyles.Any, CultureInfo.CreateSpecificCulture( "sv-SE" ), out parsedNumber );
var["$ParsedNumber"] = parsedNumber;

The example ActionDLL:

using System.Collections.Generic;

namespace ActionDLL{
  public class ThirdPartsLibraryClass{
    public static List<string> ExampleMethod( string input1, string input2 ){
    var result = new List<string>();
    result.Add( "first imput: " + input1 );
    result.Add( "second imput: " + input2 );
    return result;
    }
  }
}