3 Replies Latest reply on Jul 27, 2013 9:47 AM by MilutinPetrovic

    Field data distribution (Text parsing)

    MilutinPetrovic

      Title

      Field data distribution (Text parsing)

      Post

           OK, this should be a simple one, for You good people!

           Lets say i have a field called "Bank | Account" in the table called Employees which is consisted of records that look exactly like the field name, for example "Bank name | 123 - 456789 - 11".

           I want to create two addition fields in the same table, called "Bank" and "Account" and distribute appropriate data in to them from the original field! I know it has something to do with calculations, but I just cent seem to get the right one! 

            

           Cheers!

        • 1. Re: Field data distribution (Text parsing)
          GuyStevens

               Is the bank account number always going to be the exact same amount of characters?

               Or is the bank name always a single word? Or can it consist of multiple words? I assume it probably can. For instance "Bank Of America"

               There are functions to extract a word or certain amoint of words, or a character or certain amounts of characters from the left or right side. But if all of that is variable then it becomes a little bit more difficult.

               You could also use a filter to filter out the numbers and the hyphens but then if you have a bank name with a number in it you again have a problem.

          • 2. Re: Field data distribution (Text parsing)
            MilutinPetrovic

                 Unfortunately bank name can be anywhere from 5 or 6 letters to infinity, the same goes for the account number (plus the hyphens and spaces)...

                 If You are talking about those  Right ( Field_name ); Number_of_characters ) functions, i tried, they didn't work, or better to say, I didn't get them to work...

                 Although I have found something that goes like this:  Right(Name;Length(Name) - Position(Name;“ “;1;1)) on a official FileMaker site, which gives me a hope that this can be done being that this function is ought to return Last_Name when field is entered as First_Name Last_Name. So I guess it can be done, I just dont know how!

            • 3. Re: Field data distribution (Text parsing)
              MilutinPetrovic

                   Anyways, I answered my own question, and it will go something like this:
                    

                   Left of | mark:
                   Let(
                   [
                   ba = Banc|Account ;
                   sep = "|" ;
                   pos = Position( ba ; sep ; 1 ; 1 )
                   ] ;
                   Left( an ; pos - 1 ) // Minus 1 so that we don’t include the | mark
                   )

                         
                        Right of | mark:
                        Let(
                        [
                        ba = Banc|account ;
                        len = Length( ba ) ;
                        sep = "|" ;
                        pos = Position( ba ; sep ; 1 ; 1 )
                        ] ;
                        Right( be ; len - pos - 1 )
                        )
                    
                    
                   And I need to change the topic subject, being that correct term would be "Text parsing", but English is not my first language, so I can get it wrong from time to time...