HOWTO: Creating custom queries with Zetadocs for Accounting v3.0

ZTN3008

ID: ZTN3008

This Zetadocs technical note applies to:

• Zetadocs for Accounting v3.0

Summary

Custom queries can be created to link Zetadocs with any external ODBC compliant database such as a CRM system to acquire contact information i.e. fax number, recipient name, email etc.  This powerful feature can also be used with your linked accounts system to run specific queries such as to get a list of debtors. Custom queries can be used for sending the same document to multiple recipients, such as a letter to notify customers of a change of bank details or to send a newsletter or mailshot.

More information

Creating and maintaining " custom queries" inside of Zetadocs for Accounting is a very powerful feature that allows you to quickly distribute documents to pre-defined lists of recipients on a recurring basis. Zetadocs for Accounting supports virtually any ODBC-compliant data source in addition to any linked accounting database (if applicable). Using the custom query feature, you can maintain groups of recipients from both inside your accounting system and from external sources such as lead lists, contact manager databases (such as GoldMine and ACT!) or any other data source that contains contact information.

Please Note: The Custom Query not may be visible in your software as it depends on your linked accounting system.

Creating custom queries

In this example, we are going to create a custom query that contains all customers in our linked accounting system with balances greater than zero - that is, customers who owe us money!

1. To begin, click the [Custom Query] tab on the Zetadocs for Accounting Control panel. Then, click the [New] button to begin creating your new custom query.

2. Type in a name for your new query. In this example we want to create a custom query containing all customer records where open balances are greater than zero (in other words, people who owe us money).   Type in " Customers with open balances" in the input field and click the " OK" button.

3. Next, select the data source from the drop-down list. This drop-down will contain a list of all the ODBC data sources you have defined on your system, as well as an option to use the " linked accounting system" .   In our example, we have previously linked Zetadocs for Accounting to Sage Line 100, and since our custom query is going to retrieve all records in our accounting system where the balance is greater than zero, we'll select " SageLine132" as our data source as shown in the diagram below.

4. You will have to know basic SQL for the next step, and it helps if you know the field structure of your customer/vendor database. If you need help writing the SQL for your custom queries, contact your Zetadocs for Accounting reseller or Equisys tech support.  Using a tool like Microsoft Access can also help you create SQL queries - you can create a query using the Design view or Wizard features and then switch to the SQL view which displays the SQL statement which you can copy and paste into Zetadocs for Accounting.

In our example we'll type the following SQL statement that will retrieve those customer records that have a positive balance:

select * from sales_ledger where balance > 0

5. Click [Test Query] button to test the syntax of your SQL and retrieve your records. If you get an error message, most likely your SQL syntax is incorrect. Please verify that your SQL is valid and click the [Test Query] again to resolve the problem.

6. Zetadocs for Accounting will retrieve your data and depending on the size of your database and the number of records in your custom query, this step may take a few minutes. You can check the status by watching the status bar at the bottom of the screen.

When the query execution is complete, the resulting recordset will be previewed in the query results dialog box like the one shown in the diagram above.

7. Now you're ready to map your customer fields so that Zetadocs for Accounting knows how to send documents to the recipients in your custom query. Click the dropdown next to each field in the " Field Mappings" pane and set the correct field to its PDFBlaster counterpart. In the diagram above:

• the account_number field is the key field
• the contact field contains the contact's name
• the account_name field contains the company name
• the fax_number field contains the fax number.

(Note: only the fields highlighted in bold are required fields. In this example, the customer database does not contain specific fields for first name and last name so we'll just leave the field mappings blank. Once your field mappings have been assigned, click the [Save] button to save your custom query.

8. Click " Save" to save your query. You'll now be able so select this new custom query titled " Customers with open balances" as a recipient list each and every time you want to do a fax or email broadcast.

Using custom queries

You can use the custom query you have just created any time you want to deliver an email or fax broadcast to those customers whose balance is greater than zero.

1. To use your list of customers that you have just created, send a new document to the Zetadocs for Accounting Outbox by printing to the Zetadocs printer.  The "Custom" document will be unaddressed at this stage, as shown in the diagram above, however you can preview it by right-clicking and selecting preview.

2. When you are ready to address the document, right click and choose "send now".  The Select Recipients dialog appears as shown in the diagram above.  From here you can select addresses from a number of different data sources, such as Outlook of one of the custom queries you have created.  Choose the "custom query" radio button, highlight your query and click "select".

3. A list of recipients is shown. In this instance, your customers who owe you money.    You can select a single or multiple recipients from this list and choose a send rule before finally sending your message.

Last updated: 19^th November 2004 (PC/SV)