 |  |
How To: Configure a Load Revenue Import
1. Background
The Logger's Edge allows you to define an import format so that you can take standard files (e.g., Excel files or tab delimited files) that a mill sends you and import them into The Logger's Edge. Once you have entered or imported your loads you can also import your load revenue. For example, if the mill sends you a file with all your settlement data (revenue by load and by activity), you can import that data into The Logger's Edge. If you deliver to multiple mills, you will likely need to define a format for each mill, since each mill is likely to provide you with the load revenue data in different formats.
Assuming any given mill provides you with load revenue data in the same file format consistently, you will only need to define the import format for that mill once. Thereafter, you will be able to import the load revenue through a one-step process, assuming you have all your load information (i.e. each load ticket) is already entered in The Logger's Edge.
2. Steps InvolvedPreparing the File in Excel
- Open the file provided by the mill in Excel. Make sure that row 1 has the column header information, and the data start in row 2 of the file. If not, manipulate the file accordingly. (If the file does not have column headers, the data should start in row 1 of the file.)
- You must have the Ticket Number, Revenue Contract, Activity and Amount. The Pay Weight, Rate, and Pay Basis are optional, but highly recommended. If any of these data items are not in the file you receive from the mill, you should add them directly into the excel workbook. If you must add items, be sure that you add them into the same column each time you create an import file; otherwise you will have to change your import configuration format which can be a hassle. Also, be sure that the revenue contract, activity and pay basis value have already been set up in The Logger's Edge.
- Save the file as a tab delimited file (that is a “.txt” file) using the File | Save As menu item in Excel.
Excel File Example:
How to save:
Be sure to select 'Tab delimited' as the file format. The import will NOT work with a file format other than tab delimited -- If you try to import an Excel file, the import will most assuredly bomb in a big way.
Setting up the File Exchange in The Logger's Edge
1. In The Logger's Edge, select File | Setup File Exchange | Load Revenue. You will see a window like the following, but you will have nothing in your list. In this example, I have a format set up already for Weyerhaeuser called “Wey-LDRev” I will now add another one called GP for Georgia Pacific.
Click OK. Respond “Yes” to the question: “Import Format does not exist.
Create New?”. You will now see a window like the following:
If your import file includes a header field, check the “Data Includes Field
Header in Row 1” checkbox.
2. Click the “Import Test File” button. Browse to the text file that you created from within Excel. (Note: the file must be a tab delimited file with a “txt” file extension in order to import it.) Find your file, highlight it, and click the open button. Your window should now look something like this:
You will see the columns for the tab delimited file displayed in columns in this
grid. If your file has a header row, you will see the header row displayed
in row 1 of this grid. Scroll to the right to see all the columns that
The Logger's Edge
recognized from your file.
3. The next step is to tell The Logger's Edge which of the columns are ones that you actually want imported and to tell it which field in the The Logger's Edge load revenue table corresponds to that column. For example, suppose you want import the field labeled “Ticket No” from your import file. To tell The Logger's Edge that you want to import this field into the corresponding field called “Ticket No” in the The Logger's Edge database, you would use the drop-down box in row 6 in this grid (the “Load Field” row in the grid). Click on the box to display the drop-down list, then find the The Logger's Edge field in the drop down list that logically corresponds to the column you want to import from the file (TICKET_NO in this case). The Logger's Edge will interpret this setting to mean: “when importing each load during a file import, take the value in this field for each load and put this exact value in the 'Ticket No' field on the load slip.” In the example below, the import file has a 'Ticket No' of “22109” for the first load that is displayed in this grid. When The Logger's Edge does the import, it will look for Ticket “22109” in the list of loads you have already entered in the system. If it doesn’t find a ticket with a code of “22109”, you won’t be able to import the load revenue.
Likewise, the activity in the first row is 'HARVEST'. In order to import this row you must already have an activity with the activity code of 'HARVEST' set up.
4. When you are finished mapping your entries, you can test your import by clicking the 'Save & Test' button. This function will run a test of the import for 5 load revenue records (see below). The function will not save any load revenue data, but just checks for errors . The lower grid will show the results of the import that you can review to ensure that the import data is properly aligned with the database entries.
5. When you are finished, click OK.
Importing the Load Revenue File
When you have finished with your load revenue import configuration, you will then want to import the revenue records into The Logger's Edge.
1. In
The Logger's Edge, select Data Entry | Load
Slips | Import Load Revenue. You will see a window like the following:
The top left of the window shows the import formats you have set up.
Choose the one you want to use for the set of loads you are about to import.
Use the bottom left of the window to navigate to the location of your “txt” import file.
The bottom right panel shows all files (regardless of file type) stored in the folder you selected on the left. Be sure you choose the “txt” file and not the original “xls” file when you try to import.
2. Click the “Import” button. At this point, the system will attempt to import all the load revenue records in the file. It will import those load revenue records that it can successfully import, but will generate errors for those loads that can’t be successfully imported.
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 revenue record has an activity of 'HARVEST', but you have not yet set up the activity HARVEST or perhaps you set it up but called it HARVESTING instead.
• Confirm that your naming convention exactly matches the naming convention used in the file. As noted above, if the file calls an activity “HARVEST”, but you call it “HARVESTING”, it will fail upon import. You can either: change the value in the file to be HARVESTING and re-try the import; rename your activity to have a code of “HARVEST” instead of “HARVESTING”; or using the Import Translation Table functionality to tell the system that if it encounters a value of “HARVEST” to import it as “HARVESTING” 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, some mills do not supply certain fields (such as destination or driver) in their import file. 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.
3. Appendix
This 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 captures below shows this window where two “translations” have been set up, both for use in the “LM” file import (the import for L&M Lumber).
The way to interpret the rows set up here (with the Add button) is: for the LM
import, when importing the field in the file that you’ve indicated is the
BLOCK_CODE (by using the drop-down selections shown in step 4 above), any time
the system encounters a value of 754 in the import file, it should look for a
value of “754 Blk 152-COR” in
The Logger's Edge list of blocks, and use that value
(the “related DB value”) when writing the load into the
The Logger's Edge
loads
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 block (or species or sort or destination, etc) with a different naming convention than that used by your mill.
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 an activity code to be “LOGGING”, but the description to be “HARVEST”. If your mill uses the terminology “HARVEST” in the import file, you’ll want to use the checkbox and match on description.
Source Format: This row is typically used for specifying a date
format that is different from the standard date format the system normally uses.
If your import fails on the DATE_IN or DATE_OUT field, you may need to tell the
system how the field in the “txt” is formatted. See the following screen
capture as an example, where the date field in the txt file is in a month,
day, year (MM/DD/YYYY) format:
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. The
most common use for this functionality is to merge a date and time when the two
fields are in separate columns in the txt file. For example, to
concatenate columns 9 (date) and 10 (time in) and import it into the “Date In”
field, you would put the two column numbers you are concatenating into square
brackets (with no spaces) in the “Source Concatenation” row in the column you
are indicating as the “Date In” column.
Trick
The Concatenation can also do math! For example suppose my import file has the Pay Weight and Rate, but not the Amount. I can use the import to calculate the value of the net (= pay weight * rate). See Below. In column 4, I have selected 'Amount' as the field to import and the formula '=[5]*[6]' for the source concatenation. The equal sign '=' is required. A mathematical operator is required.
The key to the trick is you need to select a column in the import file that would not otherwise be imported or the column you select is the correct column, but you want to overwrite the column's data with the calculated value..
