Sign in to follow this  
Followers 0
tomentourage

Querying a filemaker db with SQL

10 posts in this topic

Hi, I am in the process of building a databse in access and I've been told that filemaker would be much better solution. I am fairly used to SQL server 2012 so one of my main concerns is being able to write SQL to manipulate my data and build the relationships and keys etc. Is this all done in a GUI in filemaker? I know it can be connected to SQL server or oracle but (forgive my ignorance of filemaker) why would I need filemaker, other than to build something at the front end. If I could just make selections without a full functionaly in filemaker I think this would be ok for this project. If anyone has any comments that would be great!

 

Thanks 

Share this post


Link to post
Share on other sites

In FileMaker you use a GUI for building tables / cols / relationships.

You also use a GUI for building layouts for user interaction with the data layer.

It's a distinctly different paradigm than Access or SQL Server so consider the learning curve as a significant part of the equation.

In a simplified description FM is a development environment where gui is bound to data layer and a scripting language knits the two together to facilitate building functionality for the user.

Using FM as a strictly front end tool for a SQL back end can be done but is probably not the best choice as performing CRUD transactions or finds/sorts with SQL as a back end on large data sets will cause performance issues.

Not saying you shoudn't do it but I would not approach this as a beginner.

IMHO making a decision on any development platform after a brief introduction and no platform SME at the table is more about what is the team comfortable with than anything else. Furthermore making a platform decision based on a coworker suggestion is equally fraught with risk.

Edited by Kris M

Share this post


Link to post
Share on other sites

FMI actually actively warns against using FM as nothing but a front-end to an ESS SQL database.

As to creating tables through SQL: sure, FM supports that.  But not setting up relationships.  That is done through the FM UI

Share this post


Link to post
Share on other sites

Thanks for your comments guys. can you make selections from the database using queries?

Share this post


Link to post
Share on other sites

As i said - "It's a distinctly different paradigm than Access or SQL Server so consider the learning curve as a significant part of the equation."

SQL Queries are part of the learning curve/paradigm shift as the relationships graph in FM can be thought of as a gui based query builder.

Edited by Kris M

Share this post


Link to post
Share on other sites

OK, thanks, a final related question, is that gui basis as flexible as writing queries, i.e. can make selections that are as specific as a written query ?

Share this post


Link to post
Share on other sites

can you make selections from the database using queries?

Yes and no. You can make SQL selections using SQL queries. You cannot create a Filemaker found set using an SQL query - at least not directly. Filemaker supports a limited set of SQL, but it's not an SQL database at its core.

 

OK, thanks, a final related question, is that gui basis as flexible as writing queries, i.e. can make selections that are as specific as a written query ?

Yes.

Edited by comment

Share this post


Link to post
Share on other sites

FMI actually actively warns against using FM as nothing but a front-end to an ESS SQL database.

As to creating tables through SQL: sure, FM supports that.  But not setting up relationships.  That is done through the FM UI

Actually, the point here I believe, is that FMP is not the best tool for executing high volumes of transactions form many users to a SQL database. However when same workgroup needs to work with large volumes of data, and updates are infrequent I have found " FM as nothing but a front-end to an ESS SQL database."  to be a very effective approach, for several reasons

-- Searches can be very much faster

-- Complex calculations and /or aggregations across many tables,  can be very fast using views set up in SQL. You create views that provide "Virtual Flat Tables" of aggregate values. Dramatically fast vs native FM - especially over a WAN.

-- Publishing SQL to the web is very scalable using PHP or other tools, and subcontractors to do this are easy to find.

So in a  write "a few times an hour" read "millions of times", scenario ESS is in fact brilliant.

 

You do have to learn (by experience, unfortunately) the behavior of "Flush Cached External Data" - I have found that to be certain that you are looking at the most current data this has to be done in the "context" of each table that could have been updated - not just in the layout you are looking at. A bit cumbersome.

Tools expressly designed for SQL front-ending deal with things like this,  and also avoiding "collisions" where multiple users are trying to write to the same record, much better.

Share this post


Link to post
Share on other sites

