14 Replies Latest reply on Jan 23, 2013 3:54 AM by beverly

    Best resource to demonstrate FM's ESS capability to Web developers

      Hi All,

       

      The local company which developed and maintains our web site is NOT at all au fait with FileMaker's ESS capability.

       

      We are needing to embark upon an extension of the web site that will derive its additional info from the Product Table that exists in our solution for running the Mail Order side of our business.

       

      An initial discussion with the web site developer indicates that they would prefer to build a new PHP process for importing the data from an extra .csv file, as they envisage "issues" if we were to use ESS to write our data directly to their MySQL table. From this I deduce that the developer's knowledge of FileMaker's capability is so limited that he's scared to embrace it, but instead is comfortable with billing us for work on an extended importing regime that should not be required.

       

      We already use ESS to pull orders from the web site's shopping cart directly into our solution.

       

      During my discussion with Steve Winter at DevCon in Miami, he suggested writing some PHP code for the web developers that would poll the Product table in our solution at regular intervals but this is also unnecessary when ESS should be workable and driven from our end of the connection.

       

      Can anyone provide me with an easily read paper, an example, or any other resouce that will help to overcome the web site developer's inherent resistance to the use of ESS?

       

      Thanks in advance,

       

      John Wolff

      Hamilton, NZ

        • 1. Re: Best resource to demonstrate FM's ESS capability to Web developers
          beverly

          John, there are several white papers on this forum that cover ESS. The biggest problem is usually getting permission to connect to the External Source (some ISP prohibit it except through the web interface for security; or the db design is so complex it could easily break if written through ESS and done incorrectly). If you already are pulling from the website into FileMaker with ESS, you likely have the necessary permissions.

           

          An alternative to Steve excellent suggestion, if you can do so, is to make FM an ODBC source or XML/PHP source and allow the web developer query FileMaker directly into the External source. And as you said should be unnecessary if you already have ESS for pull into FM, you should also be able to push!

           

          Perhaps the problem is a complexity that does not allow you to directly push. And your developer may know that, thus the need for the import routine that fills in the correct table(s) in the correct order with sufficient validation and other necessary values. For example, if some fields/columns are encrypted on the External Source, and FM is not, you must have a method for pushing the encrypted values.

           

          I do a great deal of web publishing using FM and SQL databases (since FM could web publish!) I've run into these kinds of problems. You might have a conversation with your web developer and discover the reason for the "issue" is not lake of FM knowledge, but thorough knowledge of the SQL design.

           

          Beverly

          1 of 1 people found this helpful
          • 2. Re: Best resource to demonstrate FM's ESS capability to Web developers

            Hi Beverly,

             

            Thank you for replying with helpful suggestions, but making our file into an ODBC or an XML/PHP source is a step that's well beyond our current expertise.

             

            I've looked hard for the white papers you mention, but only found the one by Steve Lane which is comprehensive but also advisedly cautionary when it comes to writing our data to the MySQL files. That is why I'd still like to know of examples where the push of data has been accomplised successfully.

             

            As you advise, we obviously have to discover what limitations exist in structure of the MySQL files that might lead to breakage. I cannot imagine there's any encryption but there will be some validation of the imported data. I doubt that the resistance stems from complexity of the data structures but more likely from some compromises that might have departed from a well normalised design when setup previously, or merely a fear that we might be incapable of conforming with their data structure.

             

            I form that last impression from the disparaging remarks that our web designers have made about FileMaker in general. Keeping pace with its modern capabilities has obviously remained well beyond their radar.

             

            With best regards,

             

            John

            • 3. Re: Best resource to demonstrate FM's ESS capability to Web developers
              beverly

              As you advise, we obviously have to discover what limitations exist in structure of the MySQL files that might lead to breakage. I cannot imagine there's any encryption but there will be some validation of the imported data. I doubt that the resistance stems from complexity of the data structures but more likely from some compromises that might have departed from a well normalised design when setup previously, or merely a fear that we might be incapable of conforming with their data structure.

               

              And only they know the complexity, unless they grant you access. If they ARE using MySQL, there are interfaces (phpMyAdmin, for an example web interface, or desktop applications) that would allow you to see the structure of the data in a way that perhaps you cannot currently see with FileMaker.

               

              You are right that the data may not be "normalized". The usage of EAV (entity-attribute-value) structure to data seems to be a favorite of many, but doesn't work with standard simple SQL queries or with FileMaker). But it often works well with some WordPress modules that I've used.

               

              I form that last impression from the disparaging remarks that our web designers have made about FileMaker in general. Keeping pace with its modern capabilities has obviously remained well beyond their radar.

               

              Disparaging remarks mostly from ignorance, I'd opine! A lot like IT saying Macintosh OS is not "high-tech" when it's based on Unix these days... (no flame wars, please!). You can only attempt to educate them. But mostly you must get informed about MySQL (and whatever web application is used) so that you can speak to the ways that they can be integrated with FileMaker.

               

              I have been a Web Developer since Netscape 1.1 (the first HTML). As soon as there was a database (include FileMaker) that could be accessed with web applications, I've been there, too. I don't speak all cyber languages and all dbs, but know that FileMaker can often communicate when other systems cannot.

               

              So, see if there is a way you can have a MySQL interface to study the structure and let them know what you know! If you OWN the data, you should know what it's all about. Check with MySQL.com for documentation on that database and for suggestions on applications to access it.  You might even get a copy installed on your computer just for the ability to test things out.

               

              Beverly

              • 4. Re: Best resource to demonstrate FM's ESS capability to Web developers

                Thank you Beverley.

                 

                I've looked at the phpMyAdmin pages but didn't glean enough info to start feeling confident using it just yet.

                 

                The web developers, like many businesses here, have closed their shop for the Christmas & New Year holidays and gone to the beach, meaning it will be mid January before I get the chance to talk with them and delve into the structure of their SQL tables.

                 

                I shall report back.

                 

                With my best wishes for 2013,

                 

                John

                • 5. Re: Best resource to demonstrate FM's ESS capability to Web developers
                  beverly

                  OK, John, phpMyAdmin is a PHP/web interface for working with MySQL (I believe there is a similar product for other SQL). It is all web-based, so is installed on your webserver, or the webserver where your website is located or some convenient place that can access the database. It's PHP, so that also must be running on the webserver, although you need not know PHP to use it.

                   

                  On setup you specify the domain/IP of the hosted db, the admin login (user & pass). Then you have the ability to create/delete/edit, over and above what you can do with FileMaker/ESS, this includes field/column and table-level access to the MySQL. The usual caveats apply (be careful what you wish for!)

                   

                  HTH!

                  Beverly

                  • 6. Re: Best resource to demonstrate FM's ESS capability to Web developers
                    PrinceBaskar

                    I'm working with Filemaker ESS and MySQL data sources without any hassle and with very good performance and results doing data integration of 4 external SQL databases.

                    To overcome data syncronization issues (the solution has concurrent users accessing via ASP back ends and the Filemaker Back end), a clone table has been placed in the middle to follow up changes in any of the records.

                    I agree that the developer resistance may be based in ignorance about the Filemaker capacities to integrate such external sources, or originated in security design constraints.

                    • 7. Re: Best resource to demonstrate FM's ESS capability to Web developers
                      PrinceBaskar

                      I can add to my previous intervention that being a early user of SQL databases I was in the past also agnostic in regards of Filemaker being selected instead of any solution based on SQL.

                      But this perception change dramatically since Filemaker 11 and with native SELECT tools integrated in version 12 I'm pretty aware that Filemaker is now a robust developer platform that erase previous preconcepts.

                      Then probably the developer is not aware of this news in regards of Filemaker new capacities.

                      • 8. Re: Best resource to demonstrate FM's ESS capability to Web developers
                        beverly

                        I can add to my previous intervention that being a early user of SQL databases I was in the past also agnostic in regards of Filemaker being selected instead of any solution based on SQL.

                         

                        But this perception change dramatically since Filemaker 11 and with native SELECT tools integrated in version 12 I'm pretty aware that Filemaker is now a robust developer platform that erase previous preconcepts.

                         

                        Then probably the developer is not aware of this news in regards of Filemaker new capacities.

                         

                        Thank you for your insight, Fabian! It's good to hear of converts' successes. I look forward to your input when other FM/SQL questions come up, too!

                         

                        I've worked in databases since they were text stored on punchcards or magnetic tape. I like to know what tool is best for the job and how might they communicate. It's this knowledge that helps convince reluctant IT that FM is extremely helpful in the mix!

                         

                        Beverly

                        • 9. Re: Best resource to demonstrate FM's ESS capability to Web developers
                          steve.winter

                          Regrettably Beverly, most 'reluctant IT' people aren't as enlightened as you

                           

                          Cheers

                          Steve

                          • 10. Re: Best resource to demonstrate FM's ESS capability to Web developers
                            beverly

                            exactly, Steve! that's why we, as FM developers need to know what IT are using so we can speak to all. LOL

                            Beverly

                            • 11. Re: Best resource to demonstrate FM's ESS capability to Web developers
                              jormond

                              We push data up to our webserver using an ESS import.  The one drawback that I have run into has to do with ESS errors.

                               

                              If an import fails, it tells you the number of records failed...but it's not easy to figure out which records it skipped.  There are ways around it, but you do need to know that going into it. 

                               

                              In particular, we've had some issues with importing dates.  No trouble with direct entry, or a set field in a loop, just importing those dates.  It's possible it's something in my setup or approach...but take that for what it's worth.

                               

                              There are also possible issues ( pretty well the same issues ) with converting the data to a csv and importing it into MySQL. We have run into that also.

                              • 12. Re: Best resource to demonstrate FM's ESS capability to Web developers

                                Hi Folk,

                                 

                                As promised I'm reporting back . . .

                                 

                                Last week we had a meeting with the web developers that was cordial enough but . . . their reservations were restated as "they did not want to be blamed for the web site not working if we had messed with the data and had somehow screwed up their field validations." We surmise that they will continue to be unaware of FM's modern capability as their current focus is on building promotional web sites, rather than ecommerce. They did, however, grant us write access to the SQL table that holds the product data.

                                 

                                What has become evident as we explore the workings of that table, is that its structure and the way web site updates were implemented (by deleting the old records and creating them afresh), makes for an awkward integration with FM's ESS capability. It now looks as though that data structure was cobbled together to meet our evolving requirements. At that stage I failed to see any alarm bells ringing.

                                 

                                Consequently we are contemplating a rebuild of the web site with a different web company, stating at the outset the site's need for compatibility, with our MailOrder system via ESS, at every step of the way.

                                 

                                To be fair, when we built our second web site some 10 years ago with the current providers, they were engaged for their creative flair, an attribute that they've won recognition for.  Back then, no one in our company had sufficient foresight to realise how dependent we'd become on the need for strong integration between our sytems and the web site's content. We also lacked an ability to evaluate the coder's competence in building a data structure that would be suitable for ESS access but even that was well before FMI gave us that dexterity.

                                 

                                Had I been a Beverly Voth, or at least a bit more conversant with SQL, PHP and XML data structures, I might have made a wiser decision, but the right crystal ball was not beckoning!

                                 

                                Lesson learned,

                                 

                                John

                                • 13. Re: Best resource to demonstrate FM's ESS capability to Web developers
                                  phochstr

                                  All of the entries in this thread are good suggestions. Let me add my $0.02:

                                   

                                  Main Problem when interfacing FM Solutions with SQL Server (2008R2 and 2005) for me have been Numbers:

                                   

                                  - FM's  data type is Number

                                  - SQL Server's are:

                                       - signed and unsigned integers in 1, 2, 4 and 8 byte widths

                                       - Floating Point Numbers (float, real)

                                       - decimal/numeric

                                       - Money (4 and 8 bytes; smallmoney and money) -- small money is not pocket change, btw.

                                   

                                  Mapping those correctly makes things interesting. This may well be one of the headaches your developer foresees given the subject of the project.

                                  Just to support this idea, a citation from the ESS Intro:

                                   

                                  "Supported Data Types

                                  SQL data sources, in general, support a greater number of data types than does

                                  FileMaker Pro.  Often these data types are more specific than FileMaker Pro data

                                  types, and they can be more limited in certain ways as well.  For example, most SQL

                                  data sources have different data types for integer data and floating-point data, while

                                  FileMaker Pro handles both integers and floating-point numbers with a single data

                                  type.  SQL integer types may be limited in size of data they can hold."

                                   

                                  HTH

                                  Peter

                                  • 14. Re: Best resource to demonstrate FM's ESS capability to Web developers
                                    beverly

                                    Another nickel's worth:

                                    On occasion, I have queried separate data sources (FM & SQL) directly and mashed them with the web application.

                                    No need to push all the data through either one, even if it is possible.

                                     

                                    -- sent from my iPhone4 --

                                    Beverly Voth

                                    --