Jump to content
Sign in to follow this  

ExecuteSQL Strange Bug

Recommended Posts

I am getting a strange result, I am attempting to use the ExecuteSQL command to avoid using multiple Table Occurrences.

However I am stumped when the field I need does not work on ExecuteSQL for an odd reason.

ItemNo and Level are both Indexed Number Fields with the EXACT SAME SETTINGS, however when performing these commands, ItemNo gives me a result, when Level does not. They both have data inside, etc. Any thoughts or ideas why I am receiving a "?" on the second 

ExecuteSQL("SELECT c.ItemNo FROM Test_C_any c WHERE c.TestID = 'AA'";"";"";"")

ExecuteSQL("SELECT c.Level FROM Test_C_any c WHERE c.TestID = 'AA'";"";"";"")



Share this post

Link to post
Share on other sites

I'm not an ExecuteSQL expert but I see a couple of things. You are not using the parameterized form of the query; and you are supplying to many terms at the end of the query. Parameterized form:

ExecuteSQL("SELECT c.Level FROM Test_C_any c WHERE c.TestID = ?";"";"";"AA")

Share this post

Link to post
Share on other sites
17 minutes ago, BruceR said:

I'm not an ExecuteSQL expert but I see a couple of things. You are not using the parameterized form of the query; and you are supplying to many terms at the end of the query. Parameterized form:

ExecuteSQL("SELECT c.Level FROM Test_C_any c WHERE c.TestID = ?";"";"";"AA")

I am not the best at this ExecuteSQL syntax either, but it Works for one field but not for another...

also that expression does not work for either field, so something is wrong with that method.

It seems to do with the FieldName being "Level" when I change the field name from Level to Lvl it works. maybe I am breaking a rule in the syntax using a field name Level... seems a bit silly though. I would assume many developers have a field called Level.

Share this post

Link to post
Share on other sites

You will find a list of reserved words on page 34 of the FileMaker 13 SQL Reference document.

I believe it's good practice to quote your field and table names.

Share this post

Link to post
Share on other sites
7 hours ago, cgroody said:

maybe I am breaking a rule in the syntax using a field name Level... seems a bit silly though. I would assume many developers have a field called Level.

Nothing silly about it... it is a matter of being aware of what the standards are and as Comment indicated what reserved keywords come with that standard.  FM's documentation is pretty clear.

Another good example for instance is "grant".  Which is a SQL reserved keyword that will bite you if if you have a client that works with grants.

Just always quote your table and field names.  That gets around all these issues without requiring you to change your field or table names.

