FileMaker Query Language or FQL

Hi, I need some help. I am wanting to validate a user input on one table to a value in another table without any relationship. I was hoping I could write an Execute SQL for the following. Field: "SearchPassword" in ( Table A ) Field: "Admin Password" in ( Table B ) If match continue, if not EXIT. Or I'm on ( Table C ) and I would like to enter a "Password" in a Global Field ( Table A ) and check it to the "Admin Password" also in ( Table A ) If match continue, if not EXIT. All I want to do is provide a field on any table, check that field to the "Admin Password" field in my Preference Table without have to build relationship on each table. …

I've been working on optimizing a complex part of a solution that relied on the List() function to send multiple parameters to a custom function. Through my research I came across this article: http://www.teamdf.com/weetbicks/the-search-for-fast-aggregates--trial--error/99/ This article discusses the fact that when you use List(), Sum() or other aggregate functions, FileMaker sends the entire record set to the client, not just the data from the requested field(s). Does anyone know if this is also the case with ExecuteSQL()? I would assume that an SQL query would only return the requested data, but I'd like to know if this is definitely happening or not.

Hi. I'm using a global search field ("searchString") and SQL to populate a portal. For example, when searching a People table, I have: SELECT id FROM People WHERE name LIKE %searchString% OR category LIKE %searchString% This works great. However, I would like to extend the search so that a user could search on a company name and all related people from that company would be returned: SELECT id FROM People WHERE name LIKE %searchString% OR category LIKE %searchString% OR [companyName from the related Company table] LIKE %searchString% I've tried every iterati…

I feel stupid for asking this, but I can't get my simple SQL statement to execute properly. Here is what I have: ExecuteSQL ( "SELECT SUM("Sale Amount") FROM Jobs"; ""; "") Jobs::Sale Amount is a number field, nothing special. What am I doing wrong? I get the dreaded "?" as a result. Thank you, Ryan

We are using a FileMaker database for our small business. A web development team has redone our website to a WordPress framework is now putting together the online forms that will be linked to our database. We've run into a bit of a snare. I may not be phrasing this correctly, but this is how I understand the situation: because WordPress works with MySQL, that is how the online form information is collected / stored for our website. And due to the fact that we have different languages being collected in the online data, different MySQL databases have to be created. So, can a single FileMaker database be "linked" to several MySQL databases . . . all our information, …

is it possible to return the total amount formatted with a comma and two decimal places? ExecuteSQL ( " SELECT l.category, sum ( l.amount) AS total FROM lineitem l WHERE l.id_detail= ? GROUP BY l.category ORDER BY total DESC " ; " " ; "¶" ; detail::id ) I have tried CONVERT(varchar, CAST(sum ( l.amount) AS money), 1)

I have a Filemaker database that incorporates several fields from my online SQL database. This allows me to modify a number of fields quite easily, such as selling price, quantities etc. My problem revolves around an SQL Primary auto-incremental key field which is called product_id. I would like to create a new record within Filemaker, while maintaining the incremental properties of product_id. This does not seem possible. Can anyone suggest a script that can be applied to make this occur? Eric

Sample file for displaying timestamp index bug with ExecuteSQL timestamp test.fmp12.zip

I am trying to create a bar chart (eventually a stacked bar chart, but I wanted to make sure that I got the different data for the x-axis correct first) in FileMaker 12 Pro using ExecuteSQL. This one shouldn't be so hard since I have all the data in the same table, but I can't figure out what I'm doing wrong. I have people working in four different groups ("Group"), and a column for active people ("Active"=1, "Inactive"=0) and a gender column (Male/Female). When I use the formula below, I get a nice bar chart showing how many men are working in each group. However, when I change the gender from "Male" to "Female", the amount of women is correct (3 women in one group, …

