Skip to content


Suggested GL Template format asked 8 years ago
I've seen a few demo videos and web postings about how to deal with the GL transactions import into GP with SmartConnect… sometimes I saw the amount in a single column, sometimes separate columns as DEBIT or CREDIT… I need to import thousands of journal transactions from a legacy accounting system into GP… There numbering sequence # starts from scratch at 1 every new FY… the amounts are coming in a single column with a + or – sign…
What's the best way to do it ?  How can I keep track of the original transaction ? combination of line # sequence and year ?
Thanks in advance for your tips.

Michelle replied 8 years ago

You can email me @ and I will be happy to help you. This shouldn’t be a difficult integration. You must supply both debit and credit in order for econnect to accept the entry. So in other words if the debit contains 500.00 credit must be 0. A formula can check those values but you also need to correctly group your transaction header to recognize individual journal entries.

Chris Dew replied 8 years ago


We have a built-in function that will take either the positive/negative source value so you can map the same data to the debit and credit fields. I would probably place the original number in the reference field or an Extender field if you want it separate.

Michelle is correct that you will need to get the grouping correct to ensure you create the correct number of Journals based on the date and number.

Chris replied 8 years ago

Hi Chris,
Thanks for your input… does that mean the amount will be vented directly in the proper DEBIT or CREDIT field depending on the sign ?
If so, this will work fine for Balance Sheet accounts, but we'll have an issue with the P&L accounts as they are inverted in the legacy accounting system … Meaning a negative amount is DEBIT and positive is CREDIT… really confusing.

We discussed the matter of the transactions today in a meeting and the issue is that there is too much garbage in the detailed transactions coming from the other system, we would end up creating so much rules for handling the exceptions… wouldn't make sense. We decided to go for a monthly consolidated summary for each account and put that into our GP system as a GL transaction for the net changes. Again, the BS accounts would be fine, but the P&L will have reversed signs to the logic…

Since the legacy accounts have a different numbering schema than our GP COA, we'll need to create a sort of translation table to map the external accounts. The difficulty is that the legacy system uses Cost Centers as a way to manage departements, whereas in GP we use the 3rd segement of GL to manage our departements. So account changes that are affecting Cost Centers would have to go in a  different GL account then regular accounts…

Quite a challenge…
What's your suggestion to handle this the best way… ? Obviously we need to create a different import file for BS and P&L accounts.. ?

Thanks for your help,

Michelle replied 8 years ago


Summary data usually suffices for legacy data. You will need calculated fields to handle your debit and credit values and econnect requires both a debit and credit value to be entered for each line.  In other words, if your cash account had a debit balance of 50,000 you would also need the credit value to be 0 for that record.  If you have an indicator in your source file for they type of account (ie Balance Sheet or P&L) you can build that into the same calculation and depending on the type of the account keep the sign or reverse the sign.  

Creating a different import may pose an issue if your system does not perform a close to retained earnings on a monthly basis.  If you were to import balance sheet accounts you would need a balancing entry to retained earnings for the P&L balances.  You would need the offsetting entry as well for a seperate P&L import.  

Michelle replied 8 years ago

As far as your translation table is concerned, I cannot make reccomendation without knowing how your file looks and the new account layout. replied 8 years ago

The legacy account format looks anywhere between a 4 to 6 digit  number and the cost center 3 digits… As an example :
Legacy               GP
1020               10000-2500-000-000
1109                10140-2500-000-000
110999            10140-2500-000-000

All the acounts up to 2999 are of BS type
Everything 3000 and up are P&L type account.

The GP main account is in the 1st segment and our deptartment# is the 3rd segment.
our BS accounts go from 10000 to 39999 and the P&L from 40000 to 99999

Only P&L accounts have transactions spread accross Cost centers (Departements).

I know that SmartConnect can import data for translation table, but I wasn't able to figure out how to do it so far…

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