Connection Import/Match Guide - Revision history

Kamar at 16:19, 28 August 2024

2024-08-28T16:19:52Z

Freddy at 21:56, 13 February 2019

2019-02-13T21:56:35Z

Freddy at 14:48, 17 January 2019

2019-01-17T14:48:55Z

Freddy at 14:28, 17 January 2019

2019-01-17T14:28:40Z

Freddy at 14:27, 17 January 2019

2019-01-17T14:27:26Z

Freddy at 14:26, 17 January 2019

2019-01-17T14:26:37Z

Freddy at 14:25, 17 January 2019

2019-01-17T14:25:29Z

Show changes

Freddy at 21:55, 16 January 2019

2019-01-16T21:55:24Z

Freddy at 21:40, 16 January 2019

2019-01-16T21:40:30Z

Freddy: Created page with " ==General Data Import Notes== Refer to the Data Import General Information page for information and guidance on general import timeline, formatting import templates, as..."

2019-01-16T20:40:23Z

Created page with " ==General Data Import Notes== Refer to the Data Import General Information page for information and guidance on general import timeline, formatting import templates, as..."

New page

==General Data Import Notes==

Refer to the [[Data Import General Information]] page for information and guidance on general import timeline, formatting import templates, as well as how to ensure you are using the most current template information for data imports.

==Connection Import/Match Notes==

===Timing and Preparation===

The Standard Task Import is a foundational step in using the Task Management or Time Billing systems. It should occur as soon as the required Task Management Rule Maintenance records have been completely set up. Follow the [[Task Management Setup Guide]] for details on the required setup procedures.

The Standard Task Import can also be run to update existing task records where the task code matches.

==Connection Import/Match File Data Scrubbing==

Every customer's data will have different issues that need addressing. Some of the issues will not make themselves visible until after the data has been imported and is in use during parallel testing. This is why it is critically important to perform an early import, and keep accurate notes on issues that need to be addressed on a supplemental import, or for a complete re-import. Some common things that need attention during Sales Order data scrubbing include:

* '''Codes''' - The import relies heavily on the code values from the corresponding Rule Maintenance records. Review each task, or group of tasks carefully to ensure that the correct codes are being used for the correct columns.

* '''Task''' - Work with customer to develop a task numbering/naming scheme that makes sense. Consider how the tasks will sort in look-ups, and what formatting features of the task number/name will be most helpful to end-users. If using a numbered system, carefully review for duplicates, and make sure that Excel auto-fill hasn't altered any of the numbers.

* '''Descrip''' - Standardize terms and review spelling on the descriptions. If time allows, standardize capitalization.

* If duplicate tasks (based on the f1 - Task column) are found in the file, only the last task found on the spreadsheet will be imported. Review carefully for duplicated tasks.

==Connection Import/Match Screen (IMPSTANDTASK)==

Menu Location: Transaction>>Importers/Exporters>>Task and Time Billing>>Standard Task Import

[[File:IMPSTANDTASK.png]]

'''File Name/Browse:''' Use the Browse button to locate and select the completed Standard Task Import template file (in XLS format).

'''Export Template:''' Generates a blank Standard Task Import template file

'''Map Fields:''' Fields must be mapped prior to importing. If no changes have been made to the column headings, the mapping screen should show all green, and you can click OK to continue. If any of the Input Field Name columns on the left are red, single-click on the line on the left column, and then double-click the desired mapped field in the right column to complete the mapping. Repeat for any red lines on the left that should be mapped. If there are additional columns in the source file that should NOT be mapped, they can be left unmapped (displayed in red). Mandatory fields will require that they be mapped before clicking OK.

==Connection Import/Match File Definitions==

Required fields are indicated with an *

'''*f1 - Task''' - Standard task code, number, or name (max 50 characters). This is the first line of the Standard Task field in the Standard Task screen.

'''*f2 - Descrip''' - Standard task description (max 50 characters). This is the second line of the Standard Task field in the Standard Task screen.

'''*f3 - DCode''' - Task Department Code from the Departments (DEPT) rule. Must be a valid department code.

'''*f4 - CCode''' - Task Priority Code from the Task Priority Codes (TASKCLASS) rule. Must be a valid class/priority code.

'''*f5 - WCode''' - Task Work Category Code from the Task Work Categories (WORKCAT) rule. Must be a valid work category code.

'''f6 - LCode''' - Task Service Order Group Code from the Service Order Groups (INSGRP) rule.

'''f7 - DueTime''' - Due Date Offset value entered as a date code, used to calculate the task due date from the task scheduled date. Valid date codes are ##d (for days), ##w (for weeks), or ##m (for months).

'''f8 - Admin''' - Enter a 'Y' to check the Admin box on the Options tab of the Standard Task screen. Leave blank or enter 'N' to leave the box unchecked.

'''f9 - NonBill''' - Enter a 'Y' to check the Non-Billable Task box on the Options tab of the Standard Task screen. Leave blank or enter 'N' to leave the box unchecked.

