5 Replies Latest reply on Nov 14, 2014 9:04 AM by WalterKnowles

    Create table from sql query



      Create table from sql query


      I've got a table that was imported from an Excel table that "just grewed" out of control. I want to normalize it for analytics and clarity, but I don't seem to find how. I'm probably staring right at it and not seeing what I need.

      If this were a traditional SQL database I would issue a SQL command like:

      create table cities as select distinct city from manuscripts


      insert into cities (select distinct city from manuscripts)

      but I don't see how I can get myself into a sort of DBA console -- or anyplace else -- to just abstractly execute SQLExecute() function calls. 

      What am I not seeing?  RTFM with a page number is probably the answer I'm looking for.

      Walt Knowles

        • 1. Re: Create table from sql query

          Forgot to mention, FMP 13 on OSX 10.9


          • 2. Re: Create table from sql query

            Getting a future copy of FileMaker is more like it. There is no such "console" for SQL queries built into FileMaker. SQL can be used only in very limited circumstances.

            But here's a way to get just one unique instance of each record:

            Define a new table with just the fields you want. Use Field options to specify "Unique values, Validate always" on your cities field.

            Now use import records to import all records from your current table into the new table. The above field options will filter out the duplicates during the import. (And you can import records from one table to another table in the same file, just follow the same steps you would follow if the tables were in two different files.)

            • 3. Re: Create table from sql query

              Thanks, PhilModjunk. I couldn't get your import to behave, so I just dumped the table I wanted to rework out to MySQL and made quick work of it where I could use full SQL. Then exported the whole normalized table set and brought it back in. I probably should have been able to do it with external calls, but that got it done.

              • 4. Re: Create table from sql query

                On the one hand it's good that you found a way to get the job done. On the other hand, what you report indicates that you have more to learn about how to work with FileMaker. Learning how to get the job done within FileMaker using it's tools (and those available through using FileMaker Advanced) will speed your development process. So I encourage you to pursue a better understanding of FileMaker.

                • 5. Re: Create table from sql query

                  No doubt. But then, on this project, Filemaker is just one part of a software ecosystem. If it takes longer to learn than the time it will save over the life of the project, then the tool that saves the most time gets the job. I'm using Filemaker because of the the non-coding interface and the ease of manipulating graphical data. If it gets in the way, I'm happy to move tasks around.