Combining records from a file - LawsonGuru.com

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3291
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1369
Roger French	1315
mark.cook	1244

Bob Canham

Veteran Member

Posts: 217

11/24/2015 5:34 PM

I'm modifying a flow we use to import 401k benefit changes from our vendor. We are changing to allow for after-tax Roth contributions on our 401k plan. The vendor is going to send us 2 lines per person on a file, one for pre-tax 401k and one for after-tax roth. These then get combined into a single benefit plan on BN32. I've been writing the flow to accumulate variables while the employee number stays the same, however, I'm having a hard time figuring out how to handle the final record. The iteration loop ends before it can realize that the next record would be the end of the file and there isn't a trailer record in the file for us. I've set it up to route back into the loop and out again before the end tag using a branch and boolean variable, but wondered if anyone had any ideas for a better way to handle.

Thanks

jamesraceson

Veteran Member

Posts: 52

11/24/2015 5:58 PM

Bob,
One option you could use would be to read the entire file, set a variable to represent the total rows, set another to increment by one each time you parse a line in the flow to combine them, then use that variable in a branch loop to check to see if you have reached the end or not (total variable). You might have to play around with adding one or subtracting one depending on if you start your counter at zero or one and if you increment before or after the read, but you get the idea.

Kyle Jorgensen

Veteran Member

Posts: 122

11/24/2015 6:39 PM

Whenever you're in a loop that acts upon data from a saved, previous record; you'll need to add additional logic after the loop to properly handle the last record. I'd avoid branching back into nodes inside the loop. If possible, try to do as much logic as possible in a javascript function that you declare before the loop (in an 'Assign' node). That way you can call it from within the loop and for the last record outside of the loop.

Bob Canham

Veteran Member

Posts: 217

12/1/2015 4:01 PM

James, how would you determine the line count from reading the file? I don't see that there is a variable for that.

jamesraceson

Veteran Member

Posts: 52

12/1/2015 4:58 PM

Bob,
You would set your own. For example, create one "rowCount" as an Integer in your Start node. Read the file through the Data Iterator node and within the loop, increment your variable by 1 (through an Assign node). When you are done, you would have your row count (+- 1 depending if you started "rowCount" at 1 or 0). Now "rowCount" can be used throughout the rest of the flow for any loops you want to set up.

Bob Canham

Veteran Member

Posts: 217

12/1/2015 5:04 PM

oh, I see. iterate the file twice. I thought you had a trick to pull the row count without iterating.

jamesraceson

Veteran Member

Posts: 52

12/1/2015 5:12 PM

Bob,
Yes, correct. No, there is no trick that I know of for that. Another option, depending on your Javascript skills, is to read all the file elements into an multidimensional Array (for example in an Assign node). This way you could really manipulate how and when data gets combined and you would also have a row count (length of Array). This way you would only have to read the file once as well (if this is a time concern for very large files).

Tim Cochrane

Veteran Member

Posts: 154

12/1/2015 6:57 PM

if I understand what you're trying to do...
1. spin thru a file and grab both records for employee
2. when employee number changes write the results from Step 1 to BN32...sound correct??

Kyle gave you the answer, no need to complicate the process. I've done similar loops, and what you need to add is a 2'nd BN32 AGS outside the query to handle the last record...that's all.

I created a simple flow to illustrate...but couldn't figure out how to include it.

Bob Canham

Veteran Member

Posts: 217

12/1/2015 8:35 PM

Thanks for the feedback. Unfortunately, my inside loop processing is more like 35 nodes, not just a BN32 AGS. That's why I was hoping to not have to create a second set. It does a bunch of variable manipulation, checking for multiple employment records to update both, it doesn't specify if it is a stop or change, so it has to check and do different AGS calls depending, and it has to query some additional info for an email to the employee confirming the change.

Tim Cochrane

Veteran Member

Posts: 154

12/1/2015 8:52 PM

Correct, I understand you're doing some data manipulation...my example was high level overview. So basically leave your flow the way it is...continue to manipulate the data and call BN32 within the query. Connect the end of the query to another BN32 AGS. The last employee record read will be processed within the loop, but since no other records are found, the loop will exit. The additional BN32 (outside the query) will write this last record. It sounds like your switching logic within the query works fine, since you said the only record missing is the last employee read, so adding the extra BN32 should do it for you.