3 Replies Latest reply on Feb 11, 2009 8:10 AM by TSGal

    scripting for a full name split into 2 fields.

    synergy46

      Title

      scripting for a full name split into 2 fields.

      Post

      I imported a table that has a "FULL NAME" field (Smith, John B.). I created 2 new fields: LastName and FirstName. I need to script using filter etc to strip the last name (which ends at the ',' into the last name field and then put the rest into the first name field.

      My problem is that I can not find a function for END OF FILE (as in Do While not eof() or Loop while not eof().

      Or perhaps I need to write a REPLACE function script which needs a filter to find the ',' but I don't see a corresponding function to count the number of characters -1 up to the ',' .

       

      << I am also wondering how you assign a variable to a script; ie, loop while x =25 .  What is the scripting 'assignment' statement?  I didn't find a 'let' or anything like it....   Do I create a Global variable and use the Set Field command to assign a value to it?

       

      Help is appreciated..... Thanks


        • 1. Re: scripting for a full name split into 2 fields.
          TSGal

          synergy46:

           

          Thank you for your post.

           

          Put your cursor in the Last Name field, pull down the Records menu and select "Replace Field Contents...".  Select the last option, "Replace with calculated result:" 

           

          In the next dialog box, scroll the function names on the right side and double-click on: Left ( text ; numberOfCharacters )

           

          In the bottom half, you will now see the function with "text ; numberOfCharacters" highlighted.  Double-click on "text" and only "text" will be highlighted.  In the top left, find your FULL NAME field and double-click on it.  "text" is now replaced with "FULL NAME" or "<table name>::FULL NAME".

           

          Double-click on "numberOfCharacters", scroll down the functions on the right side and select "Position ( text.....) "

           

          Make your entire calculation look something like the following:

           

          Left ( FULL NAME ; Position ( FULL NAME ; "," ; 1; 1 ) - 1 )

           

          That is take the left characters of FULL NAME up to the comma and subtract the comma.

           

          For the "First Name" field, the calculation would be:

           

          Trim (Right ( FULL NAME ; Length ( FULL NAME ) - Position ( FULL NAME ; "," ; 1; 1 ) ) )

           

          ----------------

           

          You could also do this via a script.  I'll leave that for you, but here are some additional script steps that may help you.

           

          Go to Record/Request/Page [First]

          Loop

             Set Field [ Last Name ; Left ( FULL NAME ; Position ( FULL NAME ; "," ; 1; 1 ) - 1 ) ]

             Go to Record/Request/Page [Next ; Exit after last]

          End Loop

           

          This small script takes you to the first record where you enter a loop.  I set the Last Name field to the calculation above.  I then go to the next record.  When I skip past the last record (EOF), I exit the loop.

           

          -------

           

          Set Variable [ $var ; 1 ]

          Loop

             Set Field [ DummyField ; $var ]

             Set Variable [ $var ; $var + 1 ]

             Exit Loop If [ $var > 25 ]

             Go to Record/Request/Page [ Next ; Exit after last ] 

          End Loop

           

          This script initializes a variable $var to 1.  I then enter the loop.  I set the field DummyField to the value of $var.  I then increment $var by 1, check to see if $var is greater than 25 (if so, I exit the loop), and then go to the next record.  If I reach the end of the file before $var reaches 26, then I exit.

           

          I hope this helps.

           

          TSGal

          FileMaker, Inc. 

           

           

          • 2. Re: scripting for a full name split into 2 fields.
            synergy46
              

            Thank you so much.  Your instruction was clear and it worked flawlessly.  I had no idea there was a 'position' function.

             

            However, I am somewhat confused about the script provided.  What is the purpose of the Dummyfield?  Also, is 25 supposed to represent the number of records in the table?  What would I do if I didn't know the number of records?  Isn't there an EOF function?

             

            Thanks again

             

            Ron

            • 3. Re: scripting for a full name split into 2 fields.
              TSGal

              Ron:

               

              The Dummyfield is just used as an example.  In your original post, you said:

               

              "I am also wondering how you assign a variable to a script; ie, loop while x=25.  What is the scripting 'assignment' statement?  I didn't find a 'let' or anything like it....  Do I create a Global variable and use the Set Field command to assigna value to it?"

               

              I decided to show you how to initialize a variable, and increment a variable.  I put this in a script that counts to 25 in a loop.  If the number gets above 25, then we exit the loop.  However, if the end of file (EOF) is reached before 25 is reached, then we exit.

               

              Within that loop, you are probably not just going to be counting, so I decided to set a "DummyField" to the contents of the variable.  Again, it is only an example.  If you wanted to update a Name field with the contents of the variable, then the script step would be:

               

              Set Field [ Name ; $var ]

               

              Does that make sense?

               

              TSGal

              FileMaker, Inc.