Anchor
| Table of Contents |
|---|
Step 1: Complete the Job Import worksheet using the guidelines below
There are three main sections on the Job Import worksheet: Job Information, Cost Codes & Estimates, and Bill Code Numbers.
...
For example, the Job Number field in Job Cost is limited to seventeen characters; therefore, if you are creating a new job in the custom spreadsheet, the Job Number must be seventeen characters or less. If you exceed the number of characters, Job Cost will not be updated with the new job. Be sure to check the information that you import for accuracy.
Unused fields
The following fields are not used with this release of Job Import:
- Project Billing,
- ContractMaxBillAmount
- Exclude from POC Calculation
Required fields
Required fields are marked clearly in red. If you attempt to validate or update Job Cost with any required fields blank, those fields are marked with an error in the JobImportResults.html import log file.
The remaining (non-required) fields can be completed as necessary, either in the worksheet or in Job Cost after the data has been imported.
Date fields
Dates must be entered into date fields using the following format only:
YYYY-MM-DDFor example, 2015-05-22
The year must be four digits; the month, two digits; and the day, two digits (with single digit months and days padded with a "0"). The year, month, and day must each be separated with a dash, and the order cannot be switched.
About specific fields
- Cost Code Number field
The cost code you enter must exist as a master cost code in Job Cost and match the cost code format set up in Job Cost. - Cost Element field
You must enter the number that represents the cost element (for example "1" for Labor); you cannot enter the description (for example, just "Labor"). - Division
You can only change divisions for a job if you are NOT using the Percentage-of-Completion posting option in Job Cost and you ARE using the "Change division after costs are posted to a job" setup option. You cannot change the division for a job if any of the following exist for the job: 1) unposted costs, 2) unposted invoices, 3) committed costs. - Bid Due Date and Create Date fields
These fields are informational only and do not appear on any Job Cost windows; they are stored in the tables for quick retrieval. - Estimate Amount
This field (in the Import - Cost Codes & Estimates section) is automatically calculated based on the Estimate Amount/Unit and Estimate Units columns. DO NOT delete the Estimate Amount cells. If you have to clear and start over, clear ONLY the Estimate Amount/Unit and Estimate Units fields.
Creating estimates to import
When you use the Job Import worksheet to import cost codes and estimates into Job Cost, those estimates are handled differently depending on whether the Estimate Cost by Period option is turned on in Posting Options. The system date determines what period the transactions are assigned to.
...
- If the Estimate Cost by Period option is ON, the system accumulates estimates from the multiple estimate entries. In this example, the total estimate for the cost code is 30 units at $3,000. If you were to create an additional estimate for the month of June on 6/08/15, for 5 units at $500, the total estimate for the cost code would be 35 units at $3,500.
- If the Estimate Cost by Period option is OFF, the system replaces the first estimate with the second estimate. In this example, the new estimate for the cost code is 20 units at $2,000.
Step
...
2: Validate the information entered on the worksheet
When you finish entering information on the Job Import worksheet, the data should be validated before you actually update Job Cost. This allows you to see whether the update is going to be successful based on the requirements of the Job Cost system. If, for example, you have entered an invalid cost code, validating the information catches and allows you to correct that error.
To validate your data without updating Job Cost, choose Validate Only. When the validation is complete, the Job Cost Import Log appears, displaying any errors that were encountered during validation. Refer to Viewing the Job Cost Import Log file jobcostimportlogfile below for more information.
...
The Job Cost Import Log is also saved in the Job Import installation folder as JobImportResults.html if you need to view it again later.
Step
...
3: Update Job Cost with the changes from the worksheet.
When you have completed and successfully validated the Job Import worksheet, you are ready to import the information into Job Cost. After the import, the corresponding Job Cost records are updated and/or created.
...
The Job Cost Import Log is also saved in the Job Import installation folder as JobImportResults.html if you need to view it again later. See Viewing the Job Cost Import Log file jobcostimportlogfile below.
| Note |
|---|
If you add project tasks after the initial update to Job Cost, you have to manually add the cost codes in Job Cost. The import does not create new cost codes in Job Cost; it only updates data for existing cost codes. |
| Anchor | ||||
|---|---|---|---|---|
|
The Job Cost Import Log summarizes all the cost code information that was successfully and unsuccessfully imported into Job Cost, including cost code subtotal amounts. This detailed information is useful when there are multiple worksheet entries for the same cost code.
Adding more fields for a custom spreadsheet
You can create a custom spreadsheet by populating the Job Import worksheet with additional fields for importing in Job Cost. You may want to make a copy of the default Signature Job Import.xlsm spreadsheet to customize.
| Note |
|---|
These additional fields cannot be validated. Make sure valid values are entered before importing. |
- Open the custom spreadsheet.
- If macros have been disabled, enable content for the spreadsheet.
- Select the Job Import worksheet.
- Scroll down to locate row #59, which contains the headings for fields in the Cost Codes & Estimates section. This is where you can add more fields.
- Select column I, which is the blank column immediately after the Estimate Measure column. You can add fields beginning with column I.
The labels for these columns must match exactly what is shown in the right column of the table below; otherwise, data entered into those fields will not be imported. Pay close attention to capitalization.
...
WS_Manager_ID
...
ManagerID
...
WS_Inactive
...
Inactive
...
Profit_Type_Number
...
ProfitTypeNumber
...
Profit_Amount
...
ProfitAmount
...
Next_Subdivision_Type
...
NextSubdivisionType
...
WS_Account_Index_2
...
AccountIndex2
...
Track_Production_Qty
...
TrackProductionQty
...
Production_Estimate_Qty
...
ProductionEstQty
...
Production_Actual_Qty
...
ProductionActualQty
...
Production_Qty_Curr_Per
...
ProductionQtyCurrPer
...
Production_Best
...
ProductBest
...
Production_Best_Date
...
ProductionBestDate
...
Production_Measure_Code
...
ProductionMeasureCode
...
Bill_Type
...
BillType
...
Sched_Completion_Date
...
ScheduleCompletionDate
...
ACTCOMPDATE
...
ActualCompletionDate
...
Schedule_Start_Date
...
ScheduleStartDate
...
ACTSTARTDATE
...
ActualStartDate
...
Billing_Schedule_Line
...
BillingScheduleLine
...
WRKRCOMP
...
WorkComplete
...
Labor_Group_Name
...
LaborGroupName
...
User_Define_1
...
UserDefine1
...
User_Define_2
...
UserDefine2
...
USERDEF1
...
USERDEF1
...
USERDEF2
...
USERDEF2
...
User_Def_Integer_1
...
UserDefInteger1
...
User_Def_Integer_2
...
UserDefInteger2
...
User_Defined_Integer_3
...
UserDefInteger3
...
User_Defined_Integer_4
...
UserDefInteger4
...
User_Defined_Dollar_1
...
UserDefDollar1
...
User_Defined_Dollar_2
...
UserDefDollar2
...
User_Defined_Dollar_3
...
UserDefDollar3
...
User_Defined_Dollar_4
...
UserDefDollar4
...
USRDAT01
...
UserDefDate1
...
USRDAT02
...
UserDefDate2
...
User_Defined_Date_3
...
UserDefDate3
...
User_Defined_Date_4
...
UserDefDate4
...
User-Defined_CB_1
...
UserDefCheckBox1
...
User-Defined_CB_2
...
UserDefCheckBox2
...
User-Defined_CB_3
...
UserDefCheckBox3
...
User_Defined_CB_4
...
UserDefCheckBox4
...
VNDRNMBR
...
VendorID
...
ITEMNMBR
...
ItemNum
...
PORDMNMR
...