Introduction

Through the years, not every decision as to where to put data worked ideally when future features were added. To have backward compatibility, there are some 'surprising' data locations and values.

In other cases, a decision was made that might not be the decision you would have made. But decisions had to be made. in these cases, you might look at the data and be 'surprised'. Some of that is because this system is designed to grow with you as you need more and more complexity, that complexity lets it grow. In other cases, your organization might never need 'that' complexity - but a 'custom' package typically costs between 100 and 1000x's the cost of a general package. You get some features you don't need, others get features they will never need but you need. That keeps the cost down. In a similar way MCe chooses configuration over customization. You can configure the fields away you don't use.

WO notes and SR Notes

Standard WO Notes logically enough are in a table named WONote. It should be noted that some versions of MRO have a bug where Service requestor notes go fully into GolbalNotes but also part, notably missing the subject, go into WONote.

Service Requester notes are in a table named GlobalNotes. It should be noted that some versions of MRO have a bug where Service requestor notes go fully into GolbalNotes but also part, notably missing the subject, go into WONote.

Not all WO statuses are where you expect them. The status field only has a few values. <enter them here???> All 'other' values for the status of a WO that has been ???

Contract notes goes into GlobalNotes

WO statuses

Listed statuses

The 'listed' status, in the status field are one of:

• requested
• Canceled
• Denied
• issued
• onhold
• Closed

Extended statuses:

When you are in the 'listed' status, if there are values in the date field(s) for Responded and possibly Completed and possibly Finalized, the WO is derived to be in the:

• Issued:Responded
• Issued:Completed
• Issued:Finalized

states respectively.

Sub-status:

While the above affect code and rules, you also can have your own sub-status fields. There is a lookup table WOSubStatus to put in your options.

You can define the list to be anything you want. in general however It is advised that you not pick from the lists above.

Estimates (1) vs Actuals (2)

In cost tables for work order, estimates and actuals are in the same table. Those with a type of 1 are estimates, those with a type of 2 are actuals.

Not 3NF? (Third normal form)

At the risk of oversimplifying the definition of 3NF, it means 'data exists in one and only one place and other places reference it'. Depending on how you think of it there are lots of places where there is a pk/name/id triad when in strict 3NF there should only be the PK and it should reference the master table.

For example in ProcedureMiscCost there are companypk, companyname, companyrid

What this means is that, if in the referenced table you change companyname, it won't changed in the rows that it is already in.

So think of this data as "The name and id at the time it was set", not 'the current name and id'.

This means in your reporting, if you want to show the value 'at the time the row was created' you show the procedureMiscCost.company name, if on the other hand you want to show the current value for that company, you would use the companypk in the procedureMiscCost table and look up the current value.

So consider the company that changes from 'example inc.' To 'examples and more inc.',

Many Tables names and Columns are singular, unless they are not.

The table is Asset not Assets, but among the aps tables, they are written the way they would normally be read, so most are natural plurals like apsPreferences

The most surprising is typically ProcedureProcedure

ProcedureProcedure but then Procedure* as in ProcedureTask

Columns are uniformly singular, except when they aren't

Because on a given row they only allow one value, they are singular both in the aps tables and the others.

But sometimes multiple values are allowed, in an array or in a JSON structure that permits an array. In these cases they may be named in the plural.

Most tables:

Most Accruent tables and columns are named with 'Pascal case' meaning the 1st letter of every word is capitalized. Some of Accruents columns use a cross between Pascal and Snake (underline) case, such as the survey tables. SUR_Survey, SUR_Page_Condition_Operator

MCe tables, views and aps prefixed columns use a form of Camel case - the aps or mce is lowercase, even though they are acronyms. (Asset Pro Solutions and Maintenance Connection Everywhere). Note that when writing SQL you can ignore the casing, Sur_SURvey, suR_suRvey and SUR_SURVEY are all the same table in any MC MSSQL databases.

Naming convention agreement between Maintenance Connection LLC and Maintenance Connection Canada

This goes back to an agreement between Maintenance Connection LLC and Asset Pro Solutions around 2003 or 2004 where it was agreed that, with one exception:

- any tables added by us would be prefixed with aps

- any views added by us would be prefixed with mce

- any columns added to NON-aps tables would be prefixed with aps

With one exception and that is the RowVersion column, since you can only have one RowVersion column in a table, the arrangement with the then then owner, Chris Bucher, was that whichever of us - Maintenance Connection LLC (Now Accruent) or Asset Pro Solutions - added a RowVersion column, we would name it 'RowVersionIdentity'

Why the differences between MC and APS (MCe/ITIQPro) tables?

Accruent/MC exceptions:

There are a small number of exceptions for Accruent's tables these include:

WO*, where all the WorkOrder tables are named with WO followed by the rest of the table name (the sub name)

KPI, Accruent's Key Performance Indicators, these are gauges, MCe ignores these tables because they only permit gauges, the KPIs in MCe are 'any report that fits in a small space with all the stuff around removed', so any graphic or small report, not just a gauge.

SUR_ for the survey system. This set is the most different from the norm, as all the tables start with SUR_ followed by the table name the way it would have been named if it were any other table.

MC and MC_ tables

There are a bunch of tables that star with 'MC' or 'MC_'. They are what they are! For the most part these are 'helper' tables - tables that aren't EAM/CMMS data but rather tables that help. Depending on your definition they aren't all of the helper tables, but they are the majority. The rest are named without 'MC' or 'MC_'

Suggestions to add?

Send the things that surprised you to support@maintenanceconnection.ca and we'll see about adding them to this documentation for you and others.