2 Replies Latest reply on Nov 19, 2014 3:10 PM by MikeMabie

    Query Building Question



      Query Building Question



      I've recently switched over from access and am enjoying FMP13 a lot.   One thing I am struggling with is simple query type stuff.  Here is a very simple query I'd like to run.

      I have a layout that consists of data from a remote mySQL database accessed by ODBC and a local table.  Both table consist of information about products.  For example the remote mySQL database includes Product_Id, Product_Name, Product_Price, Product_Quantity.  The local database (linked by Product_Id) includes info like Reorder_Quanity, Supplier, Shipping_Cost.

      The Reorder_Quanity is a number we set that is the point at which we should order more of that product.  So if we enter 50 for the re-order quantity, that means when product_stock drops below 50, we should order more.

      What I want to do is create a report/query that simply says: Show me the products where Product Quantity is equal or less than Product Reorder Quantity.

      My issue is I'm not sure of the best place or method to do this in FMP with my layout.  I'm not a database/SQL dude but I'm pretty good at figuring things out and I have done SQL stuff in access.

      I just need a push in the right direction as far as the method I should use.



        • 1. Re: Query Building Question

          The exact way that you would do this depends on which ODBC option you have set up. ESS which is essentially a "live" link into the data back end or a system of importing and exporting to/from the data source tables.

          But you have put your finger on a weak point of FileMaker finds--finding data where it's the comparison of two field values that determines whether the record should be returned by the query. This is not as easy as we would like in FileMaker.

          The typical method in FileMaker is to define a calculation field such as:

          Product Quantity < Reorder Quantity

          With Number specified as the result type.

          You can then do a find for all records with a 1 in this field.

          Your ODBC data source design may require that you set up a related table with this calculation in a one to one relationship before you can get this to work.

          • 2. Re: Query Building Question

            Hi Phil,

            That calculation field solution worked just fine!

            Thank you very much for the answer.