E-mail this Article to a Friend   Address used only to mail article

Using SQL Statements in Mailware Reports                                                   
In Mailware 2005 and later you can use SQL commands in custom expressions.

These commands add features to your reports that were previously unavailable. For example, using an IF statement you can change the text that appears on any report. To try this for yourself use the following steps to change the Mailware invoice. The IF statement looks for the customer type (R=Retail, W=Wholesale) and changes the company name on your invoice accordingly.

1. Open an order in Mailware and press the F10 key to preview the invoice.
2. Click the Define Search tab.
3. On the toolbar click the Insert Blank Field icon.
4. Double click the blank column that appears in the lower half of the screen.
5. In the dialog that opens paste the following into the Custom Expression:
   
   (If (PrintOrder.Customer_LastPayMeth='W','Company 1','Company 2'))
   
   Note: You can rename Company 1 and Company 2 if you wish.
    
6. Click the Field Properties tab and change the Header field to: Title
7. Click OK to save the new field.
8. Click the Layout Page tab at the top of the screen.
9. On the toolbar click the page designer icon.
10. On the Page Designer uncheck the Automatically caption fields box at the bottom.
11. In the list, locate Title and drag it into the top of your report.
12. Delete the old company name field off the invoice (or you can drag it outside the red lines so it does not print, but is available later to move back).
13. Drag the new Title into place. You can enlarge the field by clicking the little black square handles if you have a long company name. You can also adjust the font by using the pulldowns at the top of the screen for the font name and size as well as the Bold, Italics etc. buttons.
14. Click the Preview Report tab to see the results (Note: if prompted regarding sort order answer Yes). If the customer for which you opened the order in step 1 has a type of Wholesale, the first company name will be used. Otherwise, company 2 will appear. Note: You can find the customer type on the Customer screen under the Additional Info tab.
    
    Remember to save your report if you wish to keep these changes.

Using the above IF statement you can change the company address, alternate short order messages, hide or display sale notices etc. The only limitation is the expression cannot exceed 255 characters.
 

Additional commands can be used as well. For example, CASE can display alternate verbiage too. The format for the CASE command is as follows:

CASE expression
WHEN expression THEN value
[WHEN expression THEN value]
[ELSE] value
END

1. To add the CASE command to our report, follow the steps above and paste in the following expression:

(CASE PrintOrder.Orders_PayMethod WHEN 'Credit' THEN 'Credit Card' ELSE 'Other      ' END)

2. Name the field PayType (instead of Title as above) and drag it into the report to replace the field under Pay Method in the first grey line. Now the words "Credit Card" will appear when you preview the invoice. However, we need to show other methods too. Using CASE you can specify different verbiage for each one. Try replacing the expression again with the following:

(CASE PrintOrder.Orders_PayMethod WHEN 'Credit' THEN 'Credit Card' WHEN 'Check' THEN 'Check' WHEN 'PO' THEN 'Purchase Order' WHEN 'Money Ordr' THEN 'Money Order' ELSE 'Other      ' END)

3. Save the report and close the report writer. Open an order and add a payment type of PO or Money Ordr. When you press the F10 key to preview the report you will see the new verbiage in the Payment Method field.

With CASE you can type a shorter expression than would be required by the IF statement to achieve the same results.

Bonus Tip
Another use of IF allows you to print Terms on an invoice if you select them under the Details option on the Order screen, or default them to '2% 10 Net30' if not, but only for invoice/bill/po pay methods.

IF(OrderDet_BillPayMethod <> '', IF(OrderDet_Terms <> '', OrderDet_Terms,'2% 10 Net30'),'')

Additional SQL commands and more information about using them can be found in the online documentation at:

http://www.elevatesoft.com/dbisam4d5_sql_reference.htm