1 2 Previous Next 16 Replies Latest reply on Nov 19, 2012 9:11 AM by BarbaraAdams

    Splitting one field into three



      Splitting one field into three


           I have a databse that is for our company website. There are a few fields in this database that have numbers (sizes) of our produts in one field. I would like to take that one field and make it into three fields. For example W x D x H is all in one field. I want to make "W" have it's own field, "D" it's own field and "H" it's own field.

           Is there a way this can be done in Filemaker Pro? I have version 8 right now on my Mac but would be willing to get Version 12 if that would make it work. I can make it work on Excel but there is a lot of formatting that has to be done to accomplish it.



        • 1. Re: Splitting one field into three

               What is the end result that you are after? Do you want to replace that 3 in one field with three separate fields that contain one part of the original field or do you just want three calculation fields that each isolate a differernt part of the data in the combined field?

               I ask this because it is genrally easier to combine data effectively than it is to parse it into separate fields.

               The exact data rather than letters W, D and H would help me know if my suggestion will work or not, but you might be able to use:

               Leftwords ( CombinedField ; 1 )

               to extract the W value and

               MiddleWords ( combinedField ; 3 ; 1 )

               To extract D


               RightWords ( combinedField ; 1 )

               To extract H

               This assumes at least one space or other word separating character between each value and the x characters.

          • 2. Re: Splitting one field into three

                 The end result would be three separate fields in the database. The w stands for Width, the D is depth and H is height. Right now the fields looks like this 48 x 43 x 18. We want to separate this field into three fields within FMPro. So Width will have it's own field, Depth will have it's own and Height will have it's own.

                 Would Filemaker 8 do this or would I need to upgrade to 12?


                 I can take the file into Excel and separate them out, but the fields are so jumbled and some are not correctly placed in the right fields. Trying to avoid doing it in Excel. We want the DB to look and be correct in Filemaker.



            • 3. Re: Splitting one field into three

                   I've given you the first step to separating them. Define the three calculation fields. Select Number as their return type. Then inspect your records to see if they are correctly showing the data that you want in each. When you are sure that they are correct, change these fields from type calculation to type number. This will convert them into number fields storing the calculated result as a number that can now be edited.

              • 4. Re: Splitting one field into three

                     Thank you. I am a fairly newbie with this software. I have worked a little bit in it but not a whole heck of a lot.  On your first answer is this a script or where can I find the commands (and where do I do it at). Will this create a new database file or will it expound on the original one?

                • 5. Re: Splitting one field into three

                       I am describing field definitions to add to your existing table.

                       Go To Manage | Database | Fields

                       Enter the name of a new field such as "Width". Select "Calculation" as the field type and click create.

                       Then enter the expression I first posted, but use your field names in place of what I used.

                       Finally, enter layout mode and use the field tool to place this new field on your layout. Enter browse mode and check to see if it contains the value you need. If it does, repeat the above steps for depth and height.

                       Step 2,

                       Return to Manage | Database | Fields select each field and change their field types by selecting "number" in the field type drop down and then clicking "change".

                  • 6. Re: Splitting one field into three

                         Well I just looked at my version of FileMaker Pro and the commans you name above are not in it. I have version 8. What version are you working from?

                    • 7. Re: Splitting one field into three

                           Those commands are listed as originating in Version 6.0 or before.  Can you post a screen shot of where you are looking for them?

                      • 8. Re: Splitting one field into three

                             Filemaker  >>  File Menu > Manage | Dropdown > Database

                             Choose Fields Tab in Manage Database Window.   [Tables]  [Fields]  [Relationships]

                             Do you have Administrator Rights for the Database?  Is the database on your computer?

                             Are you working on a COPY of the database?  Practising on the actual database is a mistake.

                        • 9. Re: Splitting one field into three

                               Here is a screen shot of what I am looking at. If you look at diminsions2 and metdimensions2, those are the fields I want to split into three other fields. So I will have a Width, Depth and Height field in the record.


                          • 10. Re: Splitting one field into three

                                 And what I have suggested should do the job.

                            • 11. Re: Splitting one field into three

                                   I was hoping you would post a screenshot of where you were looking for the commands that Phil suggested you use, but you said were not in your version.

                              • 12. Re: Splitting one field into three

                                     Just for grins, I am uploading the specify calculation dialog box for FileMaker 5.5 in windows. Note how very little has changed with this dialog box over the years...

                                • 13. Re: Splitting one field into three

                                       When it ain't broke...

                                  • 14. Re: Splitting one field into three

                                         I'm sorry but I do not have a heading under File that says Manage Database. See my image. I have Version 8

                                    1 2 Previous Next