RBS Bankline import Hail Mary (1 Viewer)

Hi all,

Before I try and construct something myself and possibly reinvent the wheel, I thought I ask here in case someone has already created something to do this.

The charity I volunteer for has changed banks to RBS Bankline. Previously we were with Lloyds Banking Group.

LBG for all its faults has a nice feature whereby one can add multiple payments to a virtual list and then authorise them in one foul swoop.

RBS forces you to authorise each payment individually if you enter them manually, as I would do with LBG. However speaking to Bankline support, one can emulate this by importing what they call an Adhoc Bulk Payment List. This obviously has to be in a certain format and they do not supply a template.

The best they can do is supply import specification, which I have attached, just for interest.

Has anyone created an import DB/Template for RBS Bankline at all please.?

I have asked the charity, but do not hold out much hope to be honest.

TIA

mr.gasman, good for you.
i am sure you can do it.
the file format is only csv and all fields and examples are on the later part of the pdf.
goodluck.

arnelgp said:

mr.gasman, good for you.
i am sure you can do it.
the file format is only csv and all fields and examples are on the later part of the pdf.
goodluck.

Click to expand...

Morning arne,

Yes, I know I can do it,albeit slowly, but do not want to create something only to be told 'If you had asked, I have something that does that'

Hence the hail mary.

I know I will only use a few fields and TBH I am thinking an Excel worksheet would be an easier option unless I want to create a full blown system for payments.

The charity even have their own online Financial Management System, which last time I looked, I did not even consider fit for purpose, so I doubt they would have thought of this.

On the plus side it would be something to keep me occupied.

Take a deep breath. This is not as hard as it looks. It will probably take about two hours.

The reality of creating the export file is simple, you just create a query that selects the correct fields and selects nulls for the fields you are not supplying.

The hard part is understanding the rules for each type. I took a three minute look at the file spec and it is complicated because they are trying to be efficient in their definition of 6 export types so they made the spec into a matrix. Just focus on the record type you are creating. Print the spec out because it will be easier to keep your place as you refer back and forth between the definitions of what the codes mean (M= mandatory, o = optional, etc).

If you have the ability to convert pdf to Word, you can modify the spec to eliminate 5 of the 6 columns so it will be easier to focus on the fields you need. You can add your own column next to the fields you need to supply and put in tablename.columnname so you can then just go build the query once you have gone through the whole list. If you have to stick with the pdf, use a yellow marker and just draw a vertical line down the page so your eye won't stray from the column you need and write the field names from the spec next to your field names either in Excel or on lined paper.

This is similar to an EFT (Electronic File Transfer) transaction definition that we use in the US. In order to facilitate data transfer of common transactions such as medical billing, electronic funds transfers, and orders, companies agreed to a spec. It is published and everyone follows the same rules. The kicker of course is that every company has its own source system so there is no common a--> b translation since a is completely variable. Given that, there are tools that help with formatting and essentially, they give you a simpler, more normalized input format and you extract the data from your system and their tool translates your table to the multi-format line transactions.

Thank you Pat,

I spent part of today creating a table to match that spec, despite only probably needing around 6 fields. The first example Domestic Payment is fine for my needs, but you never know what will be changed. Even at the moment, some fields are reserved for future use.

I created a table for payee accounts as well and was well on the way to creating a small simple system to create the import file. Nothing elaborate as it was only for my personal use, to make my life easier.

However I have discovered that head office have not given me the facility for uploading payments, so it was all for nought.:banghead: