Journal Entries: Wrong total when importing from a sql database 1

[pacifistk]

I set the JRNLCR = JRNLDR .01 in the header.
I set DEBITTOT and CREDITTOT = .01 in the batch

I set the trans amt in 1 detail to .01 and -.01 in the other.

Accpac shows each entry as 0, and the totals as .02 for credit and debit.

Also, my details are not being assigned entry numbers properly.

If you can be of any help , I REALLY appreciate it, this is driving my crazy.

 

[jayconverse]

Don't set the transaction amounts in the header, it's done automatically.

Jay Converse
IT Director
Systemlink, Inc.

 

[pacifistk]

Thanks !

So I only need to set the transamt and the scruamt ?

Kevin Clough
Collages.net

 

[jayconverse]

Yes, and if it's single currency, you only need TRANSAMT.

Jay Converse
IT Director
Systemlink, Inc.

 

[pacifistk]

I'm still stuck, the only place I set a value for the credit and debit is in transamt, yet when I import it still goes to zero.

Would you mind looking at my test data?

 

[jayconverse]

If it's CSV, just post it on the forum.

Jay Converse
IT Director
Systemlink, Inc.

 

[pacifistk]

Here is my data going from my source table:

Detail,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
BATCHNBR,JOURNALID,TRANSNBR,AUDTDATE,AUDTTIME,AUDTUSER,AUDTORG,ACCTID,COMPANYID,TRANSAMT,TRANSQTY,SCURNDEC,SCURNAMT,HCURNCODE,RATETYPE,SCURNCODE,RATEDATE,CONVRATE,RATESPREAD,DATEMTCHCD,RATEOPER,TRANSDESC,TRANSREF,TRANSDATE,SRCELDGR,SRCETYPE,VALUES,DESCOMP,ROUTE,NewImport,ImporttoACCPACDate
49,1,1,20060606,340350,admin ,coldat,6010,ktest ,0.01,0,2,0,USD,SP,USD,20060606,1,0,3,1,Test Credit , ,20060606,GL,JE,0, ,0,1,6/13/2006 10:42
49,1,2,200606,340650,admin ,coldat,9999,ktest ,-0.01,0,2,0,usd,sp,usd,20060606,1,0,3,1,Test Debit , ,20060606,GL,JE,0, ,0,1,6/13/2006 10:42
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Header,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
BATCHID,BTCHENTRY,AUDTDATE,AUDTTIME,AUDTUSER,AUDTORG,SRCELEDGER,SRCETYPE,FSCSYR,FSCSPERD,SWEDIT,SWREVERSE,JRNLDESC,JRNLDR,JRNLCR,JRNLQTY,DATEENTRY,DRILSCRTY,DRILLDWNLK,REVYR,REVPERD,ERRBATCH,ERRENTRY,ORIGCOMP,DETAILCNT,newimport,ImporttoACCPACDate,JournalHeaderImportError,,,
49,1,20050211,452589,ADMIN ,COLDAT ,GL,JE,2002,10,0,0,JE Test,0,0,1,20041031,0,0,0,0,0,0,0,2,1,6/13/2006 10:42,,,,

Batch,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
BATCHID,AUDTDATE,AUDTTIME,AUDTUSER,AUDTORG,ACTIVESW,BTCHDESC,SRCELEDGR,DATECREAT,DATEEDIT,BATCHTYPE,BATCHSTAT,POSTNGSEQ,DEBITTOT,CREDITTOT,QTYTOTAL,ENTRYCNT,NEXTENTRY,ERRORCNT,ORIGSTATUS,SWPRINTED,SWICT,newimport,ImporttoACCPACDate,ERROR,,,,,,
49,20060211,340179,ADMIN ,COLDAT,0,JE Test ,GL,20061103,20061108,3,4,40,0.01,0.01,0,1,1,0,0,0,0,1,6/13/2006 9:15,,,,,,,


Here is what ACCPAC imports into its own data base:
Detail,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
BATCHNBR,JOURNALID,TRANSNBR,AUDTDATE,AUDTTIME,AUDTUSER,AUDTORG,ACCTID,COMPANYID,TRANSAMT,TRANSQTY,SCURNDEC,SCURNAMT,HCURNCODE,RATETYPE,SCURNCODE,RATEDATE,CONVRATE,RATESPREAD,DATEMTCHCD,RATEOPER,TRANSDESC,TRANSREF,TRANSDATE,SRCELDGR,SRCETYPE,VALUES,DESCOMP,ROUTE,EXPR1
3521,1,20,20060613,14513518,ADMIN ,COLDAT,9999,ktest ,0,0,2,0,USD,SP,USD,20060613,1,0,3,1,Test Debit , ,20060613,GL,JE,0, ,0,3521
3521,1,40,20060613,14513518,ADMIN ,COLDAT,6010,ktest ,0,0,2,0,USD,SP,USD,20060613,1,0,3,1,Test Credit , ,20060613,GL,JE,0, ,0,3521

