Regular Expression with Capture Groups to extract specific information from bank file

A regular expression is a text string that allows you to create patterns that help match, locate, and manage text. Regular expressions help search a pattern in larger text and further help in extracting them for processing, which makes them ideally suited for text processing, and data validation. Cash Application Manager allows you to use regular expressions to identify and extract specific information from the bank file. A regular expression (also known as "regex") is a way using which you can express how CAM should look for a specified pattern in a bank file and then what CAM must do when each pattern match is found. Regex also helps in increasing the match rate of incoming invoices with the NetSuite invoices and boosts the accuracy of overall Cash Application Manager.

• Multiple Regex
  

  CAM allows you to provide multiple regular expressions in the following format:
  
  [“Regex1”, “Regex2”,”Regex3”].
  
  In the above example, there are three regular expressions Regex1, Regex2 and Regex3 arranged within square brackets. Notice that each regex is within double quotes.
  
  

• Capture Groups
  

  By placing part of a regular expression inside round brackets or parentheses, you can group that part of the regular expression together. Capture groups allow you to apply a quantifier to the entire group.
  
  

• Regular Expression with Capture Groups

  One valuable concept within Regex is the use of Capture Groups which allows you to separate the required fields for further processing, from the matched expression. Capture Groups reduce the need to process the complete string and also lets the end users take control of values being provided to a connector for processing.
  
  For example:
  
  SUB-EXPRESSION1(Capture Group 1)SUB-EXPRESSION2(Capture Group 2)
  
  In the aforementioned expression, the requirement is to match a string with certain patterns of interest and to process matched string rather than entire expression. Using Capture Group 1 and Capture Group 2, you can easily extract the exact information needed from the entire expression so that the specific information can be easily processed by the code. The connector makes use of this concept to extract customer name and invoice details. Therefore, you should frame a regular expression such that the entity they want to capture from the expression is identified by the capture group.

Steps to enter Regular Expression in CAM

Use the following steps to enter the regular expression within CAM:

1. Click the Settings icon pertaining to the Bank File to NetSuite flow. The Settings dialog box is displayed.
2. Click on the File Parsing tab. 
3. In Type 88 – Continuation Record, enter the values in the Field Description, Column Index, and Regex fields.
   
4. Click Save.

Regex support to retrieve customer information

You require the following information to extract the customer name from the data present in your bank file:

• An identifier in the bank file that can be used to identify your customer name
• Customer name format.

Note: The customer name and invoice details are all part of the Type 88 record in the Wire files almost invariably.

For example, assume the following is the bank file use wish to use:

This file contains two identifiers that can be used to identify the customer name. The identifiers are ORG:= and ORIG:=. The customer name format includes Lower and Upper case alphabets, space, special character &, numbers.

How to extract Customer Name

Remember that you only need to extract the customer name from the larger regular expression. So, the tag ORG:= and ORIG:= remains out of the Capture group of your regular expression.

Example 1:

The following regex is required to extract the customer information from the bank file that contains the data as shown in the aforementioned image:

["ORG:=([A-Za-z0-9 . &]{1,60})","ORIG:=([A-Za-z0-9 .]{1,60})"]

The above expression consists of two regex one to identify customers with identified ORG and another with identifier ORIG. [A-Za-z0-9 . &] helps to identify the customer name and {1,60} help to identify total length of customer names. {1,60} this represents that a customer name with up to a length of 60 characters is accepted.

So when connector processes this multiple regex, it picks the first regex:

ORG:=([A-Za-z0-9 . &]{1,60})

From which the customer data is derived as ORG:=new TOM&MARK

With the use of capture groups, the ultimate information that is shared by the connector is new TOM&MARK. It eliminates the excess information (ORG:=) which is not required for further processing.

Example 2:

Mentioning special characters is important in regex and if the character ‘&’ is removed from regex in example 1 as shown in the following regex:

ORG:=([A-Za-z0-9 .  ]{1,60})

The input that the connector receives is new TOM instead of new TOM&MARK.

Example 3:

After processing the information from first regex, the connector processes information from the second regex:

ORIG:=([A-Za-z0-9 .]{1,60})

From which the customer data is derived as ORIG:=new customer3

With the use of capture groups, the ultimate information that is shared by the connector is new customer3. It eliminates the excess information (ORIG:=) which is not required.

Once the customer information is retrieved from the bank file, against all transaction present in the bank file, NetSuite’s Celigo Cash App Batch record is updated with the same information against each transaction in the following manner:

Note: Connector will only accept the first matching inputs for a particular regex. As a single transaction can only have one customer. In case, multiple matches are found for one transaction only the first matched customer name will be used by the connector.

Regex support to retrieve Invoice Number, Amount and discount

You need to extract the Invoice details from the data present in your bank file:

• The identifier in the bank file that can be used to identify invoices associated with transactions.
• Invoice format.
• Amount format
• Discount format

When creating a Regex for extracting invoice details, you need to ensure that capture groups are in the following order:

1. Capture Group for Invoice
2. Capture Group for Amount
3. Capture Group for Discount

Note: All the three capture groups are optional so when there is an expression with just one capture group it will always fetch Invoice Number. When we have two capture groups, then only Invoice number and Amount will be fetched and finally, if we have three capture groups only then all three values would be extracted.

The connector only supports processing information when capture groups are defined in the following format.

(Capture Group 1 - INVOICE NUMBER)SEPARATOR(Capture Group 2 -INVOICE AMOUNT)SEPARATOR(Capture Group 3 -DISCOUNT AMOUNT)

When processing invoices associated with a transaction, the connector will accept all the matching invoices as input that is associated with a transaction. The reason being, unlike transaction having one customer, there can be multiple invoices for a single transaction.

For example, assume the following is the bank file information use wish to use:

This file contains three identifiers that can be used to identify invoice information. The identifiers are INV, IN-, and IV.

How to extract Invoice Number, Amount and Discount

Remember that you only need to extract the customer name from the larger regular expression. So, the tag: INV, IN-, and IV needs to remain out of the Capture group of your regular expression.

Example 1:

The following regex is required to extract the Invoice information from the bank file that contains the data as shown in aforementioned image:

["IV[*]{1,2}([0-9]{1,20})[*]{1,2}([0-9.]{1,18})[*]{0,2}([0-9.]{0,18})", "INV[*]{1,2}([0-9]{1,20})[*]{1,2}([0-9.]{1,18})[*]{0,2}([0-9.]{0,18})", "IN-[*]{1,2}([0-9]{1,20})[*]{1,2}([0-9.]{1,18})[*]{0,2}([0-9.]{0,18})", "INV([0-9]{4,8})[*]{1,2}([0-9.]{1,18})" ]

The above expression consists of four regex. The first regex is to identify Invoice number, Amount and Discount with identified IV. The second regex is to identify Invoice number, Amount and Discount with identified INV. The third regex is to identify Invoice number, Amount and Discount with identified IN-. The fourth regex is to identify only Invoice number and Amount with identified INV.

So when connector processes this multiple regex, it picks the first regex:

IV[*]{1,2}([0-9]{1,20})[*]{1,2}([0-9.]{1,18})[*]{0,2}([0-9.]{0,18})

Let’s assume the information extract with this regex is IV*10075363**100.00*0.

With the use of capture groups the ultimate information that is shared by the connector is Invoice:10075363, Amount:100.00, Discount:0. It eliminates the excess information IV which is not required.

Example 2:

After processing the information from first regex, the connector processes information from the second regex:

INV[*]{1,2}([0-9]{1,20})[*]{1,2}([0-9.]{1,18})[*]{0,2}([0-9.]{0,18})

Let’s assume the information extract with this regex is INV*10075863**100.00*0. and INV*10075361**100.00.

With the use of capture groups, the ultimate information that is shared by the connector is Invoice:10075863, Amount:100.00, Discount:0 and Invoice:10075361, Amount:100.00. For invoice 10075361, no information is added for the discount in the NetSuite It eliminates the excess information IV which is not required.

Example 3:

After processing the information from second regex, the connector processes information from the third regex:

IN-[*]{1,2}([0-9]{1,20})[*]{1,2}([0-9.]{1,18})[*]{0,2}([0-9.]{0,18})

Let’s assume the information extract with this regex is IN-*10075362**100.00*20.

With the use of capture groups the ultimate information that is shared by the connector is Invoice:10075362, Amount:100.00, Discount:20.

Example 4:

After processing the information from second regex, the connector processes information from the third regex:

INV([0-9]{4,8})[*]{1,2}([0-9.]{1,18})

Let’s assume the information extract with this regex is INV10075364*100.00 and INV10075367*100.00.

With the use of capture groups, the ultimate information that is shared by the connector is Invoice:10075364, Amount:100.00, Discount:0 and Invoice:10075367, Amount:100.00.

Once the Invoices and associated information is retrieved from the bank file, against all transaction present in the bank file, NetSuite’s Celigo Cash App Transaction record is updated with the same information against each transaction in the following manner: