Incorrect Error 33 - Record Not Available in report

1

Had an interesting problem today (?). A report using TPS data was terminating prematurely and returning an Error 33 - Record Not Available.

The report was being processed in key order with a range limit set on the key and would terminate prematurely. I looked at the file in TopScan, using the report key and could see that there were indeed records following the point where the report terminated. I used TPSFix to rebuild the file and keys, no errors were returned.

I eventually noticed that the first field of the Key, a STRING(10), had a value starting with the ‘]’ character, probably entered by mistake. Previous records started with a 9 and subsequent records started with an A.

Removing the ‘]’ character from the field seems to have fixed the problem.

I’ve never come across this before?

2

that is weird Geoff.

the ‘]’ character is one of six that fall between the upper case alphabet and lower case alphabet. (The difference between an upper char and its lowercase equivalent is 32 and there are 26 letters hence the need for six “filler” characters).

I am wondering:

  • firstly if you re-insert the ‘]’ character does the problem happen again? ie. is it reproducible?

  • what is the filter? Does it have an upper() or lower() part?

  • is the key case sensitive or case insensitive?

cheers

Geoff R

3

As noted by Geoff in your procedure show us any FILTER() on the VIEW(), and any PROP:Filter code. Also any code in the Validate Record but that would not cause an error 33.

4

OK, you’re not going to believe me and I’ll try to create a test app to prove it, but…

  1. The report used the file key, three fields, the first was the string field and the only filter was a template generated range limit with two values supplied as parameters. This setup bailed with the errant data.
  2. IF I changed from that setup to using NO key, and applied a filter in the template setup and the sort order in the template setup…the report ran correctly.

Now, I believe that shouldn’t be possible, because eventually the same code is being used to process the records, but that’s what was happening!

As I say, I’ll try and reproduce it in a test app.

BTW, this was Clarion 6 if that makes any difference?

5

OK, I can reproduce similar behaviour in a Clarion 10 app using the Students.tps file.

I’ve uploaded the entire folder if anyone is interested in having a look at it?

BadData.zip (1.5 MB)

6

The issue with the code seems to arise from how the range filter is constructed using the UPPER function in combination with special characters like ']'. When the filter is generated as UPPER(STU:LASTNAME) >= UPPER(']G3689') AND UPPER(STU:LASTNAME) <= UPPER('Bagley'), the character ']' has an ASCII value of 93, which is higher than the ASCII value of B (66). This discrepancy causes the filter to incorrectly exclude records that fall between these ASCII values, leading to premature termination of the report. Which I think @vitesse was trying to say.

See ABFILE.CLW ViewManager.ApplyFilter

7

Thanks Mark, yes I think what you and Geoff R say makes sense and explains the difference in behaviour between the range limit and filter. But I’m not sure why using the key, without a range limit, displays the records in the correct order and why the filter doesn’t include these records in the report?

The system now has data entry filters to remove these types of character, which might prevent the problem in the future.

8

Hi Geoff B and Mark

further to what has been said already, note that KeyLastName has NOCASE on it (so the key is case insensitive).

on your filter version, you can get the result that I think you want (including the “intermediate” chars between the upper and lower letters) by changing the filter to:

upper(STU:LastName) >= upper(FromName) AND lower(STU:LastName) <= lower(ToName)

IOW using upper() for the FROM value and using lower() for the TO value.

9

Hi Geoff, I don’t see the, so called, bad data records appearing withing the filter range, which is confusing me? If they are in the key at the correct order, why aren’t they in the filtered report in the correct place?

Just occurred to me that Prop:Order might also be converting to Uppercase and so the records are relocated beyond the last record in the database? I’ll see if I can find that bit of code.

10

I see them but at the end.