Simple Summary Calc question

[captain]

Hi there,

First off, this App is amazing. I’ve used and built a database in FileMaker Pro Trial prior to discovering TapForms, and I must say: it’s way better price/value-wise plus it is easier and prettier to use! Thanks!

My database should keep track of customer’s details and also have a section to compile a list of needed parts (for a repair) and their price.
I have set up 10 text-fields with corresponding number-fields.
The number-fields are formatted as currency. (titled ‘Parts1price’, ‘Parts2price’, etc.)

I have added a new field titled ‘Total’ with Type ‘Calculation’.
The formula for the calculation is as follows:
SUM(Parts1price Parts2price Parts3price)

The problem is, that when one of the 3 number-fields is empty, the Total is not calculated.
It will only calculate a total when all fields in the formula contain values.

I guess, to solve this, I can come up with a very elaborate IFEMPTY formula, but my guess was that someone has managed to do this before me, and that I’m probably missing something very obvious.

The reason for a new topic is that I havent found any answers on here, plus the query for this very problem is very hard to formulate (googled on “Tap forms calculate summary empty values”, blabla) and thus came up with no useful info.

Attachments:

You must be logged in to view attached files.

[Leo]

You would be better of with a separate form containing all the items including price, VAT etc.
Form the client site (form) you can add a many to many link to this item form an you can add all the items you want. item, price, VAT in separate columns and if you add a calculate totale in the corresponding fields they add up as well.

This way you can also make a bill (custom layout)

If you want I can make you an example.

[Leo]

Made a quick one for you. You can also check the amount of sales per item

Attachments:

You must be logged in to view attached files.

[Brendan]

Thanks for posting the example Leo!

Captain, also you could set the Default Value on each of your Price fields to be 0 so that when you create a new record, there will always be a value and the IFEMPTY(X,Y,Z) function would not be needed.

Thanks!

Brendan

[Lane Robinson]

It would be swell if a number calculation took an empty field to mean 0. Or at least generate some indication of an error for the calculation result.

[Daniel Leu]

Just use ifempty(). From the documentation

If you don’t provide a value for every field in your formula, you will receive an empty result for the Calculation field. To resolve this issue, you can either set a Default Value for all the fields used in the formula, or you can use the IFEMPTY() function to return a default value if none is provided. For example IFEMPTY(Price; 0; Price) which will return 0 if the Price field is empty and the actual Price value if it’s not empty.

[Lane Robinson]

Sure. I did that after some head scratching as to why a calculation field was just empty and testing various things to finally discover the reason. But it would still be swell if there was some indication it can’t calculate. Every other database I’ve used just treats empty number fields as zero so this behaviour was unexpected. I do prefer using IFEMPTY instead of defaulting 0 in the field for aesthetic reasons.

cheers!

[Brendan]

I actually used to treat empty fields as zero, but a long time ago I had a bunch of feedback that people preferred treating an empty number field as really empty. Because if I didn’t, then they couldn’t do the kind of logic they wanted where 0 really means 0 and empty means something else entirely.

[Author]

Posts