Need tip to write a formulae

[Former Member]

my report looks like this :

SelectionCritera:ProductID is '10' and AccountID is '00000' and city is 'VA'

-

---

ProductID AccountID City

-

---

10 , 0000 , VA

can somebody tell how to write the formulae(logic) for the SelectionCritera to be displayed as shown above, in crystal and one more thing all ProductID ,AccountID and City have promts on them.

Thanks In Advance.

[Former Member · Accepted solution]

Hi Sai,

You can create a formula as

stringvar selection;

selection:= 'Selection criteria : ';

selection:= selection & 'ProductID is : ' & chr(39) & & chr(39);

selection:= selection & ' and Account Id is : ' & chr(39) & & chr(39);

selection:= selection & ' and city is : ' & chr(39) & & chr(39);

Hope this is your expected solution.

[Former Member · Accepted solution]

As Said all three ProductID,AccountID and City have prompt's on them so i have to modify the code as below....

stringvar selection;

selection:= 'Selection criteria : ';

selection:= selection & 'ProductID is : ' & chr(39) & {?ProductID} & chr(39);

selection:= selection & ' and AccountID is : ' & chr(39) & {?Pname} & chr(39);

selection:= selection & ' and City is : ' & chr(39) & {?City} & chr(39);

when I am doing this an error mesage pops out saying

'The array must be subscripted.For Example :Array<i>'

can you please help me to get it correct.

[Former Member · Accepted solution]

If your parameter is a multi value parameter then you need to use

join(totext({?parameter}),",")

intead of {?parameter} in the formula you have written.

Regards,

Raghavendra

[Former Member · Accepted solution]

Hai,

though I have change the code ...

selection:= 'ProductID is : ' & chr(39) & join(totext({?ProductID}),",") & chr(39);

selection:= selection & ' and ProName is : ' & chr(39)& join(totext({?Pname}),",") & chr(39);

selection:= selection & ' and SupID is : ' & chr(39) & join(totext({?SuppliersID}),",") & chr(39);

It pop's the same error msg........

can u plz help me to fix it.

[Former Member · Accepted solution]

The join function works only on String data. If you have a static parameter of number type, then you can change it to string type.

In the record selection formula, you can convert the data field to totext(productid,0) and use it like

totext(productid,0) = {?productid}.

Then the formula will work i believe.

[Former Member · Accepted solution]

Try this

stringvar selection;
stringVar array arr1;
stringVar array arr2;
numberVar i;
numberVar j;
redim preserve arr[count(?ProductID})];
for  i:=1  to  count({?ProductID}) step 1 do 
arr<i>:=totext({?ProductID}<i>);

redim preserve arr2[count({?SuppliersID})];
for  j:=1  to  count({?SuppliersID}) step 1 do 
arr[j]:=totext({?SuppliersID}[j]);

selection:= 'ProductID is : ' & chr(39) & join(({?arr1}),",") & chr(39);

selection:= selection & ' and ProName is : ' & chr(39)& join(totext({?Pname}),",") & chr(39);

selection:= selection & ' and SupID is : ' & chr(39) & join(totext({?arr2}),",") & chr(39);

Regards,

Raghavendra

[Former Member · Accepted solution]

Hai Raghavendra,

Thank you all your replies.

I tried the previous code that you have posted

stringvar selection;

stringVar array arr1;

numberVar i;

redim preserve arr1[count({?ProductID})];

for i:=1 to count({?ProductID}) step 1 do

arr1<i>:=totext({?ProductID}<i>);

selection:= 'ProductID is : ' & chr(39) & join((arr1<i>),",") & chr(39);

when I am doing this it says 'A String Array Is requried Here' and highlighting (arr1<i>).

I selected (arr1<i>) because we have to display what is there in arr1<i> (please correct me if I am wrong).

There are two places where i need clarification

1) selection:= 'ProductID is : ' & chr(39) & join(({?arr1}),",") & chr(39);

