 |  |
How To: Import Miscellaneous Expenses
1. BackgroundThe Logger's Edge has the capability to import miscellaneous expenses from an external file. For example, you may have a workbook where you record your expenses and want to import them into The Logger's Edge. This may be particularly useful where a vendor can provide invoice detail in an electronic format. For example, some oil companies will provide a detail listing of 'Cardlock' transactions that you can download into an excel file and then import into The Logger's Edge.
This import capability uses a file saved as a 'tab delimted' file from MS Excel or a straight text file where the data are separated by tabs.
This functionality has been added in Version 4.5.0
2. Setting Up a Miscellaneous Import ConfigurationLet's first look at an example Excel file with miscellaneous expenses:
Note that in the import file, we have the vendor (Imperial Oil), the Date, the Truck, the Equipment -- basically all the columns that are in the miscellaneous entry grid in The Logger's Edge. The table below shows the fields in the miscellaneous expense grid.
| Field | Required |
| Date | Yes |
| Vendor | Yes |
| Equipment Code | Yes, NONE is OK |
| Truck Code | Yes, NONE is OK |
| Block | No, NONE is Default |
| Revenue Contract | No, NONE is Default |
| Activity | No, NONE is Default |
| Account Code | No, NONE is Default |
| Invoice # | Yes |
| Item # | Yes |
| Amount | Yes |
| GST (if enabled) | Yes |
| Total | Yes |
For the items in the table, you should have a column in your worksheet. If your vendor does not provide all the information in the table, you should add the columns to your workbook and fill in the appropriate values.
Once you have set up your Excel file, you need to save it as a 'Tab Delimted' file.
In the Excel 'Save As' dialog box, you should click on the file type 'Tab Delimited'. This selection will save the current worksheet as a tab delimited file.
You
should also save the original workbook as an excel file so that you have
two files: (1) your basic workbook Excel file, and (2) your tab
delimited for your import.
Once you have created an import file, you need to set up an 'import configuration.' The import configuration tells The Logger's Edge which columns in your import file match up with which columns in the miscellaneous entry grid. Basically, an import configuration serves as a mapping between your import file and The Logger's Edge database.
Navigate to File | Setup File Exchange | Expenses from the Main menu:
This will bring up an entry window that prompts you to select an existing configuration or create a new configuration.
In order to create a new configuration, just type in the name under which you want it to be saved. In the window above, I have entered in 'Cardlock Import' as the name of the configuration. As you may have several different formats for your import files, you can set up as many different configurations as you need.
Click OK.
The import configuration window allows you to set up the mapping of the import data to the The Logger's Edge database. The first thing you want to do is click 'Load Test File.' You should select your import file:
When you load your import file, the top grid will load the first five (5) rows in your file.
Once the import file is loaded, you can use the drop down lists (in the 'Load Field' row) to match the import data to the miscellaneous grid columns. (The drop down lists have all the columns in the miscellaneous grid.)
In the example above, the data in the Truck Code column is matched to the Truck field in the miscellaneous entry grid. You will need to perform this matching for all the required columns listed in the table above.
Once you have mapped all you columns in your import file, you can test your configuration. Click on Save & Test -- this action will save your configuration and attempt to import your first five items. (if your import file has a row of column headers, be sure to check the 'Data Includes Field Headers in Row 1' checkbox, or The Logger's Edge will try to import that row as well.
If there are errors, you will receive a message window that tells you which items have problems. Otherwise, the The Logger's Edge will load the first five imported rows into the bottom grid in the window. NO real data is actually saved when you click on Save & Test -- this action simply makes sure your configuration is valid before your run it for real.
Once your configuration has successfully passed the test, you are ready to import for real.
Running the Expense File Import
In order to run your import, navigate to Data Entry | Expenses | Contractor / Vendor | Import Expenses from the Main menu:
This action brings up the Miscellaneous Expense Import window:
In this window, you select your import configuration and the file you want to import. The configuration and the import file must be consistent with one another, otherwise you will encounter a rash of errors.
Once you have made these two selections, click import.
The Logger's Edge will then import your entries. If there are errors in your file, The Logger's Edge will import the good records and skip the bad.
In terms of troubleshooting the import errors, there are a number of common problems that people encounter. The following list should help you in your troubleshooting.
• Confirm that you have set up in The Logger's Edge all the attribute values that are on the loads. For example, maybe the load has a truck # of 434, but you have not yet set up truck #434. You will need to set that truck up, before the record will import.
• Confirm that your naming convention exactly matches the naming convention used in the file. For example, if the file calls a truck “754”, but you call it “TR754”, it will fail upon import. You can either: change the value in the import file to be TR754 and re-try the import; rename your truck to have a code of “754” instead of “TR754”; or using the Import Translation Table functionality to tell the system that if it encounters a value of “754” to import it as “TR754” during the processing. See the Appendix for more details on using the import translation tables.
• If you manipulate columns in your spreadsheet when you do your import, make sure that you are doing the exact same manipulation that you did when you created the import format. For example, if a vendor does not supply certain fields (such as expense code) in their import file, you will need to add it manually. Users may find it easier to add those columns into the import file prior to import, since mass edits in Excel are more efficient than mass edits in The Logger's Edge. If you add columns, they must be added in EXACTLY the same position each time you do the import – otherwise, The Logger's Edge will not recognize the import data.
You can now review your imported entries in the Miscellaneous Entry Gird (Navigate to Data Entry | Expenses | Contractor / Vendor | Data Entry:
If
you are importing invoices that have multiple items or transactions that
you are importing, you need a way of uniquely identifying each
individual transaction (for example, see the grid above). In
The Logger's
Edge you can identify a unique
transaction by its invoice number and its 'item number' (ITEM_NO).
The item number is not normally enabled in
The Logger's
Edge , but needs to be enabled manually:
The combination of the invoice number and item number allows The Logger's Edge to identify each transaction. If the field 'ITEM_NO" is not enabled, it will default to a blank. In this case, you must be sure that each specific invoice (defined by the invoice number, vendor and date) in The Logger's Edge is unique by itself.
Technical Side Note of Determining Whether an Entry should be Added or Updated
The Logger's Edge uses the following rules to determine whether an imported entry should be added as a new entry or already exists and should be updated.
At a minimum, check an entry based only on Vendor and Entry Date.
Next, if the Account Code is being imported, also match on the Account Code,
Next, if the Account Code is being imported, also match on the Account Code,
Next, if the Equipment Code is being imported, also match on the Equipment Code,
Next, if the Truck Code is being imported, also match on the Truck Code,
Next, if the Invoice Number is being imported, also match on the Invoice Number,
Next, if the Item Number is being imported, also match on the Item Number.
Because you can have multiple entries for a given day, vendor, account and equipment, the only sure way to guarantee that an entry is unique is to include an invoice number. If you have multiple items per invoice, then you also need an item (or transaction number) in order to ensure that an entry is unique.
Warning. If your miscellaneous entries are not unique based on these rules, then you run the risk of overwriting existing entries.
New Items in The Miscellaneous Expense Grid
There are three new items in the miscellaneous expense grid that can be enabled so that you can import and track the service meter reading, the units purchased, and the product purchased. These items specifically designed so that you can track fuel purchases, but you can also track other maintenance activities, such as oil changes, etc.
There are two reports that now make use of the 'SERVICE_METER' item: The Equipment Operating Cost report and the Truck Operating Cost report. The Truck Operating Cost report is shown below:
The service meter will be reported in the 'Meter' column. This column will appear automatically if the service meter is enabled in either the Miscellaneous Expense table or the Time Details table.
3. AppendixThis Appendix provides more detail on some of the advanced functionality associated with load imports.
Using the Import Translation Tables
Many users do not adopt exactly the same naming convention as the mills they serve, or they serve multiple mills that don’t all use the same naming convention. If you find yourself in this circumstance, you may wish to use the import translation feature.
Select the menu item File | Setup File Exchange | Import File Translations. The screen below shows this window where three “translations” have been set up.
The way to interpret the first row set up here (with the Add button) is: when importing the field in the file that you’ve indicated is the
TRUCK_CODE (in your configuration file), any time the system encounters a value
of 549 in the import file, it should look for a
value of “BR549” in
The Logger's Edge list of
trucks, and use that value
(the “related DB value”) when writing the transaction into the
The Logger's Edge
miscellaneous expense table.
You can repeat this process for any field in the import file that you’d like, simply by adding a new row for each field in a given import.
If you make use of this feature, you’ll need to add a row here each time you set up a truck (or equipment code, expense code, activity code, etc) with a different naming convention than that used in your spreadsheet.
Using the Advanced Features in the Import Definition Window
You may have noticed when we configured our import file in the example above, we did not make use of a number of options that were available in the bottom of the window.
Match on Description: If you check (i.e., click on) the “match on description” box for a given column, the system will match the value in the import file with the description rather than the code you have used in The Logger's Edge when you set up the given item. For example, you may have set up a vendor code to be “ESSO”, but the description to be “Imperial Oil". If your import spreadsheet uses the terminology “Imperial Oil” in the import file, you’ll want to match on description. This option allows you to map the import file's description to your code.
Source Format: This row is typically used for specifying a date
format that is different from the standard date format the system normally uses.
For example, suppose you have an import file where the dates are formatted
DD/MM/YYYY, but you use MM/DD/YYYY in
The Logger's Edge.
If your system uses a different format that used in the import file for dates, you need to tell The Logger's Edge the explicit format that dates appear in the import file. When the import is run, The Logger's Edge will covert dates that are in your import file from the source (import) format to the format you use on your computer.
Source Concatenation: This row is useful when you need to blend two fields in the txt file into a single field for import into our system. For example, you could blend two columns in your import file into the description column in The Logger's Edge.
In the example above, the columns 19 and 21 have been combined and imported into the description column.
Trick
The Concatenation can also do math! For example suppose my import file has two detailed amounts, but not a total amount. I can use the import to calculate the value of the amount. See Below. In column 17, I have selected 'Amount' as the field to import and the formula '=[13]+[14]' for the source concatenation. The equal sign '=' is required. A mathematical operator is also required.
The key to the trick is you need to select a column in the import file that would not otherwise be imported.
