Today’s blog is specifically for EnergyCAP clients who are either in the implementation process or who are looking for better ways to make large-scale organizational changes to their EnergyCAP database.
We want to show you how to do things better—to “cheat” what could be a lengthy or tedious setup process by using Excel to import setup data or organization updates more quickly. And a big thank-you to EnergyCAP Senior Project Manager SJ Bergman for explaining these cheats to me!
Long-time EnergyCAP users know that it’s pretty convenient to move objects around in EnergyCAP using our drag-and-drop features right from the user interface. But that process can be time-consuming if you’ve got a large organization and you need to make a lot of changes.
A few years back, we came up with a way to do this by generating setup and update spreadsheets for each of our major categories of database objects, including accounts, meters, places, and vendors. Using database information, we figured out a way to generate a specially-formatted Excel spreadsheet file with “rules” that would help clients import their data into EnergyCAP from a without a lot of fuss, and with confidence that their new data met specifications/ranges appropriate for each data type.
Here’s how it works.
First of all, you need to know that there are separate setup and update sheets in EnergyCAP. You can access them both from the Setup Manager under Administration.
The process for using setup and update sheets is straightforward:
Here are a couple of cheats for using setup sheets.
Update templates for User-Defined fields are now available! This functionality is brand-new with EnergyCAP Online version 3.4.1, so if you use user-defined fields (UDFs), and you are looking for a way to get the information into EnergyCAP quickly, try these new templates.
There are four new UDF update sheets available in EnergyCAP:
You’ll note that one new cheat is built right into the new UDF fields templates. If you want to REMOVE UDF information that is already present in the database, you can just put an “x” in the appropriate column of the update spreadsheet:
And here’s another cheat: For older update sheets, just type “null” (without quotation marks) in the relevant spreadsheet column. When you import the sheet information, the existing information will be wiped clean for that field. There is power in that four-letter word.
One more cheat: Use filters when generating your import/update spreadsheet templates. There is no need to generate gas meters if you are updating electric meter information. Don’t bother sorting through spreadsheet data from the west campus when you’re only interested in the east campus. There are lots of filter options, and you will save time and effort if you take advantage of them. You’ll see the available filter options once you select the type of data object that you will be generating the spreadsheet for.
To review, setup (import) sheets are used when the database is empty and you want to add stuff. Update sheets are used to change something that is already in the database. For instance, let’s suppose that you wanted to update square footage for all your buildings. You would use the Organizations & Buildings update sheet for that purpose.
On common way to use update sheets is to move meters and/or buildings around in your organization hierarchy. Although you could use the drag and drop functionality in EnergyCAP, it would also be easy and reliable to use an update sheet. For a building departmental change, it could be as simple as changing the Parent Place for that building (or group of buildings). And you could select the Parent Place using a dropdown.
Want to learn more about setup and update sheets? Come to our Catalyst Training for Savings Conference this spring! That session starts at 9:30 on Wednesday, April 27. Earlybird registration discounts for Catalyst are available until the end of January.