T&M Import for all programs

Overview

All programs that have entries for T&M records now have the ability to import data from a CSV file as defined below. For TMC & PO invoicing, users can now import Detail records as well. Here are the features now available:

TMC & PO: Import Detail records

TMC & PO: Import Multi-Time records. Each time record has a corresponding detail number to link it to

TMC, PO, OVMT, VMC: Import Single-Time records. Time records will be imported to selected detail record

The T&M Import allows a user to import time and material data into VMC, TMC, PO and OVM Work Request/Invoice/Work Order. T&M import works identically to the previously used Power BI Import, except that the CSV file only contains the fields needed.

Using T&M Import

Nexus will look in the folder defined in the “Import CSV Files” file path setting on the Options dialog.

After importing the data, the user is presented with a dialog indicating success as well as how many records were added, skipped and why. Detailed information is also written to the error log.

Existing Time Records

Enhancement added 2024-02-02.

Existing Time records will be flagged as deleted if not already, so that after running the import, only the imported Time records are not flagged for deletion. The deleted records remain visible, albeit crossed out with their delete box checked, however the user can still elect to uncheck some or all. The deletes are only pushed to the server when the user saves their changes.

T&M Import Spec

T &M Import has been split into three imports. The original T&M Import is now called “Single-Time Import”. The new imports are “Detail Import” and “Multi-Time Import”.

Selecting one of these options will prompt you for the CSV file you wish to import. The import path shown above is the default location where Nexus will look, but you can navigate to a different folder if needed. You can also change the default path for Nexus to look using

Time Import Options

When the user selects Single-Time or Multi-Time import they will be able to select how the time records are grouped together.

· Auto-combine time records based on description: This is how time data was always grouped prior to this release. If a description is provided, then items are grouped on their description + rate code value. If there is no description, then records are grouped on rate code alone.

· Add one time record per line: A new time record will be added for each line read from the CSV file. No grouping of time records will take place.

Single-Time Import

Single-Time Import is the original T&M Import. Column names are now required for all CSV imports and must be in the order specified below. Columns names allow Nexus to ensure there is no DetailNum column provided (indicating the user meant to run the Multi-Time import). Make sure you first select the detail record you want to import time data to.

The following values are expected at the specified columns:

Column Name	Column	Description
Contractor	A	The contractor code for this record.
WorkDate	B	The work date for this record (MM/DD/YYYY)
BillingCode	C	The billing code for this record.
Qty	D	The qty (hours) to use for this item.
Description	E	Optional description. If not provided or blank, then description will be filled in with BillingCode.

After reading all the data from the CSV and performing post-processing validation (see Import Validation below) Nexus will group imported data by billing code and sum the hours for each day of the week for each billing code. Each CSV record imported represents one hour of work for its billing code for the work date indicated. An Invoice Detail Time record will be generated for the billing code and the hours.

Import Validation

Validation for the CSV import is handled by Nexus (not via stored procedure). Nexus performs validation when parsing the data from the CSV file, as well as before finally creating the Invoice Detail Time records on behalf of the imported data. To prevent accidentally trying to import a multi-time file, the single-time import will check for and disallow a column called DetailNum.

Contractor Code

(WorkOrderVendorCode)Contractor code cannot be null or empty in the CSV file.

Contractor code must match the contractor code of the Invoice Detail record the time data is being imported into.

Work Date

(WOLIActualEnd) Work Date must be parse-able as a date from the CSV file.

Work Date must be within the work week for the Invoice Detail record the time data is being imported into.

Billing Code

(WOLIActualWorkCode) Billing Code cannot be null or empty in the CSV file.

Billing Code must be valid for the Invoice Detail record the time data is being imported into. If the billing code happens to match to a L_TrimT.sTrimCode value that also has its bPowerBI_Ignore flag set, then that billing code will be considered invalid and that item will be skipped.

NOTE: Skipping billing codes that match to a bPowerBI_Ignore flagged trim code was done as work-around for SalesForce dumping these codes, which will never match to a valid rate code. It was intended to allow Nexus to skip these entries.

Prescription Brush Quantity (aka Hours, aka Units)

(PrescripBrushQty) Brush Qty will be used as-is if the value is numeric. If the value cannot be converted to a numeric value then the line will be skipped.

A value of 1 will be used if the PrescrpBrushQty column is missing (i.e., the CSV does not have enough columns to contain this field).

Description

Description, if not empty, will be used to combine time records into a single row based on code+description.

Multi-Detail Import

Multi-Detail Import allows the user to import one or more Detail records. Importing Detail causes any existing Detail records to be flagged as deleted. The user can uncheck the Delete box to preserve a record. Changes are not saved until the user saves their changes on the Edit Form.

Multi-Detail import uses column header names, not index location, to determine the field. The column headers are case-sensitive.

The DetailNum column is used to link to Time records subsequently imported using Multi-Time import.

TMC Import

Column Name	Description
Contractor	The contractor code
DetailNum	Detail number. This number is analogous to WR Sort. For the import this value is used to link to time records imported via Multi-Time import.
WeekEnding	The week ending value
StreetNum	Street number
Street	Street name
Units	Units value
Crew	The crew name
ContWR	Contractor WR number
Duration	Duration code, must be from the L_Duration lookup.
PTV	Pass-through vendor
PTI	Pass-through invoice number
Activity	Activty/Bill Code value

PO Import

Column Name	Description
Contractor	The contractor code
DetailNum	Detail number. This number is analogous to WR Sort. For the import this value is used to link to time records imported via Multi-Time import.
WeekEnding	The week ending value
Vendor Ref#	Vendor reference number
Description	Description value
ContWR	Contractor WR number
Duration	Duration code, must be from the L_Duration lookup.
PTV	Pass-through vendor
PTI	Pass-through invoice number

Multi-Detail Validation

The following validation takes place on the incoming Detail data:

· DetailNum cannot be used more than once

· DetailNum must be a number.

· Duration is required

· Duration Code must be from the L_Duration lookup.

· Week Ending date must be a date

· Week Ending date must match the current invoice’s week ending date

· Units must be a number (TMC)

· Activity must be from the L_Activity lookup. (TMC)

Multi-Time Import

Multi-Time import allows the user to import a CSV file containing multiple time records for one or more Detail records. The incoming Time records are linked to detail records using the DetailNum value.

TMC/PO Import

Column Name	Description
Contractor	The contractor code
DetailNum	Detail number, analogous to WR Sort, this value is used to link to the parent Detail record.
WorkDate	Work date the time record applies to.
BillingCode	The rate code for the time record.
Qty	The quantity/hours value.
Description	Description for this time record.