Help creating a formula field that replaces a null value.

[CherrySoda]

Crystal v.10 connecting to an Interbase (ODBC) database.

I am creating a report that uses the following fields in the detail section

Job# Date StartTime Category Hours
1 1/5/08 8:00am A 1
2 1/5/08 9:00am B 1
3 1/5/08 10:00am 1
4 1/5/08 11:00am A 1
5 1/5/08 12:00pm A 1
6 1/5/08 1:00pm 1

and so on for with multiple records for many other dates

This report is sorted by StartTime and I want to group first by date, and then by Category. Since some records have no category value, I want that value to be assigned the same category value as the previous entry, so the Category value for Job# 3 is B and the value for Job# 6 is A.

I have been trying to get this to work by creating a formula field, and using a global variable that stores that current Category value, and if the category field is null, then the currently stored value (that of the previosu record) is used. The end result is below:

Job# Date StartTime Category Hours FormulaField
1 1/5/08 8:00am A 1 A
2 1/5/08 9:00am B 1 B
3 1/5/08 10:00am 1 B
4 1/5/08 11:00am A 1 A
5 1/5/08 12:00pm A 1 A
6 1/5/08 1:00pm 1 A

I am having difficulty in writing the proper code for the formula, so that I can then group by that field.

//Begin Code
global stringvar JOBCAT

whileprintintrecords;
JOBCAT;

if (isnull({Job.Category})) or ({Job.Category} = "") then
JOBCAT
else if not isnull({Job.Category}) then
JOBCAT := {Job.Category}
else JOBCAT := "ERROR"
//End Code

This formula works, but when I then try to group by Date and the the FormulaField, something happens and the FormulaField no longer replaces the null values with the previous record's value. How can I fix this formula to allow me to then group by it? Or should I try something different?

 

[CherrySoda]

I have shortened the formula to the following, which seems to give me the same result in the ungrouped report.

global stringvar JOBCAT;

if isnull({Job.Category}) then JOBCAT
else JOBCAT := {Job.Category}

However, when I try to then insert a group based on my date field, the formula field will no longer replace the null value with JOBCAT. It stays null.

 

[CherrySoda]

If I try this formula:

//BEGIN
global stringvar JOBCAT;
whileprintingrecords;

if not isnull({Job.Category}) then JOBCAT := {Job.Category}
else JOBCAT := JOBCAT
// END

Then I can group by date and the formula will work just fine, replacing the null values with the previous value held by the global variable. However, I then lose my option to group by this field. How can I group by this formula?

 

[lbass]

You should have stayed with your original thread, instead of starting a new one. You will NOT be able to group on a formula that is dependent upon a sequence of records--it cannot be done. So you need to explain why the record logically belongs in the same category as the previous record and see if there is some other field that would assist you in creating the grouping or else create some default value so that the null values are grouped together.

-LB

 

[CherrySoda]

My organization bills for services provided to people with disabilities based on their time spent training in different job categories. Some don't have a category and in those cases, the category becomes that same as the previous job worked on during that day. That is the logical reason why the null value needs to match the previous record.

I don't think default values will work as they won't always be the same. I want to group be category so that I can then summarize the hours for each category for each day.

So now if I can't group the formula field, I'm back at square one and trying to figure out how to get the null value to match the previous record, and then be able to group by date and then category. I suppose that I could just run the report with the formula and no groups (which works) and then export the results to an excel spreadsheet, and then use that spreadsheet as the data source to then group the results by date and category, but I'm thinking that there has to be a better way?

 

[lbass]

This is really a data entry issue. For now, you could consider exporting the report to Excel (or Access) and then using the resulting file as the datasource for a new report where you should then be able to group on the category field which is now always populated.

-LB