Adding Values if Field Is Certain Value In formula

[#1]

Hello, I am trying to assign point values to the actions performed by a medical technician. These actions are recorded in a table. There are no fields for points, so I am using formulas to generate and assign point values. There are 8 fields in my table (called EXAM), that I will check to see if an action was performed for the specific exam. The fields for each exam could be empty, they could all be filled, or only some of them will be filled. My issue is that points are only being added if every field is filled. Here is my formula:

The commented sections are my original attempt at adding the points. The sections not commented are my most recent attempt which involved saving the values to an array and then adding them in later. No matter how I try and calculate the points, I only get results if every field has a value. For instance, with how the code currently is, with most of the if statements commented out, my report will only calculate point values if the exam has a value in both {EXAM.zzrabinby} and {EXAM.zzoctby}. Here is what the report looks like with some redactions and the points column highlighted red

As you can see, only 3 of the rows have point values but they are correct. These 3 rows have a value for both {EXAM.zzrabinby} and {EXAM.zzoctby}. The other rows should all have at least 2 points because in my formula, total_points and base_points start with a value of 1, and some of them should have 4 for having one of the fields with a value.

If I could get some help I would appreaciate it!

[DellSC · Accepted solution]

Your issue is caused by null values and the fact that you're not handling them correctly. Null is a funny thing in that any comparison to a null value or calculation that includes a null value with have a result of null. So, you have a several options for handling this:

1. In the Formula Editor in Crystal, change null handling to "Use Default values for null". This will automatically replace any null values with a default value.

2. If your report is joining tables to get data, create a SQL Expression for each field that will provide a default value if it is null. This will be in the same syntax as your database not in Crystal syntax. If your database is MS SQL Server, it would look like this (assuming that the fields you're using are string values):

{%ZZRabinby}
IsNull("EXAM.zzrabinby", "")

3. If you're using a command to get your data, do the null handling in the command using IsNull or its equivalent for your database.

4. Do the null handling manually in your formula. It would look something like this:

NumberVar total_points := 0;

If not IsNull({EXAM.zzrabinby}) then total_points := total_points + 2;
If not IsNull({EXAM.zzoctby}) then total_points := total_points + 2;
...

-Dell

[former_member1203330 · Accepted solution]

Thank you so much!