Jump to content

Search the Community

Showing results for tags 'executesql'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type

Community Forums

  • The New Claris Platform
  • Community Resources
    • Community Videos, Tips, & Techniques, Articles.
    • FileMaker Marketplace Discussions
  • FileMaker Platform
    • FileMaker Interface Features
    • FileMaker Schema & Logical Functions
    • FileMaker Go for iPad and iPhone
    • FileMaker and the Internet
    • FileMaker Pro 19
  • FileMaker Server Administration
    • FileMaker Server 19
    • FileMaker Server 19 (Linux)
    • FileMaker Cloud
    • FileMaker Custom SSL Certificates
    • oAuth and External Server Authentication
    • Zabbix Server Monitoring
    • Previous Version Server Discussions
  • JavaScript Integration
    • Java Script and the Web Viewer
    • Carafe.FM
    • Widget Studio
  • Brain Food
    • Security Concepts
    • The Left Brain
    • Upgrading & Migration
    • Data Analysis
    • Development Standards
    • The Separation Model
    • Relational Database Theory
    • Damaged / Corrupt File Problems
    • Hardware & Networking
    • Product Ideas
  • FMForums Affiliates & Sponsors
    • Productive Computing Support Forums
    • 360 Works Official Product Support Forums
    • Geist Interactive Product Support Forums
    • FileMaker Vendors
    • FileMaker Training Resources
  • FileMaker Classifieds
    • Announcements of FileMaker Product, Services or Resources
    • Classifieds
    • Tools Of The Trade
  • FM Forums Operations
    • FM Forums Feedback & Site News
  • FileMaker Friday Night Chat's Topics


  • Captain's Blog
  • FileMaker Weetbicks
  • FileMaker Security Blog
  • The FileMaker Rift
  • HOnza's Bits @ FMForums
  • SeedCode
  • FileMaker Hacks
  • Skeleton Key's Blog
  • Wing Forward Solutions
  • 360Works Articles and Tutorials
  • GoBillit
  • Filemaker - an amateur's view
  • Linear Blue
  • Surya Kanta Mekap's Blog
  • eXcelisys' Blog
  • Manjit Behera
  • Blue Feather's Blog
  • RCC's FileMaker News and Bits
  • Thorsen Consulting
  • DB Services Blog
  • FileMaker Magazine
  • Eye on FileMaker
  • Dev Talk
  • The Philosophy of FileMaker
  • 1-more-blog
  • Productive Computing
  • Geist Interactive
  • Soliant Consulting


  • White Papers
  • Infographics
  • Samples
  • Add-ons
  • FMGo
  • Solutions
  • Tutorials
  • Plug-Ins

Product Groups

  • Workplace Innovation Platform
  • Site Advertising
  • Development
  • FileMaker Hosting

Find results in...

Find results that contain...

Date Created

  • Start


Last Updated

  • Start


Filter by number of...


  • Start







Website URL




