6 Replies Latest reply on Jan 11, 2013 4:22 PM by WilliamK

    Taking values seperated by commas in one Field and copying them into seperate fields

    WilliamK

      Title

      Taking values seperated by commas in one Field and copying them into seperate fields

      Post

           After importing data I have a field "ItemTitle" that has data like this

      ShippingService:USPS Parcel Post, Tracking #: 9434609699939412855702, Postage: $18.37, Insurance: $0.00

      From that field I want to extract the data USPS Parcel Post and put it in a field called "Shipping Service".  I also want to extract the actual Tracking Number and put it in a field called "Tracking Number". 

      I tried a script that used the SetField with the MiddleWords Function and had limited success.  Because each value in the field is seperated by a comma, I think the best way is to parse the string and pull the data I want using the comma.  In other programs you could do this by creating an array.

      So bascially I want to tell FM Pro grab all text before first comma and put it in field "Shipping Service".  Grab all data between first comma and second comma and put it in field "Tracking Number".

      How can I do this in FileMaker Pro?

        • 1. Re: Taking values seperated by commas in one Field and copying them into seperate fields
          philmodjunk

               Substitute ( ItemTitle ; "," ; ¶ )

               will produce a return separated list from your comma separated list. This can be used much like the array that you describe.

               Get value ( Substitute ( ItemTitle ; "," ; ¶ ) ; 1 )

               for example would return the text: ShippingService:USPS Parcel Post

          and

          Let ( [ TheList = Substitute ( ItemTitle ; "," ; ¶ ) ;
                         FirstItem = GetValue ( TheList ; 1 ) ;
                         L = Length ( FirstItem ) ] ;
                         Right ( FirstItem ; L - Position ( FirstItem ; ":" ; 1 ; 1 ) )
                      )

               Will return the text: USPS Parcel Post

          • 2. Re: Taking values seperated by commas in one Field and copying them into seperate fields
            raybaudi

                  

                 Another like way could be:
                  
                 Let(
                 values = Trim ( Substitute ( ItemTitle ; [ "," ; ¶ ] ; [ ":"  ¶ ] ) ) ;
                 GetValue ( values ; 2 )
                 )
                  
                 to obtain: USPS Parcel Post
                  
                 and
                  
                 Let(
                 values = Trim ( Substitute ( ItemTitle ; [ "," ; ¶ ] ; [ ":"  ¶ ] ) ) ;
                 GetValue ( values ; 4 )
                 )
                  
                 to obtain: 9434609699939412855702
            • 3. Re: Taking values seperated by commas in one Field and copying them into seperate fields
              WilliamK

                   Thanks guys for the answers, still having problems though.  Forgive my ignorance, I have been away from FM Pro and programming in general for quite a while.

                   Phil,

                   So I am using your calc in the Set Field script step.  It accepts the calc but when I try to save the script I get the error: "You Must provide a name for this item and it can hove no more than 100 characters."  Am I using the wrong script step?  Here is how it looks:

                   Let ( [ TheList = Substitute ( TblShippingCharges::ItemTitle ; "," ; ¶ ) ;
                             FirstItem = GetValue ( TheList ; 1 ) ;
                             L = Length ( FirstItem ) ] ;
                             Right ( FirstItem ; L - Position ( FirstItem ; ":" ; 1 ; 1 ) )
                          )

                    

                   Raybaudi,

                   When I try your calc, I can't even save the calc. I get the error "An operator (e.g. +,-,*/...) is expected here".  The second carriage return symbol is highlighted  in the calc, so it does not like that symbol in that position.  Here is how I actually have it:

                   Let(
                   values = Trim ( Substitute ( TblShippingCharges::ItemTitle ; [ "," ; ¶ ] ; [ ":"  ¶ ] ) ) ;
                   GetValue ( values ; 2 )
                   )

              • 4. Re: Taking values seperated by commas in one Field and copying them into seperate fields
                philmodjunk

                     What is highlighted when you see that error message?

                     Here's one issue that many folks encounter:

                     When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                • 5. Re: Taking values seperated by commas in one Field and copying them into seperate fields
                  WilliamK

                       Arg, I figured it out.  Somehow the script name I was editing got erased, so all I had to do was renter the script name : - ).  Raybaudi, there was a missing ; in your calc.  So both of these options work nicely.  Thanks guys.

                  • 6. Re: Taking values seperated by commas in one Field and copying them into seperate fields
                    WilliamK

                         Yep, both solutions work now.  Thanks again.