1220 format data for 1099 forms

[UomoDelGhiaccio]

Any suggestions on how to map data from a 1220 format data for 1099 forms

The data is in different style fixed width lines

T Record - Only one T Record in a data file
A Record - Can be multiple A Records, but only one followed by a series of B Records
B Records - Can be multiple B Records and represent individual pages, always followed by a C Record
C Record - Can be multiple C Records, but only one following a series of B Records
F Record - Only one F Record in a data file

Typical files will be constructed similar this, but there could be 2 million B Records in a file

T Record
A Record
B Record
B Record
B Record, etc
C Record
A Record
B Record
B Record
B Record, etc
C Record
A Record
B Record
B Record
B Record, etc
C Record
F Record

[Phil]

Check out Modify records from scripts in DataMapper - OL® Learn (objectiflune.com). I think it might help you get started the proper way.

[UomoDelGhiaccio]

Looks interesting and will probably get me started.

I downloaded the resources to my 2021.1 VM and there is an error.

[Phil]

Wow, looks like the resources attached to this article are incorrect… No one has reported it until now…

The reason for the error is that the script is attempting to store value 42 in the Number field of the Products table. However, that field is defined as a string, not as a number. So instead, the script should be storing the string "42" in that field:

record.tables.products[index].set({number:"42"});

And there is another error later, in the Create 3 copies of record step. The last line of that script should read:

record.setCopy(i, oneRec); 

I don’t know why JSON.stringify() was used instead…

Anyway, I will make sure to update the resource in the article.

Sorry for the trouble… but thanks for pointing the issue out!

[UomoDelGhiaccio]

With the example the data is delimited with pipes so I see how you would split into lines for selection based on each type of line.

With the 1220 format the data is fixed width with each line type having different columns lengths, so a split will not work as I see it.

Would I extract the entire line without splitting it and then do something like record.set({Year:oneLine.substring(1,5)},{Company:oneLine.substring(1,5)});

I get the first value, but not the second. (currently selecting the same substring, but eventually they would be different)

[Phil]

The best way to handle fixed length data is to use Regular Expressions.
Say, for instance, that the data looks like this:

T2020 12345 6789
A2020 123 ABCDEFGHIJKLMNOP 44444 
B2020 ZZZZZZZZZZZZZZZZZZZZZ CCCCC 00000000000

(I added spaces in between each field, simply for readability)

Then the following code would properly handle each type of line:

var lineType = data.extract(1,1,0,1,"");
var oneLine = data.extract(2,200,0,1,"")
var fields;

switch (lineType){
	case "T":
        // 3 fields, of length 4, 5 and 4
		fields = oneLine.match(/^(.{4})\s(.{5})\s(.{4})/);
		record.set({T1:fields[1],T2:fields[2],T3:fields[3]}); 
		break;
	case "A":
        // 4 fields, of length 4, 3, 16 and 5
		fields = oneLine.match(/^(.{4})\s(.{3})\s(.{16})\s(.{5})/);
		record.set({A1:fields[1],A2:fields[2],A3:fields[3],A4:fields[4]}); 
		break;
	case "B":
        // 4 fields, of length 4, 21, 5 and 11
		fields = oneLine.match(/^(.{4})\s(.{21})\s(.{5})\s(.{11})/);
		record.set({B1:fields[1],B2:fields[2],B3:fields[3],B4:fields[4]}); 
		break;
}

Each line type has a specific RegEx that matches the structure of its fields. The RegEx captures each field in a capturing group (delimited with parentheses) that specifies its fixed length. The \s elements are there only to remove the extra spaces I added to the data lines to separate each field.

You should be able to play around with this code and make it match your own data.

[UomoDelGhiaccio]

I’m running into an error Cannot read property “1” from null, not sure what I’m missing.

• There are 4 saved elements in the pattern
• There are 4 defined fields in the data model
• There are 4 fields in the record.set argument

If I comment out Case “A”

The other lineType’s populate

[UomoDelGhiaccio]

Okay, now I feel foolish. I was looking at two different data files, so the A line was not matching the file in the data mapper, but the Regex101 page data did.

This is where I’m at the moment.

I used the example from the blog post for Line Type B for the detail table.

I’m waiting on the official fixed width column positions, but the (.{number of characters}) should work regardless of the actual characters.

Each detail record would represent a print page so have to figure out that bit, but this is much cleaner than the nasty datamappers that I was trying before.

[Phil]

Well if it works in RegEx101, then it should work in the DataMapper. Maybe one of the “A” lines in your data does not contain spaces where you expect them (e.g. maybe there are tabs in there instead)?

To debug, simplify your Regex to only extract the first field:

fields=oneLine.match(/^(.{4})/);
record.set({A1:fields[1]});

Then if that works, add the second one:

fields=oneLine.match(/^(.{4}).{6}(.{9})/);
record.set({A1:fields[1],A2:fields[2]});

And so on with the next fields. Notice that I replaced the \s{6} with .{6}, just to be safe.

EDIT: just saw you had figured it out all by yourself, but I am still keeping my reply in the thread because it may help someone else figure out what steps to take when you have to debug this kind of issue.

[UomoDelGhiaccio]

Because I don’t have the actual fixed width column positions yet, I was freewheeling a bit. I got burned because I didn’t notice that I was looking at two different data files. You are right that one file had the spaces and the other one didn’t.

I agree that using something like (.{4}).{10}(.{8}).{19}(.{3}) will allow me to skip over the characters I don’t need to capture without risk. I only need portions of the data from their feed and it does not make sense to store it all.

This has made me a “Happy Camper” in time for the Holidays! Cheers!!!