Loop returning boolean value

[MTarkington]

Good afternoon,

I am having a problem with a FOR loop returning a True/False value instead of the value that I need.

the code:
for counter := 1 to 70 do
(
if field = array_variable[counter] then
(
"success";
exit for;
)

I would think that if the field finds a match in the array, it would display "success", however, it does not display anything of fields that I KNOW it matches and it also only displays 'True' for 1 field in particular.

Any ideas?

TIA,
Mark

 

[MTarkington]

Ok, here's the code.
I had to removed the values from the arrays due to company policies.

local stringvar code1 := trim({history_member_auths.code_authorized_1});

local numbervar counter := 1;
local stringvar code;

local stringvar array cpt_code := [populate array w/ exatly 70 codes];
local stringvar array code_value := [populate array w/ the 70 corresponding values];

for counter := 1 to ubound(code_value) do
(
if code1 = cpt_code[counter] then
(
code := code_value[counter];
)
);
code;

Mark

 

[synapsevampire]

Since it finds some (or at least one), I would guess that you are having a concern with uppercase or spaces since you trim the field, the code seems OK.

Why are the codes set as strings? From the example they appear to be numerics.

Rather than trim the table.field at the time of creating the variable, try changing the comparison to numerics:

local stringvar code1 := {history_member_auths.code_authorized_1};

local numbervar counter := 1;
local stringvar code;

local stringvar array cpt_code := [populate array w/ exatly 70 codes];
local stringvar array code_value := [populate array w/ the 70 corresponding values];

for counter := 1 to ubound(code_value) do
(
if val(code1) = val(cpt_code[counter]) then
(
code := code_value[counter];
)
);
code

-k

 

[Turkbear]

Hi,
In your array of descriptions, are there any special characters in the descriptions of some of the elements?
How are your elements separated in the array?

Also, if you place an expicit reference to the code_value element corresponding to the cpt_code for one of the missing descs, do you get a description?
In other words, if code 2345 is the 5th element in cpt_code
and you display code_value[5], does it give you the correct description?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[MTarkington]

The fields are string 50 in the database and can contain alpha & alphanumeric (either or both), so I can't really compare the values. The example I gave was just a quick description.

Mark

 

[MTarkington]

Turkbear,

The arrays are comma quote delimited in the assignment. However, some of the codes do have colons in the actual value.

["1234","1237","1236:HL"...]

If I reference a specific subscript for the array, I do get the correct value.

Mark

 

[Turkbear]

Hi,
As a last resort ( or maybe as a best practice ) try creating a lookup table or view with the code and descriptions in the database and linking it to the code field..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[MTarkington]

Turkbear,

Funny you would say that.

There is actually a lookup table that we can use. However, the main table is a history table with many iterations and if we link to the lookup table, the report will crash every time. Our database is on a Sun server and if I run the report with that table linked, it pegs the CPU usage on the server.

As I noted previously, I am unable to create any procedures, views, or tables on this database. One of our future projects will be downloading the tables to an external Cache DB or MS SQL, but we just don't have the time to work on that right now.

Thanks,
Mark

 

[lbass]

Can we assume the hard-coded arrays look like the following?

local stringvar array cpt_code := ["12AB35","14CC53","etc"];

-LB

 

[MTarkington]

lbass,

Yes, however, there are some values that do have a colon within the actual codes ("8765:HL"), but other than that, the arrays look like what you've described.

Mark

 

[synapsevampire]

Perhaps one or the other is uppercase?

local stringvar code1 := {history_member_auths.code_authorized_1};

local numbervar counter := 1;
local stringvar code:="blah";

local stringvar array cpt_code := [populate array w/ exatly 70 codes];
local stringvar array code_value := [populate array w/ the 70 corresponding values];
for counter := 1 to ubound(code_value) do
(
if uppercase(trim(code1)) = uppercase(trim((cpt_code[counter])) then
code := code_value[counter];
);
if code = "blah" then
code := "not found";
code

Note that I also reset code so that you can see that nothing has occured if it isn't found.

Kind of running out of ideas here...

-k

 

[synapsevampire]

Colons won't matter providing the table.field and the array value both have the colon.

If not use a LEFT function in the comparison to look at only what is comparable.

-k

 

[MTarkington]

synapsevampire,

Everything in our system is entered in uppercase, but I added it and it made no change.

Also, after the loop, I added the if to display when the variable assignment failed. It never displayed 'not found'

I appreciate everyone's help on this.

Thanks,
Mark

 

[synapsevampire]

Did you also set the original value to "blah" as I had?

If you did not do this, then the behavior is no surprise, otherwise it's odd.

Respond and I'll try something else, however I need to know if you did as I had, or just part of it...

-k

 

[MTarkington]

Here is the code as modified:

local stringvar code1 := trim({history_member_auths.code_authorized_1});

local numbervar counter := 1;
local stringvar code := "fail";

local stringvar array cpt_code := [array populated];
local stringvar array code_value := [array populated];

for counter := 1 to ubound(code_value) do
( if uppercase(code1) = uppercase(cpt_code[counter]) then
( code := code_value[counter]; )
);
if code = "fail" then
"not found"
else
code;

If there's anything wrong with this, please let me know...

Mark

 

[lbass]

There must be some mismatch between your code1 values and your array values. Why not test this by checking the length of the code1 values compared to individual array elements or do a visual check. We cannot really help without being able to see potential discrepancies.

-LB

 

[synapsevampire]

It seems OK, I would be suspicious of the arrays themselves.

Try this:

local stringvar array cpt_code := [array populated];
join(cpt_code ,chr(13))

Place it in the report header on the left and expand it.

local stringvar array code_value := [array populated];
join(cpt_code ,chr(13))

Place this to the right of it, and make sure that each formula has CAN GROW turned on.

Now you caa see what is matching to what.

Now try this:

local stringvar code1 := {history_member_auths.code_authorized_1};
local stringvar array cpt_code := [array populated];
local stringvar array code_value := [array populated];
if uppercase(trim(code1)) cpt_code then
"Yep"
else
"Nope"

Place this in the details.

The only other though I have is that perhaps you haven't been placing this formula in the details?

-k

 

[MTarkington]

Actually, I just found the problem.

With the nature of our databases, this, to me, is a strange occurence, but tends to be a normal situation for us.

I was hard-coding the code assignment:
local stringvar code := tablename.value;

It did not like this because there was at least 1 record that had a null value (which surprises me because there should be no null values in this field, but that's another issue).

After I removed the assignment and added an IF to check for null, if not null, assign the variable, it worked perfectly.

This was one of the strangest things I've ever encountered, but I can honstly say that it doesn't surprise me.

I appreciate everyone's help and thank you for all of the time devoted to helping with this.

Thanking you again,
Mark

 

[Turkbear]

Hi,
As a general rule, unless the database itself has a method of disallowing NULLs in a field ( like, in Oracle, defining it as a Primary Key or with a NOT NULL constraint), assume the possibliity of NULLs ( and if a character field, of Blanks) and test for them first..

Glad you got it working...( Are we having fun yet..)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[MTarkington]

Tell me if this part of the code should have caused the problem: (just ammend this to what I've already submitted)

local stringvar code1 := tablename.field1;
local stringvar code2 := tablename.field2;

When I commented out the second declared variable, it works fine, if I left the variable declared, it did not work, even though I did not use it anywhere in the code.

Would it make sense that the assignment of a variable could cause the code to perform as it did?

Mark

 

[synapsevampire]

Well, if the assignment returns null, then it might.

I don't see any reference to code2 in the original formula so I can't see why it would matter.

A good cheat for this is to use File->Report Options->Convert null values to default.

Nulls will make formulas quit processing, whereas a blank will not. So if the array includes a blank, and the field is now blank instead of a null, the formula will complate, and your array can just return something appropriate for bad data in the table.

-k