5 Replies Latest reply on Mar 14, 2014 9:33 AM by philmodjunk

    Filemaker internal SQL debug help

    firebase

      Title

      Filemaker internal SQL debug help

      Post

           i am simply getting crazy on this one:

            

           ExecuteSQL ("SELECT \"field1\" FROM \"table1\" WHERE \"field1\" = \"current_table::field2" " ;"";"")

           Which means i want to see if there is a record in another db which is same as in the open layout database i am using this knob/function in.

            

           everything works if i use '1' (records in field1 = 1) instead of current_table. But as soon i want to use current_table i get a "?".

           I also tried a variable but absolutly nothing works there...

            

           Please help, i amk allreaddy hitting the wall... :)

        • 1. Re: Filemaker internal SQL debug help
          philmodjunk

               Is current_table the name of a field or the value you want to find?

               Is table1 the table you want to query for this data or the table on which your layout is based and that has the value you want to search for?

               My best guess is that you should set it up this way:

               ExecuteSQL ("SELECT \"field1\" FROM \"table1\" WHERE \"field1\" = ? " ;"";"" ; Table 2::Field )

               Where you layout is based on Table 2 and the value in the current record of Table 2 that you want to search for is found in Table 2::Field.

               And if there is any chance that there is more than one record in Table1 that will match Table 2::field, you may need to also add a record separator instead of "".

          • 2. Re: Filemaker internal SQL debug help
            firebase

                 excuse my my fault, i just edited my post.

                 its: current_table::field2

                 so its a field from a table in which layout i use this function.

                 yes, table1 is another table i want to query from.

                 Thanx that worked!!! :)

                  

                 But why is it not accepting the fieldname at the "?" position? and how/why does it work at the end?

            • 3. Re: Filemaker internal SQL debug help
              philmodjunk

                   There are two problems with putting the table and field name in place of the question mark in your query:

                   Current_Table::FieldName is incorrect syntax for a qualified field reference. You'd use Current_Table.FieldName.

                   But even if you use Current_Table.FieldName, it won't work as expected as the Query does not "know" which record in Current_Table is your current record and thus won't retrieve the correct value. I suspect that the lack of context will generate a syntax error as no part of the first part of the query refers to any table except Table1.

                   In other DB systems, I've often had to do "text surgery" where I inserted a value from a field or variable into the middle of a text expression in order for it to be part of the WHERE clause. At least in ExecuteSQL, the ? option makes that a bit simpler.

              • 4. Re: Filemaker internal SQL debug help
                firebase

                     ah now i get the problem.

                     i was thinking i can use the regular fm table::field synthax as placeholder to get this field, but when using executesql( i am allready in sql mode and there is no :: synthax anmore.

                     So that means the ? shifts it outside the boundry where it can be read?

                     But why is an variable instead not working?

                • 5. Re: Filemaker internal SQL debug help
                  philmodjunk

                       SQL can't evaluate a variable any more than it can evaluate Table::Field.

                       Note: there is an alternative approach but it's more complicated than using the ? method. I'm showing it here to illustrate the point that I am making:

                       ExecuteSQL ("SELECT \"field1\" FROM \"table1\" WHERE \"field1\" = " & current_table::field2 ;"";"")

                       By taking Current_Table::field2 out side of the quoted string, it will be evaluated by FileMaker and the value inserted into the query. This kind of "String surgery" is often used in many other systems that use SQL to query a database. In ExecuteSQL, we have the option of using ? which can be a lot simpler in many queries.