6 Replies Latest reply on Nov 3, 2011 12:24 PM by disabled_morkus

    Interactive Mode?

      Title

      Interactive Mode?

      Post

      I have a database established, but need to be able to just run a bunch of queries to play with the data.

      In all my other server databases (and client databases like dBase and Foxpro), there's an "interactive mode" where I can run queries directly against the database.

      Perhaps in FMP, this is an "expert mode" or something I can't seem to find.

      So, how do I interact directly with the data? As I see it currently, I have to put a field on a form and define something. That approach is cool for an end user but not for a "DBA" who needs to dig into the data and work with it. I need this ability to quickly interact (via queries, etc.) with the data or FMP a no-go for me I guess.

      Thanks in advance for help on this.

      -m

      P.S. I also noticed that the JDBC support seems only for other applications connecting "TO" FMP, not where FMP can connect to other applications (like Oracle) as a client. Correct? Thanks again.

        • 1. Re: Interactive Mode?
          philmodjunk

          Closest you can come to this is to perform finds from a layout set up with a table view of your data. With such a layout, you can add/remove/resize/reorder columns of data as needed and you can enter find mode and specify criteria in the columns in order to query your data.

          • 2. Re: Interactive Mode?

            Wow, surprising.

            I would want to be able to do interactive group bys and calculations.

            FMP is really not what I had hoped for for this amount of $ or I'm just the wrong target audience.

            If this functionality isn't in version "11", then I guess it's safe to assume it's not coming. I'll delete my evaluation copy.

            Thanks very much for your reply.

            (This is why we evaluate software I guess..).

            -m

            • 3. Re: Interactive Mode?
              philmodjunk

              I would want to be able to do interactive group bys and calculations. 

              FileMaker does do things differently from SQL based systems, but "group bys" can be done interactively and calculations can be set up pretty easily, it's just not done the same way as you do with SQL in a Query builder.

              • 4. Re: Interactive Mode?

                I appreciate your reply...so if you have a set of records in browse view, say a Customer table, how would you interactively do the equivalent of:

                 

                SELECT SUM(invoice_amt) from customer group by customer_ID.

                 

                (Naturally, I wouldn't have invoice_amt in the customer table, but just a quick example).

                 

                I'm still assuming calculations would require a field on the form.

                 

                Thanks.

                 

                -m

                • 5. Re: Interactive Mode?
                  philmodjunk

                  Calculations do require adding a field to the layout but this is fairly easy to do and you can delete them afterwards--not how you are used to doing it, but not all that difficult.

                  I've worked with SQL before, but I'm not sure what "select Sum (...." is supposed to produce in your example. I think you want sub totals for each group of records when grouped by a field that specifies a value common to that group.

                  If so, you can certainly add a field of type summary to compute the total of such a number field, sort your records by the field with a common value and you can then add subtotals to your table view.

                  With FileMaker Finds, (which only reproduce the "WHERE" portion of an equivalent SQL query, the joins are defined elsewhere), simple "ad hoc" queries of an existing table are far easier to do than in most systems and rarely need any programming support from the developer. THis is usually a good thing, but the "by example" methods used to specify the criteria in FileMaker reach a "tipping point" where more complex queries become easier in SQL, in a system that supports that option, than in FileMaker.

                  In some cases, it even requires scripting and/or layered finds performed in succession to produce the same results and some, like a Make Table, or Union, just aren't possible and the equivalents can be truly cumbersome to implement.

                  • 6. Re: Interactive Mode?

                    I totally appreicate your replies.

                    Being a database guy, I think FMP would just end up frustrating me. I don't want to have to get information doing "back-flips while juggling 20 balls" (so FMP will ... "do the query" -- using workarounds as you described above) when simple SQL is, well, simple.

                    My first sample FMP database I created to track household expenses, while better than Excel, really isn't _that_ much better. Althogh each line of Excel has duplicated "vendor" information, I can sort and filter it (using AUTO FILTER) easily. This is kind of the same as the Find in FMP. I can always create a macro in Excel for calculations or even create a MySQL database when Excel doesn't work.

                    I may come back to FMP at a later time. Hopefully, there will be some type of power user shell added for truly interactive SQL. Had that been there, I'm 99% I would have bought the Advanced version.

                    Again, given the price point (high) for this software, my expectations for flexiblity in getting information out are also inline with other tools I've used.

                    Thanks again.

                    -m