| Filter Invoices |
|
|
The filter feature directs the program to display only a certain set of invoices. For instance, you can set the filter so that as you page through the invoices on the Main Invoice Screen, only vendors from the state of California are displayed, or only vendors from New York are displayed. To reach the filter feature, select INVOICE TOOLS from the Menu Bar and choose FILTER INVOICES. The "Filter Invoices" dialog box appears:
Selecting Common Filtering Options. In the long blank on the Filter Invoice dialog box, enter a filter formula. To aid you in constructing a filter, a number of common filtering options are listed at the bottom of the dialog box. By clicking on the plus sign (+) next to the desired option, the filter formula will automatically be inserted for you. On the list of common filters, whenever you see the word "Current" used (i.e. " Current Customer," "Current Payment1 Year," etc) it means that the customer's name or payment year or other information will be taken from the invoice currently showing on the Invoice Screen. For instance, if you need to set up a filter so that only Baker and Taylor invoices are displayed, page up or down to a Baker and Taylor invoice. That becomes your current invoice. Bring up the Filter dialog box, then click on the "+" beside "Current Customer." The filter formula for Baker and Taylor will automatically be inserted. When you click on OK, only Baker and Taylor invoices will be shown. You can also combine filters from the list. For example, you could: 1) Click on "Current Customer"; 2) click on "AND"; and 3) click on "Current Invoice Year." The resulting filter would display all invoices created in the current year for the current customer. When you have the desired filter entered, press OK. Once you return to the Invoice Screen, you activate the filter by pressing Page Up or Page Down on your keyboard. Then as you page through more invoices, only those that match the filter will show. A filter stays in effect until you specifically remove it by selecting INVOICE TOOLS and TURN FILTER OFF. If you have used a filter and then you return to the Filter Dialog Box and create a new filter. The old filter is overwritten with the new filter. Quotes (") in the Filter. Double quotes in the filter demand special attention. If you are filtering for 2" Nails in the item, the filter will look something like: ITEM->ISBN="2" Nails" If you try to set the filter, AnyOrder will trigger an error. The problem is that AnyOrder is confused by the double quote after the "2." It's best not to use double quotes for item descriptions, rather use two single quotes - but we understand that you may not remember that. Nonetheless, you can make this filter work. Change the outside double quotes to single quotes: ITEM->ISBN='2" Nails' AnyOrder will accept this filter since there's no double quote confusion. You can also use square brackets: ITEM->ISBN=[2" Nails]. Clear and Previous. If you'd like to clear the formula line, click on "Clear." If you like to re-call the most recent filter, click on "Previous." Filters on Networks. Filters can slow network speeds, and on occasion, when using a filer you may received the message: "Invoice is Being Edited by Another User." If possible, avoid the use of filters when others are actively creating and editing invoices in the program. Saving Filters. You may wish to save your own commonly used filters. To do so, first create the filter and run through the invoices to make sure that filter does what you want. Return to the Filter dialog box and click on the "Save" button. A dialog box will appear which allows you to save up to nine filters. Click on a paste button beside an unused filter formula blank. This will insert the current (or most recently used) filter formula in the blank. Then beside the filter formula, type in a descriptive name that will help you remember the filter. The name can be up to 16 characters long. Click "Save" to save the formula and your descriptive name. You can recall a saved filter by clicking on the down arrow beside "Recall Saved Filter" and selecting the appropriate descriptive name. To remove a saved filter, delete it. To replace a saved filter with a new one, type-in the new information over the top of the existing one. Recall Saved Filter. As described above you can save your own commonly used filters under a descriptive name. To recall a saved filter click on the down arrow beside "Recall Saved Filter" and select the appropriate name. If you click the down arrow and decide not to recall a saved filter, click on the blank at the top of the list. When you click on the blank, no filter formula will be entered. Creating Reports With Filters. When you use the Filter Dialog Box, the display of invoices on the Main Invoice Screen is limited to those meeting the requirements of the filter. In addition to the display, you may wish to print a list of the filtered invoices. That's done by using Advanced Export (FILE >> ADVANCE EXPORT). The following series of steps will lead you through the process of creating a report based on a filter. As you can see, initially, there are quite a few steps. But once you do this, the program remembers it all. To create the report in the future, it's just a matter of selecting which report you want and clicking a button to start the process. It's that easy. Moreover, this method has distinct advantages. Instead of being forced to use canned reports, this technique allows you to specifically tailor reports to your business needs. Here are the steps: 1. Before going into Advanced Export, create a filter that you wish to use. 2. Once you have tested the filter and you satisfied with the result, turn off the filter (INVOICE TOOLS >> TURN FILTER OFF) 3. Next select FILE >> ADVANCED EXPORT. 4. Click on the "Create New Profile" button. 5. Give the Profile a name. 6. Select "Text - Tab Delimited" 7. Select what fields you'd like to have in your report by choosing a field from the first window and then clicking on the big plus (+). The field will be moved to the second window. Each field showing in the second window will be a column in the resulting spreadsheet. (More details on creating a profile is found here.) 8. When you have selected all of the fields that you want, save your work by clicking "Save Profile." 9. Click on the "Other Options" button. 10. The "Other Options" screen will appear. Look near the bottom and you'll see "Paste Filter" Click on it. This will paste the most recent filter (the same one that you created and tested in steps 1 and 2 above) into the Filter blank. 11. Click on the "Save" button. 12. You'll be returned to the profile page. We're all done here. Click "Close" 13. Now you'll be back at the first Export dialog box. Once back to this dialog box, in panel # 3, type in a file name. Let's call it "Report1.txt" but you can give it any name but be sure to include the .txt extension. 14. In panel #4, select "Use a filter to determine which invoices are exported" 15. In panel #5, select "If exporting to a dbase or tab delimited file: immediately start-up spreadsheet program for editing and/or printing" 16. In panel #6, put a checkmark beside: "First Record: List Field Names" 17. Then click on "Start Export Process" 18. If you haven't yet done this, you'll be asked to enter the path to Excel. Look for "C:\Program Files" on your computer and find Microsoft Office and then look for "Excel.exe." (You don't necessarily need to use Excel. You can use another spreadsheet program if desired.) 19. Click on "Start Export Process" once again. 20. Excel will open with a spreadsheet displaying of all invoices meeting your filter requirements. Note: if you'd like to run the report again, be sure to close out of Excel. Since Excel is displaying the file (Report1.txt), AnyOrder won't allow you to create a new version of it. So just close out Excel and you'll be able to run it again. AnyOrder will remember all of the above settings. After you set things up. All you need to do is select the appropriate report from the list of profiles, and click "Start Export Process." That's it and you have the report you need. For a couple of specific examples, see Export - Creating Reports Counting Formulas. You can also use the same formulas to count invoices. The Count Invoice dialog box is reached by selecting INVOICE TOOLS and COUNT INVOICES. Any of the formulas described below will also work for counting purposes. Constructing Your Own Filter. You aren't limited to the list of common filters at the bottom of the dialog box. Many others are possible by constructing your own filter formulas and entering them in the blank. Below, you'll find a list of sample filter statements that you can use or alter to construct other filters. To utilize the sample filters listed below, use the Copy and Paste function. Highlight the filter formula (shown in blue below) that you want and copy it to the clipboard. (To copy, use Ctrl+C. Or if you right click your mouse, you can also choose Copy from the Speed Menu). Click in the long blank on the Filter dialog box and paste it. (To paste, use Ctrl+V. Another way to paste it, is to right click your mouse and choose paste from the Speed Menu). Once the filter statement is in place, alter it with your own billing names, states, cities, amounts, etc. State = "MN" Only invoices with MN in the billing state blank will be shown on the screen. You can substitute other states in the place of MN (like IA, CA, etc.). Note you must include the double quotes around the abbreviation. Bill_Name = "Name of Customer" Only the invoices which match the name of the customer in the "Bill-to" blank will be shown on the screen. You can substitute any customer's name for Name of Customer. The filter will be in effect only for the way the name is spelled and capitalized. You must include the double quotes around the name. There's a trick you can use to avoid the capitalization restriction by changing everything to upper case: UPPER(Bill_Name) = UPPER("Name of Customer") As long as the spelling is the same, this will find every matching name, no matter how it might be capitalized. City = "New York" Only invoices in which the billing city is New York will be shown on the screen. You can substitute other states in the place of New York (like Boise, Chicago, etc.) You must include the double quotes around the city. Total_Due > 100 Only invoices in which the total due is greater than $100 will be listed. You can put whatever number you desire in place of the 100. Note that the invoice filter will not include 100. If you wanted all invoices in which the total due was 100 AND greater, use: Total_Due>=100 Total_Due < 0 Only invoices with credit (a minus number in Total Due) will be listed. Inv_Date < {01/01/01} Only invoices issued before 01/01/01 will be listed. You can insert your choice of a date in the curly brackets. Note that it doesn't include 1/1/01. To include it, use this: Inv_Date<={01/01/01} Inv_Date > {06/01/01} .AND. Inv_Date < {12/31/01} Only invoices issued between 6/1/01 and 12/31/01 will be listed. You can insert your choice of dates in the curly brackets. Note that the "AND" must have periods before and after it. Also note that it doesn't include 6/1/01 and 12/31/01. To include them, use this: Inv_Date>={06/01/01} .AND. Inv_Date<={12/31/01} Taxable = .T. This will list all invoices which are taxable. You can also enter: Taxable=.F. which means all non-taxed invoices. Note that "T" or "F" must have periods before and after. Inv_Date > {12/31/01} .AND. Taxable = .T. This will list all invoices issued after 12/31/01 (starting with 1/1/02) and are taxable. Shipping > 0 This will list all invoices in which shipping was charged. EMPTY(Country) = .F. This will list all invoices which have been issued to a country other than the US. The function EMPTY ( ) means: is this item empty? EMPTY(Visa) = .F. This will list all invoices which have a credit card number. Discount = 40 This will display all invoices with a discount of 40%. Indicate it as a whole number: 40 (for 40%) or 25 (for 25%). You can substitute whatever number you desire. You can also get a range: Discount=>25 .AND. Discount=<50 (This would list all invoices with discounts between and including 25% and 50%.). Paid = .F. Lists all invoices which have not been marked paid. InvCode="RET" Lists all invoice which have an invoice code of RET. "Gift Wrapped" $ Notes Lists all invoices in which the two words "Gift Wrapped" appear someplace in the NOTES at the bottom of the Invoice Screen. The "$" means to find any notes in which these words are found. "University" $ Bill_Name List all invoices in which the word "University" occurs in a billing name. The $ means to find any Bill Names with this word in it some place. YEAR(Inv_Date)=2002 List all invoices which were created in 2002. MONTH(Inv_Date)=6 .AND. YEAR(Inv_Date)=2002 List all invoices which were created in June of 2002. Item->Catalog_No="SF/AWA" List all invoices which have products with a catalog number of SF/AWA Item->Status="P" List all invoices with promotional items. "BK/" $ Item->Catalog_No Show all invoices which include products in the BK category. "A" $ InvCode List all invoices with an upper case "A" in the Invoice Code UserDef2->UserDef1="Trade Advertising Campaign" Show all invoices that have "Trade Advertising Campaign" in the first User Defined Field. (See information below on the numbering system.) UserDef2->UserDef5="Vendor Number 25" Show all invoices that have "Vendor Number 25" in the fifth User Defined Field. (See information below on the numbering system.)
List of Identifying Names for Constructing Filters. Identifying names (the names used in filter formulas) are listed below. Each identifying name is followed by item's title as it appears on the Invoice Screen. Information on how to treat the identifying name (number, word, date, or true/false) is enclosed in parentheses: Cust_no Customer # (Use a number: 105) Bill_no Customer Group Number (Use a number: 108) Bill_Name Billing Name (Use words in quotes: "Mainstreet Sporting Goods") Address1 Billing Address1 (Use words & numbers in quotes: "101 S. 2nd") Address2 Billing Address2 (Use words in quotes: "PO Box 204") City Billing City (Use words in quotes: "Boise") State Billing State or Province (Use state abbreviation in quotes: "CA") Zip_Code Billing Zip Code (Use numbers or letters in quotes: "83401") Zip_Ext (No Longer Used) Billing Zip Extension (Use numbers in quotes: "4596") Country Billing Country (Use words in quotes" "CANADA") Ship_Name Shipping Name (Use words in quotes: "Mainstreet Sporting Goods") Add1 Shipping Address1 (Use words & numbers in quotes: "101 S. 2nd") Add2 Shipping Address2 (Use words in quotes: "PO Box 204") Cit Shipping City (Use words in quotes: "Boise") St Shipping State or Province (Use state abbreviation in quotes: "CA") Zip Shipping Zip Code (Use numbers or letters in quotes: "83401") Szip_Ext (No longer used) Shipping Zip Extension (Use numbers in quotes: "4596") Scountry Shipping Country (Use words in quotes" "CANADA") PO_Number PO Number (Use numbers or words in quotes: "BA54569") PO_Date PO Date (Use a date in curly braces: {01/01/06}) Inv_Date Invoice Date (Use date in curly braces: {12/01/06} Taxable Taxable? (Use a .T. to indicate True or .F. for False) Tax_Rate Tax Rate on that invoice (Use a decimal number: .05 [5% tax]) Discount Percentage Discount (Use a whole number: 40 [40%]) Less_Disc Less Discount (Use a number: 25 [means $25]) Tax Amount of Sales Tax (Use a number: 5.14 [means $5.14]) Shipping Shipping Amount (Use a number: 30 [means $30]) Total_Due Total Due (Use a number: 200 [means $200]) Paid Has this invoice been marked paid? (Use .T. for True, .F. for False) Payment1 Paid1 (Use a number: 16.99 [means $16.99]) Date1 Date1 (Use date in curly braces: {06/07/06}) Payment2 Paid2 (Use a number: 16.99 [means $16.99]) Date2 Date2 (Use date in curly braces: {06/07/06}) Balance Balance (Use a number: 135.66 [$135.66]) Visa Visa--Credit Card # (Use number in quotes: "456913345993") Exp Credit Card Expiration (Use number & slashes in quotes: "01/02") Netdays Net Days (Use a number: 30 [means 30 days]) ShipVia Shipped Via (Use words in quotes: "UPS") Card_Name Credit Card Name (Use words in quotes: "Jim Byington" Notes Notes (Use words in quotes: "Sent a free gift") Phone Phone Number (The number should be in quotes: "208-232-3912") EmailAdd Email Address (The address should be in quotes: "bsmith@msn.com") Consigned The items on the invoice are consigned sales (Use a .T. to indicate True, it's a consigned sale or .F. to indicate that it is not a consigned sale) ShipNC If ShipNC is True then shipping on the invoice is not charged to the customer. (Use a .T. to indicate True, the shipping is not charged, or .F. to indicate the shipping is charged.) NoRoyalty If NoRoyalty is True, then royalties are not paid on this invoice. (Use a .T. to indicate True, royalties are not paid--or .F. to indicate royalties are paid.) InvCode Invoice code (Use letters or numbers in quotes: "RET") PaidVia Paid Via: Check, Cash, Credit or empty. (No quotes. Use numbers: 1 = Check, 2 = Cash, 3 = Card. If you have other named payment methods, use the numbers found in FILE >> BASIC SET-UP >> "Payment Methods." ISOName ISO 3-letter currency abbreviation. (Use letters in quotes: "USD") InvClosed If InvClosed is True, then the invoice is closed. If InvClosed is False, then the invoice is open. (Use a .T. to indicate True, the invoice is closed--or .F. to indicate the invoice is open.) TrackingNo Tracking number. (Use letter or numbers in quotes: "8568887856H") SDate S-date. (Use date in curly braces: {06/07/05}) RepCode Sales Representative Code. (Use letters or numbers in quotes: "RDW") TaxCode Tax Code. (Use letters or numbers in quotes: "CAL") AdCode Ad Campaign Code. (Use letters or numbers in quotes: "AD1") Item->Catalog_No Catalog No (User letters or numbers in quotes: "NTBP1") Item->Item Name of Item (product) (Use words in quotes: "Never Turn Back") Item->Status S-Code. (Use the S-code in quote: "P" or "S") User Defined Fields (Available with AnyOrder Level IV or higher) Here's how you would indicate the first User Defined Field: UserDef2->UserDef1 The second user defined field is: UserDef2->UserDef2. The third user defined field is UserDef2->UserDef3 and so on. Note that "UserDef2->" always stays the same. It's the second "UserDef" that you use to indicate the field you want. UserDef1 through UserDef5, and UserDef8 through UserDef10 are character fields. UserDef6 and UserDef7 are checkbox fields (True or False), UserDef11 and UserDef12 are number fields, and UserDef13 is a date field. If the User Defined field is a character field use quotes. UserDef2->UserDef4="Apple Pie" If the User Defined field is a number field, use a number: UserDef2->UserDef11=134.56 If the User Defined field is a checkbox, use true or false. UserDef2->UserDef6=.T. If the User Defined field is a date, use curly braces. UserDef2->UserDef13={06/07/06}
Operators for Constructing Filters. From the list of sample filter formulas, above, you can see that such operators as ">" or "<" are useful in constructing filters. Here's an itemized list and examples of how they are used: < Less Than. (Total_Due<100) > Greater Than. (Total_Due>1000) <= Less Than or Equal To. (Total_Due<=100) >= Greater Than or Equal To. (Total_Due>=1000) <> Not Equal To. (Total_Due<>100) $ Substring Comparison. ("Barnes" $ Bill_Name) This is the same as doing a partial word search in Advanced Search. If the word "Barnes" is found somewhere in the billing name, the invoice will be displayed. .AND. Both statements must be true for the invoice to display. (Bill_Name="BookShop" .AND. Discount=40 means that only BookShop invoices with a 40% discount will be displayed.) .OR. Either statement can be true for the invoice to display. (Bill_Name="BookShop" .OR. Discount=40 means that all BookShop invoices will display along with all invoices with a discount of 40%. Note that in this case invoices with a 40% discount will include more than just BookShop invoices. All invoices with a 40% discount will be included.) .NOT. The statement must be not true for the invoice to display. (.NOT. Taxable means that all invoices which not taxable--in other words, the taxable box has not been checked--will be displayed.) EMPTY( ) If the identifying name within the parentheses is empty, the invoice will be displayed. EMPTY(Visa) means that all invoices which do not have a credit card number will be displayed. |
