Jump to content

Recommended Posts

Posted (edited)

Hi all

Apologies in advance if this is a vague question, or if it's been extensively documented, but I could do with some basic advice on moving filemaker tables to SQL. In terms of database development, I have only really known FileMaker.

We have a 60 table, 25 user FileMaker database which takes care of most if not all of the company's operations. 

There is now talk of allowing other platforms (such as PowerApps/PowerBI) access to the data. One such reason is other people are interested in making apps/applets that serve specific functions. This would be simple if the data were held on a SQL database.

So if all the FileMaker tables were to be moved to SQL, what sort of a task is that in terms of keeping the existing FileMaker gui functioning?

Or. is there a simpler way to grant other platforms access to filemaker tables?

And of course, what are the downsides?

Thanks

Edited by sal88

Share this post


Link to post
Share on other sites

Why not use the FileMaker Data API to allow access? That is the purpose of the API after all, and it saves a lot of time trying to migrate schema and data out of FileMaker, and build ESS structures.

v16 has limited functionality so you'd really need v17 for this, but integrations become simple, standard API requests from foreign apps.

Share this post


Link to post
Share on other sites

Besides having to ensure that your data will fit properly into SQL data types, once migrated over, I don't think you should expect to just use the existing UI and repoint it to the new SQL tables.  FM should not be used strictly as UI to build off of SQL systems.  You would have to reevaluate and re create some of the UIs to efficiently use via ESS.  A bunch of things can be off loaded to the SQL side with optimized views, trigger, and stored procedures. 

Share this post


Link to post
Share on other sites
Posted (edited)

Fantastic!!! Thank you very much guys. Very exciting! API has hopefully solved this for everyone.

mr_vodka - is there a specific reason that FM should not be used strictly as UI to build off of SQL systems? Is it just pointless? And what sort of UI changes would be made? What happens to calculation fields? Is filemaker able to recreate them but alongside the SQL table?

Thanks

Edited by sal88

Share this post


Link to post
Share on other sites
On 5/18/2019 at 9:27 AM, sal88 said:

Fantastic!!! Thank you very much guys. Very exciting! API has hopefully solved this for everyone.

mr_vodka - is there a specific reason that FM should not be used strictly as UI to build off of SQL systems? Is it just pointless? And what sort of UI changes would be made? What happens to calculation fields? Is filemaker able to recreate them but alongside the SQL table?

Thanks

 

Because it wasn't created efficiently for that.  Keep in mind that you don't have control over the SQL statements that are being sent.  FM interprets a user's actions and then tries to create a query to match what you are asking of it.  Sometimes it is perfectly fine and other times very very inefficient, depending on what the structure and action given.  It is best suited for ancillary activities to a core system IMO.  

Calculation fields created in FM in the shadow table of your SQL table will be unstored calculations and not indexed.  It is better to create the calculation in the view or table in your SQL table itself.  As for what UI changes that should be made etc TBH I am sorry to have some bad news for you...  It would take a lot more than the forums to create an efficient system.  It just takes a lot of experience and trials, a very good understanding of SQL besides just basic SQL selects, and then knowing when to leverage the native SQL system features where you can.  

Again for things such as simple relationships and simple searching, you may want to give it go as it should be efficient enough.  However, if it gets pretty complex then better to find a work around.

I would start here and then also read all the additional resource info.
https://www.filemaker.com/learning/custom-app-academy/205/external-sql-sources-ess.html

 

 

 

 

  • Like 1

Share this post


Link to post
Share on other sites
Posted (edited)
On 5/18/2019 at 9:27 AM, sal88 said:

Fantastic!!! Thank you very much guys. Very exciting! API has hopefully solved this for everyone.

mr_vodka - is there a specific reason that FM should not be used strictly as UI to build off of SQL systems? Is it just pointless? And what sort of UI changes would be made? What happens to calculation fields? Is filemaker able to recreate them but alongside the SQL table?

Thanks

FM natively handles multiuser updates to the same data very smoothy - if  User A updates data User B is looking at, User B's view of it refreshes automatically. This will not happen with data stored in SQL. 

If that is accepted then you can in fact use FM as front end to a SQL database. SQL database are powerful and ideal for applications with thousands of transactions per minute - just don't use FM for that kind of situation. For a write infrequently/read very frequently application  FMP+SQL can work very well. SQL can do some things many times faster than FM , and using SQL views, Triggers and virtual  fields will let you see results much more quickly (dramatically so in some cases) than using native FM, especially over a WAN

