Tuesday, November 11, 2008

Coghead interestingly handles running total calculation

Personally I consider the opportunity to build a running total within the program as a good test in order to understand if the system has powerful tools for report creation.

How is it done in Coghead?

Before I start I would like to thank Jonathan McKibbin with Coghead support, who helps me to push things on in a right direction.

So I created a simple table with two fields Date and Amount. The thing is I try to calculate a running total for each record (Date).

Firstly I added Running Total aggregate column that calculates summary of Amount column of Sales records with the same or earlier date to the current date.

One could say this is it, there is nothing left just to calculate a running total, but the principle of working within Coghead doesn’t support dynamicall column calculation displaying the data, but all calculated and aggregate columns are processed and saved just on one occasion in the database.

On the one hand it influences positively the performance displaying the data, but it takes many pains to keep the data in a consistent state and now I will show you what I mean:

It is necessary to modify Create, Update and Delete actions, so when you update one record the other related records will also be updated. To make things easier, let me show you what I did on the update action example. In Update action flow I had to add following steps:

Firstly, I had to find all records with Date greater or equal than Date of the record being updated (stored in the Input variable) using a Retrieve Record step. Then, using a For Each loop to step through each record found in the search, you will need to update the record by using a Perform Action step to update the record.

Similar steps one should take creating and deleting a record. This is what I have got as a result:


I managed to calculate a running total in Coghead, but I think that setup is not that simple, especially actions. Such implementation can also contain a potential problem of performance and deadlock for it’s quite an issue how this solution with a constant multiple record update will work with a large volume and in case multiple users edit records.

No comments:

Post a Comment