Using a case select with an array portion of comment field 1

[jcrawford08]

Hi all,

CR XI, SQL environment:


I've narrowed down an issue I'm having with a string formula. Essentially, I am re-sorting a comment field to show what the data means, address line, csz line, etc.

One portion, the fourth paragraph of it, simply has three characters to choose from (5,p, m); depending on the situation, a user may have to enter, one, two, or all three of them.

I have a stringvar set up to identify the section of the array this data will be entered into:
stringvar desig1:=split({RV_Practitioner_Office_TaxID.Comments},chr(13))[4];

and a stringvar to indicate the needed text;

stringvar desigall;

Then in the formula I have it referenced as a case select indicating which one of the cases it needs to show:

select desig1
case "5":
desigall:="5% or More Ownership Interest"
case "p":
desigall:="Partner"
case "m":
desigall:="Managing Control"
case "pm":
desigall:="Partner/Managing Control"
case "5m":
desigall:="5% or More Ownership Interest/Managing Control"
case "5p":
desigall:="5% or More Ownership Interest/Partner"
case "5pm":
desigall:="5% or More Ownership Interest/Partner/Managing Control"
default:
desigall:="Incorrect Data Entered"


Unfortunately, when I reference it later in the formula - in the display portion - it is only giving me the default information,"Incorrect Data Entered", for some reason it is not evaluating what my variable actually equals... I tried setting it up to use the case select based on the array section, but that didn't work either; I also tried it with the more cumbersome if/then statements - also to no avail.... any ideas?

And the data I placed in the field (in the correct place ) is 5pm....

Thanks!

Not in word only, but in deed also... 1Jn3:18

 

[jcrawford08]

Again, figured it out everyone.... it seems that when you split a segment at every instance of - in this case chr(13) - it will also grab the previous chr(13); hence, even though in the fourth paragraph there is only the digits 5pm - a len() of that segment revealed four characters, and instr() put the 5 as the second place character...

Knowing this would be the case, I now just trim the string from the right based on the length of it minus one:

right(split({RV_Practitioner_Office_TaxID.Comments},chr(13))[4],len(split({RV_Practitioner_Office_TaxID.Comments},chr(13))[4])-1)

Now that I've engineered that problem out of the way, the case select works as designed....

If anyone has a better designed solution - PLEASE LET ME KNOW


Thanks!

Not in word only, but in deed also... 1Jn3:18

 

[lbass]

The split function should not return any results that include the divider. I'm betting that there might be a chr(10) in there that is taking up one character space. You could test for it by using:

instr(field,chr(10))

Or there could be a tab (chr(9)).

-LB

 

[jcrawford08]

Chr(10) it was; what character is that? I noticed I have to control for it on any situation where a full line preceeded the data...


anyway,
Right now I'm having another problem with another section of the formula - controlling for bad data entry; but that will be another topic post... expect to see it soon

Not in word only, but in deed also... 1Jn3:18

 

[lbass]

chr(10) is a line feed.

-LB