Importing General Journal Entries: Insert Only?

[Vergence]

I'm trying to set up a weekly integration through integration manager which will upload several general journal entries.
Example:
BatchID Acct Debit Credit
Tran1 123-456 100.00 0.00
Tran1 123-456 50.00 0.00
Tran1 123-457 0.00 150.00

Every time I integrate these they integrate-with warnings. The warning is that the transactions are not balanced.
If I understand correctly I need to have -one- transaction be something like this:
Journal BatchID Acct Debit Credit
100 Tran1 123-456 100.00 0.00
100 Tran1 123-456 0.00 100.00
[correct me here if I'm wrong]

In contrast to what actually happens which is:
Journal BatchID Acct Debit Credit
100 Tran1 123-456 100.00 0.00

[I'm learning this stuff pretty much on my own so bear with me]

The only option for the Destination 'General Journal' is 'Insert Only'. So I don't see how I use the Integration Manager AND ever have balanced transactions. Even if I import two transactions: one credit and one debit: I can't get them to have the same Journal Number (because it is auto-assigned) so how CAN I balance the transaction?

Thanks in advance for your help,

Ivan

 

[klewis10367]

Do it like this:

Journal Batch Account Amount
100 Test 123333 600.00
100 Test 652400 -600.00

In the Destination journal under the entries section use Debit Amount and rule Use Positive Source field (which would be the 600.00) and then for the credit amount use the rule Use negative source(-600.00). Also you want two Sources 1)Header Info ie Batch Id, Transaction Date, Reference or any of the fields on the General Journal Section. Use a seperate Source for the Entries portion, and then link them. For your header info you will want to probably group by either batch id or transaction date or both.

 

[PTina]

Each transaction (detail line) for the journal in your souce file should have the respective DR and CR amounts on the same row. For example:

100 Tran1 123-456 100.00 100.00

When you do the Desitnation Mapping in IM (General Journal --> Entries) you can then select the respective column to use for the fields Debit Amount and Credit Amount.

 

[klewis10367]

PTina, What if you are distributing the Journal Entry to 2 different accounts?

 

[PTina]

You are right klewis, thanks.

It should be more like this for is example:
Tran1 123-456 100.00 50.00
Tran1 123-457 0.00 150.00

 

[Vergence]

OK, so let me get this straight.

I can't pull all this information from 1 source. I'll need 2? One for the batch level information and a second one for each part of the transaction?
Example:
-Source1-
BatchID Trans Date Reference
Trans1 07/01/04 12345
Trans2 07/02/04 23456

-Source2-
BatchID Trans Date Acct Debit Credit
Trans1 07/01/04 123-456 100.00 0.00
Trans1 07/01/04 123-455 50.00 0.00
Trans1 07/01/04 123-654 0.00 150.00
Trans2 07/02/04 123-456 600.00 0.00
Trans2 07/02/04 123-654 0.00 600.00

If this is correct, I'm going to ask the question I'm sure I'll be asking as soon as I finish rebuilding my source files & queries: How do I link them together in Integration Manager? [I've never done it before].
Thanks again for your help!
Ivan

 

[klewis10367]

There is a query relationship right in the Intergration that you set up. Double click on it and the sources that you have will be shown. Link by batch id and date or what ever is going to get you the link you want.

PTina. I get what you are doing now, I have never tried it that way, I wonder if it is faster that way or the way I do it. Always looking for speed.

 

[jtvance]

Vergence - That is exactly the way we do it. But also, I hope of added value, you can have a description at the individual transaction level, too, which adds a ton of value for us. Much less pulling paper back up if you add good documentation on the entry.

For us, we have a single sheet in an Excel workbook with two named ranges (one for header and one for tran lines). In some cases (payroll journal entry), we only have one line in the header and 200 lines of detail. In other cases, we have two headers and four details (two journal entries with just two lines each).

I'd be happy to send the spreadsheet and provide any tips if you'd like.

 

[Vergence]

Ok, I've finished setting up my files and I tried to import them.. 1 record attempted, 1 failed to integrate.

So I look into it and the error is that the batch does not exist. The import is set to 'insert only' so I'm a little confused about the problem.

The second thing I'm confused about is why it said only one was attempted. I have three (test) transactions in one file and one batch record in the second file? It tried to integrate the batch file (only?) and failed.

Thanks for any advice anyone has.

 

[klewis10367]

On the General Journal entry there is an Option tab next to the field tab. Select the Missing Batch option and then choose the AddNew Batch Rule.

 

[klewis10367]

The reason it said only one attempted is because it failed on the first attempt and you probably have your error setting to 0.

 

[Vergence]

Thank you all so much! All I needed to do was change that Missing Batch option to AddNew and it worked perfectly. Thanks klewis, jtvance, & ptina!

[Thread Closed]