1 2 Previous Next 18 Replies Latest reply on May 4, 2017 4:40 PM by beverly

    Can a layout be opened with a saved Find?

    SMGreenfield

      I have a layout that uses ODBC to pull information down from a mysql server on AWS.  I'll almost always want a fraction of the available data, but when I switch into the layout, it always loads everything, before I can select one of the saved finds I want to apply to the layout.

       

      Is there a way to (via script, I assume) navigate to a layout but NOT load any data until I tell it what saved find / find criteria I want to search for?

        • 1. Re: Can a layout be opened with a saved Find?
          philmodjunk

          Enter Find Mode first, then change layouts.

          • 2. Re: Can a layout be opened with a saved Find?
            SMGreenfield

            Wow.  That was simple.  Who knew? You knew!  Thanks!

            • 3. Re: Can a layout be opened with a saved Find?
              beverly

              Plus one on that! Usually I make sure the layout is Form View (even if the final result will be List or Table View) before perform find.

               

              Enter Find Mode

              Go to Layout

              View as Form

              ... your find steps here ...

              Perform Find

              If (here is where I test for 0, or more than 25 records and only go to List if 1-25 found)

              View As List

              Else

              ... do something here with dialog ...

              End If

               

              Another OPTION (because you are talking about ODBC (ESS or Import?), you may have a view (a "saved" find/SQL select) that is performed on the SQL db before you get the data to FileMaker. This pre-narrows the data and you can access it like any table in the SQL db. You can then "find" within FileMaker (as above) to further refine the dataset before viewing.

              If you have permission to do this, you can set it up. Otherwise contact the SQL dba to do this for you.

              beverly

              1 of 1 people found this helpful
              • 4. Re: Can a layout be opened with a saved Find?
                ByteTheBullet

                Views are a tremendously powerful tool. Besides using it as Beverly suggested, they can also be used to merge data together from multiple SQL tables so that you can use a simpler search in FileMaker. MySQL, SQL Server, etc. can perform complex joins more efficiently than FileMaker can on ESS data, plus it can make your FileMaker searches much, much simpler.

                1 of 1 people found this helpful
                • 5. Re: Can a layout be opened with a saved Find?
                  beverly

                  Absolutely! We can have a "join" (locally via graph) or through SQL SELECT. Because SQL db's create a temp table from the query, the results can be used to query again or report or chart or ...

                  Make the View as a complex join (of the SQL tables) and returning that result as a "Table" that can be used in FM is extremely powerful and much more efficient than trying to do it all in FMP. So much of the work is done on the SQL db.

                   

                  The main caveat, of course if the ability to create those Views yourself or have to rely on the DBA.

                   

                  For a "find" that would be used again and again? So worth the effort of a view in SQL!

                  beverly

                  1 of 1 people found this helpful
                  • 6. Re: Can a layout be opened with a saved Find?
                    SMGreenfield

                    Beverly,

                     

                    This was amazing -- I created a view on my mysql server with a three-way join and even tested it using mysql workbench.  It returned the exact records I would have expected.

                     

                    BUT -- and I'm sure this is pilot error -- even though both my Actual Technologies ODBC driver AND my Filemaker Pro External Data Source is set to include views -- when I select that newly-created view in a layout I'm creating ("Show Records from:", there are no fields visible -- it's just empty. 

                     

                    I didn't create a "Table" based on that view first, nor did I create any "Relationship.  I'm obviously missing a step?

                    • 7. Re: Can a layout be opened with a saved Find?
                      beverly

                      1. check the permissions (on MySQL) for that view

                       

                      2. you must have a table occurrence (table alias) on the relationship graph (RG) for a layout to have 'context'. no relationships need to connect to the new TO (although you could), but the layout needs it.

                       

                      HTH,

                      beverly

                      1 of 1 people found this helpful
                      • 8. Re: Can a layout be opened with a saved Find?
                        SMGreenfield

                        Beverly,

                         

                        That IS awesome -- and so are you!

                         

                        So yes -- the view needed Select permissions.  And yes -- there needed to be a Table Occurrence (TO) for the view so it could define what the unique ID was amongst all the fields I had chosen.

                         

                        And -- wow -- the performance difference is striking.  To pull the 19 records from three tables (a total of 18 fields) was less than ONE second for the view, compared with almost 10 seconds pulling the data purely from the tables!

                         

                        The only detail remaining is that FileMaker doesn't seem to care about my view's "ORDER BY" statement.  I know the layout can, of course, be set to choose the sort order, but is that normal and expected?  Or is there a way to tell FileMaker to accept the order the view definition describes?

                        • 9. Re: Can a layout be opened with a saved Find?
                          beverly

                          It's been a while, but there may be some reason the ORDER BY is not preserved. The Driver or ??, I just don't recall.

                           

                          That speed is compelling though, right?!

                          beverly

                          • 10. Re: Can a layout be opened with a saved Find?
                            SMGreenfield

                            Beverly,

                             

                            I've got the authoritative word from Jonathan Monroe at Actualtech:

                             

                            Unfortunately, FileMaker does not preserve the order of the records that it brings in from ESS queries.  The sort order is undefined, although it seems to base the order on the key field.

                             

                            When ESS was originally introduced in FileMaker 9, the order of the records returned from the database was preserved.  This was really nice, because you could create VIEWs that let the database do the heavy lifting of sorting the records before handing them off to FileMaker.  They changed this (I think) with FM 12 and the new file format.  I think they did this in order to improve performance - they didn’t want to be constrained by having to put the records in any particular order.

                             

                            So I guess I'll have to handle it at the FileMaker client end.

                             

                            The Layout will only "remember" the sort order I define in the RECORDS > Sort Records... dialog until the database is closed.  Do I need to navigate to the layout via script (which I already do), then have the SCRIPT sort the records?

                            • 11. Re: Can a layout be opened with a saved Find?
                              beverly

                              Ah yes, Jonathan. I recall hearing that. Thanks for the memory jog!

                              You can trigger the layout to sort every time you go to it.

                               

                              But back to the number of rows & columns...

                              Example script (trigger or not) to use:

                               

                              View as form

                              Perform finds (if any)

                              Sort

                              View as list

                               

                              And FM doesn't have that same delay that can happen while in list view and ESS records.

                              Beverly

                              Sent from miPhone

                              • 12. Re: Can a layout be opened with a saved Find?
                                SMGreenfield

                                Beverly, those are great steps -- now that I have a view, which is like a half second instead of 10 seconds, I thought I could perform the sort operation on the table view:

                                 

                                Freeze Window

                                Go to Layout [ “Recent Activity (View)” (recent_activity_view) ]

                                Sort Records [ Restore ; With dialog: Off ]

                                Enter Browse Mode [ Pause: Off ]

                                 

                                But it won't sort (I'm trying to sort by a calculated field -- could that be the problem?)

                                 

                                The SORT command ALSO won't work for this calculated field (but will for other fields), but selecting the column sort arrow at the top of that column DOES work.

                                • 13. Re: Can a layout be opened with a saved Find?
                                  beverly

                                  Yes, it may be a problem. Perhaps you can make the field auto-enter (stored) and/or make it part of your view query, so it returns as needed?

                                   

                                  beverly

                                  1 of 1 people found this helpful
                                  • 14. Re: Can a layout be opened with a saved Find?
                                    SMGreenfield

                                    Apparently, those options are not available for a field that is calculated from the ODBC fields, or for the actual ODBC field itself.  I think I'll give FM support a call, though sometimes I've encountered a tech that knows less than I do....

                                     

                                    I'll report back.  Thanks for all your help!

                                    1 2 Previous Next