3 Replies Latest reply on Mar 14, 2014 10:03 AM by raphthepenguin

    Relationship or ExecuteSQL

    raphthepenguin

      Hey there,

       

      I am having a Database tracking of, Activity, Transactions and so on...

      I would like to integrate a new Reminder layout, that on open reminds the user of:

       

      Soon Inactive and Inactive people

      Unfinished transactions

      Contact reminders...

      and anything else that needs reminding!

       

      With that I would like to have a checkbox, so people can cross of lits of the reminder page.

       

      I have already made reminder status fields (like one that says "Inactive" for the record).

      Now we come to the actual question. I was thinking of relating the Layout to all the other ones, and then show the records in several portals.

       

      But I also have read alot about the ExecuteSQL function and it seems like, if someone knows what he is doing this will be a quicker and easier approach, with less possible error.

       

      How would I display the Function result on my layout? Will i still be able to have a checkbox, or will there just be an uneditable list?

       

      What would you recommend?

       

      Thanks alot,

       

      Raph

        • 1. Re: Relationship or ExecuteSQL
          erolst

          raphthepenguin wrote:

          But I also have read alot about the ExecuteSQL function and it seems like, if someone knows what he is doing this will be a quicker and easier approach, with less possible error.

          Which could probably be said about any human activity …

           

          But in the end, it really depends on your implementation (remember GIGO?). I cannot see why “native” Filemaker features would produce less accurate results. ExecuteSQL lets you write more expressive queries than you could realize within the constraints of a relationship, but it works with your existing data, not a super-charged version of them, so don't expect it to be a miracle cure; and it won't correct your syntax (on the contrary: SQL is quite unforgiving!)

          raphthepenguin wrote:

          How would I display the Function result on my layout? Will i still be able to have a checkbox, or will there just be an uneditable list?

          If you want a non-editable list, use a $$ (or even a $); if you need editing features, you need to put the found data into some container: records/portals, globals/repeating fields, Web Viewer/HTML …

           

          If you use a Virtual List technique in combination with ExecuteSQL, you can have the best of both worlds: write more flexible queries and allow the user to edit found data on the fly.

           

          Here's a dashboard/calender that uses repeating fields (to accommodate the cross-tab format), and script triggers to monitor changes and write them back into their original records – and I guess you could find space for checkboxes somewhere …

           

          Be aware that this is just one of many possible implementations, but maybe it gives you some inspiration to start experimenting.

           

          Having said all that: a totally different approach would of course be to maintain a combined reminder table for all your entities (or a combined notes table with a reminder function, where an empty note with a due date is just a reminder …)., which would make a “native” implementation much simpler.

          1 of 1 people found this helpful
          • 2. Re: Relationship or ExecuteSQL
            wimdecorte

            raphthepenguin wrote:

             

             

            But I also have read alot about the ExecuteSQL function and it seems like, if someone knows what he is doing this will be a quicker and easier approach, with less possible error.

             

            There is a lot of percepetion packed into that statement.

             

            First of all: I'm a big proplent of the ExecuteSQL() function.

            But you can not conclude that with more experience everything will be quicker and easier.   The best practices about the function call are still very much being established.

             

            For instance: there is a HUGE penalty for doing even a simple SELECT on a large table when you happen to have an open record in that table.  FMS will send you all the data in that table for the client to resolve the function.  Simple SELECTs even on huge tables are blazingly fast if there are no open records in yoru session.

            Because of that penatly you want to have very explicit control over when you do an ExecuteSQL() function call.  For that reason I would shy away from using it in field definitions, conditional format calcs, invisibility calcs,...

             

            The number of JOINs and the type ov SQL functions that you use also carry a penatly on performance.  MAX() and COUNT() for instance can be very painful.  To the point where it may be more efficient to do a SELECT and get all the reutls and then let FM do the count.

             

            To be clear: I am not trying to tell you not to use ExecuteSQL() but I am trying to disabuse of the notion that seems to be implied in your statement that if you learn about SQL EVERYTHING will be faster and easier.

            There are a lot of nuances here, and a lot still to be discovered.

            1 of 1 people found this helpful
            • 3. Re: Relationship or ExecuteSQL
              raphthepenguin

              Thank you both for being so helpful!

              Since I am not having too much time to learn right now and like you pointed out things will not be heaven afterwards, I will put the ExecuteSQL on the backburner and go with Portals for now.

               

              But I also definately got more sure that in the future I would like to attack the function!

               

              Thanks again! I really appreciate it!