Technical Questions: can you convert a numeric array to a string array

Print Page | Close Window

can you convert a numeric array to a string array

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=13188
Printed Date: 05 May 2024 at 7:24pm

Topic: can you convert a numeric array to a string array

Posted By: Colonel45
Subject: can you convert a numeric array to a string array
Date Posted: 12 May 2011 at 9:06am

I have a numeric array that once was a string array but we were forced to chance it to numeric in order to use it in Record Selector. But pre-existing code for the string array no longer works:

if {?Dept_ID} = '' then
    'Corporate' + chr(13) + chr(10) +
    'Education' + chr(13) + chr(10) +
    'Enterprise Architecture Solutions' + chr(13) + chr(10) +
    'Enterprise Program Services' + chr(13) + chr(10) +
    'Enterprise Technology Solutions' + chr(13) + chr(10) +
    'Health & Human Services' + chr(13) + chr(10) +
    'IEP Online' + chr(13) + chr(10) +
    'IT' + chr(13) + chr(10) +
    'LTI' + chr(13) + chr(10) +
    'Pacific North West' + chr(13) + chr(10) +
    'Project Management Solutions' + chr(13) + chr(10) +
    'Public Consulting Group' + chr(13) + chr(10) +
    'Quality Solutions' + chr(13) + chr(10) +
    'Technology Consulting'
else Join ({?Dept_ID}, chr(10) + chr(13))

Is there anyway to convert the numeric array ?Dept_ID to a string array so we can use it in the code above?

Replies:

Posted By: CircleD
Date Posted: 12 May 2011 at 12:36pm

I'm a little weak on formulas but I believe if you open the Format Field for (?Dept_ID) and insert something like totext{field} it should get you close to what you need.If not then someone will be along shortly that knows more than I do.

-------------

Posted By: Colonel45
Date Posted: 13 May 2011 at 3:25am

Where exactly would i do that?

Where is the format field for parameters?

Posted By: Keikoku
Date Posted: 13 May 2011 at 4:11am

You can always create another formula that takes in the numberic array, loops through each value and totext them to string, then add them to a new array.

And then do your record select on it.

Posted By: Colonel45
Date Posted: 13 May 2011 at 4:42am

Any code snippets that I can use as an example of your idea?

I tried this but I get an error that says "Array must be subscripted"

local stringvar array x;
x:=totext({?Dept_ID});

Posted By: Keikoku
Date Posted: 13 May 2011 at 6:46am


Global stringvar array x; //new array

Local numbervar j; //counter

Local numbervar num := count ({?Dept_ID}) // length of numbers array



Redim x[num] // resize array to the same size as input array



For j := 1 to num Do

   x[j] := totext({?Dept_ID}[j])

You should be able to reference the string array in your record select formula by declaring x in global scope and then just access it normally.

Posted By: Colonel45
Date Posted: 13 May 2011 at 6:51am

Now what if the array size is dynamic, should I set num = Ubound({?Dept_ID})

????

Posted By: Colonel45
Date Posted: 13 May 2011 at 8:07am

Sorry I missed: count ({?Dept_ID})

I'll give it a shot.

Posted By: Colonel45
Date Posted: 13 May 2011 at 9:24am

Ok I'm very close to getting it to work: this is the formula (@Dept_Array) that converts the numeric array to a string array:

//Variable daclaration section
Global stringvar array x; //new array
Local numbervar j; //counter
Local numbervar num := count ({?Dept_ID}); // length of numbers array
//Loop through the ?Dept_ID array
// resize array to the same size as input array
Redim x[num];
//Loop throug numeric array
//convert each record to text then store in x
For j := 1 to num Do
(
x[j] := totext({?Dept_ID}[j])
);

That works ok, now the new error I get is when I call this formula:

if mailto:%7b@Dept_Array - {@Dept_Array } = '' then
    'Corporate' + chr(13) + chr(10) +
    'Education' + chr(13) + chr(10) +
    'Enterprise Architecture Solutions' + chr(13) + chr(10) +
    'Enterprise Program Services' + chr(13) + chr(10) +
    'Enterprise Technology Solutions' + chr(13) + chr(10) +
    'Health & Human Services' + chr(13) + chr(10) +
    'IEP Online' + chr(13) + chr(10) +
    'IT' + chr(13) + chr(10) +
    'LTI' + chr(13) + chr(10) +
    'Pacific North West' + chr(13) + chr(10) +
    'Project Management Solutions' + chr(13) + chr(10) +
    'Public Consulting Group' + chr(13) + chr(10) +
    'Quality Solutions' + chr(13) + chr(10) +
    'Technology Consulting'
