SQL backend

[sal88]

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 May 17, 2019 by sal88

[OlgerDiekstra]

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.

[mr_vodka]

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. 

[sal88]

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 May 18, 2019 by sal88

[mr_vodka]

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

 

 

 

 

[Oliver Reid]

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 May 22, 2019 by Oliver Reid

[sal88]

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

[Wim Decorte]

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.

[sal88]

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

[Wim Decorte]

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.

 

[sal88]

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 😮