Cash Book Import from your Bank with Debtor Matching  Topic is solved

Discussions relating to plugin development, and the Jiwa API.

Cash Book Import from your Bank with Debtor Matching  Topic is solved

Postby Ernst » Fri Apr 20, 2018 3:49 pm

Hi Folks,

Thought i would Share this bit of plugin, for those who thought MYOB and XERO had one up on us.

This one uploads a Westpac CSV Bank file, but could be adjusted for other bank layouts.

I'm sure the community will help develop it further, and post to this topic.

Upgradiing it to JIWA 7.01 would be nice.

Auto allocate of the paid amount to debtor invoices would be nice.

Enjoy.. Ernst :)
Attachments
Plugin Cashbook Bank Import and Debtor Match.xml
(37.27 KiB) Downloaded 69 times
User avatar
Ernst
Kohai
Kohai
 
Posts: 219
Joined: Tue Feb 19, 2008 3:43 pm
Topics Solved: 12

Re: Cash Book Import from your Bank with Debtor Matching

Postby Mike.Sheen » Sat Apr 21, 2018 5:17 pm

Hi Ernst,

I've taken the liberty of taking your plugin and adapting it to 07.01.xx with some changes.

Firstly, rather than importing many batches - my modified version requires the user to only import into the current cash book receipt - and they must have selected a bank account first. This makes sense to me, as if you've exported a CSV file from your bank you know which account it is.

Next, I only import transactions within the same month as the batch date. If the user hasn't changed the batch date from the login date, then I set the batch date to be the date of the oldest transaction in the CSV, then just import transactions in the same month.

I try to match the debtor on the Other Party Name by looking at previous batches - if that fails then I try to match on debtor name against Other Party Name - and if that fails I fall back to a debtor configured as the cash debtor in a new system setting.

Then I try to see if the transaction is already on the current batch (to protect against importing the same file into the same batch), and also if the transaction is on a previous batch for the same bank account (matching on date, amount, remit no, et cetera).

What this should mean is importing a CSV file which has transactions across months should be no issue - I just skip the ones outside the month of the batch. Then the user can create a second batch for another month, import the same file and only the transactions not imported previously will be imported.

No doubt some flaws exist in my version - but I've taken on board your idea of this being a community developed plugin, and any issues will be fixed and submitted back :)

Mike
Attachments
Plugin Cashbook Bank Import and Debtor Match.xml
(43.46 KiB) Downloaded 86 times
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2444
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 756

Re: Cash Book Import from your Bank with Debtor Matching

Postby Ernst » Sun Apr 22, 2018 8:44 am

That Sounds like a lot of great improvements already.. Thanks Mike.
User avatar
Ernst
Kohai
Kohai
 
Posts: 219
Joined: Tue Feb 19, 2008 3:43 pm
Topics Solved: 12

Re: Cash Book Import from your Bank with Debtor Matching

Postby Ernst » Fri Apr 27, 2018 3:10 pm

Update to Cash Book Receipt CSV function.

Had trouble when a blank remit no came through,Causing a save error
This will fix it. Have also removed ' from the search field to prevent SQL errors.

If csvFields(2) <> "" Then
CashBookRct.CashBookTransactions(newTransactionKey).RemitNo = replace(csvFields(2),"'","")
End If


Dim SQL As String = "Select top 1 accountno from CB_BatchTranLines " _
& " inner join CB_BatchTrans On CB_BatchTrans.CBBatchID = CB_BatchTranLines.CBBatchID " _
& " inner join db_main On db_main.DebtorID = CB_BatchTranLines.DebtorCreditorID " _
& " where CB_BatchTrans.LastSavedDateTime > getdate()-90 And db_main.AccountNo <> 'CASSAL' " _
& " And remitno = '" & replace(csvFields(2),"'","") & "' " _
& " order by CB_BatchTrans.LastSavedDateTime desc "
User avatar
Ernst
Kohai
Kohai
 
Posts: 219
Joined: Tue Feb 19, 2008 3:43 pm
Topics Solved: 12


Return to Technical and or Programming

Who is online

Users browsing this forum: No registered users and 17 guests