OS Version

  1. I'm having a syntax problem getting the 'IN' clause to work in FM's ExecuteSQL statement. I would like to use a '?' placeholder in the statement (just looks cleaner), but when I do it doesn't work. I already have a string that contains the list of numbers (this is all numbers...except the fact that it is a string of numbers :) ). I want to use that variable in this statement. Here's a WORKING example: Let ( [ ids = "281,282,283"; sq = "SELECT ID_fk, sum(Amount) FROM Payments WHERE ID_fk IN ( " & ids & " ) GROUP BY ID_fk" ] ; ExecuteSQL ( sq ; ""
  2. 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 & " = ?
  3. 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
  4. 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 scr
  5. 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
  6. 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 por
  7. Just trying to learn this executeSQL and can not seem to get sensible results. I have a table which represents any cost prices changes that occur with a product. If I use a simple statement eg ExecuteSQL ( "SELECT ProductDescription FROM CostPriceChanges" ;"";",") i get a nice list of products show up eg Sweet Potatoes, Limes, Limes, Lettuce, etc etc However when i add where criteria I get the dreaded ? ExecuteSQL ( "SELECT ProductDescription FROM CostPriceChanges WHERE Cost Price >= .01" ;"";",") I am thinking, it is something in the syntax
  8. Hi, I can't seem to get filemaker pro to plot google charts the way I want them in web viewer. I want the graph to look something like this: ...Seems it would be pretty basic at first but I can't get this particular multi-series chart to work. It needs to plot ALL the records and MORE THAN ONE series. I've created one table called 'MyTable' with 3 fields 'CarName', 'CarSpeed' and 'CarWeight'. I've populated the table with 5 records. CarName on the Y-Axis, and against this, CarSpeed and CarWeight are plotted on the X-Axis as 2 series. i.e. series one is 'CarName' vs 'CarSpeed', seri
  9. Hi, I'm trying to use Filemaker to manage a mysql database on Amazon RDS. I'm trying to set up an import layout that will allow users to load data into into the RDS database. Mostly, Filemaker and mysql are playing well together. But I'm getting a problem when I try import data using LOAD DATA LOCAL INFILE . I've run the query successfully with a different client on the same database (Sequel Pro) but when I run it using the Execute SQL script step in Filemaker I get an error, with the message "Load data local infile forbidden". I've looked around online, and it seems like I need
  10. This is just about as simple as an ExecuteSQL gets, but I can't figure out why it generates a "?". ExecuteSQL ( "SELECT Count (\"voteYes\") FROM \"MotionVote\" WHERE \"motionYear\" = '2016' "; "" ; "" ) MotionVote is a TO; voteYes and motionYear are fields therein; voteYes can either be 1 or blank. The objective is to display a count of "yes" votes. What did I miss?
  11. Hi all Can someone tell me if this query is correct? I'm trying to find all currently open Cases, but only those that belong to a personnel whose 'exclude_from_stats' field is null. So far this does seem to give me the correct results, however I am a bit uncertain as I have been having problems recently defining criteria in related records. It seems that you sometimes have to resort to different types of joins or you run the risk of excluding/including too many records. SELECT COUNT (*) FROM Cases C JOIN Personnel P ON p.personnel_ID = c.User_IDF WHERE c.Closed IS NULL AND p.e
  12. I have a uniqueness criteria for a particular number field, but the uniqueness is based on a combination of the number field with a state field (e.g. the number must be unique within all records for that state). I built a custom function to return a boolean if the number+state combination already exists in the database, and that CF uses a simple SQL query, Issue is that the CF seems to work flawlessly when tested via Data Viewer, but when inserted as the field validation calculation, returns a validation failure for any value entered. Can anyone enlighten me as a to a limitation he
  13. Hi all, Curious about ExecuteSQL this works... ExecuteSQL ( "SELECT myfield FROM myTable WHERE myTable.color = ? and myTable.style = ? and myTable.id = ?" ; "" ; "" ; "black" ; "test" ; "3" ) but this fails... when I replace myTable.color selection from "black" to empty string "" then the result fails (when there are rows with this criteria)... ExecuteSQL ( "SELECT myfield FROM myTable WHERE myTable.color = ? and myTable.style = ? and myTable.id = ?" ; "" ; "" ; "" ; "test" ; "3" ) Q: any reason it fails? is it a syntax thing? how do I search for field =
  14. I'm working on a cross-tab report using ExecuteSQL. I'd like to try to do it as a single eSQL statement but I'm not sure if its possible or not. I'm still a bit of an eSQL newbie. Background, the database manages work done in a condominium building. Each apartment in the building is identified by a floor and a location in the building; 1-9 for the floors and N, NE, SE, W for the location. For the report, the floor is on the vertical axis and location is the horizontal axis. Each cell in the report is a sum of apartments where the work has been completed. I have the first part of the
  15. I am relatively new to sql queries in Filemaker, and I'm receiving the ? in response to the following queries. Because I don't have the advanced version of FMP, I used Evaluate() to return the error code 8310 (syntax error). However I can't figure out where my syntax error is. Hopefully more experienced eyes will spot my problem(s). The following is entered in a calculated field that returns text: Let( [ $initials=ExecuteSQL("SELECT TaskAssignedTo FROM workflow WHERE" & Quote("_IDDocLog") & "=workflow::" & Quote("_IDDocLog") & "AND workflow::TaskNumber=9"; "" ; "
  16. Hi, I'm attempting to return a literal out of an ExecuteSQL() statement so I can test and develop another SQL query but I cannot get it to return successfully (it reports SQL Syntax error). I used verbatim this model example from Beverly on the FMForums only changing the "myTableRef" reference to a valid TO name in my solution but no results are returned. Ultimately I want to coalesce or cast a text field to an integer so I can use the value in a "WHERE ... IN()" SQL statement expecting integers. I had no success using CAST, COALESCE or NUMVAL SQL functions doing this which lead me t
  17. Hello, I'm trying to create a virtual list ( 70 Columns X 300 Rows ) with the below codes. But it fails after the 30th SELECT clause. I cannot figure out, please help. Thanks, Recep Let ([ @x1 = GetValue ( $$YemekIDList ; 1 ); @x2 = GetValue ( $$YemekIDList ; 2 ); @x3 = GetValue ( $$YemekIDList ; 3 ); @x4 = GetValue ( $$YemekIDList ; 4 ); @x5 = GetValue ( $$YemekIDList ; 5 ); @x6 = GetValue ( $$YemekIDList ; 6 ); @x7 = GetValue ( $$YemekIDList ; 7 ); @x8 = GetValue ( $$YemekIDList ; 8 ); @x9 = GetValue ( $$YemekIDList ; 9 ); @x10 = Ge
  18. 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
  19. Hello All, I have a table "Staff" and a related table "Lesson_Observations". A Staff record has many Lesson_Observation records. Each lesson observation is attributed a score. I have a working ExecuteSQL calculation that finds the average of all lesson observations scores for staff in a specific Faculty ("English" in the example below) and only where the Staff record has the attribute "Active". Let ( [ $query = "SELECT AVG(L.obCritAverageVal) FROM Lesson_Observation L INNER JOIN Staff S ON L.FK_StaffCode = S.PK_StaffCode WHERE S.Fk_FacName = ? AND S.Status = ?" ; $result = ExecuteSQL (
  20. Ok Ladies and gentlemen, so I’ve dipped my toes into wanting to start using the ExcuteSQL Function, I have no idea what I’m doing with it. I downloaded Seed codes SQL Explore and I am more lost than ever. I don’t understand the function and I am hoping someone can break it down for me. So something simple to start with. If I wanted to get a total value of a field in a related recorded normally I would just use Sum(TestRecord::Field) This would give me a total How the calculation above gets broke down into ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments...
  21. Hi - I've been trying a number of approaches to get a calculated value list using ExecuteSQL code. I initially tried putting the SQL code directly into the Value List definition but that didn't work. Right now, I've done this: I created a UnusedClaims field in a Visit table, whose value is calculated by this: ExecuteSQL(" Select \"_k1_xSAClaim_ID\" FROM \"xSA Claim Status:xSAClaim\" WHERE \"_k1_xSAClaim_ID\" NOT IN ( Select \"_k2_ClaimID\" FROM \"xSA Claim Status:VisitClaims\" WHERE \"_k2_VisitID\" = ? ) " ;""; "" ; Visits::${Visit ID} ) I then
  22. Hi, I'm having problem trying to get this query work. Let ( [ q = "SELECT i.c_nomComplet FROM personnesiPad as i LEFT OUTER JOIN personnesServer as s ON i.uuid = s.uuid WHERE i.__modification_ts <> s.__modification_ts"; r = ExecuteSQL (q; ";"; "¶") ]; r ) The problem seems to be my fieldnames in the WHERE clause. I've tried to quote them with the quote function, but I still have a validation error. How do I managed to use fieldnames with special characters? BTW, I'm trying to do a synchronisation file between an ipad and a server. Thanks
  23. Hi there I'm quite new to FM and come from a SQL background, and am struggling here a bit... I'm looking to run an ExcuteSQL command on a table which contains values for many customers. Each set of values per customer on my source table has their customer ID and in turn this is related to the customer table. I need to create a portal on the customer layout, based on the value table, using ExecuteSQL. I need to use ExecuteSQL to enable dynamic variables based on user input which will filter the results in the portal, producing average, maximum, minimum and so on. ExecuteS
  24. Hi - I'm a bit confused about how fields with results of SQL calculations interact with a portal. On a layout, I have a portal into a table which is related to the layout. I'm trying to use an ExecuteSQL calculation to simplify subcategorization of the data on the primary layout. I have sQL code that works fine when I set the parameters manually (hardcode) but when I put in arguments with fields that correspond to the portal rows I'm not getting the expected results. I understand that ExecuteSQL ignores table relationships on the graph -- but is that true for the arguments I'm feedin
  25. To begin, I have about 100,000 records (and growing) in a "Parent" table. Each Parent will have at least one "Child" (usually about 2 or 3, but can be 5 or 6). Each Child has a "Quantity" calculation field {sum( in - out)} which is related to the "Transactions" table.  I'm trying to filter a table (in list view) of the "Product" aka. parent to show only products that have any "Inventory" items aka. child that have quantity > 0.  I'm able to generate the results, my problem is the speed at which it calculates  For instance, "Prod A" might have three children: A_1, with qty 1 A_
  • Create New...

Important Information

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