Create Running Totals

If you’re after running totals, a while back I did a post on a bank account style running total. There are some caveats which I’ve posted about elsewhere around making sure your ordering is consistent otherwise the numbers will be wrong. Excel has an implicit row ID in it where as Tap Forms does not, so if you don’t have a field you can sort on consistently then you will need to add your own row number (perhaps by using an autoincrementing number field).

Excel is using a different level of aggregation, generally at the sheet level though also up to the document level. It’s a better tool for modelling this data because you’re looking at a whole sheet where each column and row has a unique cell identifier. In that environment, asking for the value of the previous row is easy because it’s the same element but in a database environment, especially one like Tap Forms that features sync with offline editing capabilities, the concept of a previous row is not assured. That’s why I have the caveats I have with such an implementation being dependent upon how you sort it and also requiring recalculation if the values change. In Excel, that’s easy because it’s a simple two dimensional array controlled by a single entity.

Tap Forms is a little quirky in that it’s a document store which is a little different than most relational databases. There are plenty of document store databases like CouchDB/Couchbase (the underlying data store is Couchbase Lite), MongoDB or even Lotus Notes. This means it’s not uncommon however document stores are a little different to relational databases which the majority of the web will talk about. A lot of the same concepts apply though it won’t be a complete match to TF at times.

I’m not entirely sure where would be a good start for you so I’m going to suggest checking out database normalization if you’re not already familiar with the concept. It’s a way of thinking about how you structure a database and can make you aware of trade offs you’re making. I think this is perhaps the most useful piece of database theory to get started with.