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

    Filtering Data


      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


      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!


        • 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





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

            this will also work

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

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

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

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

                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

                  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

                      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






                        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

                          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'



                            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

                              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

                                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

                                  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

                                    You spot on.  I love this site.



                                    1 2 Previous Next