Using a Dynamic Array in selection criteria

[IanWaterman]

I have to design a Treasury report grouped by Region, each fund can allocate money to up to 7 regions, this is stored in a 2 column table eg

ACC_ID ACC_REF
1 A
2 ABCD
3 BC
4 EFG

I need to be able to filter the report so that say I only want to see funds in Region B

I have created the following formula
Local NumberVar n:=1;
Local NumberVar counter:= length({ACCREF.ACC_REF});
StringVar array Region:=["","","","","","",""];

while counter>= n do
(Region[n] := {ACCREF.ACC_REF}[n];
n:=n+1);

This creates my array and stores the Region codes, but now I can not seem to use it against a Parameter value.

If I put in selection formula

{?Region} in Region

I get the error "Array must be subscripted Array" but if I do this I limit the array to only one entry, I have also tried

{?Region} = Region and get same error.

Two questions
1. If this is the right approach how do I use it in Selection.
2. Is there a better way of doing this.

Thanks in advance

Ian

 

[Ngolem]

I don't see the need for your array.

If you want to specify the region that you want have region as your parameter as you have done (make it a multi-select parameter if you like to specify more than one region) then your record select formula will have this statement along with others that define the records you want

( ...other filter criteria...) and
{ACCREF.ACC_REF} in {?Region}

that should do the trick

Jim Broadbent

 

[IanWaterman]

Jim

The reason I need an array is because the Field ACC_REF is just a simple string.

In my example Funds 2 & 3 can have money allocated to region B. In addtion Fund 2 can also allocate money to regions A, C & D

If I use a parameter and enter just B, the selection query
{ACCREF.ACC_REF} in {?Region} will not return any result. I need tobreak down ACC_REF into its constituent parts. This can vary from just 1 up to 7 characters.

Hope this makes my problem clearer.

Thank you

Ian

 

[Ngolem]

ahhh...you didn't say that in your first post ....

Then you still don't need an array

as long as you are searching for one value use this

{ACCREF.ACC_REF} like "*" + {?Region} + "*"

this will pick out any values containing the letter specified in your parameter...if "*" doesn't work then try "%"



Jim Broadbent

 

[IanWaterman]

Jim

Sorry I should have said, I was trying to avoid using "like" as it can be quite slow.

But if there is no alternative then so be it.

Thanks

Ian