Some organizations prefer that key fields (Type, Priority) require a selection by the end-user instead of having a default value populate for new Work Orders. Here, we will explain how to run the attached scripts to adjust the 'Default Type' and 'Default Priority' Work Order Preferences in your database to not have a value, and thus require a unique selection upon Work Order creation.

Actions These Scripts Perform

The included scripts will update the Work Order Preferences for the Default Type and Default Priority fields to default to a (Not Specified) value for all Repair Centers. After running scripts to update the Preferences, the Work Order Type and Priority fields will be left blank on new Work Orders to require the input of a unique value from the dropdown list.

Note: These settings will affect Work Orders that are manually created within the MRO/TWC and MC Express interfaces. You will need to manually set the Type and Priority fields to be required in the Service Requester via the Service Requester Configuration tool.

Instructions to Download and Install

On-Premise Environment: Based upon your company hosting its own database, your IT personnel or someone with server access will need to follow the below instructions:

1. Open the desired SQL file(s):

   • Set WO.Priority Preference to NULL.sql

   • Set WO.Type Preference to NULL.sql

2. Copy contents into a new Query window in your SQL Server Management Studio (SSMS).

3. Execute SQL script against your MC entity database prefixed with "ENT".

Cloud Hosted Environment: Based upon your database being hosted in our Data Center, you will need to make a request to our Support team.

In certain cases, organizations need the ability to quickly identify Work Orders that have important business related attachments including Documents, Images, PDF's Excel worksheets, etc. By utilizing the Attachment field on the Work Order Details tab, organizations can leverage the indicator to quickly pinpoint Work Orders that have attachments by using Smart Reports, conditional formatting, or notification Rules.

This section provides the SQL script to build a Database Job in your system that updates the Attachment Work Order check box field to be enabled whenever an applicable attachment is present. Instructions also include how to enable the Database Job once it has been added to your system.

Nightly Actions This Script Performs

This SQL script updates the Attachments field on the Work Order Details tab when there is an attachment associated with the Work Order:

IMAGE

Instructions to Download and Install

On-Premise Environment: Based upon your company hosting its own database, your IT personnel or someone with server access will need to follow the below instructions:

1. Open the SQL file: MC_Sync_WO_Attachment_Indicator.sql

2. Copy contents into a new Query window in your SQL Server Management Studio (SSMS).

3. Execute SQL script against your MC entity database prefixed with "ENT".

Cloud Hosted Environment: Based upon your database being hosted in our Data Center, you will need to make a request to our Support team.

Steps to Enable Database Job

• Once the script has been run, you can access the Database Job by clicking to Tools>System>Database Job Scheduler...:

• Locate the correct Scheduled Job (Update Attachment Indicator to Reflect Attachment), highlight and then click Edit.

IMAGE

• Now you can Enable the Job and adjust the Frequency and Next Scheduled Date as needed:

IMAGE

Note: Due to existing nightly processes it is ideal to schedule the Database Job Prior to 12:00AM

• The data that will be updated on the Work Order Details tab.

The purpose of the script given in this segment is to create a rule that will automatically assign the Work Order Responder to a new Work Order if it's not already assigned to someone.

• Create a Notification Rule based on the WO Responded Event trigger.

• Select the Run Database Script option in the Actions tab.

• Paste this SQL script into the text box:

						IF NOT EXISTS
						(SELECT WOPK FROM WOAssign 
						WHERE WOPK =
						(SELECT WOPK FROM WO WITH (NOLOCK) 
						WHERE [EVENTRECORD]))
						BEGIN
						INSERT INTO WOAssign(
						  IsAssigned
						,WOPK
						,LaborPK
						,LaborID
						,LaborName
						,AssignedDate
						,AssignedHours)
						SELECT 1
						,(SELECT WOPK FROM WO WITH (NOLOCK) 
						WHERE [EVENTRECORD])
						,laborpk
						,LaborID
						,LaborName
						,GETDATE()
						,TargetHours
						FROM WO
						JOIN Labor l on l.LaborPK = WO.RowVersionUserPK
						WHERE [EVENTRECORD]
					END

• If the new rule is enabled, when a technician responds to a work order that has not yet been assigned, it will be assigned to the responding technician.

Work Order Troubleshooting Using SQL

Have you ever wondered why one of your Work Orders seemed to have disappeared or why a newly created Member can't be found to approve access to Maintenance Connection? Don't worry... you are not alone!

It's a little known fact that ampersands (&) and apostrophes (') can cause issues when entered into certain fields in Maintenance Connection. The following items discuss the reason behind the issue with examples, and offers several possible solutions.

In its current state, there are a few gaps within Maintenance Connection's code that allow nuances of the SQL Server and HTML languages.

