feature wish for calculations

[Yogie]

Hi Brendan,

would it be possible to do calculations with values from a previous record?

I just tried to build a gas usage form where I would have needed a value from the previous record to calculate the kilometer I have driven with my car since my last refueling.

Maybe there is a way to make a column to have a value of a previous record as standard?

Then I found out that I can not use calculated columns to do more calculations, this would also be great to have, now I have to build longer formulas to achieve my goal.

Here is what my Tap Form looks like:

Date
old KM
new KM
litre bought
paid
KM driven – formula: new KM – old KM
Price per litre – formula: paid / litre bought
Used fuel per 100KM – formula: litre / (new KM – old KM) * 100

So it would be great if I had the option to make the field:

old KM having the new KM from the previous record as default value

And it would be great if we can use a calculated field to be reused

so ‘Used fuel per 100KM’ formula would look like this:

litre / KM driven *100

Of course having an option in a calculation field to set a value taken from the previous record would be a nice thing too, with that I would not have to use a column (old KM) I don’t really need. Then the formula to calculate the KM driven would be:

KM driven – formula: KM – previous(KM)

but this would inherit that we could reuse a calculated field in another calculation (litre / KM driven *100).

Cheers
Yogie

[Brendan]

The tricky part with this request is… what’s the previous record? What if you change your sorting options on your form. Now what is the previous record?

As for using calculation fields within calculation fields, I have to write some code to prevent circular references across multiple formulas.

For example:

A = B + C
C = B + A

How can you calculate A if C = B + A?

Thanks,

Brendan

[Yogie]

Hi Brendan,

I absolutely understand your concerns.

I also have HanDBase installed which is able to do calculations with the previous record. And it’s going to happen that the calculations are going to be changed based on sort order. So HanDBase simply uses the previous record that is currently the previous one, which is messing up the sense in my point of view.

I would rather rely on the internal record ID. So the previous record would always be the record that has a lower ID than the current, but the most highest ID available, just in case a record would have been deleted in between. But then you would never be able to replace a record once deleted to fill the gap :-)

So now I understand that the previous record calculation in HanDBase is simply based on the sorting, so which ever record is previous.

And yes you are right circular calculations should be avoided. When I try to enter a circular calculation there can be a dialog saying that the formula is corrupt or invalid and can’t be used.

Cheers
Yogie

(Just thinking :)

[Brendan]

Tap Forms doesn’t use sequential internal IDs for record IDs. It uses randomly generated strings of digits and letters. This was to facilitate iCloud sync so that you could merge different databases and not have to worry about conflicting keys. So it would have to probably search for the previous most recently created record based on the internal record creation date.

[Author]

Posts