ToolWatch’s integration to Viewpoint Vista leverages Vista’s robust, and configurable import module. This allows customers to have a wide range of flexibility as to exactly how Equipment cost, calculated in ToolWatch, flows back into Vista.
This document serves as a guide on how to set up a best-practices configuration based on our experience working with many other ToolWatch and Vista customers. However, as always, the right approach is the approach that works for your business, so please feel free to deviate from this guide wherever appropriate.
The ToolWatch Vista integration will periodically deliver a CSV file for the customer to import into Vista. One CSV file will be delivered for each batch of posted, tool-related Job Costs in ToolWatch. The file is delivered via a download link in an email, which is automatically sent out after each ToolWatch posting.
Each CSV file contains all the relevant ToolWatch and Vista data necessary in order to bring the charges back into Vista. Specifically, the CSV file contains the following columns:
Field Number | Field | Description |
1 | EquipmentCo | The Vista Company that the Equipment belongs to. |
2 | Equipment | The Vista Equipment Code of the item the costs were accrued to. |
3 | PostedOn | The date and time that the charges were posted within ToolWatch. |
4 | CutOffDate | The costs in this batch will be the costs accrued between the last posted cut-off date in ToolWatch and this CutOffDate. |
5 | JCCo | The Vista Company that the Job belongs to. |
6 | Job | The Vista Job the costs are related to. |
7 | Phase | The Vista Phase the costs are related to. |
8 | CostCenterId | The internal ToolWatch ID of the Cost Center these costs are related to. ToolWatch Cost Centers map to Vista Jobs. This is not directly needed by Vista, but it is provided for traceability. |
9 | CostCodeId | The internal ToolWatch ID of the Cost Code these costs are related to. ToolWatch Cost Codes map to Vista Phases. This is not directly needed by Vista, but it is provided for traceability. |
10 | GLChargeAccount | In ToolWatch a GL Charge Account can be specified per Cost Center (Vista Job). If one is specified, then this is that value. |
11 | GLCreditAccount | In ToolWatch a GL Credit Account can be specified per Cost Center (Vista Job). If one is specified, then this is that value. |
12 | CalculationMethod | Indicates whether the costs in ToolWatch were calculated on a "Calendar" or "Assignment" basis. |
13 | TotalCharge | The total amount, in this posting period, that is being charged to Job for the Equipment. If the CalculationMethod is "Assignment", then this is a lump sum charge. If the CalculationMethod is "Calendar", then this is equal to the RatePerHour multiplied by the Hours. |
14 | Hours | If the CalculationMethod is Assignment then this field will be blank. Otherwise this field will contain the fractional hours that the Equipment was on the job in the posting period. |
15 | RatePerHour | If CalculationMethod is Assignment then this field will be blank. Otherwise this field will contain the effective rate at which the Equipment was charged. It is an effective rate because rate migrations (such as a transition from a weekly to a monthly rate) may have occurred during the period. |
16 | ItemId | The internal ToolWatch ID of the tool the costs were accrued to. Provided for traceability. |
17 | JobKeyId | The Vista Job KeyID. Provided for traceability. |
18 | PhaseKeyId | The Vista Phase KeyID, when applicable. Provided for traceability. |
19 | EquipmentKeyId | The Vista Equipment KeyID. Provided for traceability. |
20 | BillingEntryId | The internal ToolWatch ID of the billing entry that produced this charge. Provided for traceability. |
21 | BillingEntryType | The type of the ToolWatch billing entry. Provided for traceability. |
22 | TransferInDate | The date when the charges for the tool began. In date is the date of the originating transfer to the job. Tools begin billing when transferred into the job. |
23 | TransferOutDate | The date when the charges for the tool ended. The date of the transfer off the job. If a tool has stopped billing, this will hold the last date it billed. If the tool is still on the job, it will be blank. |
24 | CategoryNumber | The category number from the tool's model. |
25 | CategoryDescription | The category description from the tool's model. |
26 | Owner | The ToolWatch owner of the tool. Provided for traceability. |
Fields 16-26 are provided for traceability only and are not required by the Vista import; the recommended import template below uses only fields 1-15.
Part 1: Create the TWEMUsage Import Template in Vista
In order to import the Equipment costs into Vista we first need to set up an Import Template.
Go to the Imports module.
Launch the IM Template function.
Make sure the Grid tab is open.
In the list, right-click the EMUsage row and select Copy Record.
Scroll down in the grid to the empty row with an asterisk (*) in the left gutter, right-click the row, and select Paste Record.
Change the template name to TWEMUsage.
Change the description to TW EM Usage.
Click the Save icon on the top toolbar.
Click the Info tab for the new template.
Make sure the format is set to delimited.
Make sure the delimiter is set to Comma.
Make sure Starting Line Number is set to 2.
Important: Starting Line Number 2 tells Vista to skip the first row in the CSV file. That row has the CSV column headers.
Screenshots for Creating the TWEMUsage Import Template in Vista
Part 2: Map Fields and Set Default Values on the Template Detail Tab
This is really the step where all the configuration happens. On the Template Detail tab you can select which fields in the CSV map to which fields in Vista, as well as where you supply information such as what revenue code the costs will be associated with.
Click the Template Detail tab.
For each column in the Field Mappings table below, enter the number in the Record Column.
For each of those columns, make sure the Use Viewpoint Default checkbox is cleared.
For the BatchId column, make sure the Use Viewpoint Default checkbox is selected.
For the BatchSeq column, make sure the Use Viewpoint Default checkbox is selected.
In the User Default Value column, enter the values from the Standard Default Values table below for Source, BatchTransType, Description, and EMTransType.
In the User Default Value column, enter your company's values for GLOffsetAcct, JCCostType, and RevCode. The Company-Specific Values table below shows examples.
Field Mappings
The first thing you will want to do is specify how the ToolWatch CSV fields map to Vista fields. This is done by placing a value in the "Record Column" that indicates the number of the field in the CSV that should be mapped, and ensuring that the "Use Viewpoint Default" checkbox is unchecked. ToolWatch recommends the following mappings:
Column Name | Record Column | Maps to ToolWatch CSV Field |
Co | 1 | EquipmentCo |
Mth | 3 | PostedOn |
ActualDate | 4 | CutOffDate |
Equipment | 2 | Equipment |
JCCo | 5 | JCCo |
Job | 6 | Job |
JCPhase | 7 | Phase |
RevRate | 15 | RatePerHour |
RevDollars | 13 | TotalCharge |
RevTimeUnits | 14 | Hours |
(The "Maps to ToolWatch CSV Field" value is provided above for informational purposes only. It does not need to be entered into Vista.)
Standard Default Values
Once you have the essential fields mapped there are just a few decisions that need to be made. You already have the per-equipment item and per-job data mapped. Now you will need to decide essentially how to specifically code and account for these charges.
First, there are four fields that are configurable per-customer, but which are generally set to the same values for all customers. You configure these fields by entering a value into the "User Default Value" column. This will ensure that each and every record imported uses these values:
Column Name | User Default Value |
Source | EMRev |
BatchTransType | A |
Description | ToolWatch Equipment Usage |
EMTransType | J |
Company-Specific Values
Now the only columns left to configure are the ones that are truly unique to your install. You will set these by entering a value into the "User Default Value" cell:
Column Name | Example Value | Description |
GLOffsetAcct | 90400.0101 | The GL account to use for the offsetting entry. |
JCCostType | 4 | The CT value of the JC Cost Type. Often this is the "Equipment" Cost Type. |
RevCode | 1 | This should correspond to an hourly revenue code, as ToolWatch will report the time units in fractional hours. |
Screenshots for Mapping Fields and Setting Default Values in the Vista TWEMUsage Template
Part 3: Test the TWEMUsage Import Template
Once your import template is set up, you can request a sample export file from your ToolWatch implementation team. Using this file you can test your configuration by doing an import into your Vista sample company.
Ask your ToolWatch implementation team for a sample export file.
Import the sample file into your Vista sample company.
Inspect the import in the work edit.
Upload the import.
Open the EM Usage Posting program.
Make sure you see postings like the ones in the screenshot below.
Screenshots for Testing the TWEMUsage Import Template in Vista