10 Replies Latest reply on Jul 16, 2016 7:55 PM by robert-42it

    Table definitions statements?

    robert-42it

      I am just getting started doing my first project in FMP using FMP advanced 15 and I am finding creating tables using the GUI tedious and somewhat error prone.

       

      Is there a method to create (and import) table definitions like you would do for SQL?

       

      A MySQL table creation statement example.

       

      CREATE TABLE customers (

        customer_id           INT            PRIMARY KEY   AUTO_INCREMENT,

        email_address         VARCHAR(255)   NOT NULL      UNIQUE,

        password              VARCHAR(60)    NOT NULL,

        first_name            VARCHAR(60)    NOT NULL,

        last_name             VARCHAR(60)    NOT NULL,

        shipping_address_id   INT                          DEFAULT NULL,

        billing_address_id    INT                          DEFAULT NULL

      );

       

      I looked at the database design reports, both HTML and XML, but that didn't seem to yield anything really useful.

      I guess I could create tables by creating a csv file with column names and import that to create basic tables, but I wouldn't get column definitions/attributes.

       

      I am sure this will be the first of many questions as I delve into this interesting solution.

      Thank you

      Robert

        • 1. Re: Table definitions statements?
          David Moyer

          Hi,

          I believe that there is a way to get a whole bunch of hand-work done automatically via External SQL Sources.

          Please look up ODBC data sources in FM help.  Look for "Shadow Schema".

          And other folks will chime in presently.

          1 of 1 people found this helpful
          • 2. Re: Table definitions statements?
            wintertj

            What you could look into doing is creating the tables and all their field definitions in a database that has an ODBC driver that is compatible with FileMaker ESS (SQL Server and MySQL being probably the two most widely used). Add the external table to your FM relationship graph tab of the manage database window. When you do this, FileMaker will apply FileMaker compatible column attributes automatically. Then (and someone might need to back me up here) you should be able to copy and paste the ESS table on the tables tab of the manage database window, the pasted result table will be a copy of the ESS table but will be a local FM table, with all field attributes in tact. Then, just delete the ESS table and click the checkbox to also delete if from the relationship graph (or manually do it after the fact). Note: ESS won't bring in any fields that contain binary data.

            1 of 1 people found this helpful
            • 3. Re: Table definitions statements?
              robert-42it

              Thank you Tony/David I looked into what you suggestions and it looks do-able but might be more of a solution than I need at the moment (but a handy tool further down the road).

               

              So is everyone pretty much creating all the tables and etc. in the "Manage Database" GUI?

              • 4. Re: Table definitions statements?
                David Moyer

                yes.

                Once you get one table done, you can start using the Copy and Paste functions available in Advanced to speed up your development.

                And, of course, go ahead and get the keyboard shortcuts down right away.

                1 of 1 people found this helpful
                • 5. Re: Table definitions statements?
                  wintertj

                  Yep. FileMaker doesn't have anything like SQL Server Management Studio or the various MySQL tools out there where you can issue an SQL command to build a table. You have to do it in the GUI because the other option is.... nothing. The closest I can think of is to go ahead and build the tables in MySQL or SQL Server, then hook them up via ESS to FileMaker.

                   

                  The FileMaker Training Advanced series (PDF book and sample fmp12 files) costs $20 (FileMaker Training Series | FileMaker) and has a very thorough overview of ESS (External SQL Sources) in Lesson 54, which is only about 20 pages many of which are diagrams that show you how field attributes in SQL Server (or MySQL) get translated into the various field options (Auto-Enter, Validation, Storage, Furigana tabs) in FileMaker that you are finding out are in fact quite tedious if you are building a whole bunch of tables and/or fields from scratch.

                   

                  If you will be using FM a lot or even a little, the 20 bucks on the advanced training will be well worth it as in some cases it is more useful than help or even this forum. If you ever see the need to run a development version of FM Server, then save the 20 bucks and instead pay $100 for a FileMaker Developer subscription because that will get you the Advanced training series AND a copy of FM Server plus other stuff.

                  • 6. Re: Table definitions statements?
                    robert-42it

                    The advanced training for 15 just came out and I picked up a copy Thursday.  I agree it is a great resource and a bargain at $19.99.

                     

                    I will soldier on mousing my way through creating tables with 100+ columns which I know I will end up splitting off for performance reasons.

                     

                    Again thank you both for your input.

                     

                    Robert

                    • 7. Re: Table definitions statements?
                      beverly

                      XML can "import/create" table and give you some of the 'definition' (TEXT, NUMBER, DATE, etc).

                      Do a sample export (FMPXMLRESULT) and look at the METADATA section. That's the definitions you get. No summary, calculated or container fields, but it may be a help in creating a single row "definition" for import/create.

                      beverly

                      • 8. Re: Table definitions statements?
                        robert-42it

                        I tried the XML route but it looked like more work than mousing through the GUI.

                        I did however revisit the XML after your post.  In my report there is no metadata section.

                         

                        I was hoping for a poweruser interface I hadn't come across yet much like terminal in OSX versus the GUI.

                        • 9. Re: Table definitions statements?
                          beverly

                          not the DDR report. EXPORT (as XML) any table any 1-2 rows/records. Open in a browser and/or text editor.

                           

                          beverly

                          • 10. Re: Table definitions statements?
                            robert-42it

                            I just looked at the fmpxmlresult output for a table simple table I created in the Manage db GUI.

                             

                            It looked very interesting but it only seems to do the most basic column definition attributes.  The provider_id column is the Auto-enter, Unique primary key, but that information does not seem to be captured.

                             

                            It does have the benefit of syntax highlighting in BBEdit so it might be easier to work with than a csv file.  I guess you could delete the RESULTSET data and edit the METADATA to suit and import it to create the basic table.  Then modify specific fields with the "options" needed.

                             

                            Has anyone ever created tables this way?

                             

                                 <METADATA>

                                    <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="email" TYPE="TEXT" />

                                    <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="first_name" TYPE="TEXT" />

                                    <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="last_name" TYPE="TEXT" />

                                    <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="middle_name" TYPE="TEXT" />

                                    <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="nick_name" TYPE="TEXT" />

                                    <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="provider_id" TYPE="TEXT" />

                                 </METADATA>

                             

                             

                            Robert