Where {?arr1} Is an unknown field

2)selection:= selection & ' and SupID is : ' & chr(39) & join(totext({?arr2}),",") & chr(39);

where {?arr2} is also an unknown field

Is there any specific reason in writing it that way????????

Last but not the least all of them ProductID,SuppliersID and City have multiple Promts

Edited by: sai maddi on Sep 30, 2008 3:15 AM

[Former Member · Accepted solution]

Here is the exact code

stringvar selection;
stringVar array arr1;
stringVar array arr2;
numberVar i;
numberVar j;
redim preserve arr[count(?ProductID})];
for  i:=1  to  count({?ProductID}) step 1 do 
arr1<i>:=totext({?ProductID}<i>);
 
redim preserve arr2[count({?SuppliersID})];
for  j:=1  to  count({?SuppliersID}) step 1 do 
arr2[j]:=totext({?SuppliersID}[j]);
 
selection:= 'ProductID is : ' & chr(39) & join(({?arr1}),",") & chr(39);
 
selection:= selection & ' and ProName is : ' & chr(39)& join(totext({?Pname}),",") & chr(39);
 
selection:= selection & ' and SupID is : ' & chr(39) & join(totext({?arr2}),",") & chr(39);

As ProductID and SupplierID are numeric fields you need to convert each value in it to a string. So I'm looping for each value in the list and making them as string and assigning it to string array.

In your formula you didn't specify the variable position in array

arr1<i>:=totext({?ProductID}<i>);

where i value is the loop variable that loops from 1 to the lenth of the parameter values. So Try the formula that I have posted above exactly and see the output.

Regards,

Raghavendra

[Former Member · Accepted solution]

Hai Raghavendra,

The code really does not work .As said ?ProductID accepcts multiple values on that promt ,so as ?SuppliersID and ?Pname.

When I am trying to execute this

numberVar i;

redim preserve arr1[count({?ProductID})];

for i:=1 to count({?ProductID}) step 1 do

arr1<i>:=totext({?ProductID}<i>);

selection:= 'ProductID is : ' & chr(39) & join(({?arr1}),",") & chr(39);

It pops an error msg saying field name is unkonown.

could you plzzzz go tru the code if u dont mind.

Edited by: sai maddi on Oct 1, 2008 6:36 AM

[Former Member · Accepted solution]

Hi Sai,

I tried your formula but there are some mistakes in it.

I modified it. Please try the following as it is. it is working at my end when I tried with multi valued numeric parameter.

stringVar array arr1;
stringVar selection;
numberVar i;
redim preserve arr1[count({?ProductID})];
for i:=1 to count({?ProductID}) step 1 do 
arr1<i>:=totext({?ProductID}<i>);
selection:= 'ProductID is : ' & chr(39) & join(arr1,",") & chr(39);

Please check the parameter name is correct or not.

Regards,

Raghavendra

[Former Member]

Hai Raghavendra,

Thanks a lot ,It helped me a lot.

I have few more question on sm stuff like this which i'll be posting ,so please kindly answer them if you can

[Former Member]

i think you can do this much simpler.

do you want to retrieve records based upon parameters? (ProductID ,AccountID and City have promts on them)

if that is the case and the end user can retreive all records or make a selection of one or a combo of all of the above

in the parameters add ALL as the first selection.

in the selection criteria

(if {?ProductID}='ALL' then true else {ProductID)={?ProductID})

create that for all 3, use and or statement between them if they only select one. if they have to select all of the them use and and statement.

[Former Member]

Hai Sharon,

{?ProductID} accepcts multiple values on that prompt ,so as {?SuppliersID} and {?Pname}.When I remove the multiple Prompt option and check the syntax it works fine,but where as when I turn on the multiple prompt option true then an error msg pops out saying 'Array<i>'.

[Former Member]

are the parameters set up with allow multiple values checked?

does it work with range of values checked?