Hello.
One of the fields on my report should print the number of Theme packs the customer is taking. Everything works fine except for the pepperoni pizza package. The field on the report is blank unless I throw a 0 in the cheese pizza field. What am I missing?
Here is the formula that is boggling me:
IF(OR([ch. pizza]>0;[pep. pizza]>0;[burgers]>0;[ch. strips]>0);SUM(IF(OR(ISBLANK([ch. pizza]);[ch. pizza]=0);0;[ch. pizza]);IF(OR(ISBLANK([pep. pizza]);[pep. pizza]=0);0;[pep. pizza]);IF(OR(ISBLANK([hotdogs]);[hotdogs]=0);0;[hotdogs]);IF(OR(ISBLANK([burgers]);[burgers]=0);0;[burgers]))&" Theme Packs";"")
The first part of the formula just checks of the customer has taken any one of our packs. If yes then it adds them all (if the cell is null it is counted as zero).
I’m on LO 6.3.2 with a MySQL8 backend on a W10 machine.
Thank you to anyone who can assist
Hello,
Gut feeling says you have a NULL field. NULL + anything = NULL. ISBLANK is not ISNULL. You should initialize all data coming into the report.
Hi Ratslinger, what is involved in “initializing all data”?
Thanks, Jody
Hello,
Initializing data is simply insuring there is a value in a field where needed. In you situation there is a problem with numeric data fields. When entering data, you can set the default value of 0 for the field in the table or in the control on a form. When using SQL you can also replace a NULL value:
COALESCE(`myField`,0) as `value1`
COALESCE uses the first non NULL value (can be more than just the two values depicted here).
Using various methods you can/will avoid much of this checking (ie: for blanks and NULL) in creating a report.