Header,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
BATCHID,BTCHENTRY,AUDTDATE,AUDTTIME,AUDTUSER,AUDTORG,SRCELEDGER,SRCETYPE,FSCSYR,FSCSPERD,SWEDIT,SWREVERSE,JRNLDESC,JRNLDR,JRNLCR,JRNLQTY,DATEENTRY,DRILSRCTY,DRILLDWNLK,DRILAPP,REVYR,REVPERD,ERRBATCH,ERRENTRY,ORIGCOMP,DETAILCNT,EXPR1,,,
3521,0,20060613,14513520,ADMIN ,COLDAT,GL,JE,2006,6,0,0,JE Test ,0,0,1,20060613,0,0, ,0,0,0,0, ,2,3521,,,

Batch,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
BATCHID,AUDTDATE,AUDTTIME,AUDTUSER,AUDTORG,ACTIVESW,BTCHDESC,SRCELEDGR,DATECREAT,DATEEDIT,BATCHTYPE,BATCHSTAT,POSTNGSEQ,DEBITTOT,CREDITTOT,QTYTOTAL,ENTRYCNT,NEXTENTRY,ERRORCNT,ORIGSTATUS,SWPRINTED,SWICT,,,,,,,,
3521,20060613,14513556,ADMIN ,COLDAT,1,JE Test ,GL,20060613,20060613,4,1,0,0,0,1,1,0,0, ,0,0,,,,,,,,



Thanks Again!!!!!
Kevin Clough
Collages.net

 

[jayconverse]

Ah-ha, your SCURNAMT is 0, which is overriding the TRANSAMT. You need to take out all columns related to source currency and rates. In the end for the detail line, you only need:

BATCHNBR,JOURNALID, TRANSNBR,ACCTID,TRANSAMT,TRANSDESC,TRANSREF




Jay Converse
IT Director
Systemlink, Inc.

 

[pacifistk]

Thanks that was it!

Now I have 1 more problem, one of my requirements is to be able to post journal entries with a modified date, every date field I try to modify (Batch->created/edited) or (header->dateentry) or (detail->transdate) I get an exception. Which date fields can be modified ?

Thanks,
Kevin Clough

 

[jayconverse]

What import format are you using? What's the exception?

Jay Converse
IT Director
Systemlink, Inc.

 

[pacifistk]

I'm importing using the xapi from a c#.net program that I'm working on.

 

[jayconverse]

Oh, I thought you were importing using the regular UI.

In VB and VBA, the XAPI converts standard date fields, i.e., mm/dd/yy, into the internal YYYYMMDD format. I can only assume your C# code isn't a regular date.

So what's your exception?

Jay Converse
IT Director
Systemlink, Inc.

 

[pacifistk]

EXCEPTION: System.Runtime.InteropServices.COMException (0x80004005): View call failed
at ACCPACXAPILib.xapiFieldClass.PutWithoutVerification(Object newVal)
at ACCPAC_Imports.ACCPAC_Main.AddJournalEntry() in n:\ash\accpac_imports\accp
ac_main.cs:line 1156
at ACCPAC_Imports.ACCPAC_Main.Main(String[] args) in n:\ash\accpac_imports\ac
cpac_main.cs:line 41


Code at line 1156:
JournalBatchFields.Item("DATEEDIT").PutWithoutVerification (dtBatch.Rows["DATEEDIT"]);

JournalBatchFields.Item("DATECREAT").PutWithoutVerification (dtBatch.Rows["DATECREAT"]);

The dates are as follows:
edit: 1/1/1950 12:00:00 AM
create: 1/1/1900 12:00:00 AM

Thanks so much,
Kevin Clough

 

[jayconverse]

I'll bet you those array elements are text. Maybe you need something like Cdate(dtBatch.Rows["DATEEDIT"])

I don't know what the C# equivalent of Cdate is...

Jay Converse
IT Director
Systemlink, Inc.

 

[pacifistk]

I used System.Convert.ToDateTime()

Still get the same exception.

The table that the program gets the data from uses a smalldate to store the value...

Can you think of anything else to try?

 

[jayconverse]

As a rule, I never use full date-times, only mm/dd/yy. The problem also could be that 1900 and 1950 aren't in your fiscal calendar. You need dates like 6/1/2006.

Jay Converse
IT Director
Systemlink, Inc.