1 2 Previous Next 22 Replies Latest reply on Aug 4, 2017 7:15 AM by fmpdude

    Script to add a new field?

    jackmac

      Hi all,

       

      I want to push out a new front end to a system and I'd like it to add a new field to a table as part of a script it runs on open.  Is it possible to do this?  Simple request you would think but not sure if it's possible via a script.

       

      TIA

       

      Jackmac

        • 1. Re: Script to add a new field?
          philmodjunk

          Scripts cannot modify schema (table and field definitions).

           

          You'll either need to deploy an updated file with the added field or find another way to do what you want.

           

          What would you use this new field for? Much of the time, I find that such requests are better served by adding another related record instead of adding another field dynamically to a table definition--which can't be done with the current product anyway--at least via a native FileMaker tool.

          • 2. Re: Script to add a new field?
            planteg

            Hi,

             

            it's possible through JDBC,  see SQLEditor - Error when trying to export to a database

            • 3. Re: Script to add a new field?
              fmpdude

              You could use SQL Editor if you're exported the database schema to it first.

               

              OR....

               

              You could use a SQL tool like RazorSQL to connect to your LIVE filemaker database and just issue simple ALTER TABLE commands. Just tell Razor where your FMP JDBC driver is and you're on your way. Razor works with all JDBC compliant databases (all of them!). It's a great tool for $99.

               

              OR .....

               

              You can ALTER TABLE within JDBC code that you could trigger from your LIVE FMP application. Or, what I would do in practice, is to just run the JDBC code to execute the ALTER TABLE externally without FileMaker initiating it.

               

              Also, using the free FMP JDBC driver, you can get Table Metadata (table names, field names, field types) programmatically.

               

              A little code goes a long way...

               

              ------------

               

              HOPE THIS HELPS.

              2 of 2 people found this helpful
              • 4. Re: Script to add a new field?
                philmodjunk

                Which are all examples of tools not native to FileMaker. Whether dynamically adding field Is a good idea depends on why it's being added.

                 

                To further her my education, do the above mentioned tools just add the definition to the table or can they also modify layouts?

                 

                Even if you can add the field to a layout as well as the table, seems to me that there are significant limits to what your solution can do with the field once you've added it, but I welcome correction on that opinion.

                • 5. Re: Script to add a new field?
                  beverly

                  What part of ODBC/JDBC is not 'native' to FileMaker? There are specific driver(s) included with install (one for each version) just to allow sharing of FM with ODBC/JDBC. Using the guide:

                  https://fmhelp.filemaker.com/docs/16/en/fm16_odbc_jdbc_guide.pdf

                  "FileMaker 16 ODBC and JDBC Guide"

                  https://fmhelp.filemaker.com/help/16/fmp/en/#page/FMP_Help%2Fodbc-jdbc.html

                  (help topic)

                  You cannot _alter_ layouts with any method other than access to the layout. (layout mode, form or list views; table view, browse mode). And automatically adding fields on Create with Import.

                  Beverly

                  • 6. Re: Script to add a new field?
                    fmpdude

                    The above tools modify the DB structure as in DDL: ALTER TABLE

                    • 7. Re: Script to add a new field?
                      PeterCross

                      I have Razor and didn't even realize this was possible.  Thanks for sharing this.

                      • 8. Re: Script to add a new field?
                        fmpdude

                        Glad to help.

                        • 9. Re: Script to add a new field?
                          philmodjunk

                          Just keep in mind that adding a field that does not appear on any layout and which is not explicitly referenced in any script, (Question: can the methods described here redefine a calculation field already in the table to reference the new field?), is going to put some significant limits on what you can do with the new field until you go in as a developer and make additional design changes to use the new field.

                           

                          To me, if you have to do that, it's simpler to use Manage Database to add the field at the same time that you've opened up the solution to make the other changes needed in order to make full use of the field.

                           

                          And before anyone tries to correct me, yes you can use indirect references to access the new field such as using ExecuteSQL to get the name of the field, then using name based references.

                           

                          Wouldn't be surprised to be told that there are ways via JDBC and/or Java Script. But that still leaves a great many options for how we might use a newly added field unavailable until we go in and make additional changes to the design.

                           

                          So if you can work within those limits, you have an automated method for adding new fields.

                          • 10. Re: Script to add a new field?
                            fmpdude

                            ALTER TABLE is a generic database-wide (all SQL vendors support) concept.

                             

                            I don't recall that the OP's goal was to have a new field appear on a layout, Phil. That's a FMP only thingy.

                             

                            And, yes, since FMP doesn't support anything beyond relatively simple SELECT, you would use JDBC in any number of tools to execute the ALTER TABLE, depending on your needs, naturally.

                             

                            When I wrote logic to sync MySQL and FMP I created tables and fields dynamically. There was ZERO reason to worry about layouts at that time.

                             

                            So, aside from your apparent preferences on what's "best", it all depends on what the OP wants and what works best for him.

                            • 11. Re: Script to add a new field?
                              philmodjunk

                              I think that you are reading more into my reply than I wrote.

                               

                              I didn't say that I thought my approach was best, just pointed out that there are limits to the method you have described. (I really don't think that I have recommended a specific method here at all really) If you can work within those limits for what you need to do, you have a useful tool. If you can't, it's not going to work for you.

                               

                              My point from the beginning, though it could have been better stated, was that we need to hear back from the original poster as to why they want to add that field. I asked that question for that reason in the very first reply that I made and pointed out that more often than not, when someone asks this question, adding a new related record turns out to be what they really need.

                               

                              Until we hear back, we are all polishing our crystal balls and trying to fortell what might be "best" for them.

                              • 12. Re: Script to add a new field?
                                planteg

                                From the OP

                                 

                                I want to push out a new front end to a system and I'd like it to add a new field to a table as part of a script it runs on open.

                                 

                                My reading is that new front end needs to add a field to a table. The products we know that are used to transfer the new version of a file (developed file) to a production file (and still maintaining data) can't add fields to the hosted solution. Thing is it's sometimes needed. Using JDBC would let you do it. But there is another part to the story: how could one change or add a new relation ?

                                 

                                jackmac , please come back to this thread.

                                1 of 1 people found this helpful
                                • 13. Re: Script to add a new field?
                                  fmpdude

                                  This happens a lot on the forum: An "OP" posts a message and that thread then goes into a hyper-factorial-super-loop dissection of 2^n possibilities.

                                   

                                  More updates form the OP regarding what was tried, how it worked, etc., would help us better help him.

                                  • 14. Re: Script to add a new field?
                                    jackmac

                                    The "OP" as you say has been reading this post with great interest and trying to make the solutions work.  Seriously!  I thought we were a support forum and not some sort of point-scoring group! Rant over.

                                     

                                    Some fantastic suggestions here and if I'm honest I'm not sure I can get them to work effectively.  The reasoning behind the original question is that I write systems with a front end/back end which as we know allows for more flexibility and security.  Where this is difficult is when I want an update which needs another field in the 'back end'.  I always write in blank custom fields which I can use as a field but as was pointed out, the type of field and relationship is always going to be tricky.  If there were a way to create a script which effectively said File XXX, insert text field.  If this were into an already related table there may be no problems.  I think it would always be a low-level answer for basic fields and would never allow calculations etc.

                                     

                                    I try not to use 3rd party plug ins as I don't know how they were written - I prefer to write myself so at least I know how it is done but there are occasions when it's necessary.

                                     

                                    Am I looking at this in the wrong way? If I were to push out an update which had the 'back end' file in question and the new front end.  Could I write an effective import script for the 'back end' then the new front end looks at that?

                                    1 2 Previous Next