Advanced Export - Overview: 1st Screen

Advanced Export

Advanced Export - First Dialog Box - Level 3 Programs and Above

The Advanced Export feature is an extensive and versatile tool designed to move data out of AnyOrder and into other programs and applications.

This section is about exporting data from the Main Invoice Screen (including invoices, products listed on the invoices, user defined fields, and shipment data). You can also export information from all of the other databases built into AnyOrder - such as the Customer Database, Product Information Database and so on. That's usually done by selecting the "Format & Print from Spreadsheet" option found under the File menu. If a spreadsheet option is not available, you can manually open any or all of AnyOrder's databases in Excel. Make a copy of the database first and then start Excel and open the copy. A list of data files and their contents is found here.

Note that in addition to Advanced Export, there's another routine built into AnyOrder which is used to export data. It's called Basic Export. Use Basic Export when you want to move all of the data contained in an invoice (or several invoices) to an export file. You can also Basic Export to move credit card information to an export file. However, Basic Export is very limited. You are unable to export product data. You don't have control of which fields are exported and their order. Advanced Export, on the other hand, doesn't have these limitations. Moreover, it has great flexibility in creating and tweaking an export file so that it meets your exact needs.

To control which fields are exported, Advanced Export uses something called a profile. The profile is a list of instructions to AnyOrder which drives the export process. In addition to profiles, you can also control other aspects of the export process.

AnyOrder comes with some sample profiles and we have prepared a summary of how to create your own reports using Advanced Export. You'll find it here: Sample Profiles and Creating Your Own Reports.

The information, below, is about the first dialog box in Advanced Export. Constructing profiles and running reports all begin here.

Advanced Export is reached by selecting FILE and ADVANCED EXPORT from the Menu Bar on top of the Main Invoice Screen.

The following screen appears:

AdvExport1

Type of Export Files Produced. AnyOrder can produce three types of export files: Comma Delimited (the most common), Tab Delimited and dBase. For more information: Advanced Export: Supported File Types. Almost every database or spreadsheet program can import data in one or more of these formats. Additionally, AnyOrder can export data to the clipboard.

Exporting: Step by Step

The following is the step by step process involved in exporting.

Start By Creating a Profile. When you select FILE and ADVANCED EXPORT from the Menu Bar, the Advanced Export dialog box will appear. Click on the "Create New Profile" found near the top of the dialog box.

To continue following the step by step procedure: Second Dialog Box

Details on Each Part of the First Dialog Box

Create New Profile. A profile is a list of instructions which tells AnyOrder which fields to export and in what order. When you create your first export file, you need to start here and click on "Create New Profile."

View or Update Existing Profile. Once you create a profile, it is saved. Since it's saved, you won't have to re-create the profile the next time you need to make a similar export file. The "View or Update" button allows you to view an existing file, or, if desired, to make changes.

Delete Profile. If you no longer need to use a profile, you can delete it. Note that this will also delete any replacement tables that you have associated with the profile. To delete a profile, first highlight it on the profile list and then click "Delete Profile."

File Name. In the File Name blank, enter the name of the export file that you want to create. It's important to keep the file at eight (8) letters or less. Avoid spaces in the name. For more information: Supported File Types.

Directory. Select the directory where you want the export file stored. Avoid storing the file in directories with long directory names or names with spaces.

Select Invoices to be Exported. Before starting the import process, you have a variety of choices of what invoices you want to export. You can export the current invoice or a range of invoice numbers. You can select from a list, set a filter, or use a combination of filter and list. Finally you can also select from a list of invoices with credit card numbers (useful for doing a credit card export).

Current Invoice. Exports the current invoice

Range of Invoice Numbers. Select a range of invoice numbers that you want to export.

Range of Dates. Select a range of invoice dates that you want to export.

Select from a List. When you choose this option and click OK, AnyOrder will provide a list (in a spreadsheet format) of all of your invoices. You can page through the list and select those records that you want to export by clicking with your mouse on the appropriate row. When you click with your mouse, a checkmark will appear under the "Yes" Column. This means that YES, this record will be exported. Records without a checkmark will not be exported. To remove a checkmark, click again with your mouse.
To select a group of customers click on the first of the group. Make sure a checkmark is beside the first member of the group. Hold down the SHIFT key and select the last member of the group. All the customers between the two will be selected. You can de-select a group by doing the same thing, but in this case, the first member of the group should not have a checkmark beside it.
To find out how many records you've selected, click on the "Check Count" button. When you are finished selecting records, press OK and the all marked records will be placed in the export file.