Within the SQL Server and HTML languages, particular characters are "reserved" (specific actions occur when these characters are used) and cause conflicting results within Maintenance Connection.

Examples of Issue

The fields affected by reserved characters can range from Work Order Special Instructions to Document module names. The most common instances, however, occur when a reserved character is located in the Work Order Task fields or module ID fields.

The following will give you a quick look at some of the issues that can arise when reserved characters are present in Maintenance Connection:

• Reserved characters in the Work Order Reason field can cause the Work Order record to not open or disappear from the Work Order List.

• Reserved characters in the Work Order task Reading (Final) field causes the Work Order or attached Asset to not open properly.

• Reserved characters in the VendorID (Company.CompanyID) field can cause a Purchase Order to not be created/updated properly.

The following is a list of some of the other less frequent issues you might encounter:

• Updated data for existing records may not save if associated record(s) have reserved characters.

• PM Procedures with reserved characters may prevent PM Work Order generation.

• Report Criteria selections may be prevented or incorrect formatting of returned data may result.

• Newly signed up Members that need approval may be missing from the system when reserved characters are used in the name, Email address or Password fields.

Steps for Resolution

Although the issues that arise from reserved characters can be intimidating, our Solution Engineer team has a few tricks up their sleeve to get you back on track:

Manual Update: Sometimes the quickest way to fix an issue that is caused by a reserved character is to manually update the field directly on the affected record (if possible).

Smart Reports: No need for drawn out solutions! You can utilize Smart Reports to quickly access and update the fields that have reserved characters.

SQL (Advanced): There are times when a reserved character isn't easy to spot and cannot be removed from the record directly or through a Smart Report. For these situations we can use a SQL Select statement to see if there are any fields with reserved characters.

The following example scripts, based on the example problems outlined above, can be used to locate those harder to find issues:

Finding reserved characters in the Work Order Reason field:

						SELECT * FROM WO WITH (NOLOCK) 
					WHERE (Reason LIKE '%''%' OR Reason LIKE'%&%') AND IsOpen = 1  

Finding apostrophes in the Work Order Task Reading (Final) field:

						SELECT * FROM WOTASK WITH (NOLOCK)
						WHERE Measurement LIKE '%''%'
					

Finding reserved characters in the Company ID field:

						SELECT * FROM Company WITH (NOLOCK)
					WHERE (CompanyID LIKE '%''%' OR CompanyID LIKE '%&%')

If you have ever run into unapproved Work Orders mysteriously disappearing, then this information may be for you. In certain situations, Work Orders can accidentally be created with a status requiring Approval. This problem typically occurs when a user creates a new Work Order and presses the backspace key on the keyboard while not being inside an editable field. The backspace key is a shortcut for the Back button in some browsers and takes the user to the previously visited page.

During this process, the approval flag in the database is set to Required but the Authorization fields (WO.AuthStatus, WO.AuthStatusDesc) are left empty, which ultimately creates the issue. That Home Page then shows Approval Required because of the flag but the Work Order List, which looks for the Authorization fields, will not display the Work Order.

Note: At the moment, the cause for this cannot be 100% validated. If you can consistently recreate this issue, please let our MC Support team know so our developers can resolve the root cause.

Examples of Issue

When a new Work Order has accidentally been created to require Approval, the Approvals Needed button will be highlighted on the Home Page, but the Approvals Needed filter in the Work Order List will return no results.

Steps for Resolution

Here are a few resolution options available to you:

Smart Reports: To get the Work Order to display successfully in the Work Order List, we can utilize a Smart Report to access the missing records. Follow the steps below to download and install this Smart Report:

On-Premise: Based upon your company hosting its own database, your IT personnel or someone with server access will need to follow the below instructions:

1. Open the SQL file: Find WOs Needing Approval (SMART Enabled).sql

2. Copy contents into a new Query window in your SQL Server Management Studio (SSMS).

3. Execute SQL script against your MC entity database prefixed with "ENT".