There are a number of custom functions floating around that will help you with that.  And will also allow you to pass a real field reference to the SQL query that has the added benefit of keeping your query safe for TO or field name changes.



Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By Tom R.
      Having trouble with an ExecuteSQL calculation. I used SeedCode's SQL Explorer to build the calculation. It works fine if I use literal values from a specific record; but if I instead change it to a field reference, the calculation fails. I get the message <unrelated table> in the Data Viewer. The code is below. Am I misusing the field names somehow?   // Build SQL Query q = "SELECT SUM ( " & ainterestMineralDecimal & " ) FROM " & a@LINEITEMS & " WHERE " & a_id_lease & " = ? AND " & a_id_tract & " = ? AND " & a_id_unit & " = ? " ;   // Run SQL Query result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; @LineItems::_id_lease ; @LineItems::_id_tract ; @LineItems::_id_unit ) ] ;   Thanks,   Tom RuBane
    • By NewBoard
      I have used the combination of ExecuteSQL and ValueCount quite a few times throughout my databases before, with almost no issues. Generally what I will do is set a variable by running an ExecuteSQL, then take a ValueCount of that variable. Then with that ValueCount I will loop through my original ExecuteSQL variable.
      However, I am now trying to do something different with this combination. Instead of using the ValueCount variable for a loop, I'm trying to use it as a Global Variable that will be displayed on the main menu. Here is an example of the code I'm trying to use:
      Set Variable [$source; Value: ExecuteSQL ( "SELECT UUID FROM Inspections WHERE Inspector = ? AND Accepted ≠ Accepted" ; "" ; "" ; $$fullName )] Set Variable [$$accepted; Value: ValueCount ( $source )] The UUID field is, as expected, an auto-entered "Get ( UUID )" field. The Inspection table is a table containing all of our part inspections. Each inspection begins with it's Accepted field having a value of "Rejected", if the correct parameters are met this field will become "Accepted". The Inspector field contains the first and last name of whomever conducted the part inspection. The $$fullName variable is set previously in this same script by comparing the user's Username to a Table that contains all of our user's Usernames and Full Names.
      What I was hoping to do is use a Merge Variable on our main menu that displays our $$accepted variable, showing the user how many unaccepted inspections from that particular user in our database.
      However, it only ever displays a value of "1". One of our users has 6 unaccepted inspections in our database currently, but this script only shows that he has 1.
      I am wondering if you can't use Merge Variables or ExecuteSQL in this way, or if I'm simply missing something obvious.
      Please let me know of any more information I should provide. Thank you for any assistance you may offer!
    • By vanderark1
      I have created a "viewer" using a virtual list technique and ExecuteSQL.  The "viewer" is a portal that contains one field - a unstored calc text field from the virtual list table.
      This allows me to run a script for a "report" and have the results appear in this field. I can run a different script and have different data appear in that field.  Everything works great, except column widths.
      I understand how to adjust Tabs, alignment, etc using FM>>Line Spacing>>Tabs.  However, that sticks to the field regardless of the data it is showing.
      I can use hard tabs in the script to place tabs where I want but only for FM fields or text (for column headers for example).  I set a dozen tabs in FM>>Line Spacing>>Tabs - one every 50 points and then used Hard Tabs n the script to move column headers (simple text) over as many tabs as desired. This works great.
      However, I can't figure out how to control the column width WITHIN the ExecuteSQL function.  I need this, as I will end up with dozens of "reports" that I want to view in this single field, all created on the fly via scripting.
      Is it possible to control the tab width with the ExecuteSQL function or am I heading down the wrong path?
      Thanks in advance for any suggestions!
    • By Rachel
      This is my first time posting, and I apologize if this is the wrong place. I've only been filemakering for a few months, so again, apologies if this is a stupid problem.
      I could use some advice.   I'm working on a solution that must create monthly reports in the following form:   http://i.imgur.com/Cf4Fjic.png   I've given up trying to bend the summary fields to my will, and began using the following approach:   Make two new tables - Queries and Reports.   In queries table, I have fields for M1, M2, M3, Region, Category, Subcategory and four result fields (M1, M2, M3 and YTD.) I also have foreign key into the reports table.   Then in reports table, I portal into the Queries table.   When report is run, a bunch of queries in queries table are created. The Results are populated via ExecuteSQL expressions.   My question is mainly this: Is this too backwards? I feel like maybe this is too weird of an approach and I"m just missing something simple about reporting and creating summaries?

    • By Tom R.
      I am dealing with a very weird condition in my solution where an ExecuteSQL statement was failing to return a matching record. 
      My solution tracks documents such as deeds and leases, where a document can have N grantors and N grantees. So I have a Parties table, a Documents table, and identical join tables, Grantors and Grantees. 
      I noticed the problem when printing out a report, and a particular document didn't have any grantees. The report uses the executeSQL query in question. So I looked for that document in the database, and the particular grantee appeared as expected in the portal. I looked in the Grantee join table, and there was a matching record, complete with the ID of the Party and the ID of the document. I then checked, and every record where that particular party appeared, in the report that party was omitted for every record where it should have appeared. In one record, that party was 1 of 6 grantees. The ExecuteSQL statement returned the other 5 parties, but not that particular one.
      In the course of troubleshooting, I created a new record for that party, nameFirst = Mollie, nameLast = Sternberg. Then I added the new Mollie Sternberg record to those documents. The original Mollie Sternberg record, I set the formerly empty field nameMaiden to "Bugged". Then I re-ran the report. The new Mollie Sternberg record appeared as expected, but so did the original record, Mollie Sternberg Bugged. So I don't know why changing a field in the party table for that record would cause the ExecuteSQL statement to return it, when it hadn't been returning before. 
      I'm freaked out, because I identified a second party that was having the same issue. Changing that party's original record to nameMaiden = Bugged also caused it start showing up, too. But I don't know how many other Party records may be having the same problem, and I have no way to identify a characteristic of the Party record that may have caused the probelm, so that I can identify potential problem Party records. 
      Is this a bug in FileMaker? Or an issue with my SQL query? Any thoughts or suggestions most appreciated. The query is below.
      Thank you,
      // Find Document Grantors
      // Built by SQLExplorer.  Compliments of SeedCode… Cheers!
      Let ( [ 
      // Define Carriage Return Substitution Character
      ReturnSub = "\n" ;
      // Enable the second line here if you want the header in your results
      header = "";
      //header = "";

      // Define Table variables
      a@GRANTORS = Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::_id ) ; "::" ; ¶ ) ; 1 ) ) & " a" ;
      b@PARTIES = Quote ( GetValue ( Substitute ( GetFieldName ( @Parties::_id ) ; "::" ; ¶ ) ; 1 ) ) & " b" ;
      // Define Field Variables
      acapacity = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::capacity ) ; "::" ; ¶ ) ; 2 ) ) ;
      a_id_parent = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::_id_parent ) ; "::" ; ¶ ) ; 2 ) ) ;
      a_id_party = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::_id_party ) ; "::" ; ¶ ) ; 2 ) ) ;
      aakaFull__c = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::akaFull__c ) ; "::" ; ¶ ) ; 2 ) ) ;
      ainterestConveyed = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::interestConveyed ) ; "::" ; ¶ ) ; 2 ) ) ;
      b_id = "b." & Quote ( GetValue ( Substitute ( GetFieldName ( @Parties::_id ) ; "::" ; ¶ ) ; 2 ) ) ;
      bnameDisplay = "b." & Quote ( GetValue ( Substitute ( GetFieldName ( @Parties::nameDisplay ) ; "::" ; ¶ ) ; 2 ) ) ;
      // Build SQL Query
      q = 
      "SELECT " & bnameDisplay & " , " & aakaFull__c & " , " & acapacity & " , " & ainterestConveyed & " 
      FROM " & a@GRANTORS & " 
      INNER JOIN " & b@PARTIES & " ON " & a_id_party & " = " & b_id & " 
      WHERE " & a_id_parent & " = ? " ;
      // Run SQL Query
      result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; $_id_assignment ) ] ;  
      // Clean up carriage returns
       List ( header ; Substitute ( result ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  ) )  )
  • Who Viewed the Topic

    7 members have viewed this topic:
    Ocean West  comment  flook  Gabriel  TShirtDan  hutchlad  Aussie John 
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.