1 2 Previous Next 18 Replies Latest reply on Apr 15, 2014 12:06 PM by nmorales

    Execute SQL HELP

    nmorales

      Hi All

       

      Any SQL experts that can help me?

       

      I have a portal where I want to show thru conditional formatting the type of unavailability a specific DJ has.

       

      Im using portal filtering so that they can search by Region, Group and DJ Name

       

      One type of unavailability is if they are previously booked. This is the executeSQL calc I'm using to find if they are booked

       

      "SELECT isLocked

       

      FROM EventTalent

      INNER JOIN Talent

      ON EventTalent.TalentID = Talent.TalentID

      WHERE

      luEventDate BETWEEN xg_EventDate and xg_EventDateEnd AND

      luEventDateEnd BETWEEN xg_EventDate and xg_EventDateEnd

       

      This is great because it gives me a list of DJIDs that are booked within date range however in my portal I want to show if that specific related record is booked. I thought I could add the AND (in bold) but that doesn't seem to work. Any ideas?

       

       

       

      ExecuteSQL (

       

      "SELECT isLocked

      FROM EventTalent

      INNER JOIN Talent

      ON EventTalent.TalentID = Talent.TalentID

      WHERE

      luEventDate BETWEEN xg_EventDate and xg_EventDateEnd AND

      luEventDateEnd BETWEEN xg_EventDate and xg_EventDateEnd AND EventTalent.TalentID=portalname::TalentID'"

      ; "" ; "" )

      )

       

       

      Theres a Talent Table for DJs and an EventTalent Table which is a join table between the Talent and the event which has the date and time the DJ will be spinning.

       

      Any ideas suggestions are greatly appreciated! Im looking to optimize this pop up and I think this would help greatly.

       


      Thank you,

       

      Nicole

       

        • 1. Re: Execute SQL HELP
          wimdecorte

          You say it does not work: what do you get back?  A "?" or an empty result?

           

          You may want to try adding parenthesis to make your request a little more explicit

           

          WHERE

          (

          luEventDate BETWEEN xg_EventDate  and xg_EventDateEnd AND

          luEventDateEnd BETWEEN xg_EventDate and xg_EventDateEnd

          )

          AND EventTalent.TalentID=portalname::TalentID

          • 2. Re: Execute SQL HELP
            nmorales

            I got back a ? with and without parens :/

            • 3. Re: Execute SQL HELP
              nmorales

              My layout is based on Talent The portal is another TO of Talent called Talent_portal.  The portal needs to show the whole talent pool.  And each row should display different color based on availability status.  I had it in a list view before and had 5 different relationships checking date availability for overlaping dates, within, etc but this caused the layout to load slowly because the global was being set.  That is why I decided I use SQL since its a simple BETWEEN date AND date the only trouble is checking if the portal row ID is in that list that SQL returns.

              • 4. Re: Execute SQL HELP
                wimdecorte

                The ? usually indicates that there is a syntax problem with the query.

                 

                If you don't have it already, download Query Builder and read the instructions, it has the sql debug custom function built in that will tell you exactly what the error is:

                 

                http://www.modularfilemaker.org/2013/12/query-builder/

                 

                If that does not solve it, post back with the error returned and we'll figure it out from there

                • 5. Re: Execute SQL HELP
                  nmorales

                  awesome Ill download now thanks wim!

                  • 6. Re: Execute SQL HELP
                    taylorsharpe

                    You added:

                     

                        AND EventTalent.TalentID=portalname::TalentID'"

                     

                    Note that just before the double quotes at the end of the line you have a single quote.  I think that is causing a syntax error and is why you are getting a ?. 

                    • 7. Re: Execute SQL HELP
                      nmorales

                      hey wim so it seems to not be reading the ID of the portal row I think thats what the issue is. I have a layout where i do query testing and if I put int the ID manually 'DJ0001' it returns a value. So perhaps its because its in a portal it doesnt know how to read it? 

                       

                      Someone suggested I try this but it still returns a ? and I think its because the portalScreen Shot 2014-04-15 at 10.52.04 AM.png

                      • 8. Re: Execute SQL HELP
                        beverly

                        Date comparison must be SQL date format, IIRC.

                         

                        yyyy-mm-dd

                         

                        and/or perhaps your globals are not date types and you must

                        GetAsDate (xg_EventDate)

                         

                        not enough info.

                        • 9. Re: Execute SQL HELP
                          DavidJondreau

                          A simple calculation using relationships is probably better than ExecuteSQL() in this case.

                           

                          ExecuteSQL() will evaluate all records, not just the ones filtered by relationship. And since you're using BETWEEN, it won't use the index. It's going to be pretty slow.

                           

                          A properly constructed "non ExecuteSQL()" calc makes more sense here. For portal filtering, and especially for conditional formatting.

                          • 10. Re: Execute SQL HELP
                            nmorales

                            I actually originally had relationships and calcs but when the global would be set it would take forever to load the list of data. For that reason I thought if I have a execSQL calc check the availiability of each portal row it would be quicker.    O this optimization idea doesnt seem like it was optimal lol

                            • 11. Re: Execute SQL HELP
                              DavidJondreau

                              Sorry, I missed that post in this thread.

                               

                              What is the goal here? Not the method you're choosing to accomplish that goal, but the user experience you desire? Why are you viewing a list of DJs from the DJs table?

                               

                              Based on the description above, I would assume you are trying to book DJs for events. You could use the Events table and show a list of available DJs.

                               

                               

                              And one thing I realized a while back when dealing with overlapping dates is all you need to check for is:

                               

                              ( Start Date < End Date 2 ) AND ( End Date > Start Date 2 )

                               

                              If this statement is true, there's an overlap. If it's false, there's not.

                              • 12. Re: Execute SQL HELP
                                nmorales

                                Goal:

                                 

                                View ALL talent whether available or not and display thru conditional formatting their type of unavailability (vacation, booked, etc)

                                 

                                Reason :  The Talent might be marked as unavailable that day but it might just be to a certain time so user wants to view them even if marked unavillable.

                                 

                                The filtered relationship in checking for availability is the bottleneck since there are 5 rellationships. We have start dates and end dates because a Talent can be spinning for a week on a cruise.

                                • 13. Re: Execute SQL HELP
                                  DavidJondreau

                                  You've got a couple things going on here. Displaying certain records, and indicating something about them.

                                   

                                  Why are you viewing this through a Talent layout? Why not through Events? You want to see who's available for an Event, right? Your relationship can be based on the Dates of the Event rather than global dates.

                                   

                                  What are the five relationships you need? It feels like most can be based on the Event table and not globals.

                                   

                                  It seems you don't need to see all the talent, just the talent that doesn't have an overlap, or has an overlap of just the start and/or end date.

                                   

                                  A good process is: Reduce the number of records you show in the portal using relationships. Then apply portal filtering to that reduced set. Then apply conditional formatting to those records.

                                  • 14. Re: Execute SQL HELP
                                    DavidJondreau

                                    Something to keep in mind is that a portal filtering calculation acts on each available child record. So you're running that ExecuteSQL() on every single record (loading the entire record) in the Talent table.

                                     

                                    Relationships use indexes to filter available records. Using indexes is much faster than loading the entire contents of a record. You want to only use portal filtering on a small subset of records.

                                    1 2 Previous Next