The FTS Advanced book (http://www.filemaker.com/learning/training/fts.html#fts-adv), in chapter 9 - Integration, has a pretty good list of things that behave differently when interacting with an ESS table vs a native FM table.  It is must-read material for anyone who is used to working only with FM native tables.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Similar Content

    • By scain101
      I need some help with ExecuteSQL.

      I have a TO “Sales” that is related to Estimates, Inspections, and Proposals via ID_SALES:id_sales.

      I created some simple ExecuteSQL calculations that work on Estimates and Inspections, but the same calculations will not work with the related Proposals TO. 

      ·         I know that the relationship is good because I can get the SUM and COUNT from Proposals with a basic calculation (not ExecuteSQL).

      ·         I know there is not a problem with the name on the related Proposals TO (like a space between words). I've copied and pasted the working calculations and I have started from scratch, and neither works.

      This statement works:

      ExecuteSQL ( "SELECT COUNT

      (ID_ESTIMATES) FROM TO40_ESTIMATES

      WHERE ID_SALES = ? “ ;

      "" ; "" ;

      TO13_sales_ESTIMATES||id_sales::id_sales )

      ---

      This statement does not work:

      ExecuteSQL ( " SELECT COUNT

      (ID_PROPOSAL) FROM TO26_PROPOSALS

      WHERE ID_SALES = ? ";

       "" ; "" ;

      TO13_sales_PROPOSALS||id_sales::id_sales)


       
      Any help would be appreciated.

      SC

    • By Ben Moore
      I am trying to create a list of dates from multiple tables to create a gantt chart.  My projects have Shipping dates, Installation dates and task item dates.  Each of which come from their own tables.  Is there a good way to import all of these dates into one table related to the project, each with it's own record of Start date and end date.
      I'd rather have:
      Project | Item | Start_Date | End_Date|
      than:
      Project | Install_Start_Date | Install_End_Date| Ship_date | Ship_Arrive_Date | Task_Start_Date | Task_End_date
    • By DataLackey
      Hi,
      I'm trying to use Filemaker to manage a mysql database on Amazon RDS. I'm trying to set up an import layout that will allow users to load data into into the RDS database.
      Mostly, Filemaker and mysql are playing well together. But I'm getting a problem when I try import data using LOAD DATA LOCAL INFILE . I've run the query successfully with a different client on the same database (Sequel Pro) but when I run it using the Execute SQL script step in Filemaker I get an error, with the message "Load data local infile forbidden".
      I've looked around online, and it seems like I need the server and client to both enable 'local_infile'. I know it's enabled on the server (because I can check manually, and the query works on a different client). Does anyone know how to check or change this within Filemaker?
      Otherwise, does anyone have any ideas as to how I might work around this? Unfortunately I can't call a stored procedure on the server because the query relies on variables. My understanding is that you can't use variables in a LOAD DATA INFILE statement on the server, and you can't use LOAD DATA INFILE in a prepared statement.
      Does anyone know how to change the local_infile variable within the Filemaker mysql client, or failing that, does anyone have a good workaround for the restriction on variables with the LOAD DATA INFILE statement if I run it on the server?

      In case it's not already blatantly obvious, I don't really know what I'm doing, so any help (especially help that is dumbed down) would be hugely appreciated.
      Thanks,
      John
       
    • By laurend
      I created a database to track permits by building. Each permit type is stored in its own table. The only link between tables is the Building Name which is the physical location where the permit exists.
      Tables:
      v_Buildings (fields include building name, ID, building status, address, city, state, zipcode)
      Air (ID, Building Name, Expiration Date, Days_to_Expiration, Type, etc...)
      Water
      Food
      Elevator
      ...9 permit types in all, and I will add more in the future
      Each of these tables has the same fields in common:
      BuildingName
      PermitExpirationDate
      PermitOwner
      Days_to_Expiration
      I created a Dashboard layout with the idea that users could select a building name from a drop-down menu and then see all of the permits for that particular building along with their expiration date and the permit owner's name. I am just baffled as to how to do this across multiple tables. I have looked at SQL, Join tables, portals etc...., and I cannot figure out how to aggregate all of this information into one view for users.  I cannot even seem to figure out what table should be used for the Dashboard layout. I would love to hear from the community the best and hopefully scalable approach for designing this layout.  Thanks in advance from a novice user.
       
    • By Ron Neville
      Can anyone see whats wrong here:
      Let     (     [     invoiceID = id ;     decimal = Left ( Evaluate ( 1/2 ) ; 1 ) ;     SQL = ExecuteSQL    (                     "                     SELECT SUM ( b.\"Amount\" )                     FROM \"Invoice Data\" b                     WHERE b.\"id_Invoice\" = ? AND b.\"Type\" = Rental                     "                     ; "" ; "" ;                     invoiceID                     )     ] ;     If ( SQL ; Substitute ( SQL; "." ; decimal ) ; 0 )     )
      There are 2 tables Invoice and Invoice Data. The items added to the invoice can either be a rental, Sale or service item and want to total each type on the invoice layout.. Thank you