4 Replies Latest reply on Jul 14, 2017 9:34 AM by philmodjunk

    Complicated Queries




      I am very new to FileMaker. Can I know do FM support complicated queries? I am doing a project which need to deal with transactions (Quote, Order etc) calculation. Quote and Order have different calculation so I need to customize the query. Besides that, Quote and Order for my project are in separate Excel file and I need to do calculation in FM and combine the results into another table.


      Thank you in advance for helping

        • 1. Re: Complicated Queries

          You might define "queries" for more accurate answers.

          And Welcome to the Community Forum.


          FileMaker native finds are pretty powerful. There are wildcards, scripted finds, modify last find, constrains and extends, the ability to AND, OR & NOT the results.


          The context of a find can also be a factor. If you search for something in a portal, it returns the parent records with that value in the child records. If you want to find the child records only, then you navigate to the layout where the children are the context of the layout (what table occurrence?) And Export (or Save as) to Excel also may rely on the context. And export from the parent with all the child fields will look different than the export from the children and including the parent field.


          You mention "calculation" and how you are using these for your queries would be helpful.


          Are you by chance using SQL in some way? If so, can you be more specific? You may post screen shots or copy calculations and paste as text here.

          Thank you!


          • 2. Re: Complicated Queries

            Please note that if you are used to using SQL to query a database, FileMaker's Query By Example process can take some effort to get used to.


            In my experience, FileMaker's QBE makes simple, ad hoc queries very simple at the expense of making complex queries more complicated than the equivalent SQL. Sometimes a combination of scripting and several queries in a sequence, each refining the previous result can be needed or we toss this method and use a relationship, possibly in combination with a portal filter.


            And these days, we also have a handy function called ExecuteSQL that brings SQL queries into the mix of possible solutions.....

            • 3. Re: Complicated Queries

              The business process you describe (quotes & orders) are fairly typical database queries and not usually considered complex compared to say some physics queries and calcs.  Not that business decision calcs can't have some complexity, but just letting you know those are common type queries for FileMaker and I expect would meet your needs. 


              And as mentioned above, you can always get into advanced SQL calcs too. 

              • 4. Re: Complicated Queries

                More thoughts:


                In other systems, it's often the case that you base a "form" (layout in FileMaker terminology) on a specific SQL query and then the form displays all the data from that query's record set. You can often then manipulate the query expression, even rewrite it on the fly via code to pull up different record sets for the form to display.


                FileMaker doesn't base a layout on a query and that can make things a bit surprising for the developer new to FileMaker but experienced in other systems. In FileMaker, you start with your relationships graph in Manage | Database | Relationships and base your layout on a "table occurrence". That's what we call a box on the relationships graph. A Layout can display any records from the table represented by that "box" (table occurrence). Data from other tables is also accessible, but what you can access is controlled by the relationships linked to that layout's table occurrence. It's fairly analogous to basing a Visual Basic or MS Access type form on a query that reads like this:


                Select * From TableOccurrence Join....


                With a Join clause for every table occurrence linked in via the relationship graph, not only from those immediately connected, but those indirectly connected via other occurrences.


                What you don't see is any WHERE or ORDERBY clause. In FileMaker, we have what is called a "found set". This is similar to a record set returned by an SQL query but can be directly manipulated by any user in an ad hoc fashion in a number of different ways. You have to specifically block those options if you don't want the user to use them. So, unless otherwise prevented, any user can enter find mode, specify search criteria and pull up a found set of records for a given layout. They can then omit records from that layout, show all records or swap the current found set for all records that weren't found by specifying "show omitted only". They can also choose how they want the records sorted if they want them sorted at all. Note that performing the "find" replaces the WHERE functionality and Sorting the records replaces the ORDER BY clause.


                All of this is possible without a bit of programming on your part and that can be surprising to those familiar with other systems.