Vanishing data in Btrieve

Thursday 28 January 2010 @ 9:38 pm

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.

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

2 Responses to 'Vanishing data in Btrieve'

  1. Ken Hamady - January 30th, 2010 at 5:05 pm

    From Gordon Portanier of Crystalize:

    Coincidental to your posting yesterday I had the exact same situation that you mentioned with regard to Betrieve. The inexplicable returning of zero records. It wasn’t with a Betrieve back end but with another application. This time the backend was something called ProvideX. I’m using the ProvideX ODBC driver. I’ve had problems with it because it doesn’t support the UNION join which is annoying but today’s behavior is undocumented as far as I know. It took me a while to figure out what the issue was. So the statement:

    SELECT TA.Field, TB.Field, TC.Field
    WHERE (TA.F1=TB.F1) and (TA.F2 = TC.F2)

    Returns a nice set of records. But adding a group on a field from TC causes an order by clause to drop in there and then we get the following:

    SELECT TA.Field, TB.Field, TC.Field
    WHERE (TA.F1=TB.F1) and (TA.F2 = TC.F2)
    ORDER BY TC.Field

    This returns a fat zero records. As soon as I saw this I knew exactly how to fix it because I had just read your posting. Using a formula instead of the field. I just put the field in the formula (I didn’t need to use Totext) so that Crystal Reports doesn’t pass the ORDER BY Clause into the SQL which is causing the issue. I then grouped on that and I was fine. Problem solved.

  2. Ken Hamady - January 30th, 2010 at 5:06 pm

    Yes, Btrieve is not alone in this. I have run into this before in several environments where I had to ‘break’ the SQL to get things to work correctly. It is probably the same index issue. In some cases, especially when it is a filter field, putting the field in a formula isn’t enough to keep it out of the SQL, which is why I have to resort to adding a function.

Leave a Reply

Recrystallize Pro