KnowledgeSync News
Vineyardsoft Corporation (800)800-8055 vineyardsoft.com

Leading the Way in Business Activity Monitoring

Distributing Reports That Use Work Tables

Many software applications (particularly ERP) use "work" (or temporary) database tables when reports, forms, or documents (such as invoices and statements) need to be produced. And that’s fine as long as you’re in the application when you wish to generate those reports. But an external program like KnowledgeSync (KS) can’t generate those temporary tables and thus it can’t use those reports as designed.

The typical workaround is to create a copy of each report and modify the copy so that it points at the permanent database tables instead of the work tables. KnowledgeSync then uses the report copy instead of the original. As of version 9 of KS, there is an additional approach.

If the form, document, or report that you want KS to deliver has already been generated and saved to disk, you don’t need KS to re-run the report; KS can just "pick up" the report’s output and deliver it to the appropriate recipients.

The key to this is that report output files are typically dynamically-named; consider the following example of a customer’s invoice:

  • Invoice number 24513 for customer "Metropolis" . . .

    . . . is stored in a file called "inv_[customer name]_[invoice number].pdf"

    . . . in other words, a file called "inv_metropolis_24513.pdf"

So, you need to tell KnowledgeSync how to locate and deliver each dynamically-named invoice to the corresponding customer. As with many things in KS, this starts with an event’s query.

Example:

Using the above scenario of delivering invoices to customers, we start with the assumption that the invoices themselves (i.e., the PDFs) already exist on your system. The next step is to build your query.

The query retrieves a list of orders for which you wish to send out invoices. Since the invoice PDF file names contain the customer name and the invoice number, you must be sure to include those two fields as columns in your query.

Let’s assume that each invoice is a PDF stored in a folder called "C:\finance\invoices". Let’s also assume that each PDF is named "inv_[cus_name]_[invoice_no].pdf" where "[cus_name]" is the customer’s name and "[invoice_no]" is the invoice number.

  1. Create your query and choose your data fields (columns). Include the invoice number and customer name, and give them distinct customized names, such as "cus_name" and "invoice_no".
  2. Add a calculated column to your query. Make the column name a combination of the location of your invoice PDF files plus (within curly braces) the customized names of the query columns that retrieve the customer name and invoice number. Be sure to include the file extension (such as .pdf) and enclose the entire column name within single quotation marks. This column will look like this:

    ’C:\finance\invoices\inv_{cus_name}_{invoice_no}.pdf’

    Note that the file location (server, disk, folder) must not reference a mapped drive (use UNC instead).

  3. Give this column a customized name of exactly "attachment_files" (do not include the quotes) and save your query. When you preview the query, the customer name and invoice number will not show in place of the {cus_name} and {invoice_no} variables; this substitution is done only at the event-level.

  4. The KS event that uses the above query must check on all three checkboxes at the bottom of the "Email" tab of the event ("Include Reports", "Include Files", "Reports should wait").

That’s all that’s required. When this event runs, it will automatically pick up the invoice PDF file that corresponds to each order retrieved by the event’s query. That file will be attached to each outgoing email that is delivered to the customer and their salesperson.

Remember that you must be running version 9 (or later) of KnowledgeSync in order to use this feature. And if you have any questions while configuring this option, please contact our Tech Support department at:

http://www.vineyardsoft.com/support_email.php

And -- if you have any tips or tricks in your own use of KnowledgeSync, please share it with us: there’s a big KS community out there that would love to know it!


Vineyardsoft Corporation
(800)850-8055
info@vineyardsoft.com
www.vineyardsoft.com
unsubscribe