Crystal Reports-populating and printing from an array
I am creating an exception report which will check table columns for certain conditions and then print any found erros for each record on a print line. in a fomula, I am doing the "checking" of each field and loading an array with error messages. For example:
Shared StringVar Array Error_Message[20];
Redim Error_Message[20];
If isnull({REAL_CASE.dscr})
then Error_Message[1] := "**CASE NUMBER IS NULL**"
else
After I load the table with appropriate error messages (at a new record control break) I wish to print the contents of this error message array. The array is defined as Shared StringVar Array Error_Message[20];
How do I accomplish this?
Thanks
Shared StringVar Array Error_Message[20];
Redim Error_Message[20];
If isnull({REAL_CASE.dscr})
then Error_Message[1] := "**CASE NUMBER IS NULL**"
else
After I load the table with appropriate error messages (at a new record control break) I wish to print the contents of this error message array. The array is defined as Shared StringVar Array Error_Message[20];
How do I accomplish this?
Thanks
Why are you using the ReDim on the array?
That will delete all values from the array and then add the new one. If this is in the detail section then you will only get the last value added.
mlmcc
That will delete all values from the array and then add the new one. If this is in the detail section then you will only get the last value added.
mlmcc
Good point. I didn't notice that.
James
James
ASKER
Good afternoon,
Thank you very much for your suggestions and questions. I was not familiar with how to use the REDIM, but I bascially want to wip out the table after each detail record. This should probably be done in a separate formula on the group footer line.
Please take a loook at the attached. This is what I am trying to do--pretty basic. I fill the table, line by line, as an error condition is found. When a group footer break occurs,I want to print the entire contents (NO blank lines) on the report, one error message per line.
Any suggestions would be gratefully accepted!
SAMPLE-EXCEPTION-REPORT.doc
Thank you very much for your suggestions and questions. I was not familiar with how to use the REDIM, but I bascially want to wip out the table after each detail record. This should probably be done in a separate formula on the group footer line.
Please take a loook at the attached. This is what I am trying to do--pretty basic. I fill the table, line by line, as an error condition is found. When a group footer break occurs,I want to print the entire contents (NO blank lines) on the report, one error message per line.
Any suggestions would be gratefully accepted!
SAMPLE-EXCEPTION-REPORT.doc
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
mlmcc--Thanks very much. That explains how I load the array. How do i print the contents on the group footer line.
thanks
thanks
Just as shown by James in the first comment
mlmcc
mlmcc
As mlmcc said, the Join should work for you. His formula adds elements to the array as you encounter new error conditions, so you won't end up with empty elements, meaning no blank lines at the end.
However, as I understand it, you want to start the array over from scratch each time you execute the formula that puts in the error messages. If so, I'd change the beginning of mlmcc's formula to:
Shared StringVar Array Error_Message;
Local NumberVar ArrayCount := 0;
Redim Error_Message [1];
If isnull({REAL_CASE.dscr}) then
IOW, add a Redim, without Preserve, before the first if statement. That will erase any existing contents of the array.
Also, just to check something ...
You mentioned detail records and a group footer. Do you have the report grouped so that each detail record is in a separate group? If not, then trying to set the array at the detail level, but display it at the group footer, wouldn't seem to make sense.
James
However, as I understand it, you want to start the array over from scratch each time you execute the formula that puts in the error messages. If so, I'd change the beginning of mlmcc's formula to:
Shared StringVar Array Error_Message;
Local NumberVar ArrayCount := 0;
Redim Error_Message [1];
If isnull({REAL_CASE.dscr}) then
IOW, add a Redim, without Preserve, before the first if statement. That will erase any existing contents of the array.
Also, just to check something ...
You mentioned detail records and a group footer. Do you have the report grouped so that each detail record is in a separate group? If not, then trying to set the array at the detail level, but display it at the group footer, wouldn't seem to make sense.
James
ASKER
Testing it--I'll let you know--thanks
ASKER
Please take a look at the attached formula. I have this formula placed on the detail line as it reads each UNIQUE case record, Unfortunately, it is printing only one line of te array.
Any help you can offer would be greatly appreciated.
Array-load-and-print.doc
Any help you can offer would be greatly appreciated.
Array-load-and-print.doc
Where is the display formula?
Try changing it to
WhilePRintingRecords;
Shared StringVar Array Error_Message;
Join (Error_Message, Chr (13))
Another formula to try
WhilePRintingRecords;
Shared StringVar Array Error_Message;
UBound(Error_Message)
mlmcc
Try changing it to
WhilePRintingRecords;
Shared StringVar Array Error_Message;
Join (Error_Message, Chr (13))
Another formula to try
WhilePRintingRecords;
Shared StringVar Array Error_Message;
UBound(Error_Message)
mlmcc
Ignore the last comment.
In the group header add a formula
Shared StringVar Array Error_Message;
Local NumberVar ArrayCount := 0;
Redim Error_Message [1];
Change the start of you formula in the detail section to
Shared StringVar Array Error_Message;
Local NumberVar ArrayCount;
If isnull({REAL_CASE.dscr})
mlmcc
In the group header add a formula
Shared StringVar Array Error_Message;
Local NumberVar ArrayCount := 0;
Redim Error_Message [1];
Change the start of you formula in the detail section to
Shared StringVar Array Error_Message;
Local NumberVar ArrayCount;
If isnull({REAL_CASE.dscr})
mlmcc
ASKER
I created a formula with the first part you wrote:
Shared StringVar Array Error_Message;
Local NumberVar ArrayCount := 0;
Redim Error_Message [1];
It got an error saying that the result of a formula cannot be an array... ???????
Shared StringVar Array Error_Message;
Local NumberVar ArrayCount := 0;
Redim Error_Message [1];
It got an error saying that the result of a formula cannot be an array... ???????
Shared StringVar Array Error_Message;
Local NumberVar ArrayCount := 0;
Redim Error_Message [1];
''
The last line is just 2 's
mlmcc
Local NumberVar ArrayCount := 0;
Redim Error_Message [1];
''
The last line is just 2 's
mlmcc
Or you can use
Shared StringVar Array Error_Message;
Local NumberVar ArrayCount;
Redim Error_Message [1];
ArrayCount := 0;
mlmcc
Shared StringVar Array Error_Message;
Local NumberVar ArrayCount;
Redim Error_Message [1];
ArrayCount := 0;
mlmcc
ASKER
I am sorry but I am just not getting this. I have two formulas
(1) the one which loads the array
(2) The one you gave me up above
They are attached.
I have a detail line which contains a couple of sub-reports (needed to get the last entry date of specific tables).
I print the report line in the group footer (breaking on each Case number).
With that being said, I can put the "array load" formula in the detail line, but if I place your formula in the footer, I get nothing.
HELP! :)
Report-formulas.doc
(1) the one which loads the array
(2) The one you gave me up above
They are attached.
I have a detail line which contains a couple of sub-reports (needed to get the last entry date of specific tables).
I print the report line in the group footer (breaking on each Case number).
With that being said, I can put the "array load" formula in the detail line, but if I place your formula in the footer, I get nothing.
HELP! :)
Report-formulas.doc
The formula you have to output the array is one that mlmcc posted to _reset_ the array. The Redim Error_Message [1] clears the contents of the array.
Change the output formula to the following (the first formula I posted):
Shared StringVar Array Error_Message;
Join (Error_Message, ChrW (10))
You'll also need to set the "Can Grow" option in the format for that field, so that it can grow to multiple lines.
However, I think there is also a problem with the first formula in the Report-formulas.doc file that you posted.
The formula is like this:
If isnull({REAL_CASE.dscr})
then (ArrayCount := ArrayCount + 1;
ReDim Preserve Error_Message[ArrayCount];
Error_Message[ArrayCount] := "**CASE NUMBER IS NULL**")
else
If isnull({IDNT.first_name})
then (ArrayCount := ArrayCount + 1;
ReDim Preserve Error_Message[ArrayCount];
Error_Message[ArrayCount] := "**FIRST NAME IS NULL**")
else
and so on.
With the if-else-if-else-... structure, that formula will only find one error condition. If dscr is null, "**CASE NUMBER IS NULL**" is added to the array, and that's it. The formula stops there. If dscr is not null, then the formula checks the next field (first_name). etc., etc.
You presumably want to check every field, so you need to break the if-else-if-else's into separate if's, as in the example that mlmcc posted in message 32996156 on 06/15.
If isnull({REAL_CASE.dscr})
then (ArrayCount := ArrayCount + 1;
ReDim Preserve Error_Message[ArrayCount];
Error_Message[ArrayCount] := "**CASE NUMBER IS NULL**");
If isnull({IDNT.first_name})
then (ArrayCount := ArrayCount + 1;
ReDim Preserve Error_Message[ArrayCount];
Error_Message[ArrayCount] := "**FIRST NAME IS NULL**");
and so on.
James
Change the output formula to the following (the first formula I posted):
Shared StringVar Array Error_Message;
Join (Error_Message, ChrW (10))
You'll also need to set the "Can Grow" option in the format for that field, so that it can grow to multiple lines.
However, I think there is also a problem with the first formula in the Report-formulas.doc file that you posted.
The formula is like this:
If isnull({REAL_CASE.dscr})
then (ArrayCount := ArrayCount + 1;
ReDim Preserve Error_Message[ArrayCount];
Error_Message[ArrayCount] := "**CASE NUMBER IS NULL**")
else
If isnull({IDNT.first_name})
then (ArrayCount := ArrayCount + 1;
ReDim Preserve Error_Message[ArrayCount];
Error_Message[ArrayCount] := "**FIRST NAME IS NULL**")
else
and so on.
With the if-else-if-else-... structure, that formula will only find one error condition. If dscr is null, "**CASE NUMBER IS NULL**" is added to the array, and that's it. The formula stops there. If dscr is not null, then the formula checks the next field (first_name). etc., etc.
You presumably want to check every field, so you need to break the if-else-if-else's into separate if's, as in the example that mlmcc posted in message 32996156 on 06/15.
If isnull({REAL_CASE.dscr})
then (ArrayCount := ArrayCount + 1;
ReDim Preserve Error_Message[ArrayCount];
Error_Message[ArrayCount] := "**CASE NUMBER IS NULL**");
If isnull({IDNT.first_name})
then (ArrayCount := ArrayCount + 1;
ReDim Preserve Error_Message[ArrayCount];
Error_Message[ArrayCount] := "**FIRST NAME IS NULL**");
and so on.
James
ASKER
Oh my gosh I am so shocked that i did not see that! haha thank you. Of course and if/else will only give me one error message. I switched it and it does give multiple error messages!! Great!
One small issue: i want to now change the report to only print the detail for records that have at least ONE error message in the array. I assume that I need to use a suppression formula such as
Shared NumberVar ArrayCount;
If ArrayCount < 1
then true;
Any thoughts on how to accomplish this? (and thanks!!!)
One small issue: i want to now change the report to only print the detail for records that have at least ONE error message in the array. I assume that I need to use a suppression formula such as
Shared NumberVar ArrayCount;
If ArrayCount < 1
then true;
Any thoughts on how to accomplish this? (and thanks!!!)
What you wrote should work.
This might be a bit faster
Shared NumberVar ArrayCount;
ArrayCount < 1
mlmcc
This might be a bit faster
Shared NumberVar ArrayCount;
ArrayCount < 1
mlmcc
Are you simply trying to suppress the section where the contents of that array (your error messages) are output, or are you also trying to suppress other sections before that (eg. your detail section)?
If you're trying to suppress the section that contains the formula that puts the error messages in that array, then that's probably not going to work as is, because I think that any formulas in the section will be evaluated after CR checks the suppression formula. If that's the case, then simply create a new detail (or whatever) section and put it above the one where that formula is. Put the formula in the new section and suppress that section (the formula will still be evaluated). Then when CR gets to the section below that, it will have the value in ArrayCount and can suppress the section accordingly.
James
If you're trying to suppress the section that contains the formula that puts the error messages in that array, then that's probably not going to work as is, because I think that any formulas in the section will be evaluated after CR checks the suppression formula. If that's the case, then simply create a new detail (or whatever) section and put it above the one where that formula is. Put the formula in the new section and suppress that section (the formula will still be evaluated). Then when CR gets to the section below that, it will have the value in ArrayCount and can suppress the section accordingly.
James
ASKER
Thank you very much for you responses. I am sorry MLMCC but putting that supression formula on the group footer line did not display it correctly. James, I am not clear as to what you are suggesting. Hopefully this will clear things up:
I have the load/print array formula in the group-footer, which breaks on each new case record.
If the case record has an editing error and loads the array with an error message, I will the group footer line to print.
If NO editing error is found for the case record, i DO NOT wih to print it.
All array processing is done in the group footer. Any thoughts??
Thanks
I have the load/print array formula in the group-footer, which breaks on each new case record.
If the case record has an editing error and loads the array with an error message, I will the group footer line to print.
If NO editing error is found for the case record, i DO NOT wih to print it.
All array processing is done in the group footer. Any thoughts??
Thanks
Since the formula shouldn't be printing anything if there were no errors, you could suppress the section with the SUPPRESS BLANK SECTION option
mlmcc
mlmcc
You're grouping on the detail record? I ask because you want to check for errors and output the error messages for each record, correct? I think I've asked this before, but there's no harm in checking again.
mlmcc has a good point, _if_ the error messages are the only thing in that section. If you've got any other fields, etc. showing in that same section, the "suppress blank section" option won't work.
> I have the load/print array formula in the group-footer, ...
You have the formula that fills the array and the formula that outputs the array in the same section, or are they in different group footer sections? If they're in the same section, you need to make sure that they are evaluated in the correct order. If the formula that outputs the array is evaluated first, it will output the results from the previous record. You can use EvaluateAfter in the output formula to make sure that it is evaluated after the "load" formula. If those 2 formulas are in separate sections, then that shouldn't be an issue.
What I was saying before is that if, for example, you have group footer section 2, and in that section you have the formula that puts the error messages in the array, then I'm not sure if you can reliably use the count from that formula to suppress _that_ section. Maybe it's fine. If CR always evaluates the "load" formula in that section, and _then_ evaluates the suppression formula for that section, then you should be fine. But if it ever evaluated the suppression formula first, before it evaluated the "load" formula in that section, then the suppression wouldn't work properly.
I was being cautious because I don't know for sure what order CR will evaluate those formulas in. However, from a quick test I just did, it looks like a formula in a section will be evaluated before the suppression formula for that section, so, based on that, it doesn't seem to be a problem.
If it turned out that there was a problem, or you just wanted to "play it safe", you could create a new group footer section and put it above the current section. Put the "load" formula in the new section, and suppress that section (just unconditionally suppress it). With the "load" formula in the section above the one that you want to suppress, there is no doubt that the ArrayCount variable will be set when CR checks the suppression formula in the next section.
Hopefully what I was getting at is a bit clearer now. And, like I said, it may not be a problem.
James
mlmcc has a good point, _if_ the error messages are the only thing in that section. If you've got any other fields, etc. showing in that same section, the "suppress blank section" option won't work.
> I have the load/print array formula in the group-footer, ...
You have the formula that fills the array and the formula that outputs the array in the same section, or are they in different group footer sections? If they're in the same section, you need to make sure that they are evaluated in the correct order. If the formula that outputs the array is evaluated first, it will output the results from the previous record. You can use EvaluateAfter in the output formula to make sure that it is evaluated after the "load" formula. If those 2 formulas are in separate sections, then that shouldn't be an issue.
What I was saying before is that if, for example, you have group footer section 2, and in that section you have the formula that puts the error messages in the array, then I'm not sure if you can reliably use the count from that formula to suppress _that_ section. Maybe it's fine. If CR always evaluates the "load" formula in that section, and _then_ evaluates the suppression formula for that section, then you should be fine. But if it ever evaluated the suppression formula first, before it evaluated the "load" formula in that section, then the suppression wouldn't work properly.
I was being cautious because I don't know for sure what order CR will evaluate those formulas in. However, from a quick test I just did, it looks like a formula in a section will be evaluated before the suppression formula for that section, so, based on that, it doesn't seem to be a problem.
If it turned out that there was a problem, or you just wanted to "play it safe", you could create a new group footer section and put it above the current section. Put the "load" formula in the new section, and suppress that section (just unconditionally suppress it). With the "load" formula in the section above the one that you want to suppress, there is no doubt that the ArrayCount variable will be set when CR checks the suppression formula in the next section.
Hopefully what I was getting at is a bit clearer now. And, like I said, it may not be a problem.
James
ASKER
I Ihave the load aray and print array in the same formula (in group footer). It breaks to group footer each time a new case record is encontered and is checking that rcord for the various edit conditions. I did not see any reason to have a second formula because, as soon as the editing has been done, I want to print te array.
I cannot suppres a blank secton as the group foote line also contains the basic record detail fields as well. i am not clear as to what and why you mean abut creating another footer section . Please explain...
I cannot suppres a blank secton as the group foote line also contains the basic record detail fields as well. i am not clear as to what and why you mean abut creating another footer section . Please explain...
That is the problem
In the group footer you only have 1 record to work with. You don't have all the records for the group. Thus you will only ever get 1 error type.
That is why the BUILD ARRAY formula goes in the detail section and the DISPLAY ARRAY formula goes in the group footer.
mlmcc
In the group footer you only have 1 record to work with. You don't have all the records for the group. Thus you will only ever get 1 error type.
That is why the BUILD ARRAY formula goes in the detail section and the DISPLAY ARRAY formula goes in the group footer.
mlmcc
mlmcc,
> Thus you will only ever get 1 error type.
That's not correct. Each error condition in the formula is on a different field. You seem to be thinking about building a list of errors for one or more fields in a set of records, but he's building a list of errors for the fields in a single record.
As I understand it, the group is at the detail level, so each record is in a separate group, in which case having the formula in the group footer is fine.
mbroad02,
The best thing would probably be for you to post your formula. Since you've changed the formulas (combined them), we should see what your formula looks like now.
As for what I was getting at with another group footer section ...
The idea is that your formula adds to ArrayCount as it adds error messages, and if ArrayCount is 0 at the end of the formula, there were no error messages, so you don't want to show that record, meaning that you want to suppress that group footer section. You'd use the following formula (as posted earlier by mlmcc) to suppress the section:
Shared NumberVar ArrayCount;
ArrayCount < 1
First of all, this relies on ArrayCount not being reset to 0 before the section suppression formula is evaluated, which is part of the reason that I want to see your current formula - To make sure that you're not setting ArrayCount to 0 at the end or something. And if you have any other formulas that set ArrayCount to 0, where are they in the report?
Assuming that ArrayCount is not being reset somewhere, then the suppression formula above may work. However, you said that it did not work, although you didn't provide any details. Did it always suppress the group footer? Never suppress it? Suppress the wrong footers? That might help us figure out what's happening.
My concern was that this approach relies on CR evaluating your error message formula first, in order to set ArrayCount, and then evaluating the suppression formula for that group footer section after that, and I don't know if CR will necessarily evaluate those formulas in that order. I did some quick tests and it seemed to, but I don't know if it will always do that. If CR got to the group footer section and evaluated the suppression formula first, before the error message formula, ArrayCount would have the value from the previous record.
So, what I was suggesting with the second group footer section was a way to make sure that the error message formula is always evaluated before the suppression formula, but putting them in separate sections. This would also require returning to the separate "load" and "print" formulas for the error messages, as earlier discussed.
The first group footer section would contain the formula that puts the error messages in the array, and sets ArrayCount. And that's all that would be in that section. That group footer section would be suppressed, so that it doesn't produce any output on the report. It just fills the array and sets ArrayCount.
The second group footer section would contain the formula that prints the array, and your other fields. This section would have the suppression formula posted above. This way, it wouldn't matter if CR evaluated the formulas in that section first or evaluated the suppression formula first, because ArrayCount was set in the section before that.
Like I said, the 2 sections may not be necessary. From a quick test I did, a suppression formula seemed to see the value that was set in a formula in that section. But if I were doing this, I would use 2 sections, just to play it safe. And if, when you said that the suppression formula "did not display it correctly", you meant that the wrong group footers were suppressed, that could have been because the formulas were not being evaluated in the desired order and the suppression formula was getting the ArrayCount from the previous record, in which case the 2 section approach should fix it.
James
> Thus you will only ever get 1 error type.
That's not correct. Each error condition in the formula is on a different field. You seem to be thinking about building a list of errors for one or more fields in a set of records, but he's building a list of errors for the fields in a single record.
As I understand it, the group is at the detail level, so each record is in a separate group, in which case having the formula in the group footer is fine.
mbroad02,
The best thing would probably be for you to post your formula. Since you've changed the formulas (combined them), we should see what your formula looks like now.
As for what I was getting at with another group footer section ...
The idea is that your formula adds to ArrayCount as it adds error messages, and if ArrayCount is 0 at the end of the formula, there were no error messages, so you don't want to show that record, meaning that you want to suppress that group footer section. You'd use the following formula (as posted earlier by mlmcc) to suppress the section:
Shared NumberVar ArrayCount;
ArrayCount < 1
First of all, this relies on ArrayCount not being reset to 0 before the section suppression formula is evaluated, which is part of the reason that I want to see your current formula - To make sure that you're not setting ArrayCount to 0 at the end or something. And if you have any other formulas that set ArrayCount to 0, where are they in the report?
Assuming that ArrayCount is not being reset somewhere, then the suppression formula above may work. However, you said that it did not work, although you didn't provide any details. Did it always suppress the group footer? Never suppress it? Suppress the wrong footers? That might help us figure out what's happening.
My concern was that this approach relies on CR evaluating your error message formula first, in order to set ArrayCount, and then evaluating the suppression formula for that group footer section after that, and I don't know if CR will necessarily evaluate those formulas in that order. I did some quick tests and it seemed to, but I don't know if it will always do that. If CR got to the group footer section and evaluated the suppression formula first, before the error message formula, ArrayCount would have the value from the previous record.
So, what I was suggesting with the second group footer section was a way to make sure that the error message formula is always evaluated before the suppression formula, but putting them in separate sections. This would also require returning to the separate "load" and "print" formulas for the error messages, as earlier discussed.
The first group footer section would contain the formula that puts the error messages in the array, and sets ArrayCount. And that's all that would be in that section. That group footer section would be suppressed, so that it doesn't produce any output on the report. It just fills the array and sets ArrayCount.
The second group footer section would contain the formula that prints the array, and your other fields. This section would have the suppression formula posted above. This way, it wouldn't matter if CR evaluated the formulas in that section first or evaluated the suppression formula first, because ArrayCount was set in the section before that.
Like I said, the 2 sections may not be necessary. From a quick test I did, a suppression formula seemed to see the value that was set in a formula in that section. But if I were doing this, I would use 2 sections, just to play it safe. And if, when you said that the suppression formula "did not display it correctly", you meant that the wrong group footers were suppressed, that could have been because the formulas were not being evaluated in the desired order and the suppression formula was getting the ArrayCount from the previous record, in which case the 2 section approach should fix it.
James
Fourth paragraph from the end should read:
So, what I was suggesting with the second group footer section was a way to make sure that the error message formula is always evaluated before the suppression formula, by putting them in separate sections. This would also require returning to the separate "load" and "print" formulas for the error messages, as earlier discussed.
That's "by putting them in separate sections", not "but putting them in separate sections".
James
So, what I was suggesting with the second group footer section was a way to make sure that the error message formula is always evaluated before the suppression formula, by putting them in separate sections. This would also require returning to the separate "load" and "print" formulas for the error messages, as earlier discussed.
That's "by putting them in separate sections", not "but putting them in separate sections".
James
ASKER
Guys-
The ONE formula I currently place in the group footer is attached. Any and all help is truly appreciated.
Shared-StringVar-Array-Error.doc
The ONE formula I currently place in the group footer is attached. Any and all help is truly appreciated.
Shared-StringVar-Array-Error.doc
You asked for help, but didn't say with what. The error messages? The group footer suppression? Both? Something else? What's working? What's not working?
That formula uses the if-else-if-else-if-... structure you posted in an earlier message, which means that it will only include one error message. Each error test should be in a separate if statement.
You also included the Error_Message declaration again at the end of the formula (presumably a left over from when that was two separate formulas). I don't know if that's a problem or not. I've never tried declaring the same variable twice in the same formula. It might not do anything, since the variable had already been declared in that formula. Or, it might re-declare it, wiping out the existing contents (your error messages). Remove the second declaration.
James
That formula uses the if-else-if-else-if-... structure you posted in an earlier message, which means that it will only include one error message. Each error test should be in a separate if statement.
You also included the Error_Message declaration again at the end of the formula (presumably a left over from when that was two separate formulas). I don't know if that's a problem or not. I've never tried declaring the same variable twice in the same formula. It might not do anything, since the variable had already been declared in that formula. Or, it might re-declare it, wiping out the existing contents (your error messages). Remove the second declaration.
James
Too many questions and not rereading the discussion. You are correct there are many fields being tested.
mlmcc
mlmcc
ASKER
The formula attached appears to work just fine in finding errors and printing them---All I need help with now is suppressing the entire detail when there are NO errors...thanks
ASKER
I am reading the discussion throoughly---I have tried the many thing which have been advised. I just need the final fix for suppression (mentioned above)...thank you
ASKER
Ok, I see what you are talking about. My bad--I posted the wroing version of the formula. Attached is the latest. Sorry about that.
Shared-StringVar-Array-.doc
Shared-StringVar-Array-.doc
Any other fields in the section?
mlmcc
mlmcc
ASKER
on the footer line? I have case number, last name, first name and soc sec number.
Here is one way
modify your formula as follows
Add a new declaration at the top
Global StringVar DispMessages;
Instead of displaying the message in the formula use
DispMessages := Join (Error_Message, ChrW (10));
''
Add a new section and move it above the exiting section
Put the new formula in the section
Format the section to SUPPRESS BLANK SECTION
In the other section add a formula
WhilePrintingRecords;
Global StringVar DispMessages;
DispMessages
You can suppress that section with
WhilePrintingRecords;
Global StringVar DispMessages;
DispMessages = ''
mlmcc
modify your formula as follows
Add a new declaration at the top
Global StringVar DispMessages;
Instead of displaying the message in the formula use
DispMessages := Join (Error_Message, ChrW (10));
''
Add a new section and move it above the exiting section
Put the new formula in the section
Format the section to SUPPRESS BLANK SECTION
In the other section add a formula
WhilePrintingRecords;
Global StringVar DispMessages;
DispMessages
You can suppress that section with
WhilePrintingRecords;
Global StringVar DispMessages;
DispMessages = ''
mlmcc
ASKER
I wil try and let you know--thanks a lot
> I am reading the discussion throoughly--- ...
FWIW, if that happened to be in response to mlmcc posting "Too many questions and not rereading the discussion", he was referring to himself, not you. He'd lost track of the fact that you are checking a series of fields in each record, as opposed to fields from a set of records.
> I just need the final fix for suppression ...
Once again I ask, what, exactly, is happening? You're really not telling us what we're supposed to be trying to fix.
Did you try this formula to suppress the section?
Shared NumberVar ArrayCount;
ArrayCount < 1
If so, what was the result? Is the section never suppressed? Always suppressed? Sometimes suppressed? If sometimes, is it always on the wrong records, or is it sometimes right and sometimes wrong?
Putting all of those questions aside for the moment, I _may_ have found the answer. I've identified an apparent problem in the last formula that you posted. There's no way to know if it's "the" problem, since you didn't specify what was happening.
You need to reset ArrayCount each time that formula is evaluated. The old error messages are cleared by the Redim, but the count is not reset. Just add this before the first "if" statement.
ArrayCount := 0;
Without that, ArrayCount just keeps going up each time the formula is evaluated, so the only time it will be 0 in the suppression formula is on the very first record, if that record had no errors.
The problem with this theory is that if ArrayCount is not being reset, you should be seeing some blank lines before the error messages on every record except the first one, and you didn't mention anything like that. For example, if the first record had 2 errors, there should be 2 blank lines before the first error message on the next record. And if there were 3 errors on that record, there should be 5 blank lines before the first error message on the next record. The error messages should just keep getting pushed farther and farther down, because ArrayCount is not being reset.
If the error messages are not being pushed down, then I'd have to guess that ArrayCount is being reset somewhere else, in some other formula. So, if the error messages are not being pushed further down with each record, check the formulas on your report and see if any of them are setting ArrayCount to 0. If so, why? If you're not sure, post the formula and where it's located in the report and we can try to figure it out.
Having said all of that, mlmcc's latest suggestion may also work, but checking ArrayCount should work too. If ArrayCount is correct (being reset to 0 at the correct place, and not anywhere else) and the suppression is still not working, then that may bring us back to what I was saying earlier, about possibly needing to use two formulas in two separate sections, one section with a formula that would add the error messages to the array and set ArrayCount, and then the second section with a formula that would actually display the error messages, plus your other fields, and the second section would be suppressed based on the count from the formula in the first section.
James
FWIW, if that happened to be in response to mlmcc posting "Too many questions and not rereading the discussion", he was referring to himself, not you. He'd lost track of the fact that you are checking a series of fields in each record, as opposed to fields from a set of records.
> I just need the final fix for suppression ...
Once again I ask, what, exactly, is happening? You're really not telling us what we're supposed to be trying to fix.
Did you try this formula to suppress the section?
Shared NumberVar ArrayCount;
ArrayCount < 1
If so, what was the result? Is the section never suppressed? Always suppressed? Sometimes suppressed? If sometimes, is it always on the wrong records, or is it sometimes right and sometimes wrong?
Putting all of those questions aside for the moment, I _may_ have found the answer. I've identified an apparent problem in the last formula that you posted. There's no way to know if it's "the" problem, since you didn't specify what was happening.
You need to reset ArrayCount each time that formula is evaluated. The old error messages are cleared by the Redim, but the count is not reset. Just add this before the first "if" statement.
ArrayCount := 0;
Without that, ArrayCount just keeps going up each time the formula is evaluated, so the only time it will be 0 in the suppression formula is on the very first record, if that record had no errors.
The problem with this theory is that if ArrayCount is not being reset, you should be seeing some blank lines before the error messages on every record except the first one, and you didn't mention anything like that. For example, if the first record had 2 errors, there should be 2 blank lines before the first error message on the next record. And if there were 3 errors on that record, there should be 5 blank lines before the first error message on the next record. The error messages should just keep getting pushed farther and farther down, because ArrayCount is not being reset.
If the error messages are not being pushed down, then I'd have to guess that ArrayCount is being reset somewhere else, in some other formula. So, if the error messages are not being pushed further down with each record, check the formulas on your report and see if any of them are setting ArrayCount to 0. If so, why? If you're not sure, post the formula and where it's located in the report and we can try to figure it out.
Having said all of that, mlmcc's latest suggestion may also work, but checking ArrayCount should work too. If ArrayCount is correct (being reset to 0 at the correct place, and not anywhere else) and the suppression is still not working, then that may bring us back to what I was saying earlier, about possibly needing to use two formulas in two separate sections, one section with a formula that would add the error messages to the array and set ArrayCount, and then the second section with a formula that would actually display the error messages, plus your other fields, and the second section would be suppressed based on the count from the formula in the first section.
James
ASKER
Folks--I beleive I am very close. I created a new footer section, as instructed and created the following formuola to place in it:
Global StringVar DispMessages;
DispMessages := Join (Error_Message, ChrW (10));
''
Probably just a minor editing error, but it errors before I can save it, saying "the ) is missing"
Any thoughts??
Global StringVar DispMessages;
DispMessages := Join (Error_Message, ChrW (10));
''
Probably just a minor editing error, but it errors before I can save it, saying "the ) is missing"
Any thoughts??
Do you have Error_Message declared?
Shared StringVar Array Error_Message;
Global StringVar DispMessages;
DispMessages := Join (Error_Message, ChrW (10));
''
mlmcc
Shared StringVar Array Error_Message;
Global StringVar DispMessages;
DispMessages := Join (Error_Message, ChrW (10));
''
mlmcc
Did you declare the Error_Message array in that formula? You didn't show a declaration, so I'm guessing that that's causing the error. I don't see any other problems with the syntax.
But what are you trying to do with that formula? All that it does is combine the elements in the Error_Message array, which was presumably filled by your old formula, and put the results in DispMessages, and then output an empty string. What's the point? If you want to check DispMessages to see if the group footer section should be suppressed, as mlmcc suggested, then just set it at the end of your old formula. I don't see any point in creating a new formula to do that.
Also, you said that you put the new formula in a new footer section, but is that new section above or below the old section? That will determine the results that you get.
James
But what are you trying to do with that formula? All that it does is combine the elements in the Error_Message array, which was presumably filled by your old formula, and put the results in DispMessages, and then output an empty string. What's the point? If you want to check DispMessages to see if the group footer section should be suppressed, as mlmcc suggested, then just set it at the end of your old formula. I don't see any point in creating a new formula to do that.
Also, you said that you put the new formula in a new footer section, but is that new section above or below the old section? That will determine the results that you get.
James
Aw, he beat me to the part about the declaration. :-)
But the rest of my message still stands. I'm not sure what you're trying to do with that formula.
James
But the rest of my message still stands. I'm not sure what you're trying to do with that formula.
James
The point is that by putting that in its own section, above the one he is trying to suppress he can use the variable to suppress the next section conditionally.
mlmcc
mlmcc
Sure, in theory. That's the same basic idea that I've been suggesting (although I'm still not at all sure if that's actually necessary). But there are a number of problems/questions:
He said that he put that formula in a new section, but is the new section above or below the old section?
The new formula only puts the value from the array in the string variable. The array is presumably still being set in the old formula. So, the question is, where is that old formula? My guess is that it's still in the same old section, in which case even if the new section with the new formula is above the old section (where it should be), the suppression formula won't work, because the array is filled in the section below that, so the new formula will be getting the messages from the previous record.
Even if he put the old formula that fills the array in the new section, with that new formula that sets the DispMessages variable, there's no guarantee that the old formula will be evaluated first, unless he adds an EvaluateAfter to the new formula. But really, there's no point in setting DispMessages in a separate formula. It should just be set at the end of the old formula, as you originally suggested.
James
He said that he put that formula in a new section, but is the new section above or below the old section?
The new formula only puts the value from the array in the string variable. The array is presumably still being set in the old formula. So, the question is, where is that old formula? My guess is that it's still in the same old section, in which case even if the new section with the new formula is above the old section (where it should be), the suppression formula won't work, because the array is filled in the section below that, so the new formula will be getting the messages from the previous record.
Even if he put the old formula that fills the array in the new section, with that new formula that sets the DispMessages variable, there's no guarantee that the old formula will be evaluated first, unless he adds an EvaluateAfter to the new formula. But really, there's no point in setting DispMessages in a separate formula. It should just be set at the end of the old formula, as you originally suggested.
James
Is it possible to upload the rpt file?
There is no need for data.
mlmcc
There is no need for data.
mlmcc
Yeah, that's probably a good idea. I'd thought of that several times, but never seemed to get around to suggesting it for some reason.
James
James
ASKER
Ok--I will uploaad the report without data. I will have a better look at your responses in the meantime. Thanks!!!!
CRIM-GARN-EXCEPTION-REPORT-1-070.rpt
CRIM-GARN-EXCEPTION-REPORT-1-070.rpt
Why are all the sections suppressed?
I don't see any conditional suppression in the section expert?
mlmcc
I don't see any conditional suppression in the section expert?
mlmcc
That report doesn't fit what you've described. As mlmcc said, there are no sections with conditional suppression, and you've got the group footer unconditionally suppressed. Also, you said that you'd added another footer section, but that report only has one group footer section.
Putting those issues aside for the moment, there are a couple of issues with your DISPLAY_ARRAY formula.
First, it doesn't actually display the array. It clears it. So, I would rename that formula, just for clarity. You have an INIT_ARRAY formula, but there is nothing in it. I would delete the empty INIT_ARRAY formula and rename DISPLAY_ARRAY to INIT_ARRAY. I would also put a copy of that formula in the report header (so it would be in the report header _and_ the group header). In my experience it may not generally be necessary to declare shared variables in the report header, but I think it's a good idea.
The bigger problem is that you have this declaration in the DISPLAY_ARRAY formula:
Local NumberVar ArrayCount := 0;
That should be shared.
Shared NumberVar ArrayCount := 0;
With it declared Local, you're creating a second variable named ArrayCount that exists only in that formula, and setting that local variable to 0, instead of resetting the Shared ArrayCount variable that's used in the PRINT_ARRAY formula.
James
Putting those issues aside for the moment, there are a couple of issues with your DISPLAY_ARRAY formula.
First, it doesn't actually display the array. It clears it. So, I would rename that formula, just for clarity. You have an INIT_ARRAY formula, but there is nothing in it. I would delete the empty INIT_ARRAY formula and rename DISPLAY_ARRAY to INIT_ARRAY. I would also put a copy of that formula in the report header (so it would be in the report header _and_ the group header). In my experience it may not generally be necessary to declare shared variables in the report header, but I think it's a good idea.
The bigger problem is that you have this declaration in the DISPLAY_ARRAY formula:
Local NumberVar ArrayCount := 0;
That should be shared.
Shared NumberVar ArrayCount := 0;
With it declared Local, you're creating a second variable named ArrayCount that exists only in that formula, and setting that local variable to 0, instead of resetting the Shared ArrayCount variable that's used in the PRINT_ARRAY formula.
James
Good catch on the ArrayCount. I looked at the formulas but not that closely since the report isn't setup to do what he wants.
mlmcc
mlmcc
ASKER
Folks--Appreciate all of the help. this is REALLY hard to do long distance. I believe I have added/updated all of your suggestions to hte report and tested. I am still not getting the "bad" detail records displayed with assocaited error messages from the array.
Please take a look and anything you can suggest is GREATLY appreciated.
Thanks
CRIM-GARN-EXCEPTION-REPORT-1-070.rpt
Please take a look and anything you can suggest is GREATLY appreciated.
Thanks
CRIM-GARN-EXCEPTION-REPORT-1-070.rpt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works beautifully!! thank you So much!!!
ASKER
James and mlmcc worked very patiently with me and my limited understanding of arrays in Crystsal. Bravo to them!! thank you So much!!
You're welcome. Glad I could help (and glad that we _finally_ got this worked out :-).
James
James
Shared StringVar Array Error_Message;
Join (Error_Message, ChrW (10))
That will combine the elements in the array into one string, with a Line Feed in between the elements. Put that formula on the report and set the "Can Grow" option in the field format, and you you'll get a multi-line field with all of the error messages in it.
One potential problem with using Join is that it will include all of the elements in the array, so if the array has 20 elements, but you only filled in the first 10 with error messages, you'll get 10 blank lines after those error messages. If you know how many messages you put in the array, the simplest solution would be to use Redim Preserve after you put in the last error message, to redimension the array to the number of elements that you actually used. Or, instead of just starting the array at 20 elements, you could start it at 1 element and use Redim Preserve to add an element for each new message. FWIW, doing one Redim at the end to remove any unused elements seems more efficient off hand.
If producing one string with all of the error messages in it doesn't suit your needs for some reason, can you tell us more about what kind of form you'd like the list of error messages to take?
James