Special Cases

Special Cases

How should we format Bank Account Numbers?

We pull the bank account number from Xero/Quickbooks.  There is only one field available to us to get this data (unfortunately the additional banking fields in the Xero/Quickbooks contact record are not exposed to the API interface, so we can not access them).

The problem is that banks around the world have different formats and components, including Bank, Branch/Transit number, account and even suffix.  uCollect often needs to present these in pre-defined formats or in separate fields.  To allow us to do this we strip out all non-alphanumberic characters and then pull the required values out of the stripped text.  Therefore you can safely include dashes, slashes or spaces in your bank account field in Xero/Quickbooks – but they will be ignored when we evaluate the bank account number.

Here are some examples of how we obtain the bank account data from the Xero/Quickbooks Bank Account field:

New Zealand

BBbbbbAAAAAAAsss BB=Bank (2 characters); bbbb=Branch (4); A=Account (7); sss=Suffix(3)
The following are examples of valid entries: 12-3456-1234567-00, 12 3456 1234567 000; 123456-123456700; 1234561234567000
Anything included after the 16th character (of the stripped value) will be ignored

Australia

BBBBBBAAAAAAAAAAAAAAA B=BSB number (6 characters, we insert the dash between 3 and 4); A = Account (15)
The following are examples of valid entries: 123-456-1234567890, 123 456 1234567890; 123456-1234567890; 1234561234567890
Anything included after the 16th character  (of the stripped value) will be ignored.  If you include anything after the last Account character and before the 17th character  (of the stripped value)  it will cause problems.

EuroZone

BBBBBBBBAAAAAAAAAAAA B=BIC code (8 characters); A=Account (everything from character 9 onwards)
The following are examples of valid entries: NL48RABO-0694892032; NL48RABO 0694892032; NL48RABO0694892032
Anything included after the 9th character  (of the stripped value) will be treated as the account number.  If you include anything after the last real Account character it will cause problems.[/vc_toggle][vc_toggle title=”How do I change lots of Bank Account Numbers in Xero/Quickbooks?” open=”false”]Because we pull the bank account number from Xero/Quickbooks and have certain formatting rules (see “How should we format Bank Account Numbers” above) you may need to change the way that you store your bank account numbers in Xero/Quickbooks.  You can do this one-at-a-time if you want (we even give you a handy “Edit this contact in Xero/Quickbooks” link from the Edit Contact screen!).

But what if you have dozens or hundred of contacts where you need to add or edit the bank data?  First, make sure that you have the necessary data on hand.  Then, follow these easy steps:

1. Log into the Xero/Quickbooks ledger
2. Go to Contacts, Customers,
3. Click Export.
4. Find the downloaded CSV file and open it in Excel or your spreadsheet program
5. Find the column titled “BankAccountNumber” (column AD; you may want to Freeze Panes to keep the contact name and the column headers visible as you move around, and even hide columns B:AC).
6. Edit the bank account number to conform to our formatting requirement.  Please remember that any other bank account fields can not be read by uCollect.
7. Save the CSV file.
8. Back in Xero/Quickbooks, Contacts, click Import.
9. Browse to find the file you were just editing (may still be in Downloads folder).
10. Click on the option for “Empty fields should delete existing information”
11. Click Import.

This will overwrite the contacts list with the new data.  If you need help getting your bank account data formatted correctly please get in touch with us (complete our contact form or click the live support link below).  We may be able to help you.