Calculation

[Greig Gailey]

I would like to create a calculation which takes the “Balance” value from the form immediately preceding it and deducts a value on the current form “Number Sold” to provide a new “Balance”. Could someone please tell me how to do this?

[Brendan]

Hi Greig,

Tap Forms doesn’t have access in the Calculation field to previous or next records.

The problem with providing that kind of function is what happens if you sort your records a different way? What’s the previous record then? Or if you delete the previous record? Does it reference the next previous record?

[Chris Knight]

It would be kind of cool if TapForms could access the field of another record by a key identifier field. What if there could be a guaranteed unique key field and there was a function that took the key field and a field on that form as parameters? If the key field was an autoincrementing numerical field then the previous and next records would be easily obtainable regardless of sort order by just be the current record’s key field minus or plus one, respectively.

Something like; GETFIELD(key,fieldname) and returns the contents of the linked field. The key has to be guaranteed unique – not sure if that is even currently possible and probably should be a number for easier and faster backend processing. Fieldname is a string that is the fieldanme. The calculation builder already knows the names of the fields. Doesn’t seem like it should be hard to implement.

I wonder what all the use cases for it could be.

Just a thought.

~Chris

[Brendan]

The only way to guarantee a unique key is to use a UUID. For example, here’s the unique key for a record:

rec-f27e4aec910b4a58a6a42500368b715d

Too much to type in.

A sequence number wouldn’t work properly when syncing if you happened to add a record on multiple devices without first syncing. The auto-increment function actually has this issue now. The only way to do it right would be to have a server out there I could ask for the next sequence number.

One possible way to handle it would be to add a previous-record-id property to every record. It would get set every time you added a new record. The problem is what if you wanted the new record to be in the middle of a set of records then I’d have to copy the next record’s previous-record-id, then set the next record’s previous-record-id to the current record’s ID, then set the new record’s previous-record-id to the copied previous-record-id.

It’s very much like a linked list. But not a doubly linked list.

But then you’d have to do all that linked list management if you deleted any records too. And it would get really complex if you delete discontiguous records too.

Greig told me in an email the way another database app does it is to not deal with it at all. The previous record is always the previous record, no matter how the records are sorted. So that means the previous record can change depending on the sort order.

[Author]

Posts