Edited by Oliver Reid
  • Like 1

Share this post


Link to post
Share on other sites

We are struggling with accessing the FM API from PowerApps (we can connect to the API using Post Man).
Can anyone recommend some "simple to develop" PowerApps alternative that can connect to the API? We just want a simple front end that can read/write to our Filemaker table.
Thanks

Share this post


Link to post
Share on other sites

What kind of errors are you getting connecting to the Data API?  Since the FMS Data API is a standard RESTful API, there are no restrictions as to what can connect to it, and pretty much every single modern platform out there can work with it.

Share this post


Link to post
Share on other sites

Sorry for not being clearer. It's not that we're getting errors more that we don't know how to use powerapps to connect and retrieve data to/from a REST API. We can connect to a sql database. I'm wondering if anyone here has any such powerapps experience, or whether you can recommend the simplest (in terms of learning curve) platform to get our users accessing our filemaker data on their android/apple devices (i.e. web based).

Thanks

Share this post


Link to post
Share on other sites

Why not use WebDirect?  That works on both mobile platforms equally.  Of course on Apple devices you have the extra option of using FM Go.

 

Share this post


Link to post
Share on other sites

That looks like the ticket. I think I initially abandoned it because of the toolbar at the top. But it turns out you can hide that 😮

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Similar Content

    • By schotja
      Looking for someone to help setup a simple connection to a sql database on cpanel for our Ranch to connect to cattle listings on our website..  I'll be using fmp12 server or content for now just establishing a connection via fmp12 client on windows 7 pro machines.
      Specifically I am having trouble establishing a DSN connection from any of my machines and have opened ports / given access in cpanel to my static IP.
      I have also updated MS ODBC drivers on one of the machines to see if that made a difference.  It may be something quick for an expert with experience or it might end up being a gremlin.
      I have set up some test databases for troubleshooting purposes and willing to do alot of the legwork if i'm able but also willing to pay someone for their expertise as well.
      thx.
    • By mike13
      I am in the process of connecting an existing FileMaker database to a SQL server that is behind a web interface.  I wanted to know if there is a way to send a command from the web interface to kick off a sync.
      For example, a user opens a record on the web, makes some changes, hits a save button.  
       How do I get that "Save" to trigger a sync?
       
      And can I get it to sync that single record or do I need it to sync all records that have changed?
      Thanks in advance,
      Mike
       
    • By Eli Walker
      Hi all,
      I'm trying to use execute sql to populate a field with the sum of all the values in a field of related records. All I get is a ? so obviously something is wrong with my syntax but I cannot figure it out. Any help much appreciated! This calculation is occurring within the 'Collections' table.
      Let ( [
      query = "
      SELECT SUM(S.Volume.used)
      FROM \"Straws_for.entry\" AS S
      JOIN \"Animals.to.Collections_join\" AS A
           ON \"S.fk_a.to.c.join\" = \"A.pk_join\"
      WHERE \"A.fk_collection\" = ? " ;
      collection = Collections::pk_collection ;
      result = ExecuteSQL ( query ; "" ; "" ; collection)
      ] ; result )
       
      Also, after some reading on similar threads I see people suggest not using this function in a calc. field... if there's any better way of doing it I'd love to hear it. Thanks for the help, really appreciate it.
    • By sal88
      Hi all
      I'm trying to export my filemaker records to my external sql database via the execute sql script step but am getting a number of errors.
      I can see my external ODBC source and can generally write to it (I can go to the respective layout and click 'new record'). My query basically as follows:
      "INSERT INTO dbo.Log ( dbo.Log.log_id, dbo.Log.log_case_idf, dbo.Log.Labour_TOTALS_Labour_Cost, dbo.Log.Labour_TOTALS_Item_Sale) SELECT  Log_Log.log_id, Log_Log.log_case_idf, Log_Log.Labour_TOTALS_Labour_Cost, Log_Log.Labour_TOTALS_Item_Sale, FROM Log WHERE Log_Log.Log_Type = '2'" My first question is: is it even possible to export to an odbc source with a 'insert INTO SELECT' query?
      Many thanks
    • By sal88
      Hi all
      I'm trying to connect to an Azure SQL database from FileMaker 16. I've added the DSN via ODBC 32bit (see pic). However, when I go to File>Manage>External Data Sources, it doesn't show the DSN.
      Where am I going wrong?
      Thanks in advance
       

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.