List or Array Formulas 1

[khan82]

Hello,

I want to be able to base a particular record selection of the values entered in the form of a list at the prompt window. is that possible? Where can i create the list and what will a record selection formula look like?

lets say i have a table that store a list of states.... AB, CA, WA etc.

I want to enter during prompt AB,WA and generate report only for these particular states.

Thanks!

 

[Charliy]

Set up a parameter for State, and then set "Allow Multiple Values" to True.

This will let you enter as many States as you want, and will put them in an array.

In you selection formula you say {table.state} IN {?State Parameter}

 

[khan82]

so how do you enter them in the prompt? seperated by commas? i tried it out but it only works for one state...if i enter two states then it prints as a blank report

 

[Charliy]

If you set multiple values to True, then you should see a box that lets you enter one choice at a time, then add them to a larger box to the right.

 

[khan82]

I only see that screen when i run the report through crystal design but not on the report i run in the accounting software.

how can i create an array in crystal reports and then enter multiple values on once line separated by commas to make the report work?

 

[Charliy]

Then the problem is with the report viewer in you accounting software - not something I know how to fix.

 

[khan82]

any one know how to write array formulas?

 

[BettyJ]

Did you check if in your accounting software there is any option to add parameters.

(Also in Crystal Reports, did you try to create a dynamic parameter for states using the table that stores the states?)

 

[khan82]

I did check but there is buttons or fields to add list. The only space is the parameter space..which is just a plane white box.

I have not tried creating a dynamic parameter...I will give that a shot.

 

[Charliy]

You might try setting up the parameter as a single value, with a lable saying "Separate States with comma" then use Split to treat that string as an array.

 

[khan82]

what would the formula for it look like?

 

[Charliy]

Here is a select formula from a report that uses that method:

local stringvar array fdx := split({?Diagnoses},",");

(TRIM({history_diagnosis.axis_I_diag_code_1}) in fdx
or {history_diagnosis.axis_I_diag_code_2} in fdx
or {history_diagnosis.axis_I_diag_code_3} in fdx
or {history_diagnosis.axis_II_diag_code_1} in fdx
or {history_diagnosis.axis_II_diag_code_2} in fdx
or TRIM({history_diagnosis.axis_III_diag_code_1}) IN fdx
or {history_diagnosis.axis_III_diag_code_2} in fdx
or {history_diagnosis.axis_III_diag_code_3} in fdx
or {history_diagnosis.axis_III_diag_code_4} in fdx
OR {history_diagnosis.axis_III_diag_code_5} in fdx
OR {history_diagnosis.axis_III_diag_code_6} in fdx
)
and {history_diagnosis.date_of_diagnosis} in {?Date Range}

 

[pmax9999]

If the parameter is a string and the states are entered separated by commas and nothing else, Crystal will interpret that string as an array and the following Record Selection Formula should work:

{Table.State} in {?State}

Hope this helps.

Cheers
Pete

 

[khan82]

Thanks pmax9999 that was easy!

 

[pmax9999]

On rethinking my previous post and doing some testing I need to correct a misrepresentation in the earlier solution.

Crystal does not actually see the parameter string as an Array, but it does work by matching the {Table.State} field with any consecutive characters in that string. To have it work more consistently and efficiently, the use of the Split function will turn the parameter string into a true Array.

The Selection Record code would then be:

{Table.State} = Split({?State},',')

Apologies for any confusion.

Cheers
Pete

 

[Charliy]

The short method that Pete listed would work fine for States and other criteria that are of uniform length and can't "contain" each other. If you're working with things that may overlap, then you need to do the split.

For example, if I enter a series of codes like: 14,17M,18,20

If is say {table.code} in {?Codes} I can get codes I don't want, like 4, 17, 1, 8, 2, or 0

If I split, then Crystal will compare the field to each element, so I'll only get what is in the list.

 

[khan82]

i have updated the code with the split functions. Thanks for all your help.