3 Replies Latest reply on Aug 27, 2013 11:00 AM by jasongan

    how to filter word in a text

    jasongan

      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

        • 1. Re: how to filter word in a text
          erolst

          It might be better to store these numbers and their province abbr. within individual records instead of putting them all into one field; but if you (have to) put them into one field, you should use a carriage return as delimiter; it's more robust and obvious than a blank.

           

          And speaking of which: if you have two carriage-delimited lists, you can simply filter one through the other. Here an example using fixed-value lists; the expression

           

          Let

                    ( [

                    zoneOriginal = "1 2 3 901 902 903 1015 2045" ;

                    zoneAsList = substitute ( zoneOriginal ; " " ; ¶ ) ; // when/if you use a carriage-returned list, this assignment is unnecessary

            ON_Stores = List ( 901 ; 902 ; 903 )

                    ] ;

                              FilterValues ( zoneAsList ; ON_Stores )

                    )

           

          gives you

           

          901

          902

          903

          1 of 1 people found this helpful
          • 2. Re: how to filter word in a text
            raybaudi

            Hi

            you'll need to use FilterValues ( )

             

            Let(

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

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

            )

            • 3. Re: how to filter word in a text
              jasongan

              Thank you very much. this is exactly what I am looking for.

               

               

              Jason