Hello, I am wondering if anyone can explain why I get different results for the same query string between using the ExecuteSQL function in FM versus querying the database through a database browser (I'm using DBVisualizer). Specifically, if I run SELECT COUNT(DISTINCT IMV_ItemID) FROM IMV in DBVis, I get 2802. In FileMaker, if I evaluate the expression ExecuteSQL ( "SELECT COUNT(DISTINCT IMV_ItemID) FROM IMV"; ""; "") then I get 2898. This makes me distrust the ExecuteSQL function. Inside of FM, the IMV table is an ODBC shadow, connected to the central MSSQL database. In DBVis, the application connects via JDBC. However, I don't think that should make any…

Working on a nested SQL that I think should work, but is giving me wrong data: ExecuteSQL ( "SELECT COUNT (DISTINCT pgc1."C1FD~Date") FROM "PGC1~Field Days" pgc1 WHERE pgc1."C1FD~Date" BETWEEN ? AND ? AND pgc1."C1FD~Primary" IN ( 'Height Pole' ) " ; "" ; "" ; ScheduledTimeOff::q1StartDate ; ScheduledTimeOff::q1EndDate ; ScheduledTimeOff::id_EmployeeNumber ) gives me 42 ExecuteSQL ( "SELECT r.workingDaysSQLList FROM "@Resources" r " ; "" ; "" ) gives me this: Flatbed,Escort,Tillerman,Height Pole,Assem/Disassem,Route Survey,Push Truck,Pull Truck,Load/Unload,Care/Maintenance (In-Field),Off-Duty (In-Field),Repositioning/Travel,Mentoring,Supervision,Traini…

Below is a link to a basic overview of using SQL in FileMaker. It is work in progress and being added to but may give some low level SQL users a bit more of an insight, from my experience working with SQL. If you know of anyone who would make use of this, please forward it on. http://fmforums.com/forum/files/file/47-filemaker-sql-basic-overviewpdf/ Hope it helps, Jamie L. Thompson.

Trying to add some text to a query. For example here's a real simple calculation with a query that returns some text ExecuteSQL( "SELECT n_transactionAmountGross, t_transactionModel FROM Transactions WHERE FamilyID_CustomerID = ?" ; " : " ; "" ; FamilyID ) ) For a particular record it could return something like 100 : Membership 200 : Lesson I'd like to show the amount as currency. Even though the amount is a number in Filemaker, I assume that since the calculation is returning text the amounts are now a text string. So i'd like to prepend a dollar sign. But any number of operations like "$" + or Quote like used in the following…

Hi, I just started using this function, it's very helpful. I have a calculation that returns some transactions for this year. ExecuteSQL ( "SELECT n_transactionAmountGross , t_transactionModel , t_FirstName frmt FROM Transactions WHERE FamilyID_CustomerID = ? AND c_transDate_frmt = 2013 " ; " : " ; "" ; FamilyID ) ) I'd like to substitute the current year for the hardcoded year by using a variable, however this doesn't work. Let ( [ _yr = GetAsNumber (Year(Get(CurrentDate)) ) ] ; ExecuteSQL ( "SELECT n_transactionAmountGross , t_transactionModel , t_FirstName frmt FROM Transactions WHERE FamilyID_CustomerID = ? AND c_transDate_frmt = _yr " ;…

I created a global called FIELD to hold a field name. I want to reference this global as the field name it should find and use it like this: ExecuteSQL( "SELECT id FROM customers WHERE field LIKE ? " ; "" ; "" ; "%NY%" ) I think I am confused. FIELD would hold the value of 'State' in this example. So I want it to look at the State field and find all NY states. How do I reference a global field which references a real field please? I have been reading bunches of examples and still can't figure this out. I changed the name of the post to hopefully explain.

I have an ExecuteSQL statement that looks like this: ExecuteSQL ( "SELECT DISTINCT( " & GFN(WT::Version) &") FROM " & GTN(WT::Version) &" WHERE " & GFN(WT::Version) &" <> 2 or " & GFN(WT::Version) &" <> 2" ; ""; "") The output of which is: 1 3 4 5 If I remove the or statement it stops working and I get the ?. Anyone have any ideas why that is? Not Working Version: ExecuteSQL ( "SELECT DISTINCT( " & GFN(WT::Version) &") FROM " & GTN(WT::Version) &" WHERE " & GFN(WT::Version) &" <> 2 " ; ""; "")

This one has got me flummoxed. Using the ODBC driver, I am able to view, make relationships etc with my SQL online database and Filemaker. Within a typical SQL app such as Sequel Pro, I can create a new row (record) quite easily. This involves the auto creation of a 4-digit ID. The problem is, I cannot perform the same task within Filemaker. The crucial field (Product_ID), has non modify attributes, and as such, throws up an error during a new record creation script. Is there a solution via the Execute SQL script? Eric

Hi all - I am just now beginning to play around with the new ExecuteSQL() function in FM12 and I have a couple of questions regarding it's use: 1) Is it possible to use ExecuteSQL to select a set of records where a field on those records as a certain value in it when that field contains more than one value on each record? Kind of like using a multi-key field in a relationship. 2) Along those same lines....if you create a join in a SQL query do the fields used in the join have to contain a single value each or can either or both fields have multiple values? Thanks!

Hi I have been searching all over for an answer to this. Simply put I have a table (members) with fields (branches, gender) and a heap of others what I need is through a SQL query to get the following for a certain date. Here is a pic(capture1) of what I mean. I need a list of the branches with the male/female numbers and totals. I have managed half of it but cannot get the males/females to display together. I hope someone can help asap. Thanks so much, in the meantime I'll keep trying...Regards Â

Having trouble doing what I would assume is an easy task I have a table of invoice records with these Fields of interest ( invoicedate - invoicequantity - quantityontime) I would like to graph our on Time Delivery % by day but use a execute SQL statement to gather my data. The below query returns a nice list of what I would like to use for dividends and divisors as expected, However when I try to calculate the two summaries my statement returns the ? ExecuteSQL("Select sum(quantityontime), sum(invoicequantity) From ontimedeliverydetail where invoicedate between '6/3/2013' and '6/30/2013' group by invoicedate ";"";"") What changes could be made to re…

I'm not sure why this isn't working, but I have a dynamic SQL SELECT statement where both the number of fields/arguments and field values is dynamic. The problem I'm running into is when one of those values is a button container field with multiple reps. The following only represents the part of the calc that is not working. I've pulled this out and having playing with in the data viewer and I can't seem to figure out what's going wrong. Any help would be much appreciated. Let([ $SELECT_Items = " SELECT " & GFN (Items::kp_ITEMid ) & " FROM " & GTN (Items::kf_Title ) & "WHERE "; $Button = GFN (Items::Button_Log) & " = ? "; $Status = GTN (Items::…

I have been acquiring my user information via ExecuteSQL so I don't have to attach the Staff table everywhere in the universe. I write the same sql statement over and over so I thought I might store it somewhere for easy access since its perspective never changes. Maybe a custom function? Do others think this is a bad idea or how do you store some of these queries that you reuse over and over or do you just make yourself memorize them? 99% of my queries are very simple. I wish I could figure how to take those pieces and write a generic statement where I can insert those pieces, as script parameter or calc parameter. Example of what I have to write over and ove…

I am new here and was not sure where to post this, so here goes... I want to run an Excel macro from Filemaker I know how to use 'Send Event' to open the file I believe I need to use 'Send DDE Execute' to run the macro in the Commands area, but I do not know the syntax. Can anyone help please?

Hi All, Is possible to write Stored Procedures in FileMaker? If yes, how can we write? Thanks in advance.

I have found what appears to be a bug within FQL when using the union clause with subqueries. The query below gives the expected result SELECT d_OrganisationName FROM ORGANISATION WHERE a__kp_t_ORGANISATION NOT IN ( SELECT a_kf_t_Organisation FROM ORGANISATION_CONTACT_LINK WHERE a_kf_t_Contact = '" & CONTACT::a__kp_t_CONTACT & "' ) which is a list all organisations that the contact isn't currently linked to However, I also want to combine this result with another result, for example some default organisation: SELECT d_OrganisationName FROM ORGANISATION WHERE d_OrganisationName = 'Default Organisation' On it's own, the above query al…

Probably a super easy question but I cannot figure this out for the life of me. The short of it is that I'm trying to capture all records when a field value equals anything. I've tried using ALL, Any, and even Like "[a-z]%". Nothing seems to work. The bigger picture is that I'm using several global fields to filter a portal by the value list selections within said global fields. Given the selected values, I've got a calc field that captures a list of serials which filters in the portal. The issue is that for each of the global fields, I'm also using a "Show All" value that that effectively removes the global as a list condition. I'm not sure if thats clear but ba…

Hello, I am kinda of new to filemaker but I was reading up on some sql in FMP 12 and came across a virtual table concept. I currently am doing a database for a cancer clinic which contains 4 tables. the tables are named "Main", "Treatment", "Diagnosis" and "lab". treatment, diagnosis and lab are all related to Main. (__pk_Main=__fk_Main). I want to create a summary table that will extract the Treatment, diagnosis and lab records for each patient in "main" and then display it on a portal. Will sql be able to make this virtual table?

My statement is failing when I try to add something to the value of an aggregate function. For example, this work fine: select Min(Lat) from Points This fails: select Min(Lat) + 1 from Points Is this a bug? Is there some other syntax I can use to accomplish this? I'm aware I can just add 1 to the result of the ExecuteSql, but I'm doing something more complex and this is just simplified version of the bug I found.

I am developing a Professional Development database for our school district. I have several tables with one being our STAFF (our existing staff names, positions, etc). Another being COURSES begin offered for staff to take. The COURSES table has a STATUS field than is either Open or Closed. If Open, staff can sign up. I would like a Portal to display the Courses Open within the Staff table, but because the Status is a calculation table, the Portal will not work. So I was hoping to create a Script using ExecuteSQL to build a Virtual List to display the results in the Portal. Problem is, I am new to ExecuteSQL and Virtual Lists, so I am in need of some assist…

I have another ESQL statement that I am having trouble getting to work. I put this together based on a few references, but I could just have something simple misplaced (I hope). What I am trying to achieve: I have two tables, A & B, that are related via A::ID = B::A_ID_FK. It is 1-to-many (A to B ). I am trying to find a range of records in B that match a given Start/End dates, and then show those IDs along with the "Type" field from Table A. ExecuteSQL ( "SELECT A.Type, B.A_ID_FK, FROM A, B WHERE A.ID = B.A_ID_FK AND B.Date > ? AND B.Date < ? " ; "|" ; "" ; $Date_Start ; $Date_End ) Any pointers on this woud be apprecia…

I have a query that I am trying to get to aggregate some time data, so want to do a SUM() on a Time field. Data in the field is a duration (i.e. NOT a time of day) in MM:SS format, and I am trying to calculate how long something took to process. (The table has a variety of records for the same parent object, representing various stages of work, so want to find out the total amount of time put in on a parent object.) I am having trouble getting it to work, just getting question marks in response. Things work well with normal FM calculations. Here are a few of the things that I have tried: ExecuteSQL ( "SELECT SUM ( MovieRawTime_AsTime ) FROM Movies …

One apparent limitation of using ExSQL() in the separation model is that you can't use unrelated tables in a field definition. So you'd need to related all your tables to one another. I just discovered that you can bypass that limitation simply by wrapping your statement in a Let() and declaring a variable that includes wrapping a field in the GetFieldName() function. So the expression: Let( field = unrelated::table ; field ) returns an error. However, Let( gfn = GetFieldName ( unrelated::table ) ; field = unrelated::table ; field ) does not. It doesn't even have to be a field from an unrelated table! Interesting and hopefully helpful.

I successfully implemented a SQL function in my database thanks to Mr. Vodka, I tried to 'recycle' it to evaluate a numeric field but it doesn't work, what am I missing? GetValue ( ExecuteSQL ( " SELECT Left(Position + ' ',12), Count ( Position ) AS kount FROM HighScores GROUP BY Position ORDER BY kount DESC "; " "; ""; "" ) ; Get ( CalculationRepetitionNumber ) )Position is a numeric field, if I replace that field with another text field the function works just fine (it counts the instances and present…

All righty, on a recent thread I realized I may be biased against using JOINS in ExecuteSQL() statements and hence losing a valuable tool. I've decided to rectify this and have a complex result I want, but now I'm stuck. I have a table TICKETS. This represents a work order performed on a specific date and time with a cost to the customer. The time converts to a relevant shift ( of 1,2, or 3 each 8 hours long, though rarely is there a 3rd shift). There may be 0-50 Tickets run in a Shift. The relevant fields are work_Date, Shift, and Subtotal. Subtotal represents how much a customer is getting charged. I also have a table TIMES. This represents an employee's work shift.…

I've recently been studying the ExecuteSQL function, and thus SQL as well. I've learned quite a lot and am able to write equations using dynamic WHERE clauses, JOIN etc. I find nesting the function in a Let is very efficient. Ok . . . Now my silly question. Where do I use this amazingly fast and flexible function? I mean where can I insert it in an existing solution. I can see where it would allow for the elimination of many TOs, but if the TOs exist to enable data input in a related table I can't see how SQL will help. Should I view ExecuteSQL as simply a function to make reporting more efficient? Does anyone out there have a classic use of the function that really made…

At the last PauseOnError Conference in Portland I attended a discussion lead by Brian Schick, He has developed a brilliant set of custom functions that are using a compact namespace, in use you will be able to write SQL Statements in FileMaker much more efficiently with the benefit of your code being safe from breaking on schema name changes. Check it out here http://www.modularfilemaker.org/2013/03/filemaker-sql-sugar/ This post has been promoted to an article

ok this table members has the following relationships members --< address members --< phone members --< email Memberships are individual so there isn't a master "account" or parent record. however there are husband & wife that have either a matching address or phone or even email. With some SQL magic I wonder if there is a way to generate the following "unique" label. John Smith Mary Smith 123 Main Street City, State, Zip Based on any matching values in either EMAIL or PHONE in an adhoc fashion.

Hi, did someone figure out if fql supports recursive queries? (not nested queries, really recursive)? I tried it using the Oracle syntax, but it doesn't seem to work. Thanks!

I feel like I'm missing something obvious. I'm trying to write some SQL to get an ID list for deleted records in a syncing solution. I'm finding that its slower than I'd like and this method feels like it's not efficient at all, anyone have a better idea? To explain what I'm doing I'm getting a list of ID's where the creation timestamp is before the last sync and then I check to see if it doesn't exist in the other file. The problem is that for every record I'm running rather large select statement within the EXISTS(). The table SP stands for Sync Projects and Q stands for Questions, I'm using SP to get only the questions that are attached to the projects on the c…

LS, Speedwise. Do I get better performance doing a set field, with each it's own select, or should I get all data in one select and then parse the data with separate set fields. I know the last option has at least one scriptstep extra, but if one select with multiple fields is gotten way faster, that may outweigh the separate selects. Your thoughts please.

I have a question which might just come down to improper syntax. I'm giving my first attempt at using the ExecuteSQL command and I've gone through some of the primers (specifically the one by Beverly) but I am having trouble with what seems like a basic query. I'm attempting to find the primary key of my record in a table using two fields and their data. Here's what I got: Let ( [ x = 1; y = 2013; $query = " SELECT _pkLotteryRecordID FROM Lottery WHERE LotteryGrade = ? AND LotterYearOnly = ? " ; $result = ExecuteSQL ( "$query"; "" ; "" ; x ; y ) ]; $result )

This looks simple and correct to me yet in this case, FM will not return the null value for a value A6 on the left. Here is the example SQL SELECT "sach"."AchievementNumber", COUNT("searned"."_scout") FROM "sach" LEFT OUTER JOIN "searned" ON "sach"."_id" = "searned"."_achievement" WHERE "searned"."_scout" = ? and "isRegularAchivement" = 1 GROUP BY "sach"."AchievementNumber" ORDER BY "sach"."AchievementNumber" I should get 12 rows every time, yet this is all I get: A1,6 A2,8 A3,3 A4,7 A5,10 A7,4 A8,2 A9,5 A10,3 A11,4 A12,5 if I run this SELECT DISTINCT( ''||"AchievementNumber" ), "requiredAchievements" FROM "sach" WHERE "isR…

How come this works: But I can't seem to get these forms to work? [These two return: "Specified table cannot be found" and it highlights the pipe in quotes] [This returns: ? ] Been staring at these too long. - J

I'm trying to create a dynamic SELECT set up by way of the Let function where the SELECT values are the results of Let variables. The problem is that sometimes these values equal "all" or "empty". In these cases, I basically just want to ignore that value in the WHERE criteria. I tried setting it up using a Case function so that if the value equaled "all" or "empty" the result would be an "*" but that didn't return anything. Let([ $a = Case( FieldValue1 = "all" or FieldValue1 ="empty"; "*"; FieldValue1); $b = Case( FieldValue2 = "all" or FieldValue2 ="empty"; "*"; FieldValue2) ]; SELECT FieldA, FieldB FROM Table WHERE $a; $

In my People table I have these fields: First Name, Last Name, Anniversary, and ID In addition I have aDD and aMM and aYY which is 3 fields that split up the anniversary as we don't always know the year, but we at least have the month/day. From my interface table I have a global field that i use to key in "theMonth" such as 2 for February. My goal is to get the following list, day / name / ID 3 John & Jane Doe 102 3 Jack & Jill Smith 92 13 Terry & Dave Carlson 145 The tricky part here is each record is a individual person the only thing that tie them together is their ID and anniversary date (in this case the ID is the p…

I have an SQL query I am trying to run through the ExecuteSQL query. The problem I'm having (I think) is that because it's running off a table with over 5 million rows and I'm trying to grab a sum, not just the data, each query is taking anywhere from 30-60 seconds to run - and during this script, I have to run the query up to 36 times (monthly sums going back three years), so that causes this report to take too long in my opinion. I *think* I would be able to speed it up if I can do the heavy lifting (i.e. the sum) across a subset of the 5 million rows, and logic tells me that I should be able to do that by using a 2nd Select statement (select sum(my field) from (se…

I would like to order my results by name of month. If CHARINDEX was an option, this would be nice: ORDER BY CHARINDEX(LEFT(month_name, 3) , 'JAN', 'FEB', 'MAR', 'APR,' 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC') Here are my results for a google chart I am potting, I just need the proper order. ['Apr', 8], ['Aug', 7], ['Feb', 42], ['Jan', 30], ['Jul', 39], ['Jun', 26], ['Mar', 36], ['May', 31], ['Nov', 36], ['Oct', 17], ['Sep', 40]

Is it possible to select values from a variable list inside a SELECT statement using the IN clause? I've been trying to get to this work but I think the SELECT statement is just pulling off the first value in the list. I've been using the below syntax. Any thoughts? ExecuteSQL ( "SELECT Field FROM Table WHERE Field IN (?) "; "" ; ¶ ; $List )

Hello I use FM Pro 11.0v3 ADV So here is my query and use the baselements plugin. The following code is correct, but I do not get any results. I think the oli.ID_Product in the having clause is a problem. BE_FileMakerSQL ( " SELECT oli.ID_Product, sum(oli."Quantity ordered") FROM "ORD_Order_Line Items" oli WHERE oli.Kind='Material' AND oli.b_selected=1 AND oli.ID_Order='" & ORD_Order::ID & "' GROUP BY oli.ID_Product HAVING sum(oli."Quantity ordered")=(SELECT sum(ap2.Quantity) FROM Artikelposten ap2 WHERE ap2."Item Number" = oli.ID_Product) ") Now lets remove the having clause and ch…

Is there away to get a repeating fields value with a sql select statement. If so, what would proper structure of the select statement. I have tried the following. Let( ~sql = "SELECT "Tab Names" FROM PREFERENCES"; ExecuteSQL ( ~sql ; "" ; "" ) ) and Let( ~sql = "SELECT "Tab Names[17]" FROM T14_PREFERENCES"; ExecuteSQL ( ~sql ; "" ; "" ) ) Thanks, Todd Dignan.

A few months ago, I was introduced to SQL queries, and have been using them quite a bit recently (including the ExecuteSQL function. I think it's my new best friend!) However, as is often the case with a new tool in my repertoire, I'm suddenly seeing everything in the context of "Oooh, this would be *simple* to do as an SQL query!". Because of that, I'm finding myself on a regular basis wanting to import records from one Filemaker table (or database) into another using an SQL query, but I can't figure out how to do this. The ExecuteSQL function seems to be wonderful for pulling data into a field in a table, but I'm needing to actually import new records. I'm…

Is it possible to use SELECT TOP in an ExecuteSQL statement? I can find no mention or questions about this on the web. I want to find the most recent date in a range of dates and pass this to a script. SELECT TOP would enable this very easily.

I have a button set up where if I click it a checkmark is shown, while on the second click the checkmark disappears. The way I have this set up is with a container field (ButtonField) defined as an Auto-enter calculation set to: GetRepetition ( CheckmarkField ; 1 ). My second field is a global container field with two repetitions. The ButtonField is set up as button that sets its field according the following case function: Case ( ButtonField = GetRepetition ( CheckmarkField ; 1 );GetRepetition ( CheckmarkField; 2 ); GetRepetition (CheckmarkField ; 1 ) ) This all works fine. But I have a set up inside a portal where I have a list of portal records, each of …

I know how to create conditional value lists using sql based on a single field but I'm wondering if it's possible to draw upon alternative fields given a set of conditions set out in a Case function. I know how to do this when drawing upon values from a single field but I'm struggling with how to incorporate multiple fields, which live in different tables. Any ideas? In case that's convoluted, I'm using a single field in a portal and I want the value list to draw values from Field1, Table1 under one set of criteria set out in a Case function, while drawing upon Field2, Table2 under another set of criteria. Hope that's more clear. Thanks in advance!

Hey guys, I was wondering if anyone knew the answer to this problem. I'm trying to generate a value list based on a checkbox field in another table. I know how to achieve the value list portion of it, as this works when I change the checkbox field to a text field with a single value. But I can't seem to avoid getting the "?" when I use a checkbox field for the WHERE criteria. Any ideas? Let([ ~sql = " SELECT Field1 FROM Table1 WHERE CheckboxFieldValue = ?" ]; ExecuteSQL ( ~sql ; "" ; "¶" ; "Value" ) )

Hello there, I'm getting strange results using "distinct" in an agregate Count in the following query : SELECT s.Zkf_Formateur, li.Zkf_Groupe, COUNT(DISTINCT li.Zkf_Contact) FROM LISE__ AS li JOIN SEAN__ AS s ON li.Zkf_Seance=s.Zkp WHERE li.Zkf_Groupe='GROUCF63457729200OF90YL24' AND s.Zkf_Formateur='CONT63394136111XUYM50SA' GROUP BY s.Zkf_Formateur, li.Zkf_Groupe this returns, with a pipe separator and spaces added here for clarity : CONT63394136111XUYM50SA | GROUCF63457729200OF90YL24 | 11 This should return CONT63394136111XUYM50SA | GROUCF63457729200OF90YL24 | 12 as does the same request with just COUNT(li.Zkf_Contact). As a proof, here …

Hi All, I have posting this on Technet, but adding here in case someone can help me here who doesn't use TechNet. I have been playing with fql for a while, using plugins before FM12, but now have come up with something that, from my reading, may not be possible! before I spend (waste?) any more time can anyone tell me if the below is possible. In the table I am looking at there are 3 fields I am interested in, ImageID, imagesize and quantity. I want to summarise the data such that I get something like below. I want to use the results in a webviewer so cannot take into another table and process Image 1 10x8 3…

Thanks in advance, I'm an intermediate FM user and a beginner at SQL. Basically I've been struggling with using a SELECT statement with multiple WHERE clauses where one of the values is a $$global variable. I'm using a plug in (SQLrunner) but it doesn't seem to work when I just use FM native SQL function. When I use only one of the WHERE values below, it works fine. It also works fine if I give the WHERE values explicit numbers. But for some reason when I combine the two values, the function returns nothing. This is the same result whether I run it from the data viewer or via a script. I'm sure the answer is something simple but I can't seem to get my head around it. …

Hi, I have recently started using the ExecuteSQL functionality in Filemaker Pro. I can successfully run SQL in FM via an ODBC connection to an Oracle database. Please can anyone tell me how I can execute a stored procedure held in the Oracle database? Thanks in advance Rich.

Hi, I am seeking help on the proper way on using the GetAs function in FQL. Recently, I am checking the possibility to retrieve container field content and put in on web page via the JDBC connection to filemaker server. From my observation and testing carried out so far, the result is not too promising. It would be helpful if someone can give me a hand on this. Thanks, Oliver

The attached demo file, "SQL Sandbox" includes four custom functions that I demoed at POE NYC last month. Q - wraps an expression in single quotes -- I wrote this because I hate visually parsing single quotation marks Additionally, this CF "escapes" any internal single quotes or apostrophes by prepending another one... e.g., O'Malley becomes O''Malley (yes, this is a good thing if you don't want SQL code to break.) GFN - like GetFieldName(), but only returns the field name itself -- essential if you want to protect your code from the hazards of field renaming. Also, FQL has over 300 reserved words, including "date", "group" and "action". If you've used a reser…

I'm using sqlexplorer add in to write a sql function using the "LIKE" operators, as shown below. I'm trying to figure out a way to dynamically change the executeSQL parameters by using value list pop up menus the end user can select. So for example having 3 values on a pop up menu which are linked to COMBO1 whatever they select becomes COMBO1 and then when they hit 'run'. COMBO1=theirvalue in the ExecuteSQL command In microsoft access, to do this all you had to do was put COMBO1 in braces like this (COMBO1) how do we link value lists in FM 12 to executeSQL parameters? // ------------ BEGIN EXECUTESQL BLOCK ------------ Let ( [ ReturnS…

I am new to Filemaker 12 and eager to utilize ExecuteSQL queries as much as possible, but unsure of its strengths and limitations. Here is an example of a common scenerio in my database that I am hoping to streamline with its use: When I generate a report in my database that is filtered through a KEY calculation field, I am limited by the fact that the data is used to calculate the KEY field has to be indexed for the report to generate. In order for the KEY field to remain indexed, any data that is referenced in the KEY calculation has to also be stored. If the referenced data comes from another table or relationship, that data will not refresh on its own with…

Can anyone suggest any tips for improving the performance of FileMaker 12's ExecuteSQL function for queries using the LIKE operator? I was hoping to re-do a technique in FileMaker that included an off-screen find, but when I compare the performance of ExecuteSQL vs. performing a find in a new window, ExecuteSQL takes about 5 seconds and Perform Find takes about 0.1 seconds. Any thoughts? (Before anyone asks the most obvious question, the field being searched on is fully indexed.)

Hello, I'm relatively new to SQL but I'm pretty experienced in Filemaker. Because of the addition of the ExecuteSQL command, I'm judging whether or not to move a solution that I'm currently working on over to FM12 so that I can potentially simply the ERD but I'm having trouble even getting a relatively simple ExecuteSQL calculation to run without error. Basically, I have a table in my solution called ITAssignments that contains an text field called "id and a text field called "Who". I created a TO dedicated to the IT Assignments called SQL_ITAssignments Here's my query: ExecuteSQL ( " SELECT id FROM SQL_ITAssignments WHERE Who = James" ; "…

hi By accident we found a w ay to debug an sql statement. http://fmcustomfunctions.com/fid/335

Can someone help me figure out why this, apparently, simple ExecuteSQL statement doesn't work? The short description is that we are trying to count the occurrence of words. The table has some 4100+ records, each one (the "Text" field) simply a word from a document. I was trying to test if putting a numeric identifier for a word, instead of the text word itself, would be faster. (The text based 'Group By...' takes 25 seconds!!) So I have been trying to test whether different techniques will help (indexing, this numeric field thing, etc). Here's a sample of my data: ExecuteSQL ( "SELECT Text, GroupNum, Count (GroupNum) FROM Test GROUP BY GroupNum" …

Is it just me and my poor SQL skills, or has anybody else noticed that nested selects seem to be A LOT slower than a non-nested select of comparable complexity (FMPA12, OSX 10.6.8, native ExecuteSQL)?

I'd like to use ExecuteSQL to get a count on all records due today whose "Status" is not "Active". The following code returns the count on all records due today. How do I add an additional WHERE condition for Status != Active? ExecuteSQL( " SELECT COUNT ( ID ) FROM ExecuteSQL WHERE DueDate = ? " ; "" ; "" ; Get(CurrentDate) ) I know how to achieve this with "standard" SQL but am stumbling with this ExecuteSQL implementation. Thanks.

I'm starting to get my head around this new function so apologies if this is an obvious question. I have a large and quite complicated practice management system which has evolved over the years from 5.1; hence many files (sometimes by choice as well). Can the ExecuteSQL query be run from one file to another file (each file containing many tables)? Is the ExecuteSQL query limited to the tables in the current file? If it can, what is the syntax? If not I guess I'm going to have to script the command out of other files which would be a real shame. Thanks in advance for your comments/help.

I've just been playing around with the ExecuteSQL function and it is going to be really useful, I think. I'm still trying to figure out a good way to assign the results of a Select statement on multiple fields, into separate variables. For instance, I need to set the variables $leaseNumber and $leaseRoyalty, which both will contain multiple values. Right now I'm running separate SELECT statements for each variable. But the WHERE criteria are identical, and I know I can select multiple fields in a single statement. If the statement was returning a single row, I could figure that out, but since it is returning multiple values for each field, I'm not sure how to parse …

Love the DISTINCT function of the SQL but trying to figure out how to get the correct set of records. In a normalized file member ----< email there could be one or more duplicate emails in that table. suppose I am already on the FOUND set in Members, i want a unique list of email addresses - or rather its ID's - i want to go to the related email table keeping the first instance of an email and omitting subsequent duplicates. I need to end up on the email table so it can export the desired unique found set, that relate to the FOUND set in members. Savvy? Thanks Stephen

Ok this code is not correct. I am looking into my history table and wish to find all partyUIDs of people who haven't renewed yet, for a given chapter / season. Note: Season year is just a 4 digit year. I think i need a join? Let ( [ $a = system::SEASONYEAR ; $b = $a -1 ; $c = system::CHAPTER ]; ExecuteSQL ( "SELECT partyUID FROM history WHERE seasonYear = ? and Chapter = ? UNION SELECT partyUID from history WHERE seasonYear <> ? and Chapter = ? " ; "" ; "" ; $b; $c ; $a ; $c )

Hi Iam trying to conecte trought Filemaker ODBC in my mac to a Sql Database with the "Actual drivers" but i need a sample sql database for testing with a few tables and just a few records anybody has one for testing would be a great help. Thanks Walter

I wasn't sure if to start a new topic or to mention this here. So forgive me if this is not the place. I am not that familiar with MySQL but, I was playing around with the ExecuteSQL () Function in order to find all records that matched a certain criteria. I came up with this: ExecuteSQL ( " SELECT fullName FROM people WHERE fullName LIKE '%" & GLOBAL::SEARCH & "%' " ; " ; " ; ¶) This would return the names of people that match the text entered in a global field called Search. If the value in the search field was "Alex" it would return Alexis, Alexander. However if the value was "alex" (the letter A in lowercase) it would not return Alex…

For those that have field names that start with and underscore such as some do when identifying key fields, the new ExecuteSQL function does not play nice with them directly. Normal escapes with a prime or quotes do not work. You must use the Quote function. ExecuteSQL ( "SELECT " & Quote ( "_kp_ID" ) & " FROM table"; "," ; ¶ )

I am excited about this function, but I believe I have found a limitation. Can anyone confirm or expand. Three tables: artist: 1200 records artist_title (join) 7000 records title: 7000 records Setup: 1. FMPclient (local) --> ODBC (local) --> MySql (cloud) 2. FMPclient (all table in a single local file) Calcs: 1. List ( artist_title::id_title ) 2. ExecuteSQL ( "SELECT id_title FROM artist_title AT WHERE AT.id_artist = ?" ; "" ; ""; artist::id ) Results: calc 1 on either setup is snappy. calc 2, setup 2: snappy. calc 2, setup 1: extremely slow: 1.5 minutes I indexed the two key fields in the join table in setup1, to …

Has anyone run SQL queries against ESS sources with success? I have no issues on non-ESS tables but my finds on ESS tables take min. 30 mins or come back with a 101 error. These are simple finds/queries. Both my non-ESS and ESS tables have about 1 million records each, both have the same fields (I'm comparing 2010 data vs. live data). Thanks in advance.

Does anybody know the what the maximum length of a SQL statement in FQL is? Is there indeed a specific limit, or does it depend on system resources?

I'm hoping someone may have some insight into this. I'm putting together a complex app and I use SQL heavily using the Dracoventions SQL Runner plugin. Every now and then I notice that an SQL statement pauses for a time and then displays a message stating that there is a record locked (usually when I have multiple windows open). Does anyone know the rules regarding SQL operation with record locking? Specifically, if I'm just retrieveing data through a SELECT statement and one of the records in the target set is in use by another user, will that trigger the SQL pause? I can understand it if I'm doing an UPDATE or DELETE but would the SELECT still pull the data from a …

I'm using the BaseElements Plug-in to experiment with managing a simple Filemaker table named Nested_Category consisting of four fields: category_ID: number, autoenter serial name: text rgt: number lft: number result: text I am executing SQL commands in two steps like this: set variable[$mySQL; Value: "BE_FileMakerSQL (" & Quote("SELECT rgt FROM Nested_Category WHERE name = 'Televisions'") & ")"] set field[Nested_Category::result; Evaluate($mySQL)] This returns a value of 2 into Nested::Category::result, which is correct. To INSERT a new record into the table I use the following calculation to set $mySQL ($rg…

Hello Knowing a realtionship is NOT case sensiive, I am trying to create and equivalent in SQL. But SQL is case sensitive. What I came up with is the first sql statement. But this statement is really slow, compared to the second statement, which doesn't give correct result, because of case differences. Does anyone have a clue on how to create a good equivalent, without modifying records?? 1. 2.

I have only been working on filemaker and then access databases for a few years and know little about querying. I hope this is right place to post this. Sorry if it is not. What I am finding is that I can't get my data to export the way I want under the relationship structure of FM. I feel like what I am doing is fairly simple but I haven't been able to figure it out and am thinking of just moving it to a flat file structure since that is how my boss originally had it set up. All I want to do is pull out data so it is all in one row so we can run statistics on it. For example, I have two tables Patients and Labs. For each patient there are multiple labs done o…

okay been banging my head on this for a while. there are a dozen fields in TableA that I want to update in to TableB (name of fields are the same) when the id's in both table match. I am trying to use BaseElements plugin to do the ExecuteSQL

Hello, I'm experimenting with the Sql-Runner-Plugin to manage the structure of FileMaker databases via SQL. With an data separation model I'd like to change field names from the layout file via script. I successfully created/ dropped fields and set new default values etc. But what I need most is to change field names (column names) via SQL. I tried ALTER TABLE "tableName" MODIFY "oldFieldName" "newFieldName" varchar ALTER TABLE "tableName" CHANGE "oldFieldName" "newFieldName" varchar ALTER TABLE "tableName" RENAME "oldFieldName" TO "newFieldName" All I get is an "There is an error in the syntax"-error. The ODBC-manual only lists create, drop, drop ind…

There were two well-attended "Internal SQL" sessions at the recent PauseOnError conference in NYC. Clearly there is interest in the developer community. The "problem", as I see it, is that there is very little information available, and various developers have been evolving their own best practices in isolation. I'm hopeful that this forum can help remedy that situation. Suggested reading: FileMaker 11 OBDC and JDBC Guide (chapter 7: "Supported Standards")

Hi Everyone - At the generous invitation of Kevin Frank, I have been asked to share my wisdom (really, it's just my experiences) with FileMaker and Internal SQL Calls. I was fortunate to have had the opportunity to present on this topic at the most recent meeting of FMDiSC (FileMaker Developers in Southern California) on March 11th. The preparation for this presentation was, in brief, quite an adventure. In preparing the presentation I toyed with the idea of testing all the available plug-ins, but settled on ScriptMaster Advanced (360Works) and DoSQL (SH Partners / MyFMButler). After the meeting (i.e., today) I spent some time experimenting with the MMQuer…

I know of three... 1. MyFMButler (doSQL) 2. CNS (MMQuery) 3. Dracoventions (SQL Runner) Any others?

Hello all, I'm interested to know if anyone who's used MMQuery uses the convenience/helper functions we've added to the plug-in or if you just use the ExecuteSQL(Ex) functions. For instance, I personally use the AddRecord function a lot. Also, are there other convenience functions that you would find useful if added? Thanks, Jake