I cleared up a mystery today, one that I thought was just a Crystal glitch. It explains why Crystal formulas sometimes fail without an error message to describe the problem. We are not talking about invalid formulas. An invalid formula generates an error when you save it. We are talking about a formula that fails only when the report is run and it finds data that it can’t process. For example, a formula that divides one field by another is valid, but will fail if the denominator field has a value of zero.  A formula that converts characters from a string field into a date will also fail if the characters don’t make up a valid date. Both of these failures would normally pop up an error message.  So what does it mean when it doesn’t?

Take an example where formula A is used inside formula B, and then formula B is in turn used inside formula C. If formula A fails then both of the others will fail as well. Which one pops up on your screen? It is the one that the report encounters first. And if that happens to be formula B or formula C then there will be no error message. All you will see is a field highlighted in the formula. That is the field that is causing the problem in this formula. So if formula C pops up, it will highlight formula B but there will be no error message.  If formula B pops up it will highlight formula A with no error message. But if formula A pops up, the error message will show.

So how can you tell which of the three will be encountered first? If they are in different sections, the formula in the section that would be printed first is the formula that will fail first. If the fields are all in the same section they are encountered in the order that they were inserted into that section, regardless of where they were located in that section.

So what do you do when a field is highlighted but there is no error message telling you what to fix? Temporarily eliminate that field from the formula. Then rerun the report. The formula that was highlighted should now pop up. If it doesn’t pop up, make sure that the formula that was highlighted is also sitting somewhere on the report and run it again. If it pops up but still doesn’t give an error message then it will highlight another field. Repeat the same process with this highlighted field, until you get to the formula that pops up an error message. Then you know what needs fixing.