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.

Contract notes goes into GlobalNotes

WO statuses

When you are in the 'Issued' state, you need to look at other fields to find the 'Exact' status.

Explicit statuses

The 'listed' status, (Status, StatusDesc, StatusDate) the status column will be one of:

• Requested
• Canceled
• Denied
• Issued
• onhold
• Closed

Extended statuses: This is NOT the substatuses

When you are in the explicit 'Issued', 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, the 'Responded' date column, there is also RespondedInitials.
• Issued:Completed, the Complete date column, there are also CompletedInitials, CompletPercent, isAssignmentsCompleted
• Issued:Finalized, the Finalized date column, there is also FinalizedInitials

states respectively.

To be clear - if the explicit status is anything other than 'Issued', these dates are 'information only', and do not change the implied status.

The difference between Completed, Finalized and Closed is your decision. They are in that order exactly. Typically

• Completed means the technician has completed the work
• Finalized is either ignored (you go straight to close) or it is used for when clerical staff have finalized their work
• Closed means it can't be changed anymore

Sub-status: this is NOT the Extended statuses.

While the above affect code and rules, you also can have your own sub-status fields. (Sub-Status, SubStatusDesc) 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 (RecordType 1) vs Actuals (RecordType 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.

These are in the WOLabor, WOMiscCost and WOPart, in each of them there is a column named "RecordType" that will have the value 1 or 2

Not 3NF? (Third normal form, yes it's jargon, sorry)

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 automatically be 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 aren't too concerned about showing the current value you could use the easier and faster procedureMiscCost.company name, if on the other hand you want to show, for certain, the current value for that company, you would use the companypk in the procedureMiscCost table and look up the current value.

There are processes that may update some of these from time to time, but you need to consider the duplicate copies to be 'correct as of the time they were entered, and maybe updated later'

So consider the company that changes from 'example inc.' To 'examples and more inc.', The copy in the record in the company table will be accurate, but other places where it is pointed at and duplicated, it could have either the old or the current value (or some value that was older or in between the 2 values.)

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

Most MCe tables are plural 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 ProcedureLibrary

ProcedureLibrary is conceptually 'Procedure' but has the word 'Library' tagged on because 'Procedure' is a reserved word in MS SQL, it can't be used for a table name. The rest of the set, like ProcedureTask go to the more natural, consistent naming convention because, while 'Procedure' is reserved, Longer words that just happen to start with Procedure are not reserved.

Project Plan Procedures

If you are looking for a Project Plan Procedures table, look at ProcedureProcedure

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). Sur_SURvey, suR_suRvey and SUR_SURVEY are all the same table in any MC MSSQL databases.\

Note that when writing SQL you can ignore the casing, It is only there in table and column names to make it easier for a human to read.

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 2005 where it was agreed that 'all new...', 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'

For the short time before the naming convention was agreed on

There are 2 older tables in the Reg database: MCLicenseDetails and MCLicenseUsers. I think originally we were expecting Accruent to also use them, but the decided not to. So they stand with 'unexpected' names. There may be a very few more that I'm missing - if you think you found one, let us know so we can check, and if correct update the exception list.

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_'

In our KISS (Keep it Simple, Seriously) for reports the double __

In our KISS report creator, we standardize the DatabaseDesign into our internal design. The Internal design is what we used way back in 2000-2003 before we made our 'database' design compatible with Maintenance Connection

We have to have a nomenclature for accessing fields that may be several tables deep in references. These, for a variety of reasons, need to follow several sets of rules that restrict which characters we can use. As a result, if we want to talk about the repair center that is for the person who is the supervisor for the work order PM currently on the Asset, the links are defined using two _ characts "__'.

Assets__WO__Supervisor__RepairCenter__pk

Suggestions to add?

Send the things that surprised you to [email protected] and we'll see about adding them to this documentation for you and others.