else Join ( mailto:%7b@Dept_Array - {@Dept_Array }, chr(10) + chr(13))

The new error I get is "A boolean is required here" and it points to the ' ' .

It's stating that I need a boolean instead of the ' ' (space).

Posted By: Keikoku
Date Posted: 13 May 2011 at 10:02am

Try checking the size of the array.


If count {@Dept_Array} = 0 Then

...

I don't think you can compare an array with a string like that.

UBound seems to do the same thing. I'm not sure what is the difference between the two performance-wise, but it probably doesn't matter too much.

Posted By: Colonel45
Date Posted: 16 May 2011 at 2:58am

I get an error message that says "An array is required here" for the mailto:%7b@Dept_Array - {@Dept_Array } value

if ubound ( mailto:%7b@Dept_Array - {@Dept_Array }) = 1 then
    'Corporate' + chr(13) + chr(10) +
    'Education' + chr(13) + chr(10) +
    'Enterprise Architecture Solutions' + chr(13) + chr(10) +
    'Enterprise Program Services' + chr(13) + chr(10) +
    'Enterprise Technology Solutions' + chr(13) + chr(10) +
    'Health & Human Services' + chr(13) + chr(10) +
    'IEP Online' + chr(13) + chr(10) +
    'IT' + chr(13) + chr(10) +
    'LTI' + chr(13) + chr(10) +
    'Pacific North West' + chr(13) + chr(10) +
    'Project Management Solutions' + chr(13) + chr(10) +
    'Public Consulting Group' + chr(13) + chr(10) +
    'Quality Solutions' + chr(13) + chr(10) +
    'Technology Consulting'
else Join ( mailto:%7b@Dept_Array - {@Dept_Array }, chr(10) + chr(13))

The only way I can the IF porting of the statement to work is if I do the following: if ubound ({?Dept_ID}) = 1 then

But then the section under the ELSE give me an arrary stating that an array is required here.

Posted By: Keikoku
Date Posted: 16 May 2011 at 3:05am

That's strange.
It seems that you get the number of elements in the IF clause, so it clearly is an array.

I am not sure what the problem might be given the above condition.

Posted By: Colonel45
Date Posted: 16 May 2011 at 3:12am

if ubound ({?Dept_ID}) = 1 then
    'Corporate' + chr(13) + chr(10) +
    'Education' + chr(13) + chr(10) +
    'Enterprise Architecture Solutions' + chr(13) + chr(10) +
    'Enterprise Program Services' + chr(13) + chr(10) +
    'Enterprise Technology Solutions' + chr(13) + chr(10) +
    'Health & Human Services' + chr(13) + chr(10) +
    'IEP Online' + chr(13) + chr(10) +
    'IT' + chr(13) + chr(10) +
    'LTI' + chr(13) + chr(10) +
    'Pacific North West' + chr(13) + chr(10) +
    'Project Management Solutions' + chr(13) + chr(10) +
    'Public Consulting Group' + chr(13) + chr(10) +
    'Quality Solutions' + chr(13) + chr(10) +
    'Technology Consulting'
else Join ({?Dept_ID}, chr(10) + chr(13))

Actually to be exact it says a STRING array is required here in the ELSE section. So if I use ?Dept_ID or ?Dept_Array from your code below it still is looking for a STRING array. Code for the ?Dept_Array formula field is below:

//Variable daclaration section
Global stringvar array x; //new array
Local numbervar j; //counter
Local numbervar num := count ({?Dept_ID}); // length of numbers array

//resize array to the same size as input array
Redim x[num];

//Loop through numeric array
//convert each record to text then store in x
For j := 1 to num Do
(
x[j] := totext({?Dept_ID}[j])
);

Posted By: Keikoku
Date Posted: 16 May 2011 at 3:35am

I declared it as a string array so in the other formula where you're actually printing it out, you would first have to declare it as a string array as well before it will let you proceed.

Posted By: Colonel45
Date Posted: 16 May 2011 at 3:42am

Shouldn't i just be able to reference the ?Dept_Array in the formula that I'm using to print out the contents of the array?

Do you mean somethingl like this:

Global stringvar array x;
x = mailto:%7b@Dept_Array - {@Dept_Array };