Use a Filter to Determine Which Records are Exported. When you choose this option and click OK, AnyOrderwill display the Filter Invoices dialog box. In the blank on top of the dialog box you can enter a filter formula or choose one of the options at the bottom of the screen. Only those invoices which are filtered will be exported. For more information, see Filter dialog box. After you exit from the Filter dialog box, an export file will be created according to the filter.
Note that once you have the filter decided upon, and it's a filter that you want to use each time you run the export, you can save the filter formula in Other Options. If there's a filter entered in More Options, then the filter dialog box will not show and AnyOrder will automatically use filter as entered.
Note that if you use a filter with the "Group Invoice Data by Product" Mode and you filter for a certain product, you will probably see other products scattered among the results. At first glance, it would appear that something is wrong. You would expect to see only the product for which you filtered. Actually, the program is working correctly. The filter function is designed to filter invoices. When the "Group invoice data by Product" mode is chosen, the export process creates a new record for each product on an invoice. So an invoice with 5 different products will result in 5 records in the export. In other words, you'll get the product that you filtered for, but because you are filtering invoices, you'll also get any other products on that invoice. If you don't need those extra products, you can easy remove them. Do that by moving the exported data to Excel (or another spreadsheet program) and sort the list by the catalog number or item name and delete the unwanted products. To be even more precise, you can use the filter feature which found in Excel and other spreadsheet programs.
If you enter a filter in a More Options, AnyOrder only uses the filter when this option or the "Use a Filter COMBINED with the Above Range of Dates" option (described below) have been selected.

Use a Combination of a Selection List and Filter. If you choose this option a selection list will appear. The dialog box is the same dialog that appears when creating a Mailing List. You can change what's shown on the list by clicking the "Filter List" button. By adjusting the filter, the list can be narrowed down to the group of invoices or customers that you want. Then click on the "Select All" which will select all invoices which meet the conditions of the filter and show the on the list. Be sure to get the filter set first before selecting invoices. All other features found in the Mailing List module are available, including removing duplicates. Among other uses, this option is utilized when you need to create mailing list files with a different set of fields than the Mailing List Module's default files (more information).

Credit Card Export. When you click OK after choosing this option, AnyOrder will provide a list (in spreadsheet format) of all invoices with a credit card number. You can page through the list and select those records that you want to export by clicking with your mouse on the appropriate row. When you click with your mouse, a checkmark will appear under the "Yes" Column. This means that YES, this record will be exported. Records without a checkmark will not be exported. To remove a checkmark, click again with your mouse.
To select a group of customers click on the first of the group. Make sure a checkmark is beside the first member of the group. Hold down the SHIFT key and select the last member of the group. All the customers between the two will be selected. You can de-select a group by doing the same thing, but in this case, the first member of the group should not have a checkmark beside it.
If you are moving the export file into a credit card processing program, you may find that your processing software requires that the slashes be removed from the expiration date. If that's the case, place a checkmark beside "On Export: Remove Slashes from Expiration Date." Before exporting AnyOrder will remove the slashes from the expiration date field. When you return to the Main Invoice Screen, you'll notice that the slashes have been removed, and the expiration date will now appear as a 4-place number. That's fine. AnyOrder can work with a 4-place number or the MM/YY format.
Another helpful feature when dealing with a credit card export is the checkbox: "Show Non-Paid Cards. When you place a checkmark here, only those invoices with cards which haven't been paid. In order to use this feature, whenever you process a card, make sure the invoice has been marked paid. That allows you to filter for unpaid cards and you'll able to concentrate on the cards which have not been processed.
Going hand-in-hand with "Show Non-Paid Cards" is "On Export: Mark Invoice Paid and Date." This means that AnyOrder will automatically mark the invoice paid when the export is done. This feature is very useful when you export credit cards to another software program for processing. Most credit card processing programs save card data including the authorization codes, and, if that's the case, it won't be necessary to move the authorization code back into AnyOrder. By marking the invoice paid, you'll know that the credit card has been exported and processed. That allows you to use the "Show Non-Paid Card" option to filter out all processed cards. Since only new cards will be displayed, you can quickly select the entire batch by clicking on the "Select All" button.
If, after processing, you find that some of the cards are rejected, then remove the paid marking from the invoice. You can try processing those cards again with the next batch.
To find out how many records you've selected, click on the "Check Count" button. When you are finished selecting records, press OK and the all marked records will be placed in the export file.

Use a Filter COMBINED with the Above Range of Dates. This option is very similar to the one described immediately above. It displays the Filter Invoices dialog box. But in addition to using the filter, it also will limit the invoices exported to the dates from the "Range of Dates" option.
Note that once you have the filter decided upon, and it's a filter that you want to use each time you run the export, you can save the filter formula in Other Options. If there's a filter entered in More Options, then the filter dialog box will not show and AnyOrder will automatically use filter as entered.
Note: you'll need to type in a "From" and "To" date, but, when finished, make sure that you have this option selected. In other words, make sure the selection dot is beside: "Use a Filter COMBINED with the Above Range Dates."
For an example, let's say you wish to produce a report for an Invoice Code of "ABC." And, you wish to limit the report to invoices issued in the month of April. Here's a quick run down on how you would do it. First, type in the dates 04/01/09 to 04/30/09. Second, select "Use a Filter COMBINED…" Third, build and save your profile. Fourth, in Other Options, enter appropriate filter (in this case it would be InvCode="ABC"). (For more information on setting up filters, see Filter dialog box.) Fifth, make any other choices on the first export dialog box, and then run the export. The result would be only those invoices with a code of ABC and with an invoice date that falls in the month of April.
If you enter a filter in a More Options, AnyOrder only uses the filter when this option or the option: "Use a Filter to Determine Which Records are Exported" (described above) have been selected.

First Record: List Field Names. If you would like the first record of the export file to contain a list of fields, place a checkmark here. The first record will include each of the field names shown in the profile box on the second dialog box of the Export series. This may helpful in some applications. For instance, if you use the export file in a spreadsheet program, the field names in the first record can serve as column headings.

Start-up Programs. (Available With Level 4 Programs and Higher) One very convenient feature that you may want to utilize is the ability to start-up a spreadsheet or a word processing program immediately after the export process is complete. If you are exporting to a Tab Delimited or dBase File, you can start up Excel (or a spreadsheet program of your choice). If you are exporting to a Comma Delimited File, you can start-up MS Word (or word processor of your choice). Check either box if you'd like to use this function.
The start-up of MS Word has an additional advantage in that you can designate a mail merge document to automatically load into Word at start-up. That opens all sorts of possibilities. You can have pre-prepared email letters, label set-ups, envelopes or documents like a Bill of Lading. When the export is finished, the document will open with the data in place and ready to go. (To indicate a start up document and for more information, see Export: Other Options.

Start Export Process. This is the button to click after you've selected the appropriate profile, typed in the name of the file to which you want to export, and selected which invoices to export.

Copy Profile. If you've already set-up a profile and you need to set-up a new but similar profile, you can use the "Copy Profile" button. First select the profile you want to copy from the list. Then click "Copy Profile." The selected profile will be copied and given the same name with a "(2)" at the end of it. If you have replacement tables associated with the original profile, this will also copy the tables. Once the profile is copied, you can click on "View or Update Profile." Change the name to the desired new name and then make whatever adjustments are necessary.

Profile Importer. The profile importer used when the developer makes new profiles available. Registered users are notified when new profiles are available. If you'd like to use one or more of the profiles, you can use the Profile Importer to add new profile(s) to your existing list.

SDF Flat File Utility. Use this feature to export to a System Data Format (SDF) or Flat File. SDF and Flat Files are interchangeable terms for the same file type. (For more information on the structure of a SDF or Flat File Supported File Types.) AnyOrder can't directly export to an SDF file, but the Flat File Utility will convert a Comma Delimited File to a Flat File. For more on the Flat File Utility, see Flat File Utility.

UPS Worldship

AnyOrder also comes with a UPS Worldship Profile. If you use UPS shipping services, you might consider using Worldship. You can move data directly from AnyOrder into Worldship for shipment packaging. More Information: UPS WorldShip

Bill of Lading Form

AnyOrder comes with a Bill of Lading Form. The Bill of Lading was created in Microsoft Word. (Our many thanks to Vicky Rush of Rush Industries for creating and providing the form for use in AnyOrder.)

If you have a copy of Word on your computer, it's worthwhile to see how the export process works. Even if you don't use Bill of Lading forms in your business, it provides an excellent demonstration of how AnyOrder can be used to auto-fill business forms.

To use it, you'll need to create an invoice and fill out AnyOrder's Shipment Form. Start Advanced Export. Select "Bill of Lading" as the profile. Then click on "Start Export Process." The first time you run it, AnyOrder will ask for the path to Microsoft Word. Click on "Browse" and select the path.

After indicating the path, you need to run the export once again. During the process, AnyOrder will create a small export file, start Microsoft Word, and load in the Bill of Lading form. Once Microsoft Word starts, it does a mail merge and auto-fills the form.

More information: Form filling, labeling and mail merging.

On-Line Shipment Processing Profiles

AnyOrder also comes with a series of on-line shipping profiles. Be sure not to delete these profiles or the on-line shipping functions will not work.

Sample Profiles & Reports that You Can Create

For information on the sample profiles that come with AnyOrder and instructions for creating other reports, see: Reports that You Can Create.

Exporting Other Data

Advanced Export exports invoice information including everything you see on the Main Invoice Screen, plus products, User Defined Fields and Shipment Information (if you use them). You can also export nearly any other database in AnyOrder. Look under the File menu in various parts of the program for "View in Spreadsheet" (or similar). If that menu choice is not available, you can copy the database desired (indicated by a .dbf extension) from the AnyOrder directory and open it directly in Excel or Open Office Calc. Once in Excel, you can manipulate it, print it and/or move it to any other program desired.