Create Running Totals

[Alan_ds]

See attachment

Attachments:

You must be logged in to view attached files.

[Brendan]

Hello Alan,

Tap Forms can display grand totals at the bottom of the records list view. Click on the Sigma ∑ button at the bottom of the multi-column list view to display the Calculation Row. Then click the popup buttons to select the calculation to display.

Calculation fields are able to perform computations on the values in other fields on the same record, but not across other records.

Thanks,

Brendan

[Sam Moffatt]

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).

[Alan_ds]

Thank you for your help. I thought that as a database novice this would be a nice project for me to start learning how to do more with Tap Forms, but I’ve stumbled at the first hurdle. I will keep this database in its Excel form as its pivot tables allow me to quickly get the results I need and I will choose another example to use with TF.

[Sam Moffatt]

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.

[Alan_ds]

Thank you Sam for your replies. I’ve had TF for a few years now and I’ve been happy using adapted templates for my simple needs, but I realise that if I’m to progress then I should first find out more about databases in general.

Thanks, Alan

[Sam Moffatt]

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.

[Author]

Posts