7 Replies Latest reply on Feb 26, 2016 2:48 PM by user19752

    ExecuteSQL Where clause question?

    BruceHerbach

      I have a foreign key ID field that has a list of IDs.  I would like to run an ExecuteSQL query that will return another field in the record when a specific ID is in the foreign key field.  The query below works if the search value is the first/top item in the list.  The search fails if the matching value is not the first item in the list.   Is there a way to get ExecuteSQL to match on any value in the foreign key field?

       

      --- Current query,  works when ? is first value in the list.

      SELECT a."Area_ID"

      FROM "FieldManagement" a

      WHERE a."PartDailyGoal_ID" = ?

       

      Thanks much

      Bruce

        • 1. Re: ExecuteSQL Where clause question?
          user19752

          If you mean  list of IDs as values delimited by line breaks, it is not "normalized" data, bad for SQL.

           

          There is workaround not using ? like

          WHERE a. "PartDailyGoal_ID" IN (ID1,ID2,ID3...)

          If ID is text then need quoted. IN ('ID1','ID2','ID3'...)

          • 2. Re: ExecuteSQL Where clause question?
            taylorsharpe

            Assuming you have a found set and the Primary Key field is a number and it is the PartDailyGoal_ID in the other table, would this work?

             

            Let ( [

            F1 = TableName::SummaryFieldOfPrimaryKeyAsList ;

            F2 = Substitute ( F1 ; ¶ ; ", " ) ;

            F3 = "SELECT Area_ID FROM FieldManagement WHERE PartDailyGoal_ID IN ( " & F2 & " ) " ;

            F4 = ExecuteSQL ( F3 ; Char ( 9 ) ; ¶ )

            ] ; F4 )

            • 3. Re: ExecuteSQL Where clause question?
              BruceHerbach

              I took a look at all of the suggestions and think it may be one of those times the SQL is not the best answer.  Easy FileMaker solution.  Create Global field with relation to TableOccurence::PartDailyGoal_ID

               

              Enter Value in to global field, use list function to get list of values required.

               

              Down side adding something extra to the relationship graph.  Upside this greatly simplifies the scripting involved.

               

              All thanks very much for the helpful suggestions.

              • 4. Re: ExecuteSQL Where clause question?
                jgomes384

                Could you use instead:

                "SELECT a."Area_ID"

                FROM "FieldManagement" a

                WHERE a."PartDailyGoal_ID" LIKE ?" ?

                 

                -Jason

                • 5. Re: ExecuteSQL Where clause question?

                  Without some sample data, it's difficult to understand exactly what you mean.

                   

                  Have you considered getting a SQL plug-in for FileMaker or are you already using one? There are lots of them. Beverly recently listed three different ones. I do SQL remotely against the FM database (anything to get away from the Data Viewer), but that seems to be quite rare.

                   

                  So, anything that would let you experiment freely to test your queries would be helpful.

                   

                  SQL is amazingly powerful and if you can't do something with it, it may point to a design issue (but of course, depending on what you're actually trying to do).

                   

                  - m

                  • 6. Re: ExecuteSQL Where clause question?
                    beverly

                    I agree with Bruce to go with a relationship. Because we have a feature in FM that's called "multi-line" key, a LIST of return delimited values (a¶b¶c) can relate to a field in another table that has a OR b OR c.

                     

                    It's difficult to use this kind of data in SQL as it relies on the LIKE and the values are case-sensitive and just plain not as fast as "native FileMaker". Not even a plug-in would be as beneficial. You have to worry about getting the quote right because the ? does not work with lists like this (return delimited).

                     

                    Here is an article about ExecuteSQL and this kind of "data":

                    http://filemakerhacks.com/2012/12/15/executesql-using-in-with-faux-dynamic-variables/

                     

                    The multi-line key is a unique FM feature (in relationships), AFAIK.

                    beverly

                    • 7. Re: ExecuteSQL Where clause question?
                      user19752

                      If you have only one "list of IDs field", you can use it instead of global field in relation (using another TO if need).

                      If there are two or more, using global decrease "extra relationships".