1 2 Previous Next 15 Replies Latest reply on Nov 12, 2008 3:14 PM by TSGal

    transferring fields

    envirotechservices

      Title

      transferring fields

      Post

      I have one fiel where the first and last name are both in the first name field and I think there's a way to write an eqation to make all the last names move to the ladt name column but I don't know the eqation or how to get to it. I have almost 7000 entries so I would prefer not to do the entire thing by cutting and pasting each one. Any help and/or suggestions?

        • 1. Re: transferring fields
          Vaughan
            

          Assuming that the last name is *always* the last word in the field, the calculation would be:

           

          Rightwords( first name field ; 1 ) 

          • 2. Re: transferring fields
            envirotechservices
               If I press ctrl + on the keyboard a "replace field contents" box comes up. Then I press "replace with calculated result" and the box titled " specify calculation" comes up. Where do i put the equation? What do i need to highlight? When I entered in Rightwords( first name field ; 1 ) in the blank box, it said "the specified field cannot be found". What exactly are the steps that I need to take?
            • 3. Re: transferring fields
              TSGal

              envirotechservices:

               

              Thank you for your post.

               

              First of all, make sure you are doing the Replace in the empty field where you want to put the First Name.  If you do this in the existing field, it will overwrite all of your existing data. 

               

              Next, the formula:

               

              Rightwords ( first name field ; 1 )

               

              is the correct formula.  However, you need to substitute the name of the field that has the data.

               

              In the calculation dialog box, the left side contains a listing of field names from the current table.  Find the field that currently contains the field name.  Let's assume it is called "NAME".  Double-click on this field, and it will appear in the lower portion where the calculation is entered.  It might possibly contain the table name with it.  The calculation may look like:

               

              Rightwords ( NAME; 1 )

               

              Is that clearer?  Let me know if you run into any difficulty.

               

              TSGal

              FileMaker, Inc. 

               

              • 4. Re: transferring fields
                envirotechservices
                  

                So i did the formula, but it kept the last name in the first name box as well as copying it and putting it in the last name box. so now theres a first name and a last nam,e in the first name field and a last name in the last name field. how do i get rid of the last name in the first name box?

                • 5. Re: transferring fields
                  Kundinger
                    

                   
                  If I understand you correctly, you have...
                   
                      first_name_field = [John Doe]
                   
                  and, you would like it to be...
                   
                      first_name_field = [John]
                      last_name_field = [Doe]
                   
                   
                  In simplistic terms, you can do this...
                   
                  A. create a NEW field "first_name_result"  as 'TEXT'

                  B. create a NEW field "last_name_result"  as 'TEXT'

                  C. Replace the contents of "first_name_result"

                       - go to FMP menu "Records" and 'select' "Show All Records" 

                       - 'click' in the new field "first_name_result"

                       - go to FMP menu "Records" and 'select' "Replace Field Contents..." 

                       - 'click' the "Specify" button next to "Replace with calculated result:" 

                       - in the large text area (lower portion) of the dialog box, type...

                             <<  LeftWords ( first_name_field ; 1 )  >>

                       - 'click' "OK" and then 'click' "Replace"

                  D. You should now have your First Names DONE!!!

                  E. Replace the contents of "last_name_result"

                       - go to FMP menu "Records" and 'select' "Show All Records" 

                       - 'click' in the new field "last_name_result"

                       - go to FMP menu "Records" and 'select' "Replace Field Contents..." 

                       - 'click' the "Specify" button next to "Replace with calculated result:" 

                       - in the large text area (lower portion) of the dialog box, type...

                             <<  RightWords ( first_name_field ; 1 )  >>

                       - 'click' "OK" and then 'click' "Replace"

                  F. You should now have your Last Names DONE!!!

                    

                   

                  IMPORTANT NOTES:

                   

                   - I'm assuming you know how to create new fields and add them to your existing layout and delete them when finished

                   - You understand that I do NOT know the exact name of your existing "first_name_field"; you will need to change this above

                   - Using this method does NOT alter the contents of the existing field "first_name_field"

                   - If you are comfortable performing the 'replace' command, you should be able to move your data back into the existing fields of YOUR system 

                   - You could have, and most likely will have issues with names like...

                   

                            John A. Doe

                            Dr. John A.Doe

                            Dr. John A. Doe, Jr.

                            Dr. John A. Doe, Jr., PhD

                            Dr. John A. "Jack" Doe, Jr., PhD

                   

                   --- for this, I would recommend searching various FMP resources for advanced "text parsing" calculations, such as...

                   

                            <<  http://www.atpm.com/12.05/filemaking.shtml  >> 

                   

                   

                   

                  I hope this helps...  Good Luck!!!

                   

                  Kundinger

                  • 6. Re: transferring fields
                    envirotechservices
                       I don't know how to create new fields...?
                    • 7. Re: transferring fields
                      TSGal

                      envirotechservices:

                       

                      Thank you for your post.

                       

                      Pull down the File menu and select "Manage -> Database..."

                       

                      A new dialog box will display.  Click on the middle tab "Fields", and the fields for the current table will be displayed.

                       

                      In the lower part, there will be a place to enter information to the right of "Field Name: ".  Enter the field name.

                       

                      To the right of that, there is a pop-up menu (Type: ) and set this to Text (if it isn't already).  Click "Create", and a new field is created.  Click OK to return to your database layout.

                       

                      If you need clarification for any of the above steps, please let me know.

                       

                      TSGal

                      FileMaker, Inc. 

                      • 8. Re: transferring fields
                        envirotechservices
                          

                        first name result and last name result didnt appear as a new field, it appeared in the body, where all the information goes. everything is all spaced out and first name result and last name result are duplicated several times un there company field

                        • 9. Re: transferring fields
                          envirotechservices
                             the one thing i did that was different was when you said to go to manage --- database under file, i went to define---database because manage wasn't an option.
                          • 10. Re: transferring fields
                            TSGal

                            envirotechservices:

                             

                            Sorry for the confusion.  I assumed incorrectly that you were using FileMaker Pro 9.

                             

                            It sounds like you already have your fields defined, so there is no need to create another one.

                             

                            TSGal

                            FileMaker, Inc. 

                            • 11. Re: transferring fields
                              envirotechservices
                                 So this is my problem: I have a first name field and a last name field. I have the first and last name of about 6000 clients in the first name field. I need to move the last name to the last name field and keep only the first name in the first name field. using the replace field contents box, what equation do i use to make that happen?
                              • 12. Re: transferring fields
                                TSGal

                                envirotechservices:

                                 

                                For the Last Name field, use the calculation:

                                 

                                Rightwords (First Name; 1)

                                 

                                When that is finished, you will now see the Last Name in the Last Name field.  You will still see the first and last name in the First Name field.

                                 

                                Next, for the First Name field, use the calculation:

                                 

                                Left (First Name; Length (First Name) - Length (Rightwords (First Name; 1) - 1 )

                                 

                                That is, if your first name consists of one or more words,  We take everything up to the last name and subtract one more for the space that precedes the last name.

                                 

                                TSGal

                                FileMaker, Inc. 

                                • 13. Re: transferring fields
                                  envirotechservices
                                    

                                  When I do that exact equation it says " there are more "(" than there are ")" or there are more ")" than there are "(""

                                  • 14. Re: transferring fields
                                    envirotechservices
                                       ok, those smiley faces weren't supposed to be there. it says the number of parenthases arent equal.
                                    1 2 Previous Next