3 Replies Latest reply on Aug 27, 2013 11:09 AM by philmodjunk

# filter calculation question

### Title

filter calculation question

### Post

Hi Forum,

I am using FileMaker Adv 11. I need your advice on how to separate the list. Please read below:

I have store numbers entered into "Zone Filtered" field, the numbers are separated by a space.

I want to separate them into provice stores.

As I have a list stores by provinces.

For example: ON stores number list:

901

902

903

I used to have a calculation field:

ON_Stores =

If ( PatternCount ( Zone Filtered; "901")=1; "901 ")

& If ( PatternCount ( Zone Filtered; "902")=1; "902 ")

& If ( PatternCount ( Zone Filtered; "903")=1; "903 ")

my "Zone Filtered" = 901 902 904 905

So my ON_Stores = 901 902.

BUT, recently, there is a change: we need to add some stores

so store 1, 2, 3 are added into store database,now my ON store list becomes:

1

2

3

901

902

903.

I have tried to change the calculation field:

ON_Stores =

If ( PatternCount ( Zone Filtered; "901")=1; "901 ")

& If ( PatternCount ( Zone Filtered; "902")=1; "902 ")

& If ( PatternCount ( Zone Filtered; "903")=1; "903 ")

& If ( PatternCount ( Zone Filtered; "1")=1; "1 ")

& If ( PatternCount ( Zone Filtered; "2")=1; "2 ")

& If ( PatternCount ( Zone Filtered; "3")=1; "3 ")

so if "Zone Filtered" = 1 2 3 901 905

I need to see ON_Stores = 901 1 2 3

But  I only get ON_Stores = 901.

Any suggestion to re-define the ON_Stores calculation.

Thank you very much,

Jason

• ###### 2. Re: filter calculation question

FYI

Daniele from fmdev group answered me like below:

"

you'll need to use FilterValues ( )

Let(

v = Substitute ( Zone Filtered ; " " ; ¶ ) ;

Substitute ( FilterValues ( "901¶902¶903¶1¶2¶3" ; v ) ; ¶ ; " " )

)

"

• ###### 3. Re: filter calculation question

Substitute ( Filtervalues ( Substitute ( Zone Filtered ; " " ; ¶ ) ; List ( 901 ; 902 ; 903 ; 1 ; 2 ; 3 ) ) ; ¶ ; " " )