8 Replies Latest reply on Feb 13, 2014 11:45 PM by vege

    rename fields using a plugin or scripting

    vege

      Title

      rename fields using a plugin or scripting

      Post

           Hello,

           I have to rename and often to redefine huge quantities of fields, meaning I can't simple replace them with new fields, since they are connected in many ways (formula fields etc.). Are there any current (using FM Pro 11 to 13) plugins, or if not, any scripts which can do this? Windows is preferred but MacOS also is possible.

           Thanks, Vege

        • 1. Re: rename fields using a plugin or scripting
          philmodjunk

               The need to "rename often" raises the question: "Why do you need to frequently change field names?" It suggests that you may be using Field names to store data that should instead be entered into fields.

               But FileMaker will update field names throughout all your calculations, scripts and layouts when you change the name with two exceptions:

               1) If the field name is text on a layout and you have already edited the text to be different from the defined field name

               2) you have a calculation that refers to the field name inside a quoted string such as GetField ( "Contacts::__pkContactID" ). FileMaker is unable to update the quoted text. While such can be searched out and found in FileMaker Advanced's database design report, it is almost always possible to set up indirect field references that do not use quoted text to refer to the field. ExecuteSQL calculations have this same issue and can be dealt with the same way.

          • 2. Re: rename fields using a plugin or scripting
            vege

                 Hallo PhilModJunk,

                 thanks for your answer. Sorry I was unclear. I have to rename the fields only once, but in many cases I need to change the formulas too. It is a huge special purpose database with thousands of fields. The main purpose ot the renaming is to allow a sensible automatic sorting of the fields in the Manage Database dialog box which is important for im- and export procedures, among other things.

                 Any ideas?

                 Vege

            • 3. Re: rename fields using a plugin or scripting
              philmodjunk

                   As long as you do not have table::Field names inside quoted text, a name change at the Manage | Database level will also update them. And there are ways to construct your indirect field references so that they also update automatically when the field name is changed.

                   I think that there's also a tool in FileMaker Advanced for this but I could easily be wrong--have never needed it.

              • 4. Re: rename fields using a plugin or scripting
                vege

                     Yes, formulas containing the names of fields will be updated when this fields are renamed. But as I said, I have to rename thousands of fields (and often alter their complete formulas, not only the names of the fields included), therefore I want to automatise this procedure as much as possible.

                     By the way, the FM Advanced tool you are referring to probably is the database design report. You can indeed use it to search for expressions within quoted text, and then alter it in the FM files.

                     Greetings, Vege

                • 5. Re: rename fields using a plugin or scripting
                  philmodjunk

                       I was not referring to the Database Design Report.

                       

                            I have to rename thousands of fields (and often alter their complete formulas, not only the names of the fields included)

                       I'm having trouble picturing that and really don't see the purpose. Can you provide an example?

                  • 6. Re: rename fields using a plugin or scripting
                    vege

                         Hello, thanks for your patience ;-)

                         it concerns a rather specific scientific database containing case examples. For reasons of performance and the discipline involved each example has to be stored in a separate field (it can't be helped). Formula fields using the "sum" function are used to manage relating fields and combining them into groups. Therefore if a new field is added, it has to be added to the formula too.

                         The field names are encoded, which enables the user to see what the field contains and to do automatic sorting which is necessary with export procedures. E. g. s_LythFilip.CirsOlTephrCrisp_anthodo_st: The code of the name contains hierarchic information on several levels so that the fields containing the most similar information will stand next to each other when sorting by name is used, and export of groups of whatever hierarchic level ist easy.

                         Both field names and formulas are created and altered in a FM database so it is not so much work as it looks.

                         But the creating and altering process is time-comsuming and boring, therefore I want to automatize it.

                         Greetings, Vege

                    • 7. Re: rename fields using a plugin or scripting
                      philmodjunk

                           Apologies, the utility in Advanced that I was dimly recalling is for renaming the file and any file references not renaming fields.

                           There isn't really anything more that I can suggest beyond what I have already posted--that you redesign your system so that field names are not "hard wired" in to your calculations.

                           Perhaps you can post a single example of one of these calculations and then I can brainstorm with you over ways to remove the "hard wiring".

                      • 8. Re: rename fields using a plugin or scripting
                        vege

                             Thank you again Phil for concernig yourself with my problems (and my bad English). I shall take you at your word, since in fact I am not really happy with my data model.

                              

                             But first I need to tell you that I probably found a solution for my most pressing problem: the initial altering of so many field names and formulas during the process of migration:

                             I simply shall not do this but rebuild the database completely. Since FM displays fields both in the import dialog box and the design function “FieldNames” according to the order of their creation, I can use the latter to create a new database containing the old field names as records in the original order. In this database I will change the names and create the formulas and the rest of the syntax needed by a clipboard plug-in (as text string). This is done using formula fields and scripting, so it is not much work. With the plug-in I create a new database which is a copy of the original but with different field names. I shall now be able to import the contents of the original database correctly despite the fact that the field names do not match.

                              

                             However, this still leaves me with the problem of subsequent alterations. So now to my database:

                             It is containing data of vegetation science, so called releves. They basically consist of a list of plant species growing at a certain place, plus an estimation of their abundance in this place. The “normal” structure would be to create both a releve and a plant species table and to enter the abundance in a cross tab field – finished, all problems with “hard wired” formulas solved.

                             However, the main purpose of this database is to display the releves in a way that they can be compared visually. Similar records are put into a layout (called “table”, there are hundreds of them). They have to be presented in a way that matches the conventions of the discipline (no alternative will be accepted, see file): The releves are the columns and the plant species are the lines. Note please that there are additional fields to the right of the table.

                             Now the catch: The database needs an extremely good performance so that users can call up “tables” and switch from table to table very quickly.

                             The only idea I came up with within a “normal” structure was to use a script that writes a copy of the requested data from the cross tab mentioned before into a matrix like table-layout temporarily, overwriting it each time a new selection is made. But the performance of this is unacceptable slow, even with a desktop solution.

                             So I didn’t found any other way than this: Each releve takes its own field and the features – plant species – are the records! Somewhat clumsy, isn’t it? (but it works)

                             So if you have any better idea I would be very happy.

                             Greetings, Vege

                              

                             Coded name of the calculation field involved in the posted example: _StGehuFranck1984.T4.SlxAlb.SlxAlb.

                             Formula: Sum (Tabellen_test::F3641;Tabellen_test::F3642;Tabellen_test::F3643;Tabellen_test::F3644;Tabellen_test::F3645;Tabellen_test::F3646;Tabellen_test::F3647;Tabellen_test::F3648;Tabellen_test::F3649;Tabellen_test::F3650;Tabellen_test::F3651;Tabellen_test::F3652;Tabellen_test::F3653;Tabellen_test::F3654;Tabellen_test::F3655;Tabellen_test::F3656;Tabellen_test::F3657;Tabellen_test::F3658;Tabellen_test::F3659;Tabellen_test::F3660;Tabellen_test::F3661;Tabellen_test::F3662;Tabellen_test::F3663;Tabellen_test::F3664;Tabellen_test::F3665;Tabellen_test::F3666;Tabellen_test::F3667;Tabellen_test::F3668;Tabellen_test::F3669;Tabellen_test::F3670;Tabellen_test::F3671;Tabellen_test::F3672 )