Cloud Hosted: Based upon your database being hosted in our Data Center, you will need to submit a request through our Support team to install the report.

								SELECT
								   WO.WOPK
								  ,WO.WOID
								  ,WO.Reason
								  ,WO.IsOpen
								  ,WO.Status
								  ,WO.StatusDate
								  ,WO.AuthStatusDate
								  ,WO.AuthStatus
								  ,WO.AuthStatusDesc
								  ,WO.AuthLevelsRequired
								  ,WO.IsApproved
								FROM
								  WO WITH (NOLOCK)
								  INNER JOIN (SELECT
								              (CASE
								                 WHEN w.AuthStatus = 'NOTREQUIRED'
								AND w.AuthStatusDesc != '(Not Required)'
								                 THEN w.WOPK
								                 WHEN w.AuthStatus LIKE 'REQUIRED%'
								AND w.AuthStatusDesc != ('Required - Level ' 
								+ RIGHT(ISNULL(w.AuthStatus , '') , 1))
								                 THEN w.WOPK
								                 WHEN w.AuthStatus = 'APPROVED'
								AND w.AuthStatusDesc != w.AuthStatus
								                 THEN w.WOPK
								                 WHEN w.AuthStatus = 'REJECTED'
								AND w.AuthStatusDesc != w.AuthStatus
								                 THEN w.WOPK
								                 WHEN w.AuthLevelsRequired = 0 AND w.IsApproved = 0
								                 THEN w.WOPK
								WHEN ISNULL(w.AuthStatus, '') = '' 
								AND ISNULL(w.AuthStatusDesc, '') = ''
								THEN w.WOPK
								                 ELSE -1
								               END) AS theWOPK
								             FROM
								                 WO w WITH (NOLOCK)
								             WHERE
								              w.IsOpen = 1) mismatch ON mismatch.theWOPK = WO.WOPK
								WHERE
								mismatch.theWOPK != -1
								ORDER BY
							  WO.WOPK

							UPDATE WO WITH (ROWLOCK)
							SET IsApproved = 1
							,AuthStatus = 'NOTREQUIRED'
							,AuthStatusDesc = '(NotRequired)'
							,AuthLevelsRequired = 0 
						WHERE WOPK = 

Users may occasionally see an error message with the Problem Details referencing incorrect syntax near a keyword. This may happen when creating a Work Order, assigning a Work Order, adding an Asset to a Work Order, or any other event in MC:

IMAGE

Note: The Incorrect Syntax message can occur outside the Work Order module as well, as in this Asset error message:

IMAGE

Reason for Issue

The Incorrect Syntax message often occurs with Work Order Events that are tied to an Assignment or Notification Rule in the Rules Manager. For example, an Assignment Rule may be set up to automatically assign high priority Work Orders to a certain technician. If poor SQL exists in the Assignment Rule, the Incorrect Syntax message may appear whenever a user attempts to create a high priority Work Order, since the Rule is unable to function properly.

Troubleshooting

Please note that the instructions given are meant for customers with On Premise databases. Those with Cloud Hosted environments should submit a request to our Support team to resolve the issue.

Resolving this issue requires some degree of detective work in order to discover the exact Rule tied to the Work Order, and then find the incorrect SQL syntax in the Rule. First, try to locate the associated Rule in the Rules Manager (Tools>Rules Manager). For the example given above, we would be looking for an Assignment Rule for high priority Work Orders.

When we have an idea of the Rule tied to our Work Order, On-Premise customers can look at the SQL script for that rule in the SQL Administrator (Application > SQL Administrator). To do this, run the following query in the SQL Administrator.

					SELECT * FROM MCRule

IMAGE

In the results that display, locate the Assignment Rule you found in the Rules Manager.

Next, check the SQL WHERE column, and see if there might be any issues with the script. In the event the SQL appears out of place and may need an update performed, it will be best to document as many details as possible and submit a request to our Support team for help.

In certain cases, after creating a new Work Order Type, the new Type will automatically populate into the Type field for a manually created Work Order, ignoring the default Type value.

The SQL provided here resolves this issue by setting the default Type value to (Not Specified), ensuring no value automatically populates into the Type field. Please note that the instructions given are meant for customers with On Premise databases. Those with Cloud Hosted environments should submit a request to our Support team to resolve the issue.

• First, make sure the current default value for the Work Order Type field is not set to (Not Specified). Access the Preferences window (Tools>Preferences...), and then locate the Work Order>Defaults Category of Preferences. The Default Type Preference should be listed first. Select this Preference.

• The Set Preference dialog will appear. Since there are multiple Type values to choose from, you should not be able to choose the blank (Not Specified) option for the Current Value. If that is the case, close the Preferences tool.

• After confirming that (Not Specified) is not available as an option for the Type field, close all windows, navigate to the SQL Administrator (Applications > SQL Administration), and run the following script:

						UPDATE Preference
						SET 
						DefaultValue = ''
						,DefaultValueDesc = NULL
						,DefaultValuePK = NULL
					WHERE PreferenceName IN('WO_DEFAULTTYPE','WO_RE_TYPED')

IMAGE

• After the SQL script executes successfully, close the Query Analyzer and return to Work Order Default Type Preference. (Not Specified) should now be the Current Value for the Preference.

• You can also create a new Work Order and note that the Type field has no default value.