I tried that earlier and it didn't work.

Posted By: Colonel45
Date Posted: 16 May 2011 at 6:23am

So there's no straight forward way to convert a numeric array to a string array?

Posted By: Keikoku
Date Posted: 16 May 2011 at 9:59am

Seems like I overlooked something somewhat important:

x is the new string array that we created.


Global stringvar array x;

if ubound (x) = 1 then   

    'Corporate' + chr(13) + chr(10) + 

    'Education' + chr(13) + chr(10) +

    'Enterprise Architecture Solutions' + chr(13) + chr(10) +

    'Enterprise Program Services' + chr(13) + chr(10) +

    'Enterprise Technology Solutions' + chr(13) + chr(10) +

    'Health & Human Services' + chr(13) + chr(10) +

    'IEP Online' + chr(13) + chr(10) +

    'IT' + chr(13) + chr(10) +

    'LTI' + chr(13) + chr(10) +

    'Pacific North West' + chr(13) + chr(10) +

    'Project Management Solutions' + chr(13) + chr(10) +

    'Public Consulting Group' + chr(13) + chr(10) +

    'Quality Solutions' + chr(13) + chr(10) +

    'Technology Consulting'

else Join (x, chr(10) + chr(13))

Posted By: Colonel45
Date Posted: 18 May 2011 at 3:58am

Your code worked, not fully but worked. I had to combine everything into on formula:

Global stringvar array x;
Local numbervar j; //counter
Local numbervar num := count ({?Dept_ID});

Redim x[num];

For j := 1 to num Do
(
x[j] := totext({?Dept_ID}[j])
);

if ubound (x) = 1
and
   x[1] = '0.00'
then
    'Corporate' + chr(13) + chr(10) +
    'Education' + chr(13) + chr(10) +
    'Enterprise Architecture Solutions' + chr(13) + chr(10) +
    'Enterprise Program Services' + chr(13) + chr(10) +
    'Enterprise Technology Solutions' + chr(13) + chr(10) +
    'Health & Human Services' + chr(13) + chr(10) +
    'IEP Online' + chr(13) + chr(10) +
    'IT' + chr(13) + chr(10) +
    'LTI' + chr(13) + chr(10) +
    'Pacific North West' + chr(13) + chr(10) +
    'Project Management Solutions' + chr(13) + chr(10) +
    'Public Consulting Group' + chr(13) + chr(10) +
    'Quality Solutions' + chr(13) + chr(10) +
    'Technology Consulting Test' //+ ' If section -- ' + x[1]
else Join (x, chr(10) + chr(13))

I had to account for my default value which is the number 0 which once you convert it to string turns into '0.00'.

So it does convert all the dept_ID values into string but the format is the following:

#,###,###.##

So dept ID 5000008 gets turned into 5,000,008.00

Which is not a huge deal but now I need to figure out a way to convert that string dept_id into a the real name, look at the picture I posted in the earlier portion of the thread that shows my DEPT_ID input parm with the values and the description. I need to some how take 5,000,008.00 and display the word Corporate and so on and so on for all the values in the array.

How do I look though this new array and based on the value build a string that list all the real department names?

Value Description

5000006 Education

5000008 Corporate

5000012 IT

ect....

Posted By: Keikoku
Date Posted: 18 May 2011 at 4:07am

Out of curiosity: did you place my stringArray-conversion formula into the report header?

Otherwise, crystal won't run it and so the array won't exist when you try to access it later on. I would put it in the RH because anything else that requires this data to be available most likely comes after it.

Regarding the names, you probably didn't even need the array. You can just use a number of if/else if statements, to accomplish it.

if string = 50000008 then
...
else if string = 500000012 then
...
else if ...

...

I'm not sure what the array is for actually.

Posted By: Colonel45
Date Posted: 18 May 2011 at 4:12am

So I would need to get the upper bound of the array then stick an if/elseif in there?

Can you show a short example of using an array with an if/elseif?

Also I found an easier way to convert a numeric array to string:

Global Numbervar i;
Global Stringvar DisplayString;

For i := 1 to ubound({?Dept_ID})
Do
DisplayString := DisplayString + cstr({?Dept_ID},0,"") + Chrw(13) ;

DisplayString

Posted By: Keikoku
Date Posted: 18 May 2011 at 4:19am

That seems like a normal string to me, although I guess you could split on chrw(13) to get string array.

To go through each string number and convert it to a different string, one could say


