Can anyone provide some links to examples of importing bank transactions into GP using eConnect? There is no documentation or examples in the eConnect programmers guide. I have other integrations completed but there must be something unique for bank transactions.
Great to hear Richard! :) Always happy to help!
Once again Isaac saves the day. So between inserting the lines first and setting the trx option in the distributions I now have a working integration. Thanks Isaac and Mike!!!
Hi Richard,
You need to pass the Option in both the Distribution and the Header records as it is a required field in the help file.
<taBRBankTransactionDist>
|
Element name |
Data type |
Length |
Required |
Default |
Description |
|---|---|---|---|---|---|
|
Option |
i4 |
2 |
Y |
Not applicable |
Transaction option: 1=Enter transaction; 2=Enter receipt; 3=Void transaction; 4=Void Receipt |
|
ACTNUMST |
string |
75 |
Y |
Not applicable |
Account number string; must include separator characters |
|
DEBITAMT |
number |
21 |
N |
0 |
Debit amount |
|
CRDTAMNT |
number |
21 |
N |
0 |
Credit amount |
|
DistRef |
string |
30 |
N |
<blank> |
Distribution reference |
|
RequesterTrx |
i4 |
2 |
N |
0 |
Requester transaction element is not used by eConnect in this node at this time |
|
USRDEFND1 |
string |
50 |
N |
<blank> |
User-defined field—developer use only |
|
USRDEFND2 |
string |
50 |
N |
<blank> |
User-defined field—developer use only |
|
USRDEFND3 |
string |
50 |
N |
<blank> |
User-defined field—developer use only |
|
USRDEFND4 |
string |
8000 |
N |
<blank> |
User-defined field—developer use only |
|
USRDEFND5 |
string |
8000 |
N |
<blank> |
User-defined field—developer use only |
Thanks,
Isaac Olson
Microsoft Support
OK, I have made some progress. I am now down to the eConnect error message:
Error Number = 7625 Stored Procedure= taBRBankTransactionDist Error Description = The Option parameter is an invalid value - 1=Enter Trx; 2=Enter Receipt; 3=Void Transaction; 4=Void Receipt
As you can see from my code trxOption is set to type short and a value of 1. Is this some kind of bug? How do I get past this?
Here is my code rearranged. It still will not import the distributions. These are bank checks so I all I want to import is the debit side.
try
{
brBankTrxLine = new taBRBankTransactionDist_ItemsTaBRBankTransactionDist();
/* Fill in values for the Bank Transaction Distribution */
brBankTrxLine.ACTNUMST = ACTNUMST;
brBankTrxLine.DEBITAMT = debitAmt;
brBankTrxLine.CRDTAMNT = crdtAmnt;
brBankTrxLine.DistRef = dist_ref;
bRBankTrxLines.Add(brBankTrxLine);
taBRBankTransactionDist_ItemsTaBRBankTransactionDist[] brTransLines = bRBankTrxLines.ToArray();
/* Fill in values for the Bank Transaction Header */
bRBankTrxHeader.Option = trxOption;
bRBankTrxHeader.CHEKBKID = "BRI PR";
bRBankTrxHeader.CMTrxType = Model.BankCheck;
bRBankTrxHeader.CMTrxNum = chekNmbr;
bRBankTrxHeader.paidtorcvdfrom = eeNmbr;
bRBankTrxHeader.DSCRIPTN = jrnl_ref;
bRBankTrxHeader.DistRef = record_Group_By;
bRBankTrxHeader.TRXDATE = trxDate.ToShortDateString();
bRBankTrxHeader.TRXAMNT = Model.Bank_TransactionAmount;
MessageBox.Show("Debit Amt : " + debitAmt.ToString() + " Credit Amt : " + crdtAmnt.ToString() + " Trx Amt : " + Model.Bank_TransactionAmount.ToString());
BRBankTransactionType BankCheck= new BRBankTransactionType();
BankCheck.taBRBankTransactionDist_Items = brTransLines;
BankCheck.taBRBankTransactionHeader = bRBankTrxHeader;
BRBankTransactionType[] bankTransactionType = { BankCheck };
eConnectType eConnect = new eConnectType();
eConnect.BRBankTransactionType = bankTransactionType;
MemoryStream memStream = new MemoryStream();
XmlSerializer serializer = new XmlSerializer(eConnect.GetType());
serializer.Serialize(memStream, eConnect);
memStream.Position = 0;
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.Load(memStream);
memStream.Close();
eConn eConn = new eConn(Controller.Instance.Model.GPServer, Controller.Instance.Model.GPUserID, Controller.Instance.Model.GPPassword);
string response = "";
returnValue = eConn.InsertTransaction(ref response, xmlDocument.OuterXml);
message = response;
}
catch (Exception ex)
{
if (Model.StackTraceWanted)
{
string eMsg = "GSE.AccudImport 0008 ERROR - Updating Bank Import date: " + ex.Message;
eMsg += "\n" + ex.StackTrace;
MessageBox.Show(eMsg);
}
MessageBox.Show("An unexpected error occured while updating bank import date: " + ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
Try calling the distribution before the header node
The trick I believe is finding the right sequence of events. I am currently going line by line comparing an IV transfer import I have to this import. The eConnect names are completely different making things a bit difficult.That is why I was hoping someone could point me to a working example.I think I am almost there.
Hi Richard,
I wouldn't really be able to troubleshoot your C# code, but there are 2 things that you could try for troubleshooting.
1. You can check the Event Viewer (Applications and Services Logs > eConnect) for error code, error message, stored procedure and XML excerpt, and that may get you a lead.
2. Try running a SQL Profiler Trace of your executing your code, so that you can see for sure that it is hitting the database. You might potentially uncover an error in here too. Even though the procs are encrypted, you can still see the initial parameters that were sent in and could see if any hard SQL errors come out of it. Using the parameters from my link below on the trace will make sure that you can see these details.
How to use SQL Profiler to create an SQL trace in Microsoft SQL Server
support.microsoft.com/.../912281
I hope this helps!
Isaac Olson
Microsoft Support
Thanks Isaac, this part I have. My issue is with creating the integration using C#.I run my code and get no errors messages but I also get nothing imported into the checkbook.
I am trying to compare other working integrations that I have but there must be something unique for bank transactions.
public void CreateBankChecks()
{
int rc = 0;
string trxType;
string chekNmbr;
string eeNmbr;
string jrnl_ref;
string dist_ref;
DateTime trxDate;
decimal trxAmnt;
string trxIdent;
string ACTNUMST;
decimal debitAmt;
decimal crdtAmnt;
string record_Group_By;
bool returnValue = false;
const string cmdHeader = "SELECT[Trx_Identifier]" +
",CONVERT(CHAR(25), [Trx_Date], 121) AS Trx_Date" +
",[RecordID_DistributionRef]" +
",[ChkNbr]" +
",[EENbr]" +
",[Debit_Amount]" +
",[Credit_Amount]" +
",[Full_GL_Account] " +
"FROM[dbo].[ACCUDTDT] " +
"WHERE Full_GL_Account <> '1007' AND Full_GL_Account IN(SELECT ACTNUMST FROM[dbo].[GL00105]) " +
"AND[RecordID_DistributionRef] IN('PR Live Check') " +
"ORDER BY[ChkNbr]";
using (SqlConnection conn = DataAccess.Connection(Controller.Instance.Model.GPCompanyDatabase))
{
using (SqlCommand commandHdr = new SqlCommand(cmdHeader))
{
commandHdr.Connection = conn;
using (SqlDataReader dataReaderHdr = commandHdr.ExecuteReader())
{
/* Create a new DataTable */
DataTable dtBankTrxHdr = new DataTable("BankTrxHdr");
/* Load DataReader into the DataTable */
dtBankTrxHdr.Load(dataReaderHdr);
foreach (DataRow row in dtBankTrxHdr.Rows)
{
taBRBankTransactionHeader bRBankTrxHeader = new taBRBankTransactionHeader();
List<taBRBankTransactionDist_ItemsTaBRBankTransactionDist> bRBankTrxLines = new List<taBRBankTransactionDist_ItemsTaBRBankTransactionDist>();
taBRBankTransactionDist_ItemsTaBRBankTransactionDist brBankTrxLine = new taBRBankTransactionDist_ItemsTaBRBankTransactionDist();
rc += 1;
trxIdent = row["Trx_Identifier"].ToString();
trxDate = Convert.ToDateTime(row["Trx_Date"].ToString());
record_Group_By = "Live Check";
chekNmbr = row["ChkNbr"].ToString().Trim(); /* Check number is only assigned a valkue for checks */
eeNmbr = row["EENbr"].ToString().Trim(); /* Employee number is only assigned a valkue for checks */
ACTNUMST = row["Full_GL_Account"].ToString().Trim();
debitAmt = Convert.ToDecimal(row["Debit_Amount"].ToString());
crdtAmnt = Convert.ToDecimal(row["Credit_Amount"].ToString());
lblRecordIdentifier.Text = row["RecordID_DistributionRef"].ToString();
Application.DoEvents();
trxIdent = "Chk # : " + Model.Chk_Nbr + " - " + Model.Ee_Nbr;
jrnl_ref = "PR Live Check";
dist_ref = "Chk # : " + Model.Chk_Nbr + " - " + Model.Ee_Nbr;
Model.Dist_header = "PR Live Check - ";
Utilities.CheckBalances(Model.BankCheck, Model.Trx_Identifier); /* Check that the debit balance equals the credit balance */
Utilities.GetNetToCash(Model.BankCheck, Model.Trx_Identifier); /* Get the net to cash amount */
try
{
List<BRBankTransactionType> brTransBatch = new List<BRBankTransactionType>();
BRBankTransactionType brBankTrans = new BRBankTransactionType();
brBankTrans = new BRBankTransactionType();
bRBankTrxHeader = new taBRBankTransactionHeader();
bRBankTrxLines = new List<taBRBankTransactionDist_ItemsTaBRBankTransactionDist>();
/* Fill in values for the Bank Transaction Header */
bRBankTrxHeader.Option = 1;
bRBankTrxHeader.CHEKBKID = "BRI PR";
bRBankTrxHeader.CMTrxType = Model.Bank401K;
bRBankTrxHeader.CMTrxNum = chekNmbr;
bRBankTrxHeader.paidtorcvdfrom = eeNmbr;
bRBankTrxHeader.DSCRIPTN = jrnl_ref;
bRBankTrxHeader.DistRef = dist_ref;
bRBankTrxHeader.TRXDATE = trxDate.ToShortDateString();
bRBankTrxHeader.TRXAMNT = Model.Bank_TransactionAmount;
/* Fill in values for the Bank Transaction Distribution */
brBankTrxLine.ACTNUMST = ACTNUMST;
brBankTrxLine.DEBITAMT = debitAmt;
brBankTrxLine.CRDTAMNT = crdtAmnt;
brBankTrxLine.DistRef = record_Group_By;
eConnectType eConnect = new eConnectType();
MemoryStream memStream = new MemoryStream();
XmlSerializer serializer = new XmlSerializer(eConnect.GetType());
serializer.Serialize(memStream, eConnect);
memStream.Position = 0;
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.Load(memStream);
memStream.Close();
eConn eConn = new eConn(Controller.Instance.Model.GPServer, Controller.Instance.Model.GPUserID, Controller.Instance.Model.GPPassword);
string response = "";
returnValue = eConn.InsertTransaction(ref response, xmlDocument.OuterXml);
//Message = response;
}
catch (Exception ex)
{
if (Model.StackTraceWanted)
{
string eMsg = "AccudataImport 0008 ERROR - Updating Bank Import date: " + ex.Message;
eMsg += "\n" + ex.StackTrace;
MessageBox.Show(eMsg);
}
MessageBox.Show("An unexpected error occured while updating bank import date: " + ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
}
}
Hi Richard,
I put one together quick that you can use as an example. In this one you would be doing a $1000 Check and I added both a debit and credit so you can see both in the sample in case you are using both. I mainly just filled in the required fields. You could add to this using the other optional fields in the Programmers guide for taBRBankTransactionDist and taBRBankTransactionHeader.
<?xml version="1.0" encoding="UTF-8"?>
<eConnect>
<BRBankTransactionType>
<taBRBankTransactionDist>
<Option>1</Option>
<ACTNUMST>000-1200-00</ACTNUMST>
<DEBITAMT>1500</DEBITAMT>
</taBRBankTransactionDist>
<taBRBankTransactionDist>
<Option>1</Option>
<ACTNUMST>000-1300-00</ACTNUMST>
<CRDTAMNT>500</CRDTAMNT>
</taBRBankTransactionDist>
<taBRBankTransactionHeader>
<Option>1</Option>
<CMTrxType>3</CMTrxType>
<TRXDATE>04/15/2027</TRXDATE>
<CHEKBKID>UPTOWN TRUST</CHEKBKID>
<CMTrxNum>CM000001</CMTrxNum>
<DSCRIPTN>TEST1</DSCRIPTN>
<TRXAMNT>1000</TRXAMNT>
</taBRBankTransactionHeader>
</BRBankTransactionType>
</eConnect>
My checkbook uses account 000-1100-00 so the $1000 Credit at the top is automatically created (we don't need to pass that one in), but I passed in the $500 Credit and the $1500 Debit in this example to the offsetting accounts.
I hope this helps!
Thanks,
Isaac Olson
Microsoft Support
Share
Report
75,848
All responses (
Answers (
