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]
Set Field [ Last Name ; Left ( FULL NAME ; Position ( FULL NAME ; "," ; 1; 1 ) - 1 ) ]
Go to Record/Request/Page [Next ; Exit after last]
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 ]
Set Field [ DummyField ; $var ]
Set Variable [ $var ; $var + 1 ]
Exit Loop If [ $var > 25 ]
Go to Record/Request/Page [ Next ; Exit after last ]
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.
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?
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?