Hi, mstenberg (I'd address you by name, but you didn't provide one).
You can do this in two steps. Say your initial field is Field1. Your new field (where you want the last two digits) is Field2. We're going to use the Replace Field Contents function (from the Records menu).
1) FIRST STEP: Make a backup of your database. The Replace function is FileMaker's nuclear weapon. If you screw up, you can't Undo.
2) Find all records in the relevant table (Ctrl-J).
3) Place your cursor in the new field, Field2. Choose Replace Field Contents and then choose Replace with Calculated Result. You'll get the calculation dialog. Enter this calculation:
Right ( Field1 ; 2 )
This will take the rightmost 2 characters from Field1 and place them in Field2. It does not alter the contents of Field1.
4) Place the cursor in the old field, Field1. Choose Replace Field Contents again, choose Replace with Calculated Result. Enter this calculation:
Left ( Field1 ; 5 )
This will take the leftmost 5 characters from Field1 and place them in ... Field1. In other words, you "replace" the contents of Field1 with the first 5 characters of Field1.
Let me know if this doesn't make sense.
Wow, I can't believe I got a response so quick! I spend more time trying to do this than it took to get the solution. And yes that makes sense. I did not know what formula to use from the calculations list. It's mind boggling when you see it all. I tried many formulas and the Search and Replace function to no avail.
Thank you so much.
Value Added Services Inc
Good answer Mike.
Also, mstenberg, if you're doing this to new records on a regular basis it might be a good idea to create a field called MembershipCode where the code is entered and then take Mike's calculations and apply them to the membership number and membership year fields as auto enter claculations like:
MembershipNumber (auto enter calculation = Left (MembershipCode; 5)
MembershipYear (auto enter calculation = Right (MembershipCode; 2)
That way you don't have to do a manual replace field contents from time to time, it will just do it automatically when you enter the code into the code field.
If you're only needing to do this once then Mike's post is a good solution.
Thank you so much!! In just a few minutes I completed the task and only had a few records with errors. You saved me about 15+ hours of work. I would have had to do each one manually. I've worked with FMP for several years but do not know some of the details like this. I am now going through the FMP11 training series. I have done a lot with my database and still have sooooo much to learn.
Again, thank you for the quick response and the easy method.
Value Added Services Inc