Cashbook Import Definitions

• • Cashbook Transactions are the expected transactions you reconcile against the actual transactions on the bank statement

  • The most common source of the expected transactions is your source system which contains a record of all transactions you have processed

  • Cashbook Import Definitions allow you to write queries to import this data

  • Select Setup>Cashbook Import Definitions to do this

Cashbook Import Settings

Batch Type: The Batch type the imported transactions must belong to. Read more on batch types here

Database Type: SQL or Oracle

Connection String: Database and Server to get the data from

SQL: Data Source=Server;UID=User;pwd=Password;Initial Catalog=Database;

Oracle: User ID=User;Data Source=Server;Persist Security Info=True;Password=Password

SQL: Query to return and map the data - see the links on the left for guidelines

Key Field Name: The column in the query bank recon gets the Last key Value from. It must much a column in your query exactly

Data Key Type: Date or int.

The date type ignores any time component and always ticks over one day at a time. IE Last Key is 2011-06-12, the next import will use 2011-06-13. Use Date = @keyvalue (SQL) or Date = :keyvalue (Oracle)

Int key expects a number and will return any records with an ID value greater than the last Key. Use ID>@keyvalue (SQL) or ID = :keyvalue (Oracle). Updates the Keyvalue to the max(ID) returned.

Last key: The value of the last record imported. For Int it will be the ID of the last record. For Date it will be the date the last import was done for