AnsweredAssumed Answered

filter calculation question

Question asked by jasongan on Aug 27, 2013
Latest reply on Aug 27, 2013 by philmodjunk

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

Outcomes