SmartZIP: Bulk bank validation quick start

Our SmartZIP Bulk tool allows you to export all (or a selection) of your bank relationships sort codes and account numbers. These can be validated and then the results imported back into Raiser's Edge. To accomplish this there are two one-off tasks to complete, making the export and import's in Raiser's Edge.

These tasks use standard features of Raiser's Edge which most users will have access to although we do suggest you usually involve or pass this task to your Raiser's Edge administrator just due to their familiarity with the product.

Creating the Query and Export

Your first task is to export the bank relationship information from Raiser's Edge to Excel using a QUERY and an EXPORT:

Create the query

Go to Query > New Query
Select the query type 'Constituent' and click 'OK'
Select the records to be cleaned. As an example you may choose the criteria as below:
Save your query as 'SmartZIP: Bulk bank clean' and then close it

Create the export

Go to Export > New Query
Select the export type 'Constituent' with export format 'Excel 97-2000 (XLS)' and click 'Create now'
Select the records to be cleaned by clicking 'Include...' > Selected Records and selecting the 'SmartZIP: Bulk bank clean' query
On the output step select the below fields

Export fields
When prompted select:
Then double click on the 'Account number' and select 'Not masked'
Save the export as 'SmartZIP: Bank bulk export'

Running the Export

When the user uses the above export they will:

Go to Export and open the 'SmartZIP: Bank bulk export' export
Go to File > Export
Select the filename to use and 'Save' (note the file location as they then need to open the file in Excel)

Cleaning the data

Open the file in Excel
To perform the bank data clean, ensure you have set a PCA/Loqate key in the Excel add-in
Load your exported bank file which should have headers:
Click the 'Clean Accounts' button
When the process completes review the results
You must then save the file in 'txt' format. Select File > Save As and then select 'Save as type' > 'Text (Tab delimited) (*.txt) before saving the file

Creating the Import

Following a bulk SmartZIP process you will have a .txt file ready to be imported back into Raiser's Edge. To do this import please create a import as below:

Go to Admin > Import
Select 'Constituent Bank' and click 'New'
On the General tab:

Select 'Update existing records'
Select your .txt file which will be in the same folder as the exported XLS file from the above export process usually

Note: You may have to change the file types when selecting the file to show all otherwise it may not appear

The rest should be left at default values but for reference it should look as below:

Click 'Next' and change the Field Separators to 'tab', leave the other options at their defaults:
Fields should be mapped as below:
On the final tab we suggest you select to create an Exception file but the options are really down to yourselves.
Then save the Export as 'SmartBANK Bulk Import'
Finally click 'Update Now' to import the updated information back into Raiser's Edge

Notes

You may wish to review the file prior to import to correct any issues that might need manual intervention but you can also do this after the data is back in Raiser's Edge through use of the 'Bank Account notes' field which has the overall status.
You can use an attribute instead of Bank Account Notes to record the validation result.

In this case your export would have the fields:

Constituent Import ID
Bank Import Id
Bank Sort Code
Bank Account Number
[Attribute] Description (Attribute must have type text)
[Attribute] Date
[Attribute] Import ID

You would also need to change your import to use these details