Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Importing General Journal Entries: Insert Only?

Status
Not open for further replies.
Vergence

Vergence

Programmer
Jun 15, 2004
15
US
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
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
PTina, What if you are distributing the Journal Entry to 2 different accounts?
 
Upvote 0 Downvote
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


 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
The reason it said only one attempted is because it failed on the first attempt and you probably have your error setting to 0.
 
Upvote 0 Downvote
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]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

ACCT PDC
  • Locked
  • Question
Switched to DirectDeposit Payroll and now entries not carrying through to bank reconciliation
Replies
1
Views
294
ACCT PDC
ACCT PDC
texbetty
  • Locked
  • Question
USER ID on a Great Plains general journal entry
Replies
3
Views
205
VictoriaYudin
VictoriaYudin
Alex348
  • Locked
  • Question
Adjusting journal entries
Replies
2
Views
83
Alex348
Alex348
DHuggins
  • Locked
  • Question
Intercompany entries don't post all the way through
Replies
1
Views
125
VictoriaYudin
VictoriaYudin
firemedicandy
  • Locked
  • Question
GP payroll issues, too many entries for one person?
Replies
0
Views
64
firemedicandy
firemedicandy

Part and Inventory Search

Sponsor

Back
Top