Importing Customer Payments

Quite often payments arrive documented as a csv list with just columns containing date, some sort of text and an amount. To import this into MYOB using the MYOB Import Assistant you would need to add columns for deposit account, customer and invoice.

It is easier with TransPost because you can set the deposit account as a default in the mapping and it will identify the customer from the invoice.

If each payment applies to a single invoice, all you need to do is add a column for the invoice number, which, if you are lucky, is buried in that text. For example suppose we have this:

Date, Some Text, Amount
3/4/2016,"Deposit installment inv 00000007" 300.00
17/4/2016, Deposit against invoice 00000039, 50.00
17/4/2016, Deposit LLM inv# #000002,120.00
20/4/2016, FLD inv# 00000003 and 00000008, 150.00

  Add a column for the invoice number e.g.

Date, Some Text, Amount, Date, Some Text, Amount, Inv#
3/4/2016, Deposit installment inv 00000007, 300.00, 00000007
17/4/2016, Deposit against invoice 00000039, 50.00 ,00000039
17/4/2016, Deposit LLM inv# 000002,120.00 ,00000002
20/4/2016, FootLoose Dance studio, 150.00, 00000003,

Now create a TransPost mapping:

MYOB Field Input Field Default
ReceiptDate Date  
DepositAccount   1-2300
Memo Some Text  
TotalAmount Amount  
InvoiceNumber Inv#  

Note that the DepositAccount is set using a default so you do not need a column for it.

When you use this to import it will look up the customer based on the invoice and allocate the total to it.

Suppose that the last line (FootLoose Dance studio) actually pays off invoice 00000003 on which say $75.61 is owing and the balance of the payment should be applied to invoice 00000006.

To deal with this you need an amount allocated column to specify how much of the payment to apply to the invoice. Then insert a line for the second invoice. It would only need the invoice number unless there were yet more invoices to allocate the payment to.

Where the allocate column is empty TransPost will apply the whole of what is left of the payment.

Date, Some Text, Amount, allocated Date, Some Text, Amount, Inv#
3/4/2016, Deposit installment inv 00000007, 300.00, 00000007
17/4/2016, Deposit against invoice 00000039, 50.00 ,00000039
17/4/2016, Deposit LLM inv# 000002,120.00 ,00000002
20/4/2016, FootLoose Dance studio, 150.00, 00000003, $75.61
,,,00000006

Now add amount applied to the mapping. In this version $75.61 of the last payment will be applied to invoice 00000003 and then remaining amount ($74.39) will be applied to invoice 00000006

MYOB Field Input Field Default
ReceiptDate Date  
DepositAccount   1-2300
Memo Some Text  
TotalAmount Amount  
InvoiceNumber Inv#  
AmountApplied allocated  

 

This mapping may be re-used whenever you need. You can make a copy and change DepositAccount to import similarly for a different account.