8 Replies Latest reply on Dec 1, 2016 5:08 AM by user28311

    get Table name from Layout using PHP

    user28311

      I have a php script to which I pass a database name and a static layout name to get back a list of fields it contains.

       

      These fields will be used for a mail merge.

       

      My script can return the field names on the layout with no problem but I need to know the table from which these fields are coming.

      I understand I can get table names from portals on the page but I want only the default table for the layout.

       

      I need to do this dynamically with PHP.

       

      I am really struggling with the documentation and don't see methods for doing this.

       

      Maybe I'm missing something

       

      Ben

        • 1. Re: get Table name from Layout using PHP
          mikebeargie

          There really isn't a native function in the layout object class that returns the base table name. Even in XML, -layoutnames is all you can get (the names themselves).

           

          However, you can determine the table by running a script that returns the resulting details to you in the PHP API.

           

          The script "ReturnLayoutTable"would simply be:

          Exit Script [ Get(LayoutTableName) ]

           

          And the PHP to run that and return it would be (assuming $fm is your filemaker connection object):

          $layout = "YOURLAYOUT";

          $newPerformScript = $fm->newPerformScriptCommand($layout, "ReturnLayoutTable", "");

          $result = $newPerformScript->execute();

           

          The result of the script you ran would include the exit script result from FileMaker. Performing scripts with the API uses a layout as a context parameter, so it's already putting you where you need to be.

          • 2. Re: get Table name from Layout using PHP
            user28311

            Thanks man - your idea will work but the objective is to be able to connect to a database dynamically- with little to no changes required in the target database other than creating the web user account  privileges.

             

            FX.php might have a method - but I have not delved deeply yet.

            • 3. Re: get Table name from Layout using PHP
              mikebeargie

              What exactly do you need the table name for? Is it not assumedly part of the layout name? In my experience no filemaker devs will take a layout based on “Contacts” and name it “Books”. Thus a simple switch statement through some code should be able to walk you through what you want to do.

               

              If you already know the layout name when connecting, and can retrieve the list of layout names to discover like-named layouts, I’m trying to think of what you couldn’t do dynamically.

               

              FX.php is working with the same constraints that the PHP API does. It goes through web publishing, and web publishing does not have the function you are looking for. So I highly doubt that anything else, FX.php, SimplyFM, PyFileMaker, FMAngular, DjangoFM, FMAjax, RubyFM or any open-source connector to FileMaker would have functions outside of those constraints.

               

              The best recommendation I’ve heard recently is to convert your web side to consume REST, and then use something like RESTfm (free) to setup your filemaker server as a RESTful web service. This aligns with the future of the platform (most likely APIs will dwindle in favor of REST and specific SDKs for plugins).

              • 4. Re: get Table name from Layout using PHP
                Malcolm

                FX.php has the same capabilities that the FMP PHP API does. If you want to see what you can ask for look at https://fmhelp.filemaker.com/docs/14/en/fms14_cwp_guide.pdf

                 

                user28311 wrote:

                the objective is to be able to connect to a database dynamically- with little to no changes required in the target database other than creating the web user account privileges.

                That is blue sky thinking unless you are creating a specialised tool for your own purposes. Even if you were publishing to a small group of trusted colleagues on a closed network you'll always want do a lot more than create user account privileges.

                 

                However, you don't need table names. In CWP you interact with a layout, not a table. You only have access to the fields on the layout. You reference those fields without using a table name except for, as you know, related fields.

                • 5. Re: get Table name from Layout using PHP
                  user28311

                  I found the answer to my question was to use fmi/xml query from my php file.

                   

                  $eURL = $host . "/fmi/xml/fmresultset.xml?-db=".$db."&-lay=".$lay."&-findany";

                  $xml = simplexml_load_file($eURL);

                   

                  This simple query will return all the field names as well as the table name in the XML output.

                   

                  This "blue sky" concept is to develop a mail-merge FM file as a module which could be easily connected to any FM data source and produce dynamically generated letters.

                   

                  The user can select a target database from a drop-down and then another drop down is auto-populated with all the available (<<table::fieldname>>) pulled from the database with the PHP script - these can then be "pasted" using a button, wherever needed in the letter text (field) as <<table::field>>.

                   

                  The rationale for this type of solution is to minimize the amount of work required to use this Module in one of 20 different FM files which I support in a large enterprise environment. The users can effectively create unlimited number of templates for merged letters without having full access privileges to layouts etc.....

                  • 6. Re: get Table name from Layout using PHP
                    mikebeargie

                    “This "blue sky" concept is to develop a mail-merge FM file as a module which could be easily connected to any FM data source and produce dynamically generated letters.

                     

                    Don’t think you need the table at all then, layout would be fine.

                     

                    Essentially:

                     

                    UI for user to enter in their connection details for the database.

                    UI for user to pick from a list of layouts where their contacts are.

                    UI for user to pick from a list of fields on that layout corresponding to your application.

                     

                    At no point in time do I actually need the table name to allow a user to choose their settings. And any code I made from that point on could just use the items that the user selected from their settings UI to run my complete set of CRUD actions to filemaker.

                    • 7. Re: get Table name from Layout using PHP
                      Malcolm

                      If you have an FMP file as a shell you can use built-in functions to access tables, layouts, fields, etc. You can use executeSQL to query table names and field names too.

                       

                      When you've collected all of this information, you still have to address relationships between data sources. You can't ignore the contextual nature of FMP completely.

                      • 8. Re: get Table name from Layout using PHP
                        user28311

                        You maybe correct that table info is not required but I want to preserve the same model <<table::field>> used to place fields on layouts in the traditional mail merge solution,

                         

                        This schema would be familiar to the users.

                         

                        Another reason to know the table name is that there could be field name conflicts if a layout contains other tables with fields having the same name...

                         

                        Again the underlying goal is to create a plug-and-play type module for Mail Merge - which could essentially be added to any FM server and functional for all databases being served with minimal setup.