Last week a customer called me after they did an upgrade of PeachTree Accounting.  It seems that the records in the report that used to sort were now appearing in random order.  As soon as I added the sort back in and refreshed the report, all the records disappeared.  Fortunately I had seen this before because I work quite a bit with PeachTree and PeachTree uses Btrieve as a database engine.

This week I had another Btrieve customer (not PeachTree) who wanted to add a parameter to select a part number.  We added the rule in the selection formula and, again, all the records disappeared.  Same problem same solution.

Apparently when Crystal sends a query to a Btrieve engine, Btrieve tries to ‘help out’ by Continue Reading »
Vanishing data in Btrieve

If you need to pull a single character out of a character string, or a single item out of an array, you can use a subscript to identify the position you want like this:

{Customer.Customer Name} [3]

This example starts counting from the left to identify the third character or element.  Over the years I have had people tell me you could use a negative number Continue Reading »
Using negative subscripts

I recently was rereading some Tek-Tips.com FAQs and spotted a FAQ by synapsevampire that I didn’t remember.  It had to do with creating a combined list of detail values that can print as a single string.  I have a formula for this on my site, but it happens WhilePrintingRecords, which means you can only display the combined values at the bottom of the report.  With this method you populate an array during the WhileReadingRecords step of report processing.  Because this array is complete before printing starts it can be Continue Reading »
Building arrays WhileReadingRecords

If you are using a dual monitor or changing your monitor resolution, it is possible to ‘lose’ your Formula Editor.  This is because the window tries to remember where it was last positioned, and it is possible that the last coordinates are now outside your current monitor’s visible area.  You have to set the coordinates of the Formula Editor window to be within your current view.

To do this you will have to Continue Reading »
Formula editor window disappears

Several of my customers have had docking problems and I have always known that the solution was in the registry, but because of the way the registry entries are named, it has been hit or miss to figure out the solution. So when I found some articles in the SAP/BO KB for solving these problems I decided to share them. They are all very similar solutions but there are some differences in the details.

Versions 11 and 12:
If a window in the formula editor won’t dock, start with the simple option. First right-click in the middle of the window and make sure that “Allow Docking” is checked. Then double click on the window title bar and see if that puts it into place. If this doesn’t work you will have to go into the registry, which is Continue Reading »
Formula field tree that won’t dock

When you want to set the color of an object in a CR formula you can pick from one of the 15 default color words like CRRed or CRYellow. If you want an unlisted color you have to give the formula the three RGB values for the desired color.  For example Color ( 238 , 130 , 238) gives you violet.

One way find these values is using the color selector on the toolbar.  Drop down the selector and click “more’ to get what I call the “smear palette”.  This lets you click anywhere within a rainbow-like area and see the matching 3 numbers for the exact shade where you are clicking.  But what if you aren’t sure which flavor of ‘orange’ is really your basic orange?

A second approach is to go to a site like this one which maintains a color chart by NAME. That way, if you know that you want “dusty rose” you can just look up the name and get the numbers. This chart shows the  standard orange to be color (255 , 165 , 0).  Now you might think that color naming is subjective, but I checked several other charts and didn’t find any discrepancies.  For instance, all made the same exact distinction between “Bisque” (255/228/196) and “Blanched Almond” (255/235/205) although these colors are almost indistinguishable (at least to me).

Of the several sites that show colors with names, the list linked above has the longest list.  For other examples you can Google “RGB to color name”.

One of my customers recently needed to change a date into text for contract wording.  That meant changing 9/1/2009 into  “this 1st day of September, 2009″.  Most of it was simple but the tricky part was adding the “ordinal suffix” (as in 1st, 2nd, 3rd, etc).  So I did a bit of playing around and came up with this:

WhileReadingRecords;
NumberVar DayIn := Day (PrintDate);
"This "
& Totext (DayIn , 0 )
& (if DayIn in 4 to 20 then 'th' else
if remainder (DayIn , 10) = 1 then 'st' else
if remainder (DayIn , 10) = 2 then 'nd' else
if remainder (DayIn , 10) = 3 then 'rd' else 'th')
& " day of "
& Totext (PrintDate , "MMMM, yyyy")

Note that there are several formulas that can do ordinal calculations for larger numbers, but this simple one works for dates, since day numbers are never bigger than 31.

I found some clever SQL that allows you to strip the Time off of a DateTime in a SQL Server Command.  This has several uses but it makes it simple to use normal Date parameters with DateTime fields and not having to worry about losing the last day due to the times being after midnight.    The SQL would look like this:

DateAdd( day, datediff( day , 0 , YourDateTimeValue) , 0)

One link that describes this method also adds some common date calculations in SQL using similar calculations.

If you need some guidance when it comes to using SQL expressions or commands in your reports, you should get my Expert’t Guide to SQL Expressions, Options and Commands.

Universal Time (also known as UTC/Unix time/Posix time) is a DateTime value that stores the number of seconds since 1/1/1970 at the Royal Observatory in Greenwich, England.  The values for 2009 are around 1.2 billion. One of my readers did some work to convert these values into normal DateTime values and wanted to work on adjusting for her local time zones, as well as adjusting for Daylight Saving Time.   She contributed some logic and I polished it up a bit and Continue Reading »
Converting from Universal Time (UTC)

One of my customers sent me a report with an unusual “divide by zero” error. I was going to explain to her how you prevent this error with an IF-THEN statement.  You check to make sure that the field you are dividing by is not zero, like this:

If {FieldA} = 0 then 0 else
{FieldB} / {FieldA}

But when I looked at her formula I noticed that the first line did exactly what I would have done, and it was still generating a divide by zero Continue Reading »
Formula trouble using “Integer Divide” (the backslash)