| Import Replacement Tables |
|
|
Import: Replacement Tables - 4th Dialog Box Level 3 and above Replacement tables are used in the Import process. The Import feature is reached by selecting FILE and IMPORT from the Menu Bar on top of the Main Invoice Screen. More information on Import. Replacement tables allow you to change the values in an import file to values compatible with AnyBook. For instance if a company that you contract to sell your products uses different catalog numbers than those used in AnyBook, you can use a replacement table to convert their catalog number to yours. For the sake of this discussion, we'll call a company that you hire to help sell your products a "sales contractor." To access Replacement Tables, click on the "Replacement Tables " button on the bottom of the second import dialog box, Import Profile) or on the bottom of the "Other Options" dialog box:
There are two types of replacement tables: Catalog Number and Additional Tables. A Catalog Number Replacement Table allows you to replace catalog number. For example, Sales Unlimited may use the catalog number: "9988" for one of your books. In AnyBook, however, the catalog number that you use may be "BK/JUMPHI." You can set up a table to convert "9988" to "BK/JUMPHI." To convert catalog numbers or product names, click on the "Catalog Number Table" button. If you need to replace catalog numbers (or product names), click on "Catalog Number Table" button. (See "Catalog Number Table" below.) Additional replacement tables can also be set up. Let's say the import file contains a code number that indicates how the product was shipped. For instance, "U25" might mean the product was shipped UPS Ground. You can set up a replacement table that converts "U25" to "UPS Ground" (and other codes). To do so, you would give the table a name and click on "Create New Table." You can type in any name which helps you remember the table. Once you've typed in a name, click on "Create New Table." If you've already started a table and need to view it or modify it, select the appropriate table name from the list and click on "View or Update Existing Table." (See "Additional Replacement Tables" below.)
Catalog Number Table Turning it On. At the top of the "Replacement Table for Catalog Numbers" you can either turn the table on or off. To turn it on, place a checkmark in the "Turn On" box. Matching Field. A matching field is the field of the import file that you will use for comparison with either with AnyBook's catalog number or product name. In most cases, the matching field will be catalog number. In some cases, however, a sales contractor may not use catalog number, but rather a product name. If that's the situation, select "Product Name" as the matching field. Replacement Table. In the replacement table, you indicate what values should be substituted for the matching field. If your matching field is catalog number, the first column of the table will list the catalog numbers used by the sales contractor. The second column of the table will list the catalog numbers found in AnyBook. For example, lets say the sales contractor uses "B668" for a catalog number and that corresponds to "BK/BETTER" in AnyBook. Thus, you will list "B668" in the first column of the table and "BK/BETTER" in the second column. In most cases, you'll also want to replace the product name with your own name. You could use the sales contractor's product name if desired, but using your own name is better. It provides consistency throughout your invoices. Therefore, in the third column, enter the name of the product. Finally, in the last column, you can enter the product's price. In most cases, you'll enter your normal retail price for the product in this column. However, if you have variable prices, and haven't established consistent retail prices, you may want to leave it out and use the price data from the import file. Pasting Products to the Table. To help make things easier in setting up the table, two buttons are included just below the table. These allow you to paste products from your product information database to the table. You can either paste one at a time or paste the entire database. To paste on at a time, place your cursor in the table where you want the product pasted and click on the +. If you need to delete a line from the table, click on "Delete Line." When finished building the table click on "Save." When the Catalog Number Exceeds 13 Characters. Let's say that you are importing a catalog number found in your import file which is 16 characters long. And let's say that you want to convert that catalog number using a Replacement Table to the catalog numbers that you've set up in AnyBook. If you set up your profile to import to the AnyBook's catalog number, you'll run into the 13 character limit. (Catalog numbers in AnyBook can't exceed 13 characters.) But there's a trick you can use. In your profile instead of mapping the import field to AnyBook's catalog number, rather map it to the Item Name. The Item Name has plenty of space. Then, when you set-up the catalog replacement table, select Item Name instead of catalog number. Essentially what that does is to allow you to import long catalog numbers and obtain the desired conversion to your own catalog number. Additional Replacement Tables. Important Note: Before starting work on an Additional Replacement Table, make sure you've finished creating an import profile in the second dialog box of the Import series. The profile values will needed as you build the table. Matching Field. At the top of the Additional Replacement Table dialog box, you'll be able to indicate the matching field and replacement field(s). In almost all cases the matching field and first replacement field is the same. Choose a matching field from the profile list and click the top most + to insert it in the matching field blank. If you make a mistake, you can remove it by clicking on the - sign beside the blank. Note that the list from which you are selecting is the profile that you developed in the second dialog box in the Import series. On this list you are looking for the field from the import file that you'll be using for comparison. Let's say the sales contractor has different codes for the shipping method. One example might be that their code "U25" means "UPS Ground." If so, you would look in the profile list for the field that represents this code. It might look something like: 2) U25->Ship Via 48. Select this line and click on the top most + to enter it in the Matching Field blank. Replacement Field. Next, you need to indicate a replacement field. In almost all cases, this will be the same as the Matching Field. In the example above, you want to replace "U25" with "UPS Ground" in the Ship Via blank on AnyBook's Main Invoice Screen. Thus, you would select 2) U25->Ship Via 48 and click on the second + to enter it in the Replacement Field1 blank. Different Replacement Field from Matching Field. There are occasions where you may wish to select a replacement field which is different than the matching field. The process is the same. From the profile list, select the field where you want the replacement to occur. When the process occurs, the replacement is made only in replacement field (and not to the matching field). Different Replacement Field: Setting up a Dummy Field. In most cases, the replacement field that you want will not appear on the profile list. That means that you need to set up a dummy field. Choose a field from the import file that won't be using. There's always one or more fields that you may not be importing. Use one of those fields. Let's call it "Miscellaneous." Let's say that you live in Indiana and you need to charge tax for all Indiana customers. That means for every Indiana customer, you want to turn on the "Taxable?" field on the Main Invoice Screen. (The Taxable field is a checkmark field. When it has a checkmark, tax will be added to the customer's sale.) That means you need to have "Taxable" in the profile. So when you set up the dummy field, you connect Miscellaneous (the field you won't be using) with Taxable. It might look something like: 16) Miscellaneous -> Taxable 23 Now when it comes to select the replacement field, you'll find it on the profile list. Let's run through the example from the beginning. To set up the replacement table, you would start by choosing a Matching Field. In this case, we want the billing state. The matching field that we select might look like: 5) Billing State -> Billing State 8. The Replacement Field would be 16) Miscellaneous -> Taxable 23. In the Replacement Table (described below), you would type in "IN" in the matching field column ("IN" is the state abbreviation for Indiana). In the Replacement field, you would type in "TRUE." (Since "Taxable?" Is a checkbox field, it is either true or false, so our replacement value must be either True or False.) When the import is done, any customer with a billing state of IN, will have the Taxable? checkbox turned on, and the customer will be charged tax. Replacement Table. The replacement table is the spreadsheet at the bottom of the dialog box. It is filled out much like the Catalog Number table discussed above. You indicate the value from the import file in the leftmost column. The replacement value to use in AnyBook is indicated in the next column. Using the example above, you would type in "U25" in the first column and "UPS Ground" (the replacement value) in the second column. You would continue to type in the shipping codes in the first column and the replacement values in the second until the table is finished. If for some reason, you want a blank for a replacement value. Type in three carrots (^^^) in the second column. Other Replacement Fields. You can also replace two other fields if desired. This isn't very common, but AnyBook provides the opportunity if you need it. For example, let's say you'd like to include a copy of the sales contractor's shipping code in the Notes area for your own reference. If so, the following values should appear on the profile (which would have been previously set-up in the 2nd dialog box): [C] Shipped Via -> Notes 52 2) U25->Notes 52 At the top of the Additional Tables dialog box, you would select 2) U25->Notes 52 as Replace Field2. Then, as you type in replacement values in the Replacement Table, you would enter: "U25 (UPS Ground)" as the replacement value for the third column (Replace Field2). When you actually do the import, the following will appear in the Notes Area of the Main Invoice Screen: "Shipped Via: U25 (UPS Ground)." More Information on Importing Import Profiles (Second Dialog Box) Other Options (Third Dialog Box) Flat File Utility Working with Import Files in Excel
|
