11 Replies Latest reply on Jan 16, 2015 6:32 AM by Mike_Mitchell

    How can I show only selectec data from a table with eSQL?

      Hi there,

       

      can anyone give me some basic hints how to run a SQL statement against a tabe in my Filemaker solution and how so show the selected records in a layout?

       

       

      Thanks,

      Luna

        • 1. Re: How can I show only selectec data from a table with eSQL?
          Mike_Mitchell

          Luna -

           

          A good primer for using ExecuteSQL can be found here:

           

               The Missing FM 12 ExecuteSQL Reference | FileMakerHacks

           

          As to how to display the information on a layout, that's a little more involved. ExecuteSQL doesn't "find records". It returns, basically, a block of text. Therefore, in order to display that block of text as records on a layout, you have to translate the text it returns into something that operates as records.

           

          One way to do that is to create a "scratch" table and insert the values into records via a script. That can be done if you want to preserve the values past the current user session (for reporting purposes, maybe, so the user can go back to them later).

           

          Another method is to use a Virtual List technique. This involves creating a table that uses unstored calculations to parse the content of one or more memory variables based on either the Get ( RecordNumber ) function or a serial number field in the table to display consecutive values in a list as records in a table. A basic calculation might look like this:

           

               GetValue ( $$list ; Get ( RecordNumber ))

           

          where $$list is a return delimited list of values resulting from your ExecuteSQL statement.

           

          There are plenty of good references to the Virtual List technique online, but be aware that it's a more advanced technique. It requires a good understanding of manipulating return delimited lists in FileMaker and the calculation functions that are available to you. Here's a brief list of references:

           

               http://www.mightydata.com/blog/virtual-list-in-three-easy-steps/

               http://www.seedcode.com/virtual-list/

               Virtual List | FileMakerHacks

           

          Also, there's coverage of the Virtual List technique in the FileMaker Training Series (advanced).

           

          HTH

           

          Mike

          • 2. Re: How can I show only selectec data from a table with eSQL?
            erolst

            Adding to Mike's virtual list suggestions: a quite simple way of creating a “found set” with ExecuteSQL() is to set a field with a list of the primary IDs of the records that match the SQL query, and use that field as a multi-line key to drive a relationship; this gives you access to all matching records and all their fields, which you can e.g. display in a portal, without copying anything or requiring an additional table.

             

            It really depends on your requirements; as always, it's good to know different techniques to apply to different scenarios.

            • 3. Re: How can I show only selectec data from a table with eSQL?
              Mike_Mitchell

              Yes, good one. Forgot about that option. Thanks, Oliver.

              • 4. Re: How can I show only selectec data from a table with eSQL?

                Yes, this sounds like the apporach I need - the others are a bit to advanced or to comlicated in my humble ears as just only semi-pro-user. Thanks for that one! I will try to laborate on this.

                • 5. Re: How can I show only selectec data from a table with eSQL?

                  I just tested a simple design that works great. This is how if anyone s interested:

                   

                  1. In a table (here) named "Start" I have a global field (here called) "ID_list" that is the target of a simple eSQL search.
                  2. An eSQL saves the IDs of found records of a certain search in a table (here) named "Data" in this field "ID_list".
                  3. Then I have a Cartesian relationship between these two tables.
                  4. I create a portal in the layout (here: in a layout based on "Start"). This shows for the moment "all" data records of "Data"
                  5. The I "filter" the records of the portal using "MusterAnzahl" (German) that checks whether the ID of the current row in the portal is in the field "ID_list" - if so the function returns "1", of not "0". And this triggers if the record is shown or not.

                   

                  Done.

                  • 6. Re: How can I show only selectec data from a table with eSQL?
                    Mike_Mitchell

                    You’ve made it considerably more difficult than it needs to be. There’s no need for a portal filter at all, and a Cartesian join is not a very good performer. All you really need is a relationship (equijoin - equals on both sides) between ID_list and the ID field on the child side.

                     

                    To show all records, simply use:

                     

                         SELECT ID FROM (table)

                     

                    and set your global field to that.

                     

                    You’ll get better performance and it’ll be easier to maintain.

                    • 7. Re: How can I show only selectec data from a table with eSQL?
                      erolst

                      As Mike noted – that's „von hinten durch die Brust ins Auge“ …

                       

                      Just define the relationship between the global field into which you put the IDs, and the ID field from the portal TO. That's what I referred to in my other post as “multi-line key”: each line in a return-delimited list is regarded like it was a single key field – effectively a way to implement an OR into the definition of a relationship (that normally only knows AND).

                      • 8. Re: How can I show only selectec data from a table with eSQL?

                        Dear Mike and Erol,

                         

                        thanks to both of you!!! I did not know that

                        • "each line in a return-delimited list is regarded like it was a single key field"

                         

                        I thought it should be realizied like I wrote it down - and did not test it your way. And as I indeed have some performance problems in my actual solution I will immediately test if I can improve it this way.

                         

                        My other idea to enhance the perfomance (I just have about 10.000 addresses in a certain table but it takes a while when searching for a certain company) was to eleminate "calculation fields". I have read that theses can bring down the performance, too.

                         

                        But, do not laugh, I still have to find out if calculation fields are - in German: - "Formel" or "Statistik"-fields...

                        • 9. Re: How can I show only selectec data from a table with eSQL?
                          erolst

                          Luna.media wrote:

                          But, do not laugh, I still have to find out if calculation fields are - in German: - "Formel" or "Statistik"-fields...


                          Look at the shortcuts in the field type selector popup:

                           

                          S = Summary / Statistik

                          L = CaLculation / FormeL

                          • 10. Re: How can I show only selectec data from a table with eSQL?

                            Thanks forthe hint (and "mind bridge"). Now I can hunt for the perfomance obstacles. In the meantime I have tried the "cartesian portal and filter"-approach against the "eSQL ID list in a field"-approach. It seems not to make the big difference...

                            • 11. Re: How can I show only selectec data from a table with eSQL?
                              Mike_Mitchell

                              It will ...

                               

                              As the solution grows, or as you deploy it to the open Internet, or as the number of users increases.

                               

                              Speaking of which, if all you want to do is view the related records, this method works quite well. However, if you want to edit them, don't use the portal. You might run into record locking issues in a multi-user environment. Instead, establish the related set and then use Go to Related Record to switch to a List or Form view for editing.