 |  |
How To: Configure a Log Scale Import
1. BackgroundThe Logger's Edge allows you to define a log scale import format so that you can take standard files (e.g., Excel files or tab delimited files) that a scaler (or scaling company) provides you and import them into The Logger's Edge. If you use multiple scaling systems, you will likely need to define a format for each scaling, since each scaling unit is likely to provide you with the log scale data in different formats.
Assuming any given scaler provides you with log piece data in the same file format consistently, you will only need to define the import format for that scaler once. Thereafter, you will be able to import the logs through a one-step process, assuming you have all your log attribute information (e.g., species, sorts, grades, etc.) already set up in The Logger's Edge.
In order to import your log scale data, the header record for the load ticket must already exist in the database. Essentially, this means that your load tickets have to be entered or imported prior to the log scale data being imported.
2. Steps InvolvedReviewing the Scale Import File
Generally, a log scale import file is an ASCII text (.txt) file that can be viewed easily in notepad. Below is an example of a file:
Generally, the file is organized as a series of records where you have a header (load) record(s) followed by a all the individual log records in that load. In the file above, a record that has the letter 'H' in the first column is a header record; a record with an 'L' represents a log's data. The data for the header and log records are different as you might expect, and their format varies as well. When setting up an import for scale data, you need to setup two import formats: one for the header data and another for the log data.
The data for each record is usually in fixed columns. For example, the ticket number might be in columns 6-13. The species might be in columns 22-23. In configuring an import you will need to specify the column for each of your load or log attributes.
Setting up the File Exchange in The Logger's Edge
1. In The Logger's Edge, select File | Setup File Exchange | Scaled Logs.
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 as 'Handheld Log Scale'
From this window, you can either select an existing format or create a new format. You create a new format by typing in a new name and clicking OK. You then have to 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. You will next need to define the columns for both your header records and your detail records.
You first enter the character(s) that identifies the header (load) rows and the character(s) that identifiy the log detail rows. For example if the first character of a header row is 'H' you would enter an H for the Header Char(s); likewise if the first character of a log detail record is 'L', you would enter an 'L' for Detail Char(s).
Next, click on 'Header Format':
Each row in this grid represents an individual field in the import file for your header records. For example in row 2 above, the entry specifies that the field 'Woods Ticket' starts in column 6 of the import file and is 8 characters wide -- meaning the columns 6 through 13 contain the field Woods Ticket.
The
'Name' column id free format and is completely up to the terminology you
want to use.
Next, click on 'Detail Format':
Again, each row in this grid represents an individual field in the import file for your detail records. For example in row 6 above, the entry specifies that the field 'Species' starts in column 22 of the import file and is 2 characters wide -- meaning the columns 22 through 23 contain the field Species.
The
'Name' column id free format and is completely up to the terminology you
want to use.
Note
that in the example above, there are three entries for Gross and Net MBF.
This occurs because the scale file has may break down individual (long) logs
into up to three lengths -- for example field one may be for under 20 feet;
field two, 20 to 40 feet; and field three, 40 feet and longer. If this
is the case, you can add the parts together to obtain the total MBF for the
log during the import.
Once you have specified the header and detail format, you can load a test file.
3. 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. This function will load the fist 5 records into the grid so that you can check to make sure your column definitions are correct. Your window should now look something like this:
You will see three tabs: (1) Header; (2) Details; and (3) Log Pieces. Note that the Log Pieces tab will only be visible if you have configured your database to store each individual log.
The headers of the columns on each tab is the name for each column that you have supplied in step 2.
4. 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 loads table corresponds to that column.
For example, suppose you want to import the column 'Species' into the data item 'Species' in the The Logger's Edge. To tell The Logger's Edge that you want to import this field into the corresponding field called “Species” in 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 (Species - C_PART_4_CODE 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 'Species' field on the load slip.” In the example above, the import file has a Species of “38” for the first load that is displayed in this grid. When The Logger's Edge does the import, it will look for Species “38” in the list of species you have already set up in the system. If it doesn’t find a species with a code of “38”, you won’t be able to import the logs.
Note, of course, that this rule assumes that you and your scaler each using the same naming convention for each species. There are ways to handle different naming conventions, as described in the Appendix to this document.
5. 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 loads (see below). The function will not save any loads, but will just check 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.
In the lower grid, the test results show five loads with their corresponding log detail. In The Logger's Edge, logs are grouped and stored as aggregate records. For example, in row 5 above, the aggregate record represents 33 logs (note the entry in the piece count column) that has a total scaled volume of 3.03 MBF. In this way, The Logger's Edge can take an import file of hundreds of logs and condense the resulting records into grouped records that are far fewer in number. This allows the system to manage the data for payment and reporting much more efficiently.
When you are finished, click OK.
The
grouping that occurs in
The Logger's Edge
is based on the load attributes that have been set up in your system.
For example if you have setup the two attributes Species and Sort, the logs
will be grouped by Species and Sort. Likewise if you have setup the
three attributes Species, Sort and Grade, the logs will be grouped by
Species, Sort and Grade. This strategy ensures that all the relevant
groupings are available for payment. (Payment rates are based on your
log attributes -- i.e., Species, Sort and Grade -- and several other load
attributes; namely destination, truck, truck type, truck driver and route.
In the log import, these other load attributes are actually passed down from
the load onto the grouped records so that the grouped records inherits all
the attributes of the load, with the exception of attributes you have
specified to import. Thus, if you decided not to import Sort in the
log import, the logs would inherit the sort of the load header.
A
note on piece data. In
The Logger's Edge
is also possible to store your individual log scale data. These data
are stored in a separate table from the loads table that is used for pay
processing. This separate table allows you to run reports on the
individual scale data, but it does not get in the way of processing
contractor pay. In order to enable the saving of piece data, you must
enable the Basic Setup item 'Import Log Pieces':
This setting enables the third tab on the import setup and tells The Logger's Edge to save the piece data along with the grouped log data when the file is imported.
There is a report in The Logger's Edge that allows you to generate a tally sheet, by load. The report (Log Tally Sheet, report #317), shows each log and its details for each scaled load over you selected data range and tract.
There are 10 configurable attributes on the load pieces table that require pre-defined lists in order to import. These fields are in the form 'P_PART_XX_CODE' where XX is a number from 1 to 10. These parts can be activated for the log import and are available for reporting.
These 10 new fields require related entries in a pre-configured list in order to import the data. In the example above, the attribute P_PART_1_CODE has been activated and is defined as 'Quality'.
The piece part attributes are available from the main menu under the load attributes:
In this example, the quality codes are in PIECE_PART_1_CODE, and are setup under Setup | Quality.
Importing the scaled Log File
1. In
The Logger's Edge, select Data Entry | Load
Slips | Import Scaled Loads. 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 scaled logs 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 scaled logs in the file. It will import those scale logs that it can successfully import, but will generate errors for those scaled logs 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 the load header is already in The Logger's Edge. In order to import the log scale data, the load header must be in the The Logger's Edge database. The Logger's Edge will use the ticket number and date in the import file to try to find the corresponding load record in the The Logger's Edge.
• Confirm that you have set up in The Logger's Edge all the attribute values that are on the scaled logs. For example, maybe the log has a species code of 38, but you have not yet set up a species with a code of 38.
• Confirm that your naming convention exactly matches the naming convention used in the file. For example, if the file calls a species “38”, but you call it “38-DF”, it will fail upon import. You can either: change the value in the file to be 38-DF and re-try the import; or rename your species to have a code of “38” instead of “38-DF”; or using the Import Translation Table functionality to tell the system that if it encounters a value of “38” to import it as “38-DF” during the processing. See the Appendix for more details on using the import translation tables.
3. Appendix
This Appendix provides more detail on some of the advanced functionality associated with scaled log imports.
Using the Import Translation Tables
Many users do not adopt exactly the same naming convention as all the scalers they use, or the scalers use a shorthand naming convention, but you want to use codes that are more explanatory (eg, the scaler uses a two-digit numeric code like 38, but you want to use a more useful code like DOUG FIR. If you find yourself in this circumstance, you may wish to use the import translation feature -- this allows you to convert 38 into a code like 'DOUG FIR'
Select the menu item File | Setup File Exchange | Import File Translations. The screen captures below shows this window where “translations” have been set up for different species codes and their mapped values in The Logger's Edge.
The way to interpret the rows set up here (with the Add button) is: when importing the field in the file that you’ve
indicated is the C_PART_4_CODE (by using the drop-down selections shown in step 4 above), any time
the system encounters a value of 38 in the import file, it should look for a
value of “38-WL” in
The Logger's Edge list of blocks, and use that value
(the “related DB value”) when writing the log or aggregate logs 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 species (or grade or sort or destination, etc) with a different naming convention than that used by your scaler.
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 species code to be “38-WL”, but the description to be “Western Larch”. If your scaler uses the terminology “Western Larch” in the import file, you’ll want to match on description. This option allows you to map the scaler's import file 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.
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 11 (date) and 12 (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 gross and tare weights, but not the net weight. I can use the import to calculate the value of the net (= gross - tare). See Below. In column 22, I have selected 'Net' as the field to import and the formula '=[13]-[14]' 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.
