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

    filter calculation question

    jasongan

      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