I was recently given a challenge to integrate Banner (LINK) data and transform it to an EDI file, specifically a 4010 - 130 Student Educational Record / Transcript (LINK). Upon researching the Banner structure provided, I figured it would be a good community post for handling complex flat files which contain positional records and have cross record relationships with looping contained therein.
There are many different ways to handle this so I'm providing what I found to be most logical. I look forward to your feedback on approaches you may have taken in the past to accomplish similar scenarios and I'll try my best to reply to comments or questions as they are posted.
Lets get started:
Many Higher Education organizations use Ellucian Banner for their Student Information System.
Though the sample implementation is Ellucian Banner specific, the same concepts for the Flat File profile may be applied to any positional file which needs to maintain relationships between records - think 'hierarchy'. Also, though the end target system is EDI with specific segments, like everything in Boomi, the target destination can easily be swapped out for other systems (JSON, XML, DB etc.).
- Reference Guide Articles
- Scenarios on How to Use Flat File to EDI
- Common Errors
Reference Guide Articles
Here are some links to our Reference Guide, which you may find useful when using and configuring this scenario.
- Multiple Record Formats - Profile (LINK)
- Relationships between flat file record formats (LINK)
- FF Profile Elements (LINK)
- EDI Profile Identifier Instances (LINK)
Scenarios on How to Use Flat File to EDI
Scenario 1 - How do I configure a Flat File Profile which is positional AND contains hierarchies?
Below is a screen shot of of a sample document attached which was provided by Banner.
Items of note (from top to bottom):
- BLUE box: a repeating element with the first two characters "RP" as the identifier when OUTSIDE of the S1 hierarchy
- RED box: Starts with the S1 identifier and continues until the next S1 identifier
- YELLOW highlight box: contains information related to S1 parent. This is a mix of individual lines (S2 & SUN) as well as sub-hierarchies (C1, C2 & RP)
- LIGHT BLUE box: individual courses taken with additional information contained in the S2 line and optional RP line
- GREEN box: repeating element with T1 -> SB -> TS relationship
- ORANGE box: Identification items for the student's transcript (name, address, DOB etc.)
- When breaking down the individual lines, the file is POSITIONAL with specific start / end character locations which define the specific record components
- Understanding the FF relationship between records is key to correctly defining the FF record profile
Using the same color coding on the screen cap, the image below shows the FF Profile hierarchy configuration.
Overall profile options were setup for "File Type" = "Data Positioned" (others were left as default)
Data Elements were created, and configuration for "Detect This File Format By" was set to "Specified unique values"
Positional Start Column, Length & Identity Format configuration was set for each level and element:
For each of the fields identified, configuration was setup
Scenario 2 - How do I work with packed number formatting?
One of the fields within the source flat file contained 6 digits ("006000"), but in reality it represents a decimal value with the last three characters being the implied decimal location.
Part of the profile options allow you to specify the Data Format, in which you can say it's a Number type and an "Implied Decimal" value. This will auto-convert that field to 006.000 for the output map without having to do any additional math function (my first attempt was to divide by 1000).
Scenario 3 - Mapping the FF profile to EDI (4010-130 - Transcripts)
Though the Boomi Suggest will map many of the fields based on past customer maps, there maybe a few items in the EDI profile that need to be configured based on your needs.
For me, I needed to create two identifier loops for N1 (N101=AS and N101=AT) to accomplish the desired output and simplify my mapping. Visit this LINK if more information about the identifier instances is required.
Some of the target EDI loops also needed to be adjusted for the "Looping Option" to be set to "Occurrence" as opposed to the default "Unique" selection in order for the output results to next as expected.
Error: Data is not being presented as expected
Sometimes the source profile needs to be adjusted based on how the actual data is flowing through and where the record identifiers are placed. Make sure the data is represented in the profile the same way as the FF itself. You can drag / drop elements and records between levels in the profile. I would also suggest targeting an XML or JSON profile you manually create to test out the data (easier to see the results than in an EDI file).