If I want to remove the decimals of several fields at once you can use CTRL-click or a cursor lasso to select all the fields, then go to the toolbar and hit the “reduce decimals’ button. Each click will remove one decimal from all of the selected fields.

You can do something similar when you want to apply a formatting condition formula. For example, if you want to turn negative numbers red while leaving positive numbers black. The font color condition formula looks like this:

if currentfieldvalue < 0
then CrRed
else CrBlack

To apply this formula to one field you select that field and then select the menu items “Format > Field”. On the “Font” tab you click the condition formula button next to font color. It will usually look like the top one of these buttons:

Once inside you paste in the formula above and then click “Save and Close. The formula button should turn red and look like the middle button above. This means the condition has a formula. When you click OK the negative values for that field will turn red.

Note that the formula doesn’t refer to a specific field, but to the function “CurrentFieldValue”. This function is only available when you do condition formulas and refers to the value of the field you are formatting. The advantage is that the same logic can be used on any numeric field and the condition will be exactly the same, rather than each field having to have a different formula that mentions a specific field.

If you want to apply this formula to several fields at once you could select that group of fields and then select the menu items “Format > Objects”. Like above, you go to the “Font” tab, click the [x+2] and paste in the formula. When you click “Save and Close, then then click OK all of those objects should have that property.

One thing to look out for when you are formatting multiple fields at once is a purple condition button (bottom example in the picture above). This only appears when you try to format multiple fields at once. This tells you that some/all of these fields already have a condition and that not all of them are the same. If you click a purple condition button it will show you a blank formula. If you put in a new formula you will overwrite any existing logic and all of the selected fields will end up with the new condition.

I have written before about links that tap into indexes and how they can speed things up. Especially when you can hit ALL of the fields in the index.

This week I was troubleshooting a report that took 2 hours to run and found a similar case. The report was from a Sage/MAS accounting application. I saw a link between invoiceHistoryHeader and InvoiceHistoryDetails where it took 2 fields to make a unique match.  I checked the index tags for the primary key (red colored tabs) and and found that there were 3 fields in the details table primary index while the header table had only 2. Since we only had two of those fields to use for linking I wanted to make sure the link went from the details to the header, so that the link would completely hit the index. From the arrangement of the tables that appeared to be true, but when I hit “Auto Arrange” I could see that the join actually started with the header and went to the details.

Reversing the join allowed the report to complete in 7 minutes. Still slow, but a huge improvement over 2 hours.

<< for a simpler approach, see this later article on the same topic >>

I often find one or more “address blocks” at the top of form reports, like invoices or purchase orders. These are blocks of text that typically show a customer name, two or three lines for address info, and a last line for City/State/Zip. The simple approach is to arrange the individual fields on the report, but if some address lines are blank you get empty rows in the block. Here is the approach I use to make sure that there are no empy lines in the block.

First you write a formula that combines the city/state/zip into one row like this:

//{@City, State & Zip}
{Cust.CITY} & ", " & {Cust.STATE} & "  " & {Cust.ZIP}

Then you write a formula that combines the first formula with the other potential rows of the address, like this:

{CONTACT1.Company} &
(if {Cust.ADDRESS1} > "" then CHR(13) & {Cust.ADDRESS1} else "") &
(if {Cust.ADDRESS2} > "" then CHR(13) & {Cust.ADDRESS2} else "") &
(if {Cust.ADDRESS3} > "" then CHR(13) & {Cust.ADDRESS3} else "") &
CHR(13) & {@City, State & Zip}

The formula above assumes that every record will have a Company value and a City/State/Zip value. The address lines are added if they have a value. And when they are added a carriage return is also added, using CHR(13). This way each line appears on its own row, but only when it has data. There are no blank lines when a field has no data.

Two things to watch for.
1) You should set both of the formulas above to use “Default Values for Nulls”. Otherwise a Null value for one of these fields will cause a blank address block.
2) Make sure you format the address block with “Can Grow” and then don’t put anything right below it in the same section.  Otherwise the address field might grow right over the object below it.

I have several customers that use the donor tracking software Raiser’s Edge(RE) and pull data out of it with Crystal Reports. To run CR against RE data usually involves exporting the data to an MS Access (MDB) file and then reading that MDB with Crystal. The challenge is that Microsoft doesn’t really support the classic MDB format anymore.

This week I heard from several RE users that a recent Windows update has broken the process. They go to run the report and get an error that the file format is in an “unrecognizable database format”.

One customer was able to resolve the problem by changing the export from “Blackbaud Report Writer Database (MDB)” to “MS Access 2000 Database” but there is some concern about making this change. Some users have said that these exports work fine when you run a report from Crystal, but that these reports will not always run fine from within the RE application menu. Others have had success running reports from these exports in both environments. I haven’t found the specific difference but I suspect that it may have to do with the version of the ODBC driver being used.

I will post more information as it comes in.