Formula mixes two groups into one

Tuesday 2 July 2024 @ 10:04 pm

I had a strange one today – working on someone else’s report. The report was grouped by a formula that combined 2 fields, ItemCode and ItemSize. The formula looked like this:

{table.ItemCode} & ' - ' & {table.ItemSize}

I group on formulas like this often, so I didn’t see anything unusual. But the customer reported that two different sizes were being combined into one group. To troubleshoot this I put the ItemCode, the ItemSize and the formula all on the details band and looked at the values in the problem group. It was clear that two sizes (1.48 and 1.481) were being combined in a group that showed them both as 1.48.

I checked the data type of the ItemSize field and found that it was numeric which pointed to the problem. If you concatenate numeric fields and don’t specify the format using the ToText() function, Crystal will use your default windows format. This is typically to round to 2 decimals. So the two values above were rounded to the same value.

I changed the formula to be:

{table.ItemCode} & ' - ' & Totext({table.ItemSize} , 5 , '' )

This forced it to include 5 decimal places, and the empty string in the second argument eliminates commas in any numbers over 999. This version of the formula separated this item into two separate groups.

This is easy to miss so I thought others might appreciate the tip.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro