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 tapping into it’s indexes.  This can happen when you add a rule to the selection formula, or when you sort or group the data.  In most environments indexes are helpful but for some reason Crystal and Btrieve indexes don’t always play nicely together.  When this happens you will see incomplete data returned, or more likely no data returned.  You then have to change the request coming from Crystal so it doesn’t include any of the things that will invoke the indexes.   There are two solutions:

1) Go into File – Report Options and look for the property “Use indexes or server for speed”.  If it is checked then take out the check mark.  This is supposed to tell the database to ignore the indexes.  It should work in most cases, but can slow down some reports.

2) An alternative is to take the field you added (the one that caused the problem) and replace it with a formula that uses a Crystal function.  For instance if the original field is a number I usually put it inside the Round() function like this:

Round ( {MyNumber.Field} )

If it is a character field I use the ToText()  function like this

Totext ( {MyText.Field} )

Surprisingly, the ToText function doesn’t complain when used with a character field.  It doesn’t actually change the value of the field, but ToText(), like Round(), is processed in Crystal after the data is returned.  That means you can filter, sort or group on this formula and the indexes never get involved.

This is just one of the tricks I have learned reporting from Btrieve databases like PeachTree.  If you have a PeachTree or Btrieve challenge, give me a call.