1 2 3 Previous Next 36 Replies Latest reply on May 31, 2015 11:52 AM by wimdecorte

    moving to sql database


      Is there a walk thru for moving a FM table from FM (FM13) to and SQL database?


      It would need to include the following:


      1. Move data from FM to an SQL database (let's say 1 table)

      2.  How would the scripts /layouts respond when the table was removed from FM and replaced(every layout field must normally exist in a table)?

      3. Caveats (besides the obvious...... calculated fields etc).




      And yes I'm well aware of ODBC/setup/ datasource & getting an external table-pointer  into FM table space, what I am more interested in, is  other developers having made this journey.


        • 1. Re: moving to sql database

          Scripts and layouts are based on a TO, not a table.   So you could repoint the existing TO to the new external datasource.  But... all of the links are done on the internal hidden field IDs and those will not match whatever FM can pick from the SQL database so all of the links will broken and need to be reset.

          • 2. Re: moving to sql database

            Check this link:FileMaker, mySQL, and ESS; A Little Known Secret, to Me Anyway

            Though the above is for mysql it may give you some ideas.



            • 3. Re: moving to sql database

              Great article! Keep in mind that developers in FM tend to name fields (and other things) in ways that are NOT very SQL-friendly. If any attempts fail using the methods in the article, perhaps the names may be the issue.



              • 4. Re: moving to sql database

                Wow, I as much as the next person wouldn't miss a good rant...

                "In real databases" ... Filemaker is not a database it's an RDE. Try dropping a field on a html layout then redefine it in the database and keep the link intact. There must be reasons why fields are linked the way they are.

                Anyone with half a brain knows not to put spaces and weird characters in field names. Filemaker allows it and for the sake of the demo's they are "human readable" to a broader audience. I guess a marketing decision.

                I'm all for standards and at the moment Filemaker is just, different...

                • 5. Re: moving to sql database

                  codeslave wrote:


                  Put a bullet in their heads.


                  Aren't you a nice guy...

                  You may not like what you see or discover but there is no call for that kind of language.


                  Besides; if you someone buys a car and drives it on the ice and crashes it, do you blame the car or the driver.  Just the because the car allows it does not make it car's fault.

                  • 6. Re: moving to sql database

                    Excuses in advance for the flat joke, but I can't resist: You're the master of the code you write, not the slave .


                    There is no SQL database or development platform without caveats and inconsistencies and FileMaker has it's share.

                    But code you write as your own chef. In every programming or scripting language I know, one can write messy, unreliable code.


                    It is written and documented in more than one place (i.e. FM SQL Reference) that spaces should be avoided in table and field names, if used via ODBC. Ok, that's news for beginners in database programming and there's a learning curve to make (aka driving licence)


                    FM provides the tools for making consistent record IDs:

                    - Check and restrict what users type in, if you really want users creating IDs

                    - Automatic generation of numeric IDs and even alphanumeric IDs


                    Some 2 years ago I connected a FM DB with a MySQL DB for the first time. Far from being a big SQL wizard, I can make it work. Starting with zero knowledge, everyone can do this and there's enough documentation and help available.

                    • 7. Re: moving to sql database

                      Keep in mind 2 really important points:


                      First, most of the people using FileMaker are NOT from a programing background. So you are simply insulting those that don't come from that side of IT. Things like this have nothing to do with a persons intelligence. It is instead a blurring of technological capabilities, and level of experience. Because it used to be that you couldn't ( or shouldn't ) use spaces in file names. So would it be fair to to say the millions of users that put spaces in their names "don't have a half a brain"?! Clearly not.


                      Secondly, FileMaker doesn't use the "table name" to link anything, that's why it typically doesn't matter what characters are used. In the backend, FileMaker uses table IDs, field IDs, Layout IDs...well you get the picture. It's what allows FileMaker to easily update the references to a table in most scripts and calculations if a table name/occurrence changes.

                      electon wrote:

                      Anyone with half a brain knows not to put spaces and weird characters in field names. Filemaker allows it and for the sake of the demo's they are "human readable" to a broader audience. I guess a marketing decision.

                      • 8. Re: moving to sql database

                        It was a figure of speech.

                        What was insulting was OP's remarks, so to accentuate the point...

                        I'm well aware of the fact that many Filemaker users didn't study computer science. I'm one of them.

                        What I mean is that it kind of comes with the territory. You either research stuff before or it can bite you later.

                        Filemaker is easy on the naming conventions and I myself consider it bad practise "at the moment" because it's not industry compliant and teaches bad habits from the go.

                        The reason for "at the moment" is: it may well be that in the future all database engines will allow spaces in field names.

                        Hard to imagine, but also hard to predict. What will the argument be then?

                        • 9. Re: moving to sql database

                          Gotcha. I clearly never saw some of the original post. It appears it has been edited or removed.


                          Either way, it's good for other readers to see it!!

                          • 10. Re: moving to sql database


                            It is perfectly sound advice, you may not like it, but any hired 'professional' that has not grasped such basics, has absolutely no business consulting or writing software.


                            I have columns named:

                            "This is the difference in figures between 01 2013 and 01 2014 for the purpose of the budget"

                            "This is the difference in figures between 01 2013 and 01 2014 for the purpose of the budget summary across suppliers"


                            You may consider this amusing or  a "Human right" for programmers and professionals to work this way, I don't.


                            I set up a complete reporting solution and postgres database in a day, a week later I am still battling trying to get data exported from filemaker into this database.

                            • 11. Re: moving to sql database

                              Yes connecting a small database may be a 'wizz' , but this is over 400 tables, some of which have >800 fields.

                              with a total of 80Gb of data......... and it is not a connection, it is a requirement to move some critical tables out

                              where data integraty and business rules can be applied.

                              You know... silly things , like accounts and invoicing....

                              Documentation...... for what it is worth only covers 'perfect cases' not really based in the real world.

                              Much of this mess could be avoided with a decent set of development tools and a lot less stupidity with 'modal' windows in FM


                              As regards for 'beginner comments', FM is selling this as an enterprise solution, where poorly qualified consultants  come in with slick powerpoint presentations and con management teams that it is a good enterprise solution... with licensing costs higher that Oracle.......

                              • 12. Re: moving to sql database

                                Ah...well we use "enterprise" systems in our company. And some of them are the most horrible solutions I ever used. In fact, I have replaced the ones I can with my own FileMaker solution. Say what you will about crap solutions, they exist in ANY development platform.


                                Wim's comment about the car...it's true. Bad driver's are not the fault of a bad car. Bad drivers will drive ANY car bad. I assure, had a developer like Wim or some of the other guys built that file, you would have no need to replace it. Accounting, invoicing, or otherwise. So please, so respect, even if you don't like the solution you are working with.


                                And when, or where, has FileMaker ever sold itself as an "enterprise solution"?

                                • 13. Re: moving to sql database



                                  I think it comes down to the developer / team and not the tool such as FileMaker. During the years I saw some amazing solutions done in FileMaker and quite a few horrible ones. At the same time I saw some very badly written T-SQL code. I am not an expert, but I can recognise it. This also usually confirmed by a dba who has to live with it. This was done by a professional SQL developers with salaries running nearly twice as high as an average inhouse FileMaker developer. This was done using advanced and expensive tools. This was tested, code reviewed and went through quality assurance. It does not stop it being horrible just because there are some constraints on the tool.


                                  I agree with previous posters, a "better" tool will not make you a better developer. I'd rather try to become a better developer using what I have.


                                  Finally, as far as I know, SQL server allows spaces in the table and column names. The reason developers are avoiding them are similar to the one in FileMaker -  you need to escape them in your queries and web apis. Same applies to MYSQL.

                                  I am not sure, but quick search shows me that PostgreSQL does not have this constraint either. So I suspect it is a convention and not constraint.


                                  Good luck with your migration and I hope you make friends in PostgreSQL community.

                                  1 2 3 Previous Next