Out of the box time cockpit ships with a powerful importer that can import both CSV and Microsoft Excel files. In previous versions the importer could only be used to import new records. In the new version February 2014 we have improved the importer to also support updating existing records. In this blog post I would like to show you how to comfortably edit records with Microsoft Excel and afterwards use the importer to bulk update the edited records in time cockpit.
Scenario
In our scenario we have various projects that are billable but do not have a project budget assigned. These projects are time and material projects so they are billed at cost. However, to perform at rudimentary project controlling, we want to assign a cost cap for each project that is missing a project budget. That way, we could for example use the Budgetary Control list in time cockpit to keep track of the effort that has already been spent on a project. Further, we could implement a notification mechanism if a project is likely to exceed its budget (see Get Notified When a Project is Exceeding Its Budget).
Agreed, this is a rather simple scenario. But imagine you need to consolidate a large amount of data over a number of people. You might want to send out a spreadsheet with the data to consolidate to your colleagues and reimport the changes as a bulk.
Exporting Data
First, we need to export the data we want to edit in Microsoft Excel from time cockpit. There are multiple ways to export data in Excel format. For one, you can use Excel export in the ribbon to export a list as-is in Excel. Another option is to use the Print feature which was introduced in the October version of time cockpit. The print feature is much more sophisticated in terms of options you can choose. For our scenario, we use the Quick excel export. For further detail on the Excel export see Reporting with time cockpit and Microsoft Excel.
To be able to update existing time cockpit records via the importer, make sure to export a unique key for a record. In our case the unique key is the Project Code. This unique key is used to identify existing records and update them accordingly.
Creating an Import Definition
To import records from either CSV or Microsoft Excel, you can create so called import definitions that map the columns from a file (CSV or Excel) to properties or relations of a time cockpit record. You can find a detailed description on how the importer works at http://www.timecockpit.com/blog/2012/09/30/Reporting-with-time-cockpit-and-Microsoft-Office-Excel. In this blog post I only want to cover the new feature of updating existing records with the importer.
Having used the Quick excel export feature to export all our project records from time cockpit into an Excel spreadsheet, we can take a look at the generated file. The excel file should contain all the project records in a worksheet called Projects. To spare yourself as much work as possible you would like to rename the worksheet from Projects to Project. This way, the importer knows that you are about to import Project records and is able to generate most of the import definition itself saving you time and effort.
In the excel file, we assign cost caps to all the project records that have no project budget yet. The new project budget represents the change the importer should apply on the existing project records in time cockpit.
Now it is time to create a import definition. Since we are updating project, we called our import definition Project Update. Afterwards, we provide a sample file for the import definition. In our sample we use the exported and modified project Excel file as sample file. Click Load sample file and choose the file where you have made your adaptions. You will notice that the importer will automatically find Project as the target entity for the import (see Worksheets grid).
If you sample file is very large it may decrease the performance of the Import Definitions list. In this case it would be better to provide a sample file with only a few records. The sample file is not used to import the data from that file. It is only used to map the columns from the Excel to properties in time cockpit.
Next, we try to automatically generate a mapping from the column names in the Excel file to property/relation names in time cockpit. By clicking Generate mapping automatically, time cockpit will try to create a mapping suggestion in the Columns grid. You will notice two highlighted rows the importer is not able to map automatically, Filter and True. These two columns represent the filter criteria that were used during the export. You can safely remove the column mappings using the "-" button at the bottom of the Columns grid.
To enable the importer to update existing items in time cockpit it has to know which columns is used as key. In our sample this is the Project Code. Open the Advanced Settings hyperlink and check the Is Key checkbox for the Project Code column. Whenever the importer finds an existing item with a matching key it updates the record with the given key instead of creating a new one.
In the Advanced Settings dialog you can also set the following properties:
- Is Optional: This setting determines whether import errors raised by this column are ignored.
- Pre-processing Formula: The pre-processing formula describes a transformation formula that is applied on an item. You can access the value from the Excel via the @SourceValue parameter.
- Post-processing Formula: The post-processing formula is executed after the pre-processing formula has been applied and the object has been filled with all values of the currently processed record.
Using a pre-processing formula or a post-processing formula enables you to perform transformations before storing a record to the time cockit database. In our example we want to add a time stamp to the project name property of each project record to see when the record has been altered. The formula for this pre-processing transformation would be @SourceValue + ' (' + :Today() + ')'. The processing formulas are written in TCQL which is used throughout time cockpit. The special parameter @SourceValue lets you access the value of a column, in this case Project Name.
The post-processing formula is used after all the values of a project record in the spreadsheet are transferred to an object that can be saved by time cockpit (a.k.a EntityObject). In the post-processing formula you can perform additional transformations. In contrast to the pre-processing formula where you have only the value of the current column (@SourceValue), in the post-processing formula you can access all properties of the given object by using Current.PropertyName. If you would like to prepend the project code to the project name, you could define the formula Current.ProjectCode + '.' + Current.ProjectName.
Now we have mapped all the necessary fields and can save the import definition.
Import Data
To start an import click Import in the import definitions list. In the upcoming dialog choose the file you want to import. Again, we choose the file that we have updated in Microsoft Excel. In this case, the file serves two purposes. First, it defines the format and structure how an Excel file needs to look like so that the importer can use it with a given import definition. Second, the file also holds the data that is going to be imported. Of course, you can import any file holding project records that fits the import definition we have just created.
Click Select source file and select the file containing the project records you want to import. To make sure that the import will succeed you can click Test Import first, to see if the records would successfully be imported. If not, the importer will give you detailed information about what went wrong in the Error messages grid.
In this scenario there exist two project records in time cockpit with the same code. Since the project code should be unique, this is an error in our master data that can be corrected by assigning each project a unique project code. Testing the import again with corrected master data, the test import completes successfully and we can Execute import to persist the bulk updates made in Excel.
As you can see from the above dialog, the importer did not create any new records, but only updated existing ones. This is because the importer found for all project codes in the Excel file a corresponding project record in time cockpit. If the importer did not find a record, it would have created a new project record in time cockpit. The following screenshot shows the result of the import for one single record. Please note that the project budget and the project name has been updated. The budget was edited directly in Excel. The project name resulted from the transformation defined in the pre-processing formula.
Summary
In this blog post we showed how to use the new update feature of time cockpit's built-in importer to perform a bulk update for externally edited data. For the update to work, it is necessary to provide a column in the file to import (Excel or CSV) that represents a unique key for a record. This key is used to identify an existing record in time cockpit to decide whether to update a record or insert a new one instead. Although this scenario presented was quite simple, I hope you got an idea of how to use the new importer feature to your advance.
Further Reading
comments powered by 