Creating complex GL account string from source data
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:
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.
Now you could write calculation in a big calculation:
FirstSeg = _AS400String.Left(2)
GPSegment1 = “”
select case FirstSeg
GPSegment1 = “01”
GPSegment1 = “02”
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.
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.