Creating a field with a number to use it as index

August 10, 2018

Recently, a user has posted an interesting question in this website forum:

I’m writing to a text file. Here’s my “Text to insert”: ‘1’,'<%customerID%>’,'<%customerID%>’,”,”,”,”,’ADMIN’,'<%shipToID%>’,”,”,”,”,N’ I’d like some way to increment the “1” with each successive line I extract from the e-mail…. I’ve been trying to create a static field above, and increment it.. tried a script & do it in C#… Help!!! and Thanks, Rich

In this case, the user wanted an index value to write to a text file but it may also happen that you need an index value to insert to a database table. If doing so note that database tables can generate their own index when the SQL INSERT command is sent. But you can also provide them your own index value manually if you want to. For example, if we retrieve the following fields from an incoming email:

And we want to insert to the text file the following:

1,4564458-A,RTAW3642
2,32133-C,RTAX2901
3,9547184-C,RTAZ2902

To achieve that we need another field that we call “my_number” and contains the values 1, 2 and 3. We also need that “my_number” contains as many values as the rows we are retrieving from the email. So, if we parse two rows “my_number” should hold only 1 and 2, not 3. In order to get that we write this small script:

using System;
using System.Text;
using System.IO;
using System.Net.Mail;
using System.Collections.Specialized;
using EmailParserBackend.ScriptingInterface;
 
public class MyAction : ScriptBasedAction
{
    // Implement this method with your own code. Run() will be called each time
    // this Action runs
    public override bool Run(MailMessage email, NameValueCollection fields)
    {
 
    int counter = 1;
 
    // for each value in shipToID we create a new number in my_number
    foreach(string s in fields.GetValues("shipToID"))
        fields.Add("my_number",counter++.ToString());
 
    return (true);
    }
}

You can check out how Email Parser scripting works if you need more details about how scripting works. In this particular example, the script takes the already existing field “shipToID” and depending on the number of values it has it creates a new value in the field “my_number”:

After that, all we need is to use the field “my_number” as any other field in the Write to Text file action:

For reference, you can see the full configuration in the following screen captures:

Tags

Archives

Creating a field with a number to use it as index