Skip to content
+1-888-319-3663

COMMUNITY FORUM

Creating complex GL account string from source data

Sherry Whitten asked 5 years ago
We are integrating general ledger entries into Dynamics GP from an AS400 source file.  Our GL account number in GP is 5 segments – XX-XXX-XX-XXXX-XX.  The AS400 doesn’t have our GL account numbers anywhere, but we can compile the final GL account number from data in the AS400 source file.  For example, let’s look at segment 1 – location.  The following AS400 values translate to the first GP segment:
AM = 01
BC = 02
DE = 03
FT = 04

ZA = 99
 
Each of the other four segments has a similar translation from AS400 to Dynamics GP GL account segment.  For example, the following full AS400 strings could be translated to the full GP account numbers:
AS400:
ZA-8973-9MM-ABCD-78
GL Account: 
99-023-66-7603-22
AS400:
FT-8975-9MM-ABCD-78
GL Account: 
04-023-66-7603-22
There are obviously thousands of possible combinations that need to be translated from AS400 data to GP GL account string.
I am trying to decide the best way to transform the AS400 source file data into the full GL account string.  What would you recommend?  Thanks in advance.
Answers
Patrick Roth Staff answered 5 years ago
That’s going to be a tough issue with only one good solution that I can see.
Now you could write calculation in a big calculation:
FirstSeg = _AS400String.Left(2)
GPSegment1 = “”
select case FirstSeg
  case “AM”
     GPSegment1 = “01”
  case “BC”
     GPSegment1 = “02”
etc
end select
now do that in a calculation for all of the fields.  And at the bottom combine them into the output string.
This would work but the script would be very long and maintenance somewhat more work if you ever had to fix/add/delete anything.
The other thing you could do is create a SQL table (or one table per segment).
In the table, you would have the Segment number (if you just make one table) and then the “From” and “To” fields.
Now you would create a calculation using vb/C#. In it, you connect to the SQL data and run a query to find the “To” value using the restriction on the “from”.
Do that for each segment.  and in the end, put the results into the return string.
 
 
 
Sherry Whitten replied 5 years ago

Patrick – Thanks for your prompt reply. The calculation option just doesn’t seem feasible to me; writing it would be awful, and maintaining it would be a nightmare.

The best idea I came up with is to use the SQL table approach. I think I want to have just one table with From and To columns – the full account string. That means only one call out to SQL to do the translation when the map runs. Otherwise, I have to have one call for each segment. I also plan to write a SC map to allow the user to integrate into the SQL table. They will just have to maintain a spreadsheet and update it when segments are added, then use the map to integrate the new list. Thanks again. Just wanted to be sure I wasn’t missing something.


If you would like to submit an answer or comment, please sign in to the eOne portal.