1 2 Previous Next 19 Replies Latest reply on Jul 19, 2017 5:21 AM by BlancheMcdade

    Filtering Data

    BlancheMcdade

      Hi File Maker Pro Community,

       

      I need help with a filtering issue.  Below is the field I need filtered into a new field.

      Field 1Field 2 Filtered Results
      AMA|P|0|RA|0AMA
      CAACH|NP|0|M|0CAACH
      GAAM|P|0|NBA|60GAAM

       

      Field 1 data could have from 3 to 6 letters.

      In Excel, I can  do this by simply using Text to Column, output Delimited and selecting as the starting point |  ).

      How can I do this in File Maker Pro 16?

      I have tried using Filter (texttoFilter;filterTex) and FilterValues ( textToFilter ; filterValues )

      Thanks ,help is appreciated!

      Blanche

        • 1. Re: Filtering Data

          Substitute (Field;  "|"; ¶)    to separate the field into a list

           

          then use getvalue() to pull the numerical line you want to display

           

           

           

          Capture.PNG

          2 of 2 people found this helpful
          • 2. Re: Filtering Data
            coherentkris

            this will also work

            Left ( textField ; Position ( textField ; "|" ; 1 ; 1 ) -1 )

            2 of 2 people found this helpful
            • 3. Re: Filtering Data
              bigtom

              Left ( Field1; Position ( Field1; "|"; 1; 1)-1)

              2 of 2 people found this helpful
              • 4. Re: Filtering Data
                BlancheMcdade

                This worked except, it did not bring over the data that did not have the text I did not want.  Is there another operation I can use to bring in the other correct data?

                 

                Thanks Blanche

                • 5. Re: Filtering Data
                  BlancheMcdade

                  Thanks, but I don't quite understand this formula.  I am a self taught File Maker user.

                   

                  Still learning Blanche

                  • 6. Re: Filtering Data

                    This is why the community is so beneficial.  always multiple ways to accomplish a task.  Definitely helps people with a wide range of conceptual understanding.    Pick which method works best, but more importantly one that you understand and are comfortable with.

                    • 7. Re: Filtering Data
                      fmpdude

                      It sounds from your posting and your Field2's "Filtered Results" that you only want the LEFT portion of the field's data. Any of the proposed postings here gives you that data.

                       

                      For example, using bigtom's suggestion:

                       

                      LEFT("AMA|P|0|RA|0", Position ( "AMA|P|0|RA|0"; "|"; 1; 1)-1)  ==> AMA

                       

                      Same as your stated requirement:

                       

                       

                      In your actual application, you would have the field name that has the data where I hardcoded "AMA|P|0|RA|0" in the two places above.

                       

                      Problem solved, right?

                      • 8. Re: Filtering Data

                        which formula?

                         

                        mine puts field A into this format

                        AMA

                        P

                        0

                        RA

                        0

                        then you select which "line" you want with the getvalue(field A; #)

                         

                         

                        the Left()  formula looks for the separator and the pulls the  text relative to  left side

                        • 9. Re: Filtering Data
                          bigtom

                          What is the problem exactly?

                          • 10. Re: Filtering Data

                            My original  post with the Let() function makes it more complicated looking, but is really two functions put together.  You can use this one below if it makes more sense.  I highlighted the only number you would have to change if you you wanted  any other value from 'Field A'

                             

                            Examples:

                            Field A = AMA|P|0|RA|0

                             

                            Field B

                            GetValue( Substitute (field A;  "|"; ¶) ; 1) = AMA

                             

                            Field C

                            GetValue( Substitute (field A;  "|"; ¶) ; 2) = P

                             

                            Field D

                            GetValue( Substitute (field A;  "|"; ¶) ; 3) = 0

                            1 of 1 people found this helpful
                            • 11. Re: Filtering Data
                              coherentkris

                              the calculations already proposed do exactly what you asked for.. what is it that you really want? Define "the other correct data" and what you expect from the calculation...

                              • 12. Re: Filtering Data
                                coherentkris

                                this formula .. Left ( textField ; Position ( textField ; "|" ; 1 ; 1 ) -1 )

                                uses two functions..

                                Left() and Position()

                                You can look up how they work in the help documentation.

                                Left() requires two arguments.. the text to work with and an integer that represents the number of characters to return from the text.

                                Left ( "this text" ; 3 ) will return "thi"..

                                In order to extract the portion of text from the start of the text to the first | char you need to find the position of the first | . That is what the position function does.

                                • 13. Re: Filtering Data
                                  philipHPG

                                  Your statement with three negatives is quite confusing, but after reading through it numerous times, I think what you are saying is that the calculation suggested by bigtom and coherentkris work only when there is a pipe character followed by a list of other characters (that you want to filter out). If there is no pipe character and other characters to filter out, then that calculation doesn't work.

                                   

                                  In that case you need to apply that calculation only if there is a pipe character, so:

                                   

                                  If ( Position ( textField ; "|" ; 1 ; 1 ) ; Left ( textField ; Position ( textField ; "|" ; 1 ; 1 ) -1 ) ; textField )

                                   

                                  Alternatively, you can use @brianb's calculation which should still work regardless of whether there is a pipe character or not.

                                  • 14. Re: Filtering Data
                                    BlancheMcdade

                                    You spot on.  I love this site.

                                     

                                    Blanche

                                    1 2 Previous Next