Handle null / empty value from X12 schema to database schema

BizTalk — take EDI 850 (X12_00401) N1 looing section as example

Photo from: https://safe-software.gitbooks.io/fme-desktop-basic-training-2017/content/DesktopBasic2Transformation/2.04.SchemaMapping.html

The default route for dealing with EDI850 could be:

1. Receiving EDI850 (source in)
2. BizTalk mapping and processing
3. Store into database table (target)
4. ERP processing
5. Others...

According to EDI 850 schema, we having Looing N1 elements in header section as below definition.

Looing N1 raw data might looks as below:

N1*BT* Accts Payable~
N3*P.O. Box 149257~
N4*Austin*TX*78714-4927~
N1*ST*Address IT~
N2*Address CO*Address IT~
N3*Address*15/Address, IT Address~
N4*Address*IA*76763*US*SP*TEST~
PER*BD**TE*515-242-3492*EM*Mail@domin.COM~

Here was the physical mapping for the N4 sector design between EDI element → Database field]:

# IF N101 IS BT
N401 --> BILL_TO_CITY_NAME
N402 --> BILL_TO_STATE
N403 --> BILL_TO_POST_CODE
N404 --> BILL_TO_CNTY_CODE
N405 --> By Pass(Fixed code: SP for this case)
N406 --> BILL_TO_LOCA_ID

# IF N1101 IS ST
N401 --> SHIP_TO_CITY_NAME
N402 --> SHIP_TO_STATE
N403 --> SHIP_TO_POST_CODE
N404 --> SHIP_TO_CNTY_CODE
N405 --> By Pass(Fixed code: SP for this case)
N406 --> SHIP_TO_LOCA_ID

So the N1 looping overall mapping diagram might looks as below:

Note that we have fixed value of N405:SP, so we decide not to pass through to database this moment.

We have doing some trick over the N1 looping:

1) Concatenate Functoid
  - Separated each element with in delimiter '，'
  - Separated whole N1 sector with in delimiter '；'

Functiod inputs as below:

2) Cumulative Concatenate Functoid
 - Input the string of concatenate from step-(1)
 - Output cumulative concatenate string to script 

Inside of script we write down below inline C#:

// source data combined two parts
// using code (N101: BT/ST) to decide the part
// the Idx: index sequence of location that create by the step-(1)
// return the value of each element (N101/N102...N406)
public string GetData (string SourceData, string Code, int Idx) {
      string Result = "";
      string[] a = SourceData.Split ('；');
      foreach (string word in a) {
          string[] b = word.Split ('，');
          if (b[0] == Code) {
              Result = b[Idx];
              break;
          }
      }
      return Result;
  }

As per scripting functoid we could setup corresponding “Code” and “Idx” input for getting the desired element and mapping to target database field.

The problem here we faced is according EDI definition that N405 and N406 pair should be pair of present or dismissed. But however sometimes we have problem that receiving below format:

# N4 without N405 & N406
N4*Address*IA*76763*US*~

The format of N4 entry cause that the target database field: SHIP_TO_LOCA_ID became “” (empty value) instead as null value. So we added:

3) Size Functoid
  - Input from the step-(2)
4) Greater Than Fuctoid
 - Input from the step-(3)

So we got final step:

5)  Value Mapping Fucntoid
-  If the Sizeof(step-(4)) > 0 then assign the value of step-(4) to the target database field
- Otherwise the target database remain null

Reference

• https://blog.sandro-pereira.com/2014/06/12/biztalk-mapper-working-with-nillable-values-xsiniltrue/
• https://docs.microsoft.com/en-us/biztalk/core/technical-reference/cumulative-concatenate-functoid
• https://stackoverflow.com/questions/60898340/concatenating-values-from-the-repeating-nodes-under-repeating-nodes-in-biztalk-m