14 Replies Latest reply on Nov 11, 2015 9:25 AM by LenLevin

    Parsing Data

    LenLevin

      I am a FMP neofite.

       

      I am trying to separate the data in a field into three separate fields.  The data is First Name, Last Name, Middle Initial and Date of Birth in the format  LN,[space] FN[space]MI[space]9/10/1968.  I want to delete the 'comma' as part of the separation.

       

      I have the data field and the three separate fields.  Is there a way to separate the data into three individual fields in one calculation.  I only need to do it one time

       

      I googled this and found some help about parsing.  Have not had much help in making this work.

       

      Any help would be treating appreciated.

       

      Len

        • 1. Re: Parsing Data
          Mike_Mitchell

          Hello, Len. You have four bits of data and three destination fields, so I'm going to assume you want last name, first name, and birthdate. You'll be able to extrapolate from this calculation to get what you need if you need the middle initial.

           

          Let ( [

          sourceData = (field with all the stuff) ;

          sourceDataList = Substitute ( sourceData ; " " ; "¶" ) ;

          lastName = Substitute ( GetValue ( sourceDataList ; 1 ) ; "," ; "" ) ;

          firstName = GetValue ( sourceDataList ; 2 ) ;

          birthDate = GetValue ( sourceDataList ; 4 )

          ] ;

           

          (lastName, firstName, or birthDate, depending on what you want)

           

          )

           

          Use this calculation in a Replace Field Contents command to populate the three fields you need.

           

          HTH

           

          Mike

          • 2. Re: Parsing Data
            jbrown

            Hi.

            Each of the fields you mention would have to be calculation fields that would parse the data in the following manner:

            YourTable::FirstName  =  GetValue ( Substitute (DataField ; ", " ; "¶") ; 1 )

             

            This calculation first takes out the comma and the space and replaces those with a ¶.  So you get this:

            Jeremy

            Brown

            1/1/2000

             

            Then the calculation gets the first row using the GetValue() function. That goes into the FirstName field.

             

            You'd do the same with the other fields using 2 and 3 in the GetValue function instead.


            Look up those two functions.GetValue   and Substitute

             

            I think you could do this as an auto-enter calc as well. That would probably work if you're importing or you create a new record. Be sure the checkbox "Do Not Evaulate . . ." is unchecked.

            • 3. Re: Parsing Data
              coherentkris

              If your doing any kind of text parsing you should get intimate with Left(), Right(), Middle(), Left Words(), Middle Words(), Right Words(), Left Values(), Middle Values(), Right Values(), GetValue(), Substitute(), PatternCount(), WordCount(), Value Count() Position() and Replace(). These are the bread and butter functions of text parsing. The delimiters should also be  solidly in your vocabulary.

              Word separators in FileMaker Pro | FileMaker

              • 4. Re: Parsing Data
                jbrown

                Sorry. I missed the part about you doing it only one time. GO with Mike's suggestion

                • 5. Re: Parsing Data
                  Mike_Mitchell

                  Jeremy Brown wrote:

                   

                  Hi.

                  Each of the fields you mention would have to be calculation fields ...

                   

                  No they wouldn't. You missed the part about "only need to do it one time". If you make them calculation fields, you'll have to have the data in the original format for all new records going forward.

                   

                  Edit: Didn't see your reply. Blasted lack of a refresh!   

                  • 6. Re: Parsing Data
                    ErikWegweiser

                    Hi, Len:

                     

                    Where "Data" is your raw data field, you might try:

                     

                    LN = Left( Data; Position( Data; ","; 1; 1 ) - 1 )

                     

                    FN =

                    Let(

                    rightpart = Right( Data; Length( Data ) - ( Length( LN ) + 2 ) );

                    LeftWords( rightpart; WordCount( rightpart ) - 2 )

                    )


                    MI = MiddleWords( Data; WordCount( Data ) - 1; 1 )


                    DOB = GetAsDate( RightWords( Data; 1 ) )


                    This works with first names and last names that have spaces in them.

                    This will not work if there are commas within the names

                    This will not work if the middle initial is other than a single word or letter (including blank).


                    -- Erik

                    • 7. Re: Parsing Data
                      LenLevin

                      MIke,

                       

                      Thanks for the reply.

                       

                      Just to be clear.

                      • I have a field with the original data.
                      • I need fields for each of the parts
                      • I need a field, in your example 'SourceDataList' that is a calculation or is this a script?

                       

                      Thanks

                       

                      Len

                      • 8. Re: Parsing Data
                        Mike_Mitchell

                        Len -

                         

                        That is a calculation. You would use that in either a field (I don't suggest that for a one-time operation) or, as I said, in the Replace Field Contents command in just a plain field (Text or Date).

                         

                        menu.png

                        • 9. Re: Parsing Data
                          LenLevin

                          Mike,

                           

                          I am really struggling with the concept.

                           

                          What I have done is to create fields FN, LN & DOB as text files.  I created a field sourceDataList as a text field.  The sourceData field is named FNLNDOB

                           

                          In Browse mode I select the sourceDataList field, goto Records Menu, Replace Field Contents, select Replace with Calculated result, specify

                          I entered the following

                                    Let ( [

                                    sourceData = (LNFNDOB) ;

                                    sourceDataList = Substitute ( LNFNDOB; " " ; "¶" ) ;

                                    LN = Substitute ( GetValue ( sourceDataList ; 1 ) ; "," ; "" ) ;

                                    FN = GetValue ( sourceDataList ; 2 ) ;

                                    DOB = GetValue ( sourceDataList ; 4 )

                                    ] ;)

                           

                          When I click OK I get the following error.

                           

                          Screen Shot 2015-11-11 at 12.06.08 PM.png

                           

                          Am I even close.

                           

                          Len

                          • 10. Re: Parsing Data
                            jbrown

                            After the "];"  you need to set what variable will be returned.   If you're in the firstName field, put "FN"  as the returned value from the Let statement.

                             

                              Let ( [

                                      sourceData = (LNFNDOB) ;

                                      sourceDataList = Substitute ( LNFNDOB; " " ; "¶" ) ;

                                      LN = Substitute ( GetValue ( sourceDataList ; 1 ) ; "," ; "" ) ;

                                      FN = GetValue ( sourceDataList ; 2 ) ;

                                      DOB = GetValue ( sourceDataList ; 4 )

                                      ];

                                           FN

                                      )

                            And be sure you're in one of your fields other than the DataSource field. Click into that field before doing the Replace Field Contents.

                            • 11. Re: Parsing Data
                              Mike_Mitchell

                              LenLevin wrote:

                               

                              Mike,

                               

                              I am really struggling with the concept.

                               

                              What I have done is to create fields FN, LN & DOB as text files.  I created a field sourceDataList as a text field.  The sourceData field is named FNLNDOB

                               

                              In Browse mode I select the sourceDataList field, goto Records Menu, Replace Field Contents, select Replace with Calculated result, specify

                              I entered the following

                                        Let ( [

                                        sourceData = (LNFNDOB) ;

                                        sourceDataList = Substitute ( LNFNDOB; " " ; "¶" ) ;

                                        LN = Substitute ( GetValue ( sourceDataList ; 1 ) ; "," ; "" ) ;

                                        FN = GetValue ( sourceDataList ; 2 ) ;

                                        DOB = GetValue ( sourceDataList ; 4 )

                                        ]

                               

                              When I click OK I get the following error.

                               

                              Screen Shot 2015-11-11 at 12.06.08 PM.png

                               

                              Am I even close.

                               

                              Len

                               

                              Sort of.  

                               

                              You don't need the sourceDataList field.

                               

                              DOB should be a date field.

                               

                              To fix FN, the calculation should be:

                               

                              Let ( [

                              sourceData = LNFNDOB ;

                              sourceDataList = Substitute ( sourceData ; " " ; "¶" ) ;

                              lastName = Substitute ( GetValue ( sourceDataList ; 1 ) ; "," ; "" ) ;

                              firstName = GetValue ( sourceDataList ; 2 ) ;

                              dateOfBirth = GetValue ( sourceDataList ; 4 )

                              ] ;

                               

                              firstName

                               

                              )

                               

                              To fix LN, the calculation should be:

                               

                              Let ( [

                              sourceData = LNFNDOB ;

                              sourceDataList = Substitute ( sourceData ; " " ; "¶" ) ;

                              lastName = Substitute ( GetValue ( sourceDataList ; 1 ) ; "," ; "" ) ;

                              firstName = GetValue ( sourceDataList ; 2 ) ;

                              dateOfBirth = GetValue ( sourceDataList ; 4 )

                              ] ;

                               

                              lastName

                               

                              )

                               

                              And to fix DOB, the calculation should be:

                               

                              Let ( [

                              sourceData = LNFNDOB ;

                              sourceDataList = Substitute ( sourceData ; " " ; "¶" ) ;

                              lastName = Substitute ( GetValue ( sourceDataList ; 1 ) ; "," ; "" ) ;

                              firstName = GetValue ( sourceDataList ; 2 ) ;

                              dateOfBirth = GetValue ( sourceDataList ; 4 )

                              ] ;

                               

                              dateOfBirth

                               

                              )

                               

                              Now, the question is ... what did Len do wrong?   

                               

                              sourceData, sourceDataList, firstName, lastName, and dateOfBirth aren't field names. They're temporary variables, used only within the scope of the calculation. Each one builds on the previous one, so when you define sourceData, FileMaker knows what its value is, and you can use it until you redefine it or the calculation ends. Same for the others. This is the point of the Let statement - to make it easier to read, and (sometimes) to improve calculation performance (because FileMaker only has to evaluate a calculation once).

                               

                              I've deliberately repeated sections of the calculation to make it a teaching exercise for you; you could use a much simpler calculation. For example, you could fix FN using this:

                               

                                   GetValue ( Substitute ( LNFNDOB ; " " ; "¶" ) ; 2 )

                               

                              But I wanted to write it out so you could see how the parsing works. It's more valuable for you to understand how to break this down than just to give you an answer.

                              • 12. Re: Parsing Data
                                Mike_Mitchell

                                Since FN is a field name, that won't work.  

                                • 13. Re: Parsing Data
                                  jbrown

                                  DoH. I should read the whole strand.

                                  • 14. Re: Parsing Data
                                    LenLevin

                                    Jeremy,

                                     

                                    That did the trick.

                                     

                                    thanks so much to Michael, you and the others who helped out.

                                     

                                    Len