for x := 1 to ubound(array)

   if array[x] = "00001" then

      "some string"

   else if array[x] = "00002" then

      "some other string"

But it is quite inefficient. There may be other ways to do it.

Posted By: Colonel45
Date Posted: 18 May 2011 at 5:05am

This is what I ended up having to do:

stringVar array a := mailto:%7b@Dept_Array - {@Dept_Array };
numbervar b:=0;

Global Stringvar DeptString;

DeptString

The only problem is if a user selects multiple department's then it only selects one, it won't build the string correctly. If you select three departments I'd want it to display all three. Is there an alternative to if/then?

If you select three department's I'd like to display

Dept1

Dept2

Dept3

Posted By: Keikoku
Date Posted: 18 May 2011 at 5:19am

It should not be an issue because you take an array of string numbers and loop over each item.

The purpose of looping it is to build your string with all of the items and eventually end up with the desired list of departments.

I am not sure why it would be only displaying one.

Verify that the number of items in the array matches the number of options selected.

Posted By: Colonel45
Date Posted: 18 May 2011 at 5:49am

Should there be a loop statement at the bottom?

The ubound(a) is returning a value of 1. I also tried a count(a) and it also returns the value of 1.

Posted By: Colonel45
Date Posted: 18 May 2011 at 6:08am

This is the code for @Dept_Array:

Global Numbervar i;
Global Stringvar DisplayString;

For i := 1 to ubound({?Dept_ID})
Do
DisplayString := DisplayString + cstr({?Dept_ID},0,"") + Chrw(13) ;

DisplayString

Posted By: Colonel45
Date Posted: 18 May 2011 at 6:24am

Could it be that I'm not passing an array instead it's receiving a string value (@Dept_Array)?

Posted By: Colonel45
Date Posted: 18 May 2011 at 8:32am

Anyone out there that can figure out why this code is not working????

local stringVar array a := mailto:%7b@Dept_Array - {@Dept_Array };
local numbervar b:=1;
Global Stringvar DeptString;

for b:=1 to ubound(a)
do
if left(a,7) = "5000001"
    then DeptString:= DeptString + 'Public Consulting Group' + chrw(13)
else if left(a,7) = "5000002"
    then DeptString:= DeptString + 'Health And Human Services' + chrw(13)
else if left(a,7) = "5000006"
    then DeptString:= DeptString + 'Education' + chrw(13)
else if left(a,7) = "5000008"
    then DeptString:= DeptString + 'Corporate ' + chrw(13)
else if left(a,7) = "5000012"
    then DeptString:= DeptString + 'IT' + chrw(13)
else if left(a,7) = "5000741"
    then DeptString:= DeptString + 'CRM' + chrw(13)
else if left(a,7) = "5000961"
    then DeptString:= DeptString + 'Technology Consulting' + chrw(13)
else
    DeptString :=    'Corporate' + cstr(ubound(a)) + chr(13) + chr(10) +
                     'Education' + chr(13) + chr(10) +
                     'Enterprise Architecture Solutions' + chr(13) + chr(10) +
                     'Enterprise Program Services' + chr(13) + chr(10) +
                     'Enterprise Technology Solutions' + chr(13) + chr(10) +
                     'Health & Human Services' + chr(13) + chr(10) +
                     'IEP Online' + chr(13) + chr(10) +
                     'IT' + chr(13) + chr(10) +
                     'LTI' + chr(13) + chr(10) +
                     'Pacific North West' + chr(13) + chr(10) +
                     'Project Management Solutions' + chr(13) + chr(10) +
                     'Public Consulting Group' + chr(13) + chr(10) +
                     'Quality Solutions' + chr(13) + chr(10) +
                     'Technology Consulting';

DeptString

Posted By: Keikoku
Date Posted: 18 May 2011 at 10:06am

Originally posted by Colonel45

Could it be that I'm not passing an array instead it's receiving a string value (@Dept_Array)?

Your formula is creating a string.

Global Stringvar DisplayString;

You will also have to modify your loop, once you pass in the an array. Or create an array somehow, like for example splitting on chrw(13).

local stringVar array a := {@Dept_Array};
local numbervar b:=1;
Global Stringvar DeptString;
for x:=1 to ubound(a)
do
if left(a[x],7) = "5000001"
then DeptString:= DeptString + 'Public Consulting Group' + chrw(13)
...

You will be checking the x'th item in array a at each iteration of the loop.

Print Page | Close Window