'''f10 - InHouse''' - Enter a 'Y' to check the In-House box on the Options tab of the Standard Task screen. Leave blank or enter 'N' to leave the box unchecked.

'''f11 - AlertStandIn''' - Enter a 'Y' to check the Alert Stand-In box on the Options tab of the Standard Task screen. Leave blank or enter 'N' to leave the box unchecked.

'''f12 - InspectReq''' - Enter a 'Y' to check the Inspection Required box on the Options tab of the Standard Task screen. Leave blank or enter 'N' to leave the box unchecked.

'''f13 - CheckDup''' - Enter a 'Y' to check the Check for Duplicates box on the Options tab of the Standard Task screen. Leave blank or enter 'N' to leave the box unchecked.

'''f14 - ExtAuth''' - Enter a 'Y' to check the External Authorization Required box on the Options tab of the Standard Task screen. Leave blank or enter 'N' to leave the box unchecked.

'''f15 - IntAuth''' - Enter a 'Y' to check the Internal Authorization Required box on the Options tab of the Standard Task screen. Leave blank or enter 'N' to leave the box unchecked.

'''f16 - ExcGrid''' - Enter a 'Y' to check the Exclude from Grid box on the Options tab of the Standard Task screen. Leave blank or enter 'N' to leave the box unchecked.

'''f17 - Milestone''' - Enter a 'Y' to check the Milestone box on the Options tab of the Standard Task screen. Leave blank or enter 'N' to leave the box unchecked.

==Connection Import/Match Reconciliation==

Reconciling imported data should begin with spot-checking several records field-by-field for complete data import. Pick records from the source file that have the most data columns filled in. Verify that all source file data fields imported correctly and display as expected.

Run the Standard Task List report for the imported records. Review the report for basic details and look for any missing or incorrect records.

==Connection Import/Match Additional Steps==

Manually update any additional details, such as detailed notes, resources, parts, solution codes, and any other fields that are not available on the import.

==Connection Import/Match Database Tables==

'''RTSTASK''' - Each imported standard task creates a new KEYNO in the this table.

[[Category: Tasks and Time Billing]]
[[Category: Import Guides]]

← Older revision		Revision as of 16:19, 28 August 2024
Line 1:		Line 1:
−		+	__NOTOC__
	==General Data Import Notes==		==General Data Import Notes==

@@ Line 27: / Line 27: @@
 The obsolete connection import programs are listed below:
-* Connection Import/Match (Old) - WATERMATCH
+* Connection Import/Match (Old) - WATERMATCH (all of the functionality exists in WATERMATCH2, plus enhancements)
-* Water District Update - WATERUPDATE
+* Water District Update - WATERUPDATE (Combines 3 files to make a new file, older screen)
-* District Meter Import - WATERIMPORT
+* District Meter Import - WATERIMPORT (no checks for duplicate records, no auto numbering)

@@ Line 14: / Line 14: @@
 The following Organization Attributes need to exist in the Organization Attributes (ATT_ENT) rule before creating Districts, Builders, Subdivisions, and Connections:
-* Standard Customer attributes (BILLTO, SOLDTO, SHIPTO) - The SOLDTO attribute description is commonly relabeled as 'Dsitrict'
+* Standard Customer attributes (BILLTO, SOLDTO, SHIPTO) - The SOLDTO attribute description is commonly relabeled as 'District'
 * Builder attribute (with BUILDERDEP form name)
 * Subdivision attribute

@@ Line 109: / Line 109: @@
 '''f16 - Lot''' - Lot number in the Connections jump screen (alphanumeric, 50 characters max)
-'''*f17 - Service Address''' - Service connection street address. This value is set as the Organization company name as well as the Address1 value.
+'''*f17 - Service Address''' - Service connection street address. This value is set as the Organization company name as well as the Address value.
 '''f18 - Email''' - Primary email address associated with the service connection

← Older revision		Revision as of 14:27, 17 January 2019
Line 47:		Line 47:


−	==Connection Import/Match Screen (~~IMPSTANDTASK~~)==	+	==Connection Import/Match Screen (WATERMATCH2)==

	Menu Location: Transaction>>Importers/Exporters>>Task and Time Billing>>Connection Import/Match		Menu Location: Transaction>>Importers/Exporters>>Task and Time Billing>>Connection Import/Match

@@ Line 49: / Line 49: @@
 ==Connection Import/Match Screen (IMPSTANDTASK)==
-Menu Location: Transaction>>Importers/Exporters>>Task and Time Billing>>Connection IMport/Match
+Menu Location: Transaction>>Importers/Exporters>>Task and Time Billing>>Connection Import/Match
 [[File:WATERMATCH2.png]]

← Older revision		Revision as of 21:55, 16 January 2019
Line 67:		Line 67:
	'''Create Subdiv/Builders:''' If checked, the import process will assign the SUBDIV and BUILDER attributes to the organization records, and create the required parent links to the associated Districts		'''Create Subdiv/Builders:''' If checked, the import process will assign the SUBDIV and BUILDER attributes to the organization records, and create the required parent links to the associated Districts

−	'''Match X Characters in Street Name:''' Set the number of characters the matching logic should use to scan for matches in the street address value. ~~For example~~, you ~~would need to set~~ the ~~value to at least 10 to avoid matching 1201 Oak Court and 1201 Oak Lane~~.	+	'''Match X Characters in Street Name:''' Set the number of characters the matching logic should use to scan for matches in the street address value. Exact matches are automatically processed as a match. Otherwise, the system will scan X characters to present a grid of possible matches, where you can select the appropriate matched record, or choose No Match.
		+
		+	[[File:WATERMATCH2_Matching.png]]

@@ Line 10: / Line 10: @@
 ===Timing and Preparation===
-The Standard Task Import is a foundational step in using the Task Management or Time Billing systems. It should occur as soon as the required Task Management Rule Maintenance records have been completely set up. Follow the [[Task Management Setup Guide]] for details on the required setup procedures.
+The Connection Import can occur after the District Organization records have been added. If the customer will be tracking Builder and/or Subdivision information, Organization records for builders and subdivisions will need to be added prior to importing connection records.
-The Standard Task Import can also be run to update existing task records where the task code matches.
+The following Organization Attributes need to exist in the Organization Attributes (ATT_ENT) rule before creating Districts, Builders, Subdivisions, and Connections:
@@ Line 20: / Line 37: @@
 Every customer's data will have different issues that need addressing.  Some of the issues will not make themselves visible until after the data has been imported and is in use during parallel testing.  This is why it is critically important to perform an early import, and keep accurate notes on issues that need to be addressed on a supplemental import, or for a complete re-import. Some common things that need attention during Sales Order data scrubbing include:
-* '''Codes''' - The import relies heavily on the code values from the corresponding Rule Maintenance records. Review each task, or group of tasks carefully to ensure that the correct codes are being used for the correct columns.
+* '''DCustNo''' - The district customer number is how the imported service connections are linked to the water district. It can also be used to establish the connection customer number. Review the data in this column carefully to ensure that connection addresses are linked to the correct district.
-* '''Task''' - Work with customer to develop a task numbering/naming scheme that makes sense. Consider how the tasks will sort in look-ups, and what formatting features of the task number/name will be most helpful to end-users. If using a numbered system, carefully review for duplicates, and make sure that Excel auto-fill hasn't altered any of the numbers.
+* '''Account''' - The account column is the customer number associated with the service connection record. The connections can be manually numbered, or if the account column is left blank, the system can assign a sequential number for each imported connection. The system will use the district customer number followed by a dash and then a sequential number starting with 1 (or from the highest number already used in that district) using the spreadsheet order to set the sequence. For example, if your district customer number is 71, the system will generate the connection numbers as 71-1, 71-2, 71-3, and so on.
-* '''Descrip''' - Standardize terms and review spelling on the descriptions. If time allows, standardize capitalization.
+Keep in mind that the customer number field only allows for 10 characters max. So, if you district number is long, or you have thousands of connections to import, you may run into auto-numbering issues since the system can't create a customer number over 10 digits.
-* If duplicate tasks (based on the f1 - Task column) are found in the file, only the last task found on the spreadsheet will be imported. Review carefully for duplicated tasks.
+* '''Address Info''' - Standardize address details as much as possible. Review for misspellings, extra spaces, and anything that could affect the quality of the data. Review any issues with the customer before altering the source data. If time allows, standardize capitalization.
@@ Line 32: / Line 49: @@
 ==Connection Import/Match Screen (IMPSTANDTASK)==
-Menu Location: Transaction>>Importers/Exporters>>Task and Time Billing>>Standard Task Import
+Menu Location: Transaction>>Importers/Exporters>>Task and Time Billing>>Connection IMport/Match
-[[File:IMPSTANDTASK.png]]
+[[File:WATERMATCH2.png]]
-'''File Name/Browse:''' Use the Browse button to locate and select the completed Standard Task Import template file (in XLS format).
+'''File Name/Browse:''' Use the Browse button to locate and select the completed Connection Import template file (in XLS format).
-'''Export Template:''' Generates a blank Standard Task Import template file
+'''Export Template:''' Generates a blank Connection Import template file
 '''Map Fields:''' Fields must be mapped prior to importing. If no changes have been made to the column headings, the mapping screen should show all green, and you can click OK to continue. If any of the Input Field Name columns on the left are red, single-click on the line on the left column, and then double-click the desired mapped field in the right column to complete the mapping.  Repeat for any red lines on the left that should be mapped.  If there are additional columns in the source file that should NOT be mapped, they can be left unmapped (displayed in red). Mandatory fields will require that they be mapped before clicking OK.