Fixed-Length File Mapping Questions
Hello,
I have the following X12 EDI JSON that I'm mapping into a fixed-length file format.
Where I'm stuck is that I'm trying to access the Quantity from the flow data, but I don't know how to get to that information and I've had some difficulty finding documentation on what I'm trying to accomplish.
Secondary question about "children". Based on what I've been able to gather, "children" are independent looping structures within the rules. I have two sets of loops that I need to include. The first is to loop through the address information to get the Ship To, Ship From, and possibly Warehouse information. The second is to loop through the line items of the transaction.
For the most part, the address loop is working with the exception that it is creating blank lines for two of the sections.
Lastly, in the data, you'll see that there are line terminators( '\r ) from the original EDI and I'm not sure how to get them out.
Any ideas for any of these issues would be appreciated.
Comments
Jack Harris can you provide your JSON input and file definition JSON?
Here's the input
{"1020": {
"Authorization Information Qualifier": "00",
"Authorization Information": "",
"Security Information Qualifier": "00",
"Security Information": "",
"Interchange ID Qualifier": "01",
"Interchange Sender ID": "006127708",
"CodeList Summary": "12",
"Interchange Receiver ID": "7045875587",
"Interchange Date": "231211",
"Interchange Time": "1115",
"Interchange Control Standards Identifier": "U",
"Interchange Control Version Number": "00501",
"Interchange Control Number": "000012533",
"Acknowledgment Requested": "0",
"Usage Indicator": "P",
"Component Element Separator": ":",
"Functional Identifier Code": "OW",
"Application Sender's Code": "4144979345",
"Application Receiver's Code": "7045875587",
"Date the EDI was created": "20231211",
"Time": "1115",
"Group Control Number": "2985",
"Responsible Agency Code": "X",
"Version / Release / Industry Identifier Code": "005010",
"Transaction Set Identifier Code": "940",
"Transaction Set Control Number": "3316",
"Order Status Code": "R",
"Depositor Order Number": "0007213778",
"Master Reference (Link) Number": "0085673654",
"Transaction Type Code": "TS",
"N1": [
{
"Entity Identifier Code": "SF",
"Name(N102)": "Bay Valley Foods, LLC - Charlotte",
"Identification Code Qualifier": "54",
"Identification Code": "1615"
},
{
"Entity Identifier Code": "ST",
"Name(N102)": "Bay Valley Foods LLC",
"Identification Code Qualifier": "9",
"Identification Code": "0000001610",
"N3": [
{
"Address Information": "Kings Mountain SEDC Southeast DC",
"Address Information(N302)": "120 Woodlake Pkwy"
}
],
"City Name": "Kings Mountain",
"State or Province Code": "NC",
"Postal Code": "28086-9225",
"Country Code(N404)": "US"
}
],
"N9": [
{
"Reference Identification Qualifier": "PIN",
"Reference Identification": "DEFAULT"
}
],
"G62": [
{
"Date Qualifier": "10",
"Date": "20231212"
},
{
"Date Qualifier": "02",
"Date": "20231213"
}
],
"Shipment Method of Payment Code": "PP",
"Transportation Method/Type Code": "M",
"LX": [
{
"Assigned Number": "10",
"W01": [
{
"Quantity": "42",
"Unit or Basis for Measurement Code": "CA",
"UPC Case Code": "000044616444",
"Product/Service ID Qualifier(W0106)": "VN",
"Product/Service ID(W0107)": "09196870756",
"Product/Service Condition Code": "01",
"G69": [
{
"Free-form Description": "4/1 REF SAUERKRAUT -SCHWZ"
}
],
"N9": [
{
"Reference Identification Qualifier": "LI",
"Reference Identification": "10"
}
],
"W20": [
{
"Weight": "36",
"Weight Qualifier": "G",
"Weight Unit Code": "L",
"Unit Weight": "1512"
}
],
"LX": [
{
"Assigned Number": "20"
}
]
},
{
"Quantity": "325",
"Unit or Basis for Measurement Code": "CA",
"UPC Case Code": "003430000135",
"Product/Service ID Qualifier(W0106)": "VN",
"Product/Service ID(W0107)": "71818464328",
"Product/Service Condition Code": "01",
"G69": [
{
"Free-form Description": "12/2 REF DELI STYLE SAUERKRAUT-DELEX"
}
],
"N9": [
{
"Reference Identification Qualifier": "LI",
"Reference Identification": "20"
}
],
"W20": [
{
"Weight": "26",
"Weight Qualifier": "G",
"Weight Unit Code": "L",
"Unit Weight": "8450"
}
],
"LX": [
{
"Assigned Number": "30"
}
]
},
{
"Quantity": "126",
"Unit or Basis for Measurement Code": "CA",
"UPC Case Code": "000044655002",
"Product/Service ID Qualifier(W0106)": "VN",
"Product/Service ID(W0107)": "29655010756",
"Product/Service Condition Code": "01",
"G69": [
{
"Free-form Description": "1/5 REF KSH DLL SPR 375 425 -SCHWZ"
}
],
"N9": [
{
"Reference Identification Qualifier": "LI",
"Reference Identification": "30"
}
],
"W20": [
{
"Weight": "48.27",
"Weight Qualifier": "G",
"Weight Unit Code": "L",
"Unit Weight": "6082.02"
}
],
"LX": [
{
"Assigned Number": "40"
}
]
},
{
"Quantity": "60",
"Unit or Basis for Measurement Code": "CA",
"UPC Case Code": "000044627002",
"Product/Service ID Qualifier(W0106)": "VN",
"Product/Service ID(W0107)": "29257010756",
"Product/Service Condition Code": "01",
"G69": [
{
"Free-form Description": "1/2 REF KSH DLL SPR 145 175 -SCHWZ"
}
],
"N9": [
{
"Reference Identification Qualifier": "LI",
"Reference Identification": "40"
}
],
"W20": [
{
"Weight": "19.974",
"Weight Qualifier": "G",
"Weight Unit Code": "L",
"Unit Weight": "1198.44"
}
],
"LX": [
{
"Assigned Number": "50"
}
]
},
{
"Quantity": "40",
"Unit or Basis for Measurement Code": "CA",
"UPC Case Code": "000044627230",
"Product/Service ID Qualifier(W0106)": "VN",
"Product/Service ID(W0107)": "29257230756",
"Product/Service Condition Code": "01",
"G69": [
{
"Free-form Description": "1/2 RF KSHHDS3/16 1100 1300 -SCHWZ"
}
],
"N9": [
{
"Reference Identification Qualifier": "LI",
"Reference Identification": "50"
}
],
"W20": [
{
"Weight": "19.948",
"Weight Qualifier": "G",
"Weight Unit Code": "L",
"Unit Weight": "797.92"
}
],
"LX": [
{
"Assigned Number": "60"
}
]
},
{
"Quantity": "28",
"Unit or Basis for Measurement Code": "CA",
"UPC Case Code": "000044654580",
"Product/Service ID Qualifier(W0106)": "VN",
"Product/Service ID(W0107)": "29654580756",
"Product/Service Condition Code": "01",
"G69": [
{
"Free-form Description": "1/5 RF KSH LWSL3/16 700 800 -SCHWZ"
}
],
"N9": [
{
"Reference Identification Qualifier": "LI",
"Reference Identification": "60"
}
],
"W20": [
{
"Weight": "48.27",
"Weight Qualifier": "G",
"Weight Unit Code": "L",
"Unit Weight": "1351.56"
}
],
"LX": [
{
"Assigned Number": "70"
}
]
},
{
"Quantity": "42",
"Unit or Basis for Measurement Code": "CA",
"UPC Case Code": "000044655210",
"Product/Service ID Qualifier(W0106)": "VN",
"Product/Service ID(W0107)": "29655210756",
"Product/Service Condition Code": "01",
"G69": [
{
"Free-form Description": "1/5 REF WH KSHR DILLS 60 70 -SCHWZ"
}
],
"N9": [
{
"Reference Identification Qualifier": "LI",
"Reference Identification": "70"
}
],
"W20": [
{
"Weight": "48.33",
"Weight Qualifier": "G",
"Weight Unit Code": "L",
"Unit Weight": "2029.86"
}
],
"LX": [
{
"Assigned Number": "80"
}
]
},
{
"Quantity": "28",
"Unit or Basis for Measurement Code": "CA",
"UPC Case Code": "000044658230",
"Product/Service ID Qualifier(W0106)": "VN",
"Product/Service ID(W0107)": "29658230756",
"Product/Service Condition Code": "01",
"G69": [
{
"Free-form Description": "1/5 REF KSH HMB DLL SLC3/16 -SCHWZ"
}
],
"N9": [
{
"Reference Identification Qualifier": "LI",
"Reference Identification": "80"
}
],
"W20": [
{
"Weight": "48.275",
"Weight Qualifier": "G",
"Weight Unit Code": "L",
"Unit Weight": "1351.7"
}
],
"LX": [
{
"Assigned Number": "90"
}
]
},
{
"Quantity": "126",
"Unit or Basis for Measurement Code": "CA",
"UPC Case Code": "000044658280",
"Product/Service ID Qualifier(W0106)": "VN",
"Product/Service ID(W0107)": "29658280756",
"Product/Service Condition Code": "01",
"G69": [
{
"Free-form Description": "1/5 RFKSHHMBSL1/4CC1400 -SCHWZ"
}
],
"N9": [
{
"Reference Identification Qualifier": "LI",
"Reference Identification": "90"
}
],
"W20": [
{
"Weight": "48.315",
"Weight Qualifier": "G",
"Weight Unit Code": "L",
"Unit Weight": "6087.69"
}
],
"LX": [
{
"Assigned Number": "100"
}
]
},
{
"Quantity": "120",
"Unit or Basis for Measurement Code": "CA",
"UPC Case Code": "000044623040",
"Product/Service ID Qualifier(W0106)": "VN",
"Product/Service ID(W0107)": "29253040756",
"Product/Service Condition Code": "01",
"G69": [
{
"Free-form Description": "1/2 RF SPC HMB SL1/4CC 675 -SCHWZ"
}
],
"N9": [
{
"Reference Identification Qualifier": "LI",
"Reference Identification": "100"
}
],
"W20": [
{
"Weight": "19.979",
"Weight Qualifier": "G",
"Weight Unit Code": "L",
"Unit Weight": "2397.48"
}
]
}
]
},
{},
{},
{},
{},
{},
{},
{},
{},
{}
],
"Quantity": "937",
"Weight": "31258.67",
"Unit or Basis for Measurement Code": "LB",
"Number of Included Segments": "62",
"SE Transaction Set Control Number": "3316",
"Number of Transaction Sets Included": "1",
"GE Group Control Number": "2985",
"Number of Included Functional Groups": "1",
"IEA Interchange Control Number": "000012533"
}
}
Here's the output
Here's what I currently have for the definition
{"_id": "658c6f251a35905369379249",
"lastModified": "2024-01-03T13:13:04.907Z",
"name": "Geodis 940",
"sandbox": true,
"description": "Geodis 940",
"version": "1",
"format": "fixed",
"fixed": {
"rowSuffix": "",
"rowDelimiter": "\n",
"paddingChar": " "
},
"rules": [
{
"name": "File Header",
"required": true,
"maxOccurrence": 2,
"elements": [
{
"name": "PARTNER QUALIFIER",
"length": 8,
"value": "PARTNER"
},
{
"name": "Partner",
"length": 10,
"value": "BVE01"
},
{
"name": "ROUTE QUALIFIER",
"length": 9,
"value": "\nROUTE"
},
{
"name": "ROUTE",
"length": 10,
"value": "BVE01"
},
{
"name": "COMPANY QUALIFIER",
"length": 9,
"value": "\nCOMPANY"
},
{
"name": "COMPANY",
"length": 2,
"value": "W1"
},
{
"name": "SET QUALIFIER",
"length": 9,
"value": "\nSET"
},
{
"name": "SET",
"length": 3,
"value": "940"
},
{
"name": "BEGIN",
"length": 9,
"value": "\nBEGIN"
}
],
"children": [
{
"required": true,
"maxOccurrence": 100,
"relativeDataPath": "1020",
"elements": [
{
"name": "RECORDID",
"length": 20,
"value": "I940_1020_004010"
},
{
"name": "RECORDTYPE",
"length": 4,
"value": "1020"
},
{
"name": "Order Status Code",
"length": 1,
"value": "{{[Order Status Code]}}"
},
{
"name": "Depositor Order Number",
"length": 22,
"value": "{{{[Depositor Order Number]}}}"
}
],
"children": [
{
"required": false,
"maxOccurrence": 3,
"relativeDataPath": "N1",
"elements": [
{
"name": "Record ID",
"length": 20,
"value": "I940_1040_004010"
},
{
"name": "Record Type",
"length": 4,
"value": "1040"
},
{
"name": "N101",
"length": 4,
"value": "{{{[Entity Identifier Code]}}}"
},
{
"name": "Name(N102)",
"length": 60,
"value": "{{{[Name(N102)]}}}"
}
]
},
{
"required": false,
"maxOccurrence": 3,
"relativeDataPath": "N1",
"elements": [
{
"name": "Record ID",
"length": 20,
"value": "I940_1060_004010"
},
{
"name": "Record Type",
"length": 4,
"value": "1060"
},
{
"name": "Address1",
"length": 55,
"value": "{{{[Address Information(N302)]}}}"
}
]
},
{
"required": false,
"maxOccurrence": 3,
"relativeDataPath": "N1",
"elements": [
{
"name": "RecordId",
"length": 20,
"value": "I940_1070_004010"
},
{
"name": "RecordType",
"length": 4,
"value": "1070"
},
{
"name": "City",
"length": 30,
"value": "{{{[City Name]}}}"
},
{
"name": "State",
"length": 2,
"value": "{{{[State or Province Code]}}}"
},
{
"name": "Zip",
"length": 15,
"value": "{{{[Postal Code]}}}"
},
{
"name": "Country",
"length": 10,
"value": "{{{[Country Code(N404)]}}}"
}
]
},
{
"required": false,
"maxOccurrence": 3,
"relativeDataPath": "G62",
"elements": [
{
"name": "RecordId",
"length": 20,
"value": "I940_1110_004010"
},
{
"name": "RecordType",
"length": 4,
"value": "1110"
},
{
"name": "Qualifier",
"length": 2,
"value": "{{{[Date Qualifier]}}}"
},
{
"name": "Date",
"length": 10,
"value": "{{{[Date]}}}"
}
]
},
{
"required": false,
"maxOccurrence": 3,
"relativeDataPath": "LX.W01",
"elements": [
{
"name": "RecordId",
"length": 20,
"value": "I940_2020_004010"
},
{
"name": "RecordType",
"length": 4,
"value": "2020"
},
{
"name": "Quantity",
"length": 17,
"value": "{{{[Quantity]}}}"
},
{
"name": "Sku",
"length": 2,
"value": "{{{[Unit or Basis for Measurement Code]}}}"
},
{
"name": "UPC",
"length": 12,
"value": "{{{[UPC Case Code]}}}"
},
{
"name": "ItmQual",
"length": 2,
"value": "VN"
}
]
}
]
},
{
"name": "File Trailer Record",
"required": true,
"maxOccurrence": 1,
"elements": [
{
"name": "E",
"length": 3,
"value": "End"
},
{
"name": "CODE CLIENT(End)",
"length": 3,
"value": "{{{[CODE CLIENT(End)]}}}"
},
{
"name": "ENTITY(End)",
"length": 4,
"value": "{{{[ENTITY(End)]}}}"
},
{
"name": "FILE NUMBER(End)",
"length": 16,
"value": "{{{[FILE NUMBER(End)]}}}"
}
]
}
]
}
]
}
I figured out the last two issues.
Jack Harris so you're still trying to figure out how to access the Quantity field? On the other 2 that you figured out, it could be helpful for other users to say what you did.
That is correct, I'm trying to figure out how to access the fields in the Quantity area and loop through it because it's the lines of the transaction to be added.
You're correct I should do that.
The '\r was being added to the original EDI parsing as the rowSuffix. Originally I had that misconfigured. When I changed it to have "rowSuffix":"'\r" the single quotes and extra lines went away.
Jack Harris can you type out your desired output using that sample input? The file definition rules you have don't look right and I'm not sure how to direct you without knowing what we would need to change.
This is what it's supposed to look like with most of the data as I didn't want to write out all 200 required lines manually.
Jack Harris I don't think the format you want is possible with the particular input format you have. For example, you have 3 definitions pointing to the N1 segment and it's why you end up with empty lines like this:
What you're really wanting is a segment just for the ship to and another segment for the ship to, but since this is an array, there isn't a way to put N1 3 times and then say only where identifier code is SF. If the ship to doesn't need to be dynamic, then you could hardcode a new element definition and then use the N1 for ship to. If it does need to be dynamic, then you either should add a new segment just for ship to or add address information to the ship from object. Could look like this:
On the quantity piece, you can't use a relative path that is nested like LX.W01 so you would need to first specify the LX and then have a child definition within it to be to W01. Additionally, you would want to get rid of all these empty LX objects.
Here is the definition rule made. I made up assumptions for some lengths.
{"_id": "6595ad5b538ba6069637b0ee",
"lastModified": "2024-01-03T18:55:51.094Z",
"name": "Geodis 940",
"sandbox": false,
"description": "Geodis 940",
"version": "1",
"format": "fixed",
"fixed": {
"rowSuffix": "",
"rowDelimiter": "\n",
"paddingChar": " "
},
"rules": [
{
"name": "File Header",
"required": true,
"maxOccurrence": 2,
"elements": [
{
"name": "PARTNER QUALIFIER",
"length": 8,
"value": "PARTNER"
},
{
"name": "Partner",
"length": 10,
"value": "BVE01"
},
{
"name": "ROUTE QUALIFIER",
"length": 9,
"value": "\nROUTE"
},
{
"name": "ROUTE",
"length": 10,
"value": "BVE01"
},
{
"name": "COMPANY QUALIFIER",
"length": 9,
"value": "\nCOMPANY"
},
{
"name": "COMPANY",
"length": 2,
"value": "W1"
},
{
"name": "SET QUALIFIER",
"length": 9,
"value": "\nSET"
},
{
"name": "SET",
"length": 3,
"value": "940"
},
{
"name": "BEGIN",
"length": 9,
"value": "\nBEGIN"
}
],
"children": [
{
"required": true,
"maxOccurrence": 100,
"relativeDataPath": "1020",
"elements": [
{
"name": "RECORDID",
"length": 20,
"value": "I940_1020_004010"
},
{
"name": "RECORDTYPE",
"length": 4,
"value": "1020"
},
{
"name": "Order Status Code",
"length": 1,
"value": "{{[Order Status Code]}}"
},
{
"name": "Depositor Order Number",
"length": 22,
"value": "{{{[Depositor Order Number]}}}"
}
],
"children": [
{
"required": false,
"maxOccurrence": 3,
"relativeDataPath": "N1",
"elements": [
{
"name": "Record ID",
"length": 20,
"value": "I940_1040_004010"
},
{
"name": "Record Type",
"length": 4,
"value": "1040"
},
{
"name": "N101",
"length": 4,
"value": "{{{[Entity Identifier Code]}}}"
},
{
"name": "Name(N102)",
"length": 60,
"value": "{{{[Name(N102)]}}}"
}
],
"children": [
{
"required": false,
"maxOccurrence": 3,
"relativeDataPath": "N3",
"elements": [
{
"name": "Record ID",
"length": 20,
"value": "I940_1060_004010"
},
{
"name": "Record Type",
"length": 4,
"value": "1060"
},
{
"name": "Address1",
"length": 55,
"value": "{{{[Address Information(N302)]}}}"
}
]
},
{
"required": false,
"maxOccurrence": 3,
"elements": [
{
"name": "RecordId",
"length": 20,
"value": "I940_1070_004010"
},
{
"name": "RecordType",
"length": 4,
"value": "1070"
},
{
"name": "City",
"length": 30,
"value": "{{{[City Name]}}}"
},
{
"name": "State",
"length": 2,
"value": "{{{[State or Province Code]}}}"
},
{
"name": "Zip",
"length": 15,
"value": "{{{[Postal Code]}}}"
},
{
"name": "Country",
"length": 10,
"value": "{{{[Country Code(N404)]}}}"
}
]
}
]
},
{
"required": false,
"maxOccurrence": 3,
"relativeDataPath": "G62",
"elements": [
{
"name": "RecordId",
"length": 20,
"value": "I940_1110_004010"
},
{
"name": "RecordType",
"length": 4,
"value": "1110"
},
{
"name": "Qualifier",
"length": 2,
"value": "{{{[Date Qualifier]}}}"
},
{
"name": "Date",
"length": 10,
"value": "{{{[Date]}}}"
}
]
},
{
"required": false,
"maxOccurrence": 1000,
"relativeDataPath": "LX",
"elements": [
{
"name": "LX",
"length": 20,
"value": "LX"
}
],
"children": [
{
"required": false,
"maxOccurrence": 1000,
"relativeDataPath": "W01",
"elements": [
{
"name": "RecordId",
"length": 20,
"value": "I940_2020_004010"
},
{
"name": "RecordType",
"length": 4,
"value": "2020"
},
{
"name": "Quantity",
"length": 17,
"value": "{{{[Quantity]}}}"
},
{
"name": "Sku",
"length": 2,
"value": "{{{[Unit or Basis for Measurement Code]}}}"
},
{
"name": "UPC",
"length": 12,
"value": "{{{[UPC Case Code]}}}"
},
{
"name": "ItmQual",
"length": 2,
"value": "VN"
},
{
"name": "prodID",
"length": 12,
"value": "{{{[Product/Service ID(W0107)]}}}"
}
],
"children": [
{
"required": false,
"maxOccurrence": 1000,
"relativeDataPath": "G69",
"elements": [
{
"name": "RecordId",
"length": 20,
"value": "I940_2020_004010"
},
{
"name": "RecordType",
"length": 4,
"value": "2020"
},
{
"name": "description",
"length": 40,
"value": "{{{[Free-form Description]}}}"
}
]
}
]
}
]
}
],
"closeRule": {
"name": "File Trailer Record",
"required": true,
"maxOccurrence": 1,
"elements": [
{
"name": "RecordId",
"length": 20,
"value": "I940_2020_004010"
},
{
"name": "RecordType",
"length": 4,
"value": "2020"
},
{
"name": "Quantity",
"length": 17,
"value": "{{{[Quantity]}}}"
},
{
"name": "Weight",
"length": 10,
"value": "{{{[Weight]}}}"
},
{
"name": "Sku",
"length": 2,
"value": "{{{[Unit or Basis for Measurement Code]}}}"
}
]
}
}
],
"closeRule": {
"name": "File Trailer Record",
"required": true,
"maxOccurrence": 1,
"elements": [
{
"name": "E",
"length": 3,
"value": "End"
},
{
"name": "CODE CLIENT(End)",
"length": 3,
"value": "{{{[CODE CLIENT(End)]}}}"
},
{
"name": "ENTITY(End)",
"length": 4,
"value": "{{{[ENTITY(End)]}}}"
},
{
"name": "FILE NUMBER(End)",
"length": 16,
"value": "{{{[FILE NUMBER(End)]}}}"
}
]
}
}
]
}
That worked perfectly!
Do you know how I can prevent the Parsed Output from putting in the "page_of_records" tag?
Jack Harris I'm not sure where you are seeing that. Can you screenshot what you're looking at.
Jack Harris pages of records, records, and rows are essential pieces to the way Celigo works. So while you may see that in preview, input for AFE editors, and some other places, it won't be there after the file is actually built. When we grab data from a source application, we chunk the data into records and each chunk of records is a page. So if we get 100 MB of data from the source, we would make a max of 20 pages since a page of data has a limit of 5 MB. You can specify how many records are on each page of data by going to your export and manipulating it.
There will only be one record.
It's manipulating an X12 file with only 1 transaction each time.
Jack Harris I'm not following what your issue is here. Like I said, this is how data passes through Celigo.
Please sign in to leave a comment.