10 Replies Latest reply on Jul 23, 2013 12:05 PM by BruceRobertson

    ExecuteSQL in a global calc field?

    flybynight

      I've just started playing with ExecuteSQL. I started using SQLexplorer from SeedCode, and made some tweaks along the way.

       

      My plan is to build a picker that will generate a list of IDs for current jobs in our system. The Status field can have several possible entries, so I'm selecting all of them that would indicate a job is current. I suppose I could also say that Status <> "Done" or "Invoiced", but I would think the results would be the same.

       

      Anyway, I had in my head that my list of CurrentTickets could just be a global calculation field. But, when storage is set to global, it just shows the dreaded "?" instead of my array of data. With a regular calc, it works… as long as "Do not store calculation results…" is checked. Looks like it's not possible to have a global field that is not stored, so that could be the crux of the issue here.

      It just seems unnecessary to me to have this calc in every record, when it is going to be the same value. Seems like a global would be less overhead.

       

      Or maybe I'm just thinking about it wrong.

      Looking for some enlightenment.

      Thanks!

      -Shawn

        • 1. Re: ExecuteSQL in a global calc field?
          Mike_Mitchell

          Shawn -

           

          Can the Status field have a different value on different records?

           

          Mike

          • 2. Re: ExecuteSQL in a global calc field?
            flybynight

            Yes. The Status field is not global. Each record has only 1 status, though. The layout has that field set to a radio button selection.

            The CurrentTickets field finds all of the IDs for records set to certain Status values.

             

            Laters,

            -Shawn

            Sent from my iPhone

            • 3. Re: ExecuteSQL in a global calc field?
              Mike_Mitchell

              Never mind. I had something else I was thinking of.

               

              I believe your basic observation is correct: You can't have a global calculation that is unstored. In my experience, ExecuteSQL doesn't seem to work in a calculation field when the result is stored. Haven't figured out why yet. (Maybe someone smarter than I can enlighten us.)

               

              For purposes of building a value list, you can still use ExecuteSQL, but you'll likely need to have a field in a utility table (single record) that you populate via script. You can then build the value list based on that. OTOH, it might be easier just to put a calculation field in your current table with all the available options for "Current" as a return-delimited list, create a self-joining relationship between that field and the Status field, and just make a relationship-driven value list. Ignore ExecuteSQL altogether.

               

              HTH

               

              Mike

              • 4. Re: ExecuteSQL in a global calc field?
                flybynight

                Mike,

                 

                Part of this is that I'm trying to force myself to do some exploring and learn more about ExecuteSQL. I tend to learn best when I'm lost in the weeds!   ;^)

                 

                I was thinking about moving this field to a utility table, like you said. That way, even with a non-global calc, it's still just one record and one field.

                 

                I started out trying to make a TO with this relationship, but I was having trouble figuring out to how to make the relationship valid for Status = "x" OR Status = "y" OR Status = "z" etc., for all of the status states that we consider a JobTicket to be "current." Again, I suppose I could have used the TO to define all of the states that Status <> "p" AND Status <> "q" etc... or I could have made another calc field with all of my OR statements that could = 1 (true).

                 

                Many ways to skin the cat, but I think that ExecuteSQL is one of those things that will become a more powerful tool, the more you use it. And I like that a simple calc field can keep my graph less cluttered. I have enough of that already!

                 

                Laters,

                -Shawn

                Sent from my iPhone

                • 5. Re: ExecuteSQL in a global calc field?
                  Mike_Mitchell

                  Shawn -

                   

                  If you put all your acceptable statuses in a return-delimited list on the "parent" side of the relationship, FileMaker will match them. Like this:

                   

                  x

                  y

                  z

                   

                  will match any of x OR y OR z on the "child" side.

                   

                  It's not a whole lot different from using ExecuteSQL and doesn't require constant refreshing. I'm a big fan of ExecuteSQL ... just not in this application.

                   

                  Mike

                  • 6. Re: ExecuteSQL in a global calc field?
                    flybynight

                    Cool! I didn't know that a return delimited list would work as an OR. That actually solves a bunch of other issues, not related to this one. Thanks!

                     

                    For this situation, I'll probably test out a few options. This will be adding functionality, so I'm not under a deadline. Love it when I have time to play.

                     

                    And this is one of those cases where my original question was more about learning something new (to me), than it was about finding the best solution to the problem.

                     

                    Probably isn't a quick question, but do you have some practical examples of where you like to use ExecuteSQL?

                     

                    Laters,

                    -Shawn

                    Sent from my iPhone

                    • 7. Re: ExecuteSQL in a global calc field?
                      Mike_Mitchell

                      Sure.

                       

                      1) Reporting on user-defined fields.

                      2) Reporting when you don't want to clutter your Relationships Graph and you're using the Virtual List technique. (The fact that you get a return-delimited list directly as a result of the ExecuteSQL is almost tailor-made for the Virtual List.)

                      3) Pulling statistics for user display when the user can define what value to place in the stat.

                      4) Charting.

                       

                      Just off the top of my head.

                       

                      Mike

                      • 8. Re: ExecuteSQL in a global calc field?
                        flybynight

                        Mike,

                         

                        I know that we are quite it bit off topic from my original post, but…

                         

                        I was playing around with what you suggested, making my TO relationship to a return-delimted list, and I can't get it to work.

                         

                        I did get it to work, by building the relationship on all that status option that it is NOT equal to. I have several global fields with each choice in my Status value list. If I build my relationship saying Status <> zg_Status_Option8 AND Status <> zg_Status_Option9, etc (these are the options that do not consider to be a "current" job ticket.

                        But I made a new global field called zg_Status_Current and set that as a global calc field =

                        "Option1¶

                        Option2¶

                        Option3¶

                        ...etc"

                        Then, made the relationship Status = zg_Status_Current and I get no values defined.

                         

                        So, what I have is working… I'm just curious as to what I'm doing wrong to get the way that you suggested working.

                         

                        Thanks!

                        -Shawn

                        • 9. Re: ExecuteSQL in a global calc field?
                          Mike_Mitchell

                          The global field has to be on the "parent" side of the relationship. It won't work if it's on the "child" side.

                          • 10. Re: ExecuteSQL in a global calc field?
                            BruceRobertson

                            It would be helpful if you post a copy of your file or simplified example of what you are trying to do.