FileMaker Query Language or FQL

strange issue ... I am running an executeSQL to get a value and store that into a variable, then when the user goes to another record and runs the same executeSQL to get the new value it does not updating correctly. Have tried ... adding a goto field, refresh window, commit record, goto another layout and come back and nothing seems to work. more detail on the issue below ... we have a INVOICE NUMBER field that gets a value that is incrementally higher than the last entry. It is technically not a "serial number" but rather a text field (since the value has text and numbers in it) that users' can alter, so don't confuse it with a primary key. Basically, when the u…

I am working on my filemaker EMR and part of it is medication reconciliation. there is a nice database of meds offered by FDA that is public docs and it allows to mimic the behavior of their site [email protected] locally inside my solution. I downloaded the data files and imported them inside my tables. I linked the tables together like their ERD. however what they do not offer is the scripts. Is it possible to figure out the scripts from the behavior of the site and the ERD with the table definitions that is likely SQL so i can have that in filemaker 11 calculations? The ERD location is: http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm The …

Hi, Everyone, Is there any way to use FileMaker's Internal SQL on a solution, where the names of the fields contain spaces? Statements like: SELECT Last Name from People WHERE Team Role = 'Judge' or even SELECT zzID from People WHERE Last Name = 'Tan' fails spectacularly - I think because the SELECT clause expects a comma delimited list of field names. Here, the field names in my solution use spaces (for legibility - and FM does not complain when I do that). I have tried SELECT 'Last Name' but that only gives the literal in every row. The FM ODBC/JDBC Guide is not much help. All it says on page 47 (Field Names) is "The…

Hey gang, Trying to figure something for a fellow lister. I am tring to run a query in FileMaker using ExecuteSQL() that works perfectly when I run a similar query in a normal SQL editor: SELECT p.pk_ProductID, p.Description, SUM(i.Quantity) AS sQTY FROM invoice_invoicelines_Product p JOIN invoice_InvoiceLines i ON p.pk_ProductID = i.fk_ProductID AND i.fk_InvoiceID IN (1,2,3) WHERE p.pk_ProductID IN (1,2,3) GROUP BY p.pk_ProductID It produces a "?" (which is very informative). I guess need to know if this is too much for FileMaker's SQL engine or if FileMaker is going to force me to use something that is not tradition SQL protocol/syntax. Any feedback …

I've been reading up on FM joins via execute SQL I want to be able to roll back and forth through changes in my 2nd table, and only record the fields listed in table 2. I believe I can do that with an Outer join between Table A and Table B. What is the proper ExecuteSQL command for this ? This script will Trigger when ever a commited record on Table A is performed. ( i think this is the right way to have it work in webdirect, if not, please tell me which is the correct method) Also, I want to Highlight the change between last record, This part I can figure out, i believe using a change since last record. Ultimately, there will be a layout where the Current Version …

Quite simply can anyone point out what is wrong with this? Let ([ ~begin = Reports » Selector::gDateBegin ; ~end = Reports » Selector::gDateEnd ; ~type = Reports » Selector::gPaymentType ]; ExecuteSQL ( " SELECT SUM ( n.commission ) FROM Commission n INNER JOIN Payments p ON p._pk_payment_id = n._fk_payment WHERE p.date >= ? AND p.date <= ? AND n.commissionType = ? " ;"";""; ~begin ; ~end ; "Sales" ) ) Just trying to pull the total Sales commissions for a date range. Thanks Never mind. I got it. Just had to do some quoting... Let ([ ~begin = Reports » Selector::gDateBegin ; ~end = Reports » Selector::gDateEnd ; ~type = Reports » Selector::gPaymen…

I'm in the middle of upgrading from FM11 to FM13/14 and found sets of data will not import to mySQL correctly. Instead of the found set importing, the whole table gets imported. I get the same result on FMPA 13 and FMPA 14 32 bit. FMPA 14 64 bit won't even import at all. This process has been working for years in v11 and older versions. Server: mac 13.0.10.1004 Client: Windows 7 mySQL: 5.6, ODBC driver: 5.2 and both the 32 bit and 64 bit drivers connect to mySQL successfully. Anybody else have this issue? Thanks, John

Quick question....Is there a way to include a time stamp field in the WHERE clause, but check only the date and not the entire timestamp. In other words, ExecuteSQL("SELECT field1 from table1 WHERE timestamp_Created = ?"; ""; ""; Get(CurrentDate) ) I could do this with greater than or less than conditions in the WHERE clause. I'm just wondering if something like the sample query above can be done instead of the greater than or less than conditions.

I've spent the morning on this SQL calc for a chart, where GLOBALS::DATE_START = 9/1/2015 GLOBALS::DATE_END = 10/1/2015 Calc for chart data: Let ( [~sql = " SELECT ~party_to , SUM (~~amount), SUM (payment_application.amount_applied_invoices) FROM ~payment LEFT OUTER JOIN payment_application ON payment.id=payment_application.id_payment WHERE id_payment_purpose =? AND ~date_effective BETWEEN ? AND ? GROUP BY ~party_to"; $sqlQuery = Substitute ( ~sql; // ["~id_payment_purpose" ; SQLGetTableName ( payment::id_payment_purpose )]; ["~party_to" ; SQLGetFieldName ( payment::party_to ) ]; ["~~amount" ; SQLGetFieldName ( payme…

I think I am missing a fundamental piece.. If I run the following: Execute SQL [ ODBC Data Source: MySqlDatabase; Calculated SQL Text: "SELECT * FROM Client"] And it works, where is the data/object returned? I am trying to query an external MySql database and check to see if a value exists in a table, and if it does to reurn the uid of that record. But, can seem to get a very simple statment like the above to work?

I'm trying to sum a set of related records that only contain a specific value: ExecuteSQL ( "SELECT SUM (Subtotal) FROM LineItems WHERE id = InvoiceID AND sFlag = 1" ; "" ; "" ) but I'm getting a null result. The Subtotal field is a calculated field. Ideas?

Hello friends This is my first post, and I've seen that in this forum there is content and people with true knowledge. I will try to post my questions and if you are doing something wrong, or posting on the wrong page, peopl anticipated excuses. But let's go I have a question execSQL and wonder if I can help. As shown below, they have a ratio of three files: Plan Contas_Lançamento: file with chart of accounts Description: This would be the main information that is necessary to make the sum of what is credit and some also what is debt Released: These are the account details Press Values: Where are the recorded values. My problem is that I try to create separate total…

Hi, I have the a calculation field running the following: ExecuteSQL ("SELECT MIN(checkins.date_due) FROM checkins WHERE checkins.id_contact=? AND checkins.isComplete=0"; ""; ""; id) If I tell the field that the calculation result is a number, then I appear to get the correct date back in the following format: YYYYMMDD, otherwise I get nothing or a ?. However using this result in a comparison with say get(CurrentDate) does not work. The strange thing is, that if I set the data formatting in the inspector to be 'general' i get YYYY-MM-DD, so it must know its a date somehow? Any help about how to get fop to recognize the sql result as a fmp date would be greatly appr…

HI, I've got the following piece of code which pulls some information from a table to allow me to manipulate. The last two fields Summary and Actions can be large pieces of text. What I've found is, if a staff member puts carriage returns in the middle of the text to format it into paragraphs, I lose the rest of the data within that field and subsequent fields that follow. Is there anyway to avoid this from happening as Senior Leadership team will want to be able to see the data formatted nicely into paragraphs. TIA ExecuteSQL( "SELECT LLSDate, DeptID, YearGroup, LLType, Rationale, Summary, Actions FROM LearningLenseSummary WHERE LLSDate >=? AND LLSDate <=? A…

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 ( $query ; "" ; "" ; "English"; "Active") ]; $result ) Each Lesson_Observation r…

I'm having a bit of an issue with an ESQL query. I would like it to always return 2 decimal places on the value retrieved. This is a currency field, and "$45.5" looks weird. I am generating a list of payments to displayed (via a global variable in the UI) to the user, so it should look money-like. It's a simple 'here's what you currently have set up' kind of display. So here's the basic query (I believe folks here are familiar with the GFN() and GTN() functions - they are just a way to robustify the query in FileMaker): ExecuteSQL ( "SELECT '$' || " & GFN ( Payments::Amount ) & " FROM " & GTN ( Payments::aaPaymentUUID ) & " WHERE " & …

I am using Import script step to move data into Oracle database using ODBC connection. It is working if there is data/text in the field but it does not push blanks into fields in Oracle db. Any suggestions?

Why I am receiving this error "invalid character" when I am trying to update some records using this statement in FileMaker (it is working on oracle side but when I run it through FM ): ExecuteSQL(OC;DB1;"Update cost.ct_e Set Seg=N'"& $$Seg &"' Where Cn=N'" & $$CNo &"' ;") The variables are text: $$Seg=test1 and $$CNo=test1.test2. OC is the name of the ODBC connection and DB1 is the database.

I am using this FQL to generate data for a chart. The chart works fine when I have just the GROUP BY clause, but as soon as I add the SORT BY clause, it gives me an error "All non-aggregated column references in the SELECT list and HAVING clause must be in the GROUP BY clause." Does FQL not let you use both clauses at the same time? Let ( [ ~sql = " SELECT w.~month, SUM(w.~smh) FROM ~workorders w WHERE w.~resort = 1 AND w.~date = 1 GROUP BY w.~month /* ORDER BY w.~created */ <------ Stops working if I remove the comments. "; $sqlQuery = Substitute ( ~sql ; [ "~workorders" ; SQLTableName ( WORKORDERS::WorkOrde…

Hi all I'm pulling my hair out with this one, I've tried everything but to no avail, very frustrating as I know it's a simple one. I just want to list the postcode fields from the addresses table, where the log_idf of the mileages table is equal to the current record of the Log_Log table. See pic for the relationship I'm trying to recreate. This is what I have so far: ExecuteSQL ( " SELECT S.Postcode FROM Addresses S JOIN Mileages T ON T.Address_IDF_From = S.Address_ID WHERE T.Log_IDF = ? "; ""; ""; Log_Log::Log_ID) TIA Ah sorted now. The field types in the JOIN were not of the same type. BTW, should ID's be set as numbers always? I've always done them as te…

I saw it was added new in 13 but I do not remember its name so I can not find it to read up on how to do it. It was something new added to SQL so we could ask for only the top x records. What is it called? Thank you for reading.

Hi there I HOPE this is in the right place... I'm still trying to wrap my head around the FM SQL syntax and to a certain extent have had success. I'm preparing some chart information and need to show a margin percentage over a period of time. This would be calculated as (Income-Cost)/Income/*100. The ExecuteSQL statements at the end of the code should give me 5 percentages to compare over a period of time. My income and cost records are defined clearly in the data table each one having a text tag of either INC or COST, so DataTable::Amount records are either INC or COST. In turn, these are split into two other columns so I can summarise Income and Cost elsewhere in…

Hi all I could really do with a push in the right direction here, being still very new to SQL (i'm not sure if the title is accurate). I have a Labour table, which has related data in an items table. Each item is also related to a line in the stock table. So it's a one to many for Labour==Items and one to many for Stock==Items I would like an ExecuteSQL calculation which will tell me if all the Items related to a Labour record meet the criteria of their related record in the stock table having the required quantity. The required quantity is defined as being equal to or above the quantity of the Items record. If the stock record's 'unlimited' field is set to 1, then i…

Hi guys, can anyone tell me what is wrong with my statement? I'm not getting any results and the variables are not empty. ExecuteSQL ("UPDATE CustomerDB SET CustComments = ? WHERE CustomerID = ?"; ""; ""; $CustEmailBody; $CustomerID)

Is the following query possible? If yes, what is missing from my format. If not, what are my alternatives to a achieve the same goal? ExecuteSQL ("SELECT Name FROM Customerdb WHERE ROW = 1"; ""; ""; "") or ExecuteSQL ("SELECT Name FROM Cutomerdb WHERE RECORD = 1"; ""; ""; "") Thanks in advance.

Hi, I am in the process of building a databse in access and I've been told that filemaker would be much better solution. I am fairly used to SQL server 2012 so one of my main concerns is being able to write SQL to manipulate my data and build the relationships and keys etc. Is this all done in a GUI in filemaker? I know it can be connected to SQL server or oracle but (forgive my ignorance of filemaker) why would I need filemaker, other than to build something at the front end. If I could just make selections without a full functionaly in filemaker I think this would be ok for this project. If anyone has any comments that would be great! Thanks

I thought I should post this. It seems GROUP BY in FMPs ExecuteSQL function requires all the SELECT fields to work properly where that is not the case with mySql. ca-500.zip

Hi everybody I'm putting a "notes" field on my invoice screen that shows all notes entered that's related to the open invoice in one scrollable text field (the notes are created and modified in another window and in this one instance I would like to avoid a portal). It's 90% working, however it is displaying notes that are related to every Invoice in the system, not just the active invoice record: Let ( [ ReturnSub = "\n" ; header = ""; header = ""; aNotes = Quote ( GetValue ( Substitute ( GetFieldName ( Notes::id_Invoice ) ; "::" ; ¶ ) ; 1 ) ) & " a" ; bInvoices = Quote ( GetValue ( Substitute ( GetFieldName ( Invoices::id ) ; "::" ; ¶ ) ; 1 ) ) & " b"…

So trying to use ExecuteSQL but needing a way to debug the query. I tried using the function sql.debug but I obviously am doing that wrong since I don't get any response. Looked for additional documentation on that function but no luck. How do you all debug sql statement? Thanks Thom

HI, I'm attempting to join two tables in my Select statement for my executeSql function. All I'm getting back is '?' although if I select from each table seperately I get restults I know the select statement is correct as I've tested it seperately (outside of filemaker) with a sample of the data. Is FM capable of working through semi complicated SQL queries? The help seems to suggest it can deal with joins! This is my script and as you'll see it's not very complicated at all. ExecuteSQL( "SELECT b.TermDesc, a.ObsDate, a.Grade FROM TutorObservations a INNER JOIN SchoolYear b on (a.ObsDate ≥ b.StartDate) and (a.ObsDate ≤ b.EndDate) "; "|"; "¶" ) TIA

Hi. I know how to read unique records from a table with the "Select Count(Distinct)" function, but not sure how (if at all possible using this same function) to further narrow the selection. I just can't seem to get the syntax of the WHERE right. I'm not using spaces in my table of field names (never do), and the criteria I select (e.g. gender = "Male" doesn't seem to work) For example, I am reading the unique list of clients attending a hotel. 500 clients in total, and 450 are unique. How do I then break that down further to say how many unique male (or female) clients visited the hotel between date_A and date_B (given that there is a date field recording their visit a…

Hello. I have inherited a FileMaker 12 in-house application. I am an Oracle and MSSQL DBA, so SQL is not foreign to me. The FM implementation of SQL is a little odd, but totally usable. One of the things the in-house app has is a Quick Find field that is able to seach nearly instantly for a substring within the database. I would like to mimic this from the SQL side for some web presentation of data, but all of my attempts are taking 20-30 seconds. The search is against a text field that has space delimited data of serial numbers. The query is to find all records that contain the substring. SELECT id FROM SerialNumber_Record where serials like '%SN750001%' Wildcard % i…

Hi, I'm querying some results and would like to title the column (first row). I wrote my sql statement as follows but I'm only receiving the results without the column header: ExecuteSQL("SELECT CustID AS ID , SerialLS AS Serial_Number, DateNTime AS Date, Operator AS Name WHERE SerialLS = ?"; " "; ""; Serial_LS) Am I doing something wrong? What I was hoping to get is: ID Serial_Number Date Name 123 J-804-KL 4-22-2014 Joe Doe 654 M-465-NO 2-01-2015 Marry Jane Instead what I get is: 123 J-804-KL 4-22-2014 Joe Doe 654 M-465-NO …

Is there a way for ExecuteSQL to only search provided arguments instead of searching for empty arguments? ExecuteSQL ( "SELECT id_contact FROM contacts WHERE contactFirstName = ? AND contactLastName = ? and contactDOB = ? and contactSSN = ? ; "" ; "" ; searchNameFirst ; searchNameLast ; searchDOB ; searchSSN ) I'm trying to write an ExecuteSQL statement where a user can provide a first name, last name, date of birth, and social security number that returns a list of contact ID's that match the provided arguments. However sometimes DOB and SSN will not be available. How would I write an ExecuteSQL statement that omits empty search criteria instead of searching for an …

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 for helping!

Hi, I'm trying to sort my FQL by date and then time ExecuteSQL("SELECT DISTINCT OrderID FROM Tests ORDER BY ODate, OTime DESC"; ""; ""; "") but for some reason it is not working. It gives me the list of OrderID's but not the Descending order that I asked for. Instead, it gives it to me as if I had placed ... ORDER BY ODate ASC. It sorts correctly if I have ODate DESC by itself but... I also need to have it sort it by time. Other information: It's a Specify Calculation Filemaker Pro Advance 13 Windows 8 Thanks in advance

Here is what I need. I have global field called gColorized. How would I write this, please? Select ProductNumber from Products where ProductDescription contains value in Globals::gColorized or where Refactor contains value in gColorized Everything I've tried, I only get ? Thank you for helping. SQL does not like me yet.

I inherited a FM application and it was working fine. Now when I open it, a form is displayed and initially 30 records appear and then immediately the records count drops to 7 before any action is taken. The data is in mySQL. If I watch a trace on the database I see an initial query "select * from invoice", and then quickly a second query "select * from invoice where year = 2012" which limits the results. I opened a FM support ticket and was instructed to uninstall and reinstall FMP. I've done this multiple times. I also attempted to delete all FMP files in "Library" with input from support. First Uninstall FileMaker then go to the user Library: Finder>…

Hi there, I'm trying to get a simple "SUM" calculation working using executeSQL (I need to expand on it's complexity later) I have 2 Tables: ProductSalesReportLines Vend_Sales_Lines_API In the ProductSalesReportLines I have 4 fields - 3 with existing Values and one that I'm trying to calculate (Sales) based on on data in another table. _kf__Product__UUID - Product Key (existing) StartDate - Start Date (Global field - populated) EndDate - End date (Global field - Populated) Sales - Number of sales to be calculated from Vend_Sales_Lines_API between start and end date above Vend_Sales_Lines_API has 3 values _kf__Product__UUID Date Quantity …

I am trying to get a list of UIDs from a select for Distinct CatalogNumber WHERE Tech = TechName UID CatalogNumber Tech Just can't seem to get the syntax right.

Cannot get to the bottom of this one... This ExecuteSQL query works fine (all escape characters etc removed for clarity) SELECT a."Month Name", SUM(a."Total") FROM "Orders" a WHERE a."Year" = ? GROUP BY a."Month Name" I have a Month Number field also... Month Number = Month (date) But adding that into the query then kills it completely (returns "?") SELECT a."Month Number", a."Month Name", SUM(a."Total") FROM "Orders" a WHERE a."Year" = ? GROUP BY a."Month Name" I have checked, seems to be appropriate data in all the fields. Any clues? Driving me batty...

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. ExecuteSQL (" Select max(FTE) from Values where Type =?";"";"";GlobalFields::GF_Types …

How can I create a SQL query that grabs all field names in all tables (without being required to put the fields on a layout)? I would like each row to have the following information: table name | field name | field type | field comments I found this custom function but it only returns the field names from a single table: ExecuteSQL ( "SELECT FieldName FROM FileMaker_Fields WHERE TableName='" & TableOccurrenceName & "' ORDER BY FieldName" ; "" ; "" )

Hello, When I run this query: ExecuteSQL ( "select * from Emplacement where NoTerrain = '3' "; ""; ""; "" ) It works. The recording are displayed. However, when I run this query: ExecuteSQL ( "delete from Emplacement where NoTerrain = '3' "; ""; ""; "" ) Error... I get ? as a result, and the query does not work. Could someone explain me the problem ? Thank you for your time.

Hi. I'm having trouble adding ORDER BY to an already working SQL statement. This statement works (the global variables are grabbed from two other looping scripts): SELECT People.id FROM People LEFT JOIN Companies ON People.id_Company = Companies.id WHERE " & $$peopleWHERE & " UNION SELECT id_People FROM DevPlayed LEFT JOIN DevInstruments ON DevPlayed.id_instruments = DevInstruments.id WHERE " & $$instrumentWHERE ; Adding the ORDER BY causes it to fail: SELECT People.id FROM People LEFT JOIN Companies ON People.id_Company = Companies.id WHERE " & $$peopleWHERE &a…

Hey Guys, I have a list of UUIDS all separated via a carriage return. I'm using a where IN clause in my execute SQL and understand that if I want to put my values in the IN then I need to remove the carriage returns and surround each value with a quote mark. Does anyone already have a standard way of doing this or a custom function already available they can point me towards. Thanks Jalz

Okay, I'm close; it involves two fields, CompanyName__lxt and NoteImportant__lxt. What I want to do is count the number of records in a table (JOB) where the calc looks at the current company name in the CompanyName__lxt field and counts how many records with that same company name also have a value in NoteImportant__lxt. Example: Blivet, Inc., may have a total of eight records in JOB but only four of them have a value in NoteImportant__lxt, so the calc should return a count of 4. Currently, I have this calc but it counts all the records in JOB that have a value in NoteImportant__lxt; how do I tweak it so it accomplishes the above? I've added AND arguments bu…

For the first time ever I'm working with ExecuteSQL and I can sure appreciate how--moving forward--it needs to be at the heart of a lot of my projects. But I'm still having challenges with exact syntax. I am trying to do a simple summary count...no subcategories...just a grand total. I can successfully generate a list, but don't want the list of items--just the count. So how do I transform my working syntax to generate a simple count? I've been dismayed that Google can't help me pull up any examples of this. Here is what works for the list: ExecuteSQL ( " SELECT (SCalendarDate) FROM CCALENDAR WHERE Category ='Scheduled Holiday' AND ComingPast='Co…

Please delete this post test0.zip

Howdy all, I could ask this in Stack Overflow or similar but it seems FM SQL has certain limitations when creating more complex requests (joins, group by, etc). I have a table of sales figures (location_id, date, amount). Assuming there's only one location, and I want to graph the sales over time... x-axis: ExecuteSQL("SELECT date FROM sales_figures ORDER by date ASC") - I'd get a list of all the dates y-axis: ExecuteSQL("SELECT amount FROM sales_figures ORDER by date ASC") - I'd get a list of all the amounts The problem is multiple locations are in the list and I want the graph to show sales data for one or more locations that I select. For the …

Hi. Some years ago I used FM7 to control a large 'one table' database, but now having acquired FM13, would like to do far more. However considering what I would like to do, videos on Youtube appear suggest that knowing the language behind FM would be very advantageous. What language lends itself to FM... JS, PHP or MYSQL? A few years ago I learned HTML5 and CSS3 to build static webpages, but weighing-up the pluses and minuses of learning the more comprehensive languages of MYSQL and PHP, decided not to learn either as I would rarely use them... but now I am once again using FM, am wondering if it might be beneficial to learn at least one of these languages, but w…

I have a table of contacts, here are the essential fields: id, acct, first, last, mobile, phone a few other fields are flag fields that I would use in the WHERE clause, isActive = 1, isDeceased ≠ 1, phone ≠ null, mobile ≠ null, the desired result is: id, acct, first, last, phone I would like to also only include anything that has a phone number as some records have a phone some have a mobile number others have both, in addition two people (same account) may also have a phone number that is duplicated between records. My end result is a unique list of phone numbers. If two people have the same phone number then the first person with that phone numbe…

Due to areas outside of my control, I have a need to dynamically control the otherwise static arguments required for the ExecuteSQL function to work. To make a long story short, I am modifying an installation SeedCode ProMaps so that I can deal with a data type mismatch. During the course of my troubleshooting, I found that ExecuteSQL was returning a data type mismatch when I was attempting to search on the ID field of a table. The problem that I've encountered is that while I use numerical ID fields, ProMaps assumes the use of text ID fields. I used SeedCode's SQLExplorer to craft a query that works when using the arguments function of ExecuteSQL, but now I've hit …

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 feeding it? Or to put it another way - if I have fields in a portal that are calculate…

I have an inventory table in my database with approximately 180,000 records. I am trying to show the sellthrough rate for each product from a specific date until now. To do that, I need to see the current stock for each product, and its stock at the start of the date range. The problem is that start date for each product within that range is variable since certain products arrive in stock at different times. I can get the earliest date for each product within the range by using the MIN statement, but the challenge is to show what the SUM for each product is on that MIN date. In my example below, I attempted to capture the MIN date in a subquery, but doing so causes…

Two tables Journalists ------ RecordID = JournalistID ------ Affiliations (very simple) 1 Value List called "ListAffiliationsForJournalist", based on Journalists, Affiliations::Name 1 Unstored calculation field on Journalists, ValueListItems ( Get (FileName) ; "ListAffiliationsForJournalist" ) All of this works - if a journalist record has two affiliations records, the unstored calculation field lists the names of both affiliations. HOWEVER: I'm worried about performance. Is there a way to replace this unstored calculation with an Execute SQL function? I just spend several hours reading up on SQL and FileMaker (I'm really new with this), and I…

Hello, I am currently running a report based on a certain table in my DB that has an SQL function pulling data from another table. The problem is the report will not function properly with a portal since i would have to do some complicated relationship maps. So I decided to use SQL to pull data from the other table and it works great. Its pulling the correct data. My issue is in how it displays the data. The data that is pulled includes the fields Date Day Day of the week and Description Those would be the header titles and I want the data to be displayed in a table format. I cannot find how to do that and I noticed a program called SQL explorer that…

I have a Computer table and a Service table. They are related on field pk_ComputerId and fk_ComputerId respectively. I am trying to find all the computers that do not have a service call. I have been able to find the computers that do no problem as follows: ExecuteSQL ( "SELECT pk_ComputerId FROM Computer WHERE pk_ComputerId = ANY(SELECT fk_ComputerId FROM Service)" ; "" ; "" ) -or- ExecuteSQL ( "SELECT pk_ComputerId FROM Computer c JOIN Service s ON c.pk_ComputerId = s.fk_ComputerId)" ; "" ; "" ) I must be missing something in my logic because I would think you could replace the = with a <> in either of the statements above and get a list of computers …

Hi, Some wisdom please ... I have a PO table and I have an Estimate table. In my Estimate table I have a field that I want to list all the PO's that have been assigned to an Estimate. I want to use a "SQL Select" only because is list horizontally, however the calculation will not update whenever a new PO has been added ?? It only updates when I physically navigate into the SQL calculation then back out to the layout. Then the SQL updates. I've try "Refresh" as well as clearing the cache check box that everybody says to avoid … but still the SQL only update after I navigate out of the SQL calculation. So I tried the function "LIST" and this works …

Hi I'm New usig SQL in FM I succesfully made a query that retrieve grouped Data from a large database. for the last 12 months I summarized information of two field. I used the following SQL calculation ExecuteSQL( "SELECT TimePeriod , SUM (Total_Sales) AS totalsales , TimePeriod , SUM (Total_Margin) AS totalmargin FROM bbdd WHERE bbdd.id_TimePeriod >= ? AND bbdd.Branch = ? GROUP by TimePeriod"; ""; ¶; report::id_last_12M; report::branch) the field display the data in this way for every branch TimePeriod Total_Sales TimePeriod Margin 2013M09 45795 2013M09 199 2013M10 53987 2013…

Hey all, I have a table called ClockIn, which stores various codes(letters) for employees in a couple of text fields AM and PM. Within that table, I also store the EmployeeID and the Date. What I would like to do is extract the AM, PM session for 5 days in a row for a given employee. Infact I have a list of employees I want to plot in a field, but I would settle for one as Im sure you just loop through employeeID's until you get to the bottom. I have a custom function which works out the date for $Monday , $Tuesday, $Wednesday... $Saturday . I know need help in constructing he sql query. I can do basic sql, but get my head messed up once I need to start to use…

I do this ExecuteSQL( " SELECT " & GFN ( Preferences::FiscalDate) & "FROM " & GTN ( Preferences::FiscalDate) ; ""; "" ) The FiscalDate field now is date field with 9/27/2014. But what comes out when I set a global variable with this calculation is 2014-09-27. Strange. I want 9/27/2014. I think making it a variable makes it text and messes it up but I do not know how to change it. I tried GetAsDate() but it did not work. I do not know how to change the calculation. I read variables are text only so I tried that but no luck either. How to fix it please?

Well, that title may not fit exactly but here's the issue: An FM file referenced as an external data source will not open on its own (in the parent file) until it is first referenced on a layout of the parent. This reference could act from ANY calculation such as: conditional formatting, tooltip, hide, merge variables, custom menu calcs ... there are MANY ways calculations can exist on a layout. So any calculation which references another file from the current layout will open the second file. But I did not expect this to open a second file ... For a changelog process, I am capturing schema at startup and then at close, comparing it to produce a list of only…

I am using a slightly modified version Mr_Vodka's SQL sorting method to sort a portal based on a field in the table: ExecuteSQL ( "SELECT id FROM " & $$portal & " ORDER BY " & Case ( MiddleWords ( FieldType ( Get ( FileName ) ; $$p ) ; 2 ; 1 ) = "Text"; "LOWER(" & $field & ")"; $field ) & Case ( $$sort_desc; " DESC" ); ""; ""; "" ) I've also created a variation using LEFT JOIN so that I can sort on fields in the portal that are from a related table: ExecuteSQL ( "SELECT " & $$portal & ".id FROM " & $$portal & " LEFT JOIN " & $$relatedTable & " O…

I need some help with executesql statements. I am trying to compare a date in the Contracts table with a date stored in a global field in the BidR table. I want to use executesql to get the date from the contracts table. My statement is: ExecuteSql ( "SELECT estimatedawarddate_dt FROM contracts WHERE contracts.contractid_t = bidr.contractid_t";"";"") The field "contractid_t" has the same value for both tables. The returned value is"?" when the statement is executed. Can anyone help with the syntax? Also, how would I compare the date returned by sql with a filemaker date? Thanks.

Hi, The following SQL Select works great And it is where I started … ExecuteSQL ( " Select ToDo_Name_Calc, ToDo_Name From ToDo WHERE ToDo_Alert_Date = ? and ToDo_Ck_Bx Is Null Order By ToDo_Alert_Date Asc " ; " - " ; "" ; cDateOfFirstPortal ) Based on this new field "ToDo_Ck_Bx_Plus =1 ( checked )" i want to use the following date range inside my "Where" clause …. WHERE ( ? Between ToDo_Date and ToDo_Alert_Date ) And if the new field "ToDo_Ck_Bx_Plus = "" ( Not Checked )" i want to use the following date range inside my "Where" clause …. WHERE ( ? Between ToDo_Alert_Date and ToDo_Date ) The "OR" didn't work meaning it only …

Hi, I have very limited SQL experience and am trying to do some calculations on a SELECT statement in a local FMP database; When I do: SELECT COUNT(tab) as t, SUM("amounts") as a FROM SummaryData WHERE tab >15 GROUP BY tab Result is: 16, -152 17, 113 18, 255 When I add in a calculation: SELECT COUNT(tab) as t, SUM("amounts") as a, a * t FROM SummaryData WHERE tab >15 GROUP BY tab Result is (invalid): ? I have tried various combinations that seem to work on standard SQL systems, but Filemaker errors. Does Filemaker SQL not support calculations? Any ideas or pointers would be greatly appreciated.

The below code takes 5-6 seconds to process (it's inside a webviewer). Client machine is connected to local Filemaker Server over wireless network. I'm trying to avoid creating a relationship just for this one usage, hence ExecuteSQL(). There is an intermediate table JoinClientsWebsites between Clients and Websites since some clients have the same website. Could I do something differently to improve performance? Thanks! ExecuteSQL(" SELECT url FROM Websites JOIN JoinClientsWebsites ON Websites.id=JoinClientsWebsites.id_websites JOIN Clients ON JoinClientsWebsites.id_clients=clients.id and JoinClientWebsites.primary='yes' WHERE Clients.id=?" ; "" ;…

Hi - I have two tables, Visits and Members. A particular member will have many visits, so there is a primary key in Member : Member ID and a matching foreign key in Visits. Each visit has a field which contains an expense. (OOP Amnt Copy). I'd like to get the sum of all the visit expenses for each member, put it in a field for that member and show this in a portal of Members. I'm using the ExecuteSQL function. If I do this: ExecuteSQL("Select Sum (Visits."OOP Amnt Copy") From "Family Member" Left Outer Join Visits on "Family Member"."Member ID" = Visits."Member ID"";"";"") I get the sum across ALL members of all the visits - not individual sums fo…

Hi, I'm having trouble using the WHERE command to allow a User to dynamically select a date range. The process works well for a single date. But not when I try to establish a date start & date end. The scripts are below. The first one works, second doesn't. Any help would be greatly appreciated! (Note: there is one additional script that kind of "kick starts" the main script. It contains an optional script parameter: INVOICE::virtual_salesboard_date_start) (first line in the script is Set Variable [$date;Value:Get(ScriptParameter) // ------------ BEGIN EXECUTESQL BLOCK ------------ Let ( [ ReturnSub = "n" ; // We need to swap out carriage ret…

Hi, I have a one record table that holds my password. I would like to check to see if it is "NOT Empty". If not empty then I want to call my "Admin Password" script. The following if statement i wrote is ignored. If ( ExecuteSQL ( " Select PasswordAdmin From Preferences Where PasswordAdmin Is Not Null " ; "" ; "" ) Admin Passcode Script ) End If I am having trouble locating simple examples for Filemaker, all the the examples for Filemaker are complicated to me and the W3 School only has the following for Is Null ... SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL Any assistance I would be grateful. Thank you. To…

I have a query along the lines of ExecuteSQL ( "SELECT IDa, Name, Age FROM tableA WHERE IDa IN ( SELECT IDb FROM tableB WHERE IDc = '1234' )" ; "XX"; "YY" ) I have removed most of the fields to be returned as well as additional WHERE criteria that is working. I need the XX and YY in the outside query, but I need commas as the record separator for the IN ( SELECT... sub query Am I missing something obvious....

Hello. I am trying to determine if using an ExecuteSQL function to return results in a portal will be faster than using a Filter Portal option. Here's the need: I have a Report table that is related another table Called Expenses. The expense file has around 10,000+ records and will grow monthly. My design is to display only some of those expenses based on the criteria included in the different reports from the Report table. For example, I may want to drill down on a certain date range, expense values, category of expense, etc. What is the most efficient way to display these results? Since there are many possible variables, it doesn't seem a straight relationship w…

Trying to wrap my head around ExecuteSQL(). I haven't used it enough to be fluent in it yet. I have a calculation in the Classes table to count the number of boats in a region: ExecuteSQL( "SELECT count ("OwnerID") FROM "Class Member Data" WHERE "Region" = ? and "ClassID" = ? and "ownerCurrent" = ?"; "";""; 1; ID; 1; 1 ) (ID is a field in the Classes table.) Which gives me a number, but not the same number as performing a Find for Region = 1 and ClassID = Classes::ID and ownerCurrent = 1 in a Class Member Data layout. So I want to create a list of OwnerIDs to see where the discrepancy lies to allow me to figure out the reason for it. I created ano…

Hello I'm in the process of redeveloping an FM11 solution in FM13. I have a dream of only having one table occurrence per actual table in my FM13 update. I have been happily doing all my lookups utilizing executeSQL until this evening when I attempted to building a simple select statement with a WHERE clause that included references to fields of type "TEXT" I have tried inclosing the reference fields in " as suggested in many forum postings I have read but I still get the dreaded ? as the result. Example $query = " SELECT code FROM Courses WHERE FM11PK = " & Sections::FK_course the above example works as expected $query = " SELECT code FROM Courses …

Does ExecuteSQL() enable a more efficient, simple to implement separation model while still using Filemaker to store the data? I expect this will largely depend on how efficiently the function is implemented, Norman

I am attempting to build an ExecuteSQL using SeedCode's SQLExplorer that will return a list of ID's based on querying a return delimited variable. So far, the best way that's been determined to do this is by using the 'IN' parameter in place of the '=' parameter. However, I've so far been unable to get the query to function correctly. Here is the query with the where the '=' has been replaced by 'IN': Let ( [ // Define Carriage Return Substitution Character ReturnSub = "n" ; // Enable the second line here if you want the header in your results header = ""; //header = "b._id_table3"; // Define Table variables aTABLE2 = Quote ( GetValue ( Substitute ( GetFiel…

Hi all! I am trying to perform an SQL calculation in filemaker 13. I have two related tables of importance to the calculation 1. Main 2. Entries __pk_Main = __fk_Main (in entries table) In the entries table I have the fields Date, EndDate, Format I want to make an SQL statement that lists the Date,EndDate only with format = "chemotherapy" Here is my statement that is giving me a "?" ExecuteSQL ( "SELECT date, EndDate, format FROM Entries WHERE format = chemotherapy ARRANGE BY Date"; "" ; "") Thanks for the help!

Hi, I'm trying to import records via ODBC from FM12 Server to FM11. I can successfully import using the query builder with a WHERE clause but this needs to be a repetitive automated process for a user. I would like to use the Calculated SQL text option and have no problem if I just use a SELECT statement with no WHERE clause to bring in records. As soon as I add the WHERE clause, FQL errors out. The text from the Query Builder is exactly identical to the text from the Calculated SQL text but the Query Builder works and the Calculated SQL Text does not. I have tried every trick I could find on this to no avail. Thanks for any help you can provide. I will post the exact Ca…

In a solution I'm writing I've used several ExecuteSQL () unstirred calculations. Some of the first were written very similar to this as I have a need to use variables to grab information. Does it slow things down to incase it in a Let function (see Let Code)? In several areas I could easily rewrite it to be more clean as I don't need to base anything off of variables (see clean code example). Let Code.. Let ( [ $event = event_item::_kf_event; $item = event_item::_kf_item; $type = 1] ; ExecuteSQL ( "SELECT SUM (Qty) FROM "itemtrans" a WHERE a."_kf_event" = ? AND a."_kf_item" = ? AND a."transtype" = ?" ; " " ; " " ; $event ; $item ; $type )) C…

I cannot figure out how to sum values for last 4 quarters. FMP has function to SUM all values but not selected values..How do i sum the last 4 quarters for the given product... unique id product id quarter id sales 1 1 2013-q1 10 2 1 2013-q2 20 3 1 2013-q3 30 4 1 2013-q4 40 5 1 2014-q1 50 6 1 2014-q2 60 7 2 2013-q1 70 …

When I create a calculation field with the following ExecuteSQL function, it works fine: ExecuteSQL ( "SELECT DISTINCT "Regatta Contact AMYAnum" FROM "Regatta Schedule" WHERE OnLineReg = ? "; "" ; "¶"; 1) But when I add a further qualifier ExecuteSQL ( "SELECT DISTINCT "Regatta Contact AMYAnum" FROM "Regatta Schedule" WHERE OnLineReg = ? and Date > CURRENT_DATE() "; "" ; "¶"; 1) It returns the dreaded "?". What is the correct syntax?

I have an ExecuteSQL statement in a calculated field that finds the most recent Job that was referred by a Company. The calculation looks like this: ExecuteSQL ( "select MAX("Call Date") from Jobs where "Referral Company" = ? AND "Sale Amount" > 0"; "" ; ""; Companies::COMPANY ID) I have unchecked "Do not evaluate if all referenced fields are empty". So here is my dilemma: If "Do not store calculation results -- recalculate when needed" is unchecked, then my data is not always accurate. This field is used in another calculated to determine a "Rating" which is displayed to the user. If "Do not store..." is CHECKED, then my data is accurate, but any layou…

More and more as I go on, I wonder if the SQL link ups I've seen work come from one flat table. I'm trying to create a Staff Database interface which comes from many tables in SQL. I have created a view as one source, but that doesn't cut it in many regards - especially when updating data. Of course it could otherwise just be my lack of knowledge One example I'm currently working on is displaying a filepath as a container image (i.e staff photo). In SQL I have calculated it as {serverpathstuff from SQL with ConcatenationofNameandDOB.jpg}. In filemaker when I pick up this field, it obviously defines it as text, when I need it to display as a container. The tabl…

Hi, I need some assistance. I have a table with 2500 records. The current SQL select looks at the entire table if I'm not correct. I need my SQL Select to look though a relationship that only list records for a date range we select. I've attached my current SQL Select statement with where I think the new "Relationship Table Occurrence" should go. Could you kindly please have a look see and guide me here where I would place my "Table Occurrence" so my SQL Select will only look for a "Match" though "A Date Range" relationship table occurrence. Thank you. Tom :-) SQL Select.pdf

Sorry for the vague title. Here is the simple statement ExecuteSQL("select count(Distinct Date) from Reports";"";"") The final query is more complicated than that but I simplified it because I know that the problem relies here. This query will return and error ("?") If I create a calculated field Date2 (=Date) and replace it in the query, then the query works. The only thing I can see is that I have other fields named "Date" in other tables but as you can see these other tables are not even in the example query so it's clear that the Date is the on of the table Reports. That must be a simple thing I don't get it .

Can someone enlighten me why this fails: ExecuteSQL( "SELECT COUNT ( IDs ) FROM TableA WHERE aaName = "Bob Bob" " ; "" ; "" ) But this one works? ExecuteSQL( "SELECT COUNT ( IDs ) FROM TableA WHERE aaName = 'Bob Bob' " ; "" ; "" ) To mine eyes the only difference is the single quotes, and I thought that the escaped double quotes should be an acceptable form. I know I have used escaped double quotes before, I swear. (Oh wait, that's right, I am swearing too much recently. ) I guess I have used escaped quotes around field/table names but never as part of the WHERE clause's string-to-match. I typically have used the replaceable parameter in these cases; but t…

I'm performing relatively simple select statement that is using criteria from a multi-keyed global field to perform the select The statement is basically ExecuteSQL( "SELECT Table1.fieldName FROM Table1 WHERE Table1.id = ? ; "" ; "" ; <MULTIKEYEDGLOBALFIELD> ) The query works but it is only returning a single result even when there are multiple keys in the global field. Any suggestions?

Hi, I have the following ExecuteSQL statement which selects the highest document number from the control table based on a given type. The statement is: ExecuteSQL ( "SELECT Max(DocNum) FROM Control WHERE Type=?";"";"";Control::Type) This works fine. I have a second table called "Obsolete" that has the same field "DocNum". I would like to get the the Max of DocNum from both tables. I tried just adding Obsolete to the the FROM statement but that did not work. I am new to SQL, any help is greatly appreciated. Thanks. Daniel

Hi, I haven't had much to do with SQL, but with the inclusion of a new member of staff who's a SQL guru, I wanted to use Filemaker to be the front end to his tables. Prove its worth as you will. The issue is, the guides have been of no assistance. I have created a connection to the SQL database. I have then created a layout in filemaker where the underlying table is the SQL table. I want to now be able to execute a search and return only those records that match. I've simplified it down to no avail Where $PersonID is the value of David Execute SQL [DSN:ASPIRE; Calculated SQL Text:"select * from people where firstname = '" & $PersonID & "'" I c…

Hi, I am having trouble summing a found query in my SQL select statement. ( Select Sum ( AmountPaid ) From Consult Where ? Between SearchDate1 and SearchDate2 " ; "" ; "" ; ConsultDate ) I get a result from the entire datafile. I just want to sum my query between my two date range. When I type in Jan 1 thru Jan 31 I get the entire year summed ?? Please forgive the syntax error, I can not copy the exact SQL Select Statement till later tonight, if you see an obvious error in the overall SQL please ignore, I'm writing from memory and I'm very new to SQL. Can someone provide a basic SQL that sums only records within a date range for me to…

The following ExecuteSQL statement returns the correct count: ExecuteSQL ( " SELECT count (*) FROM "Class Member Data" WHERE ClassID = ? " ; "" ; ""; 53) But when I add some additional qualifying conditions like ExecuteSQL ( " SELECT count (*) FROM "Class Member Data" WHERE ClassID = ? and Region = ? " ; "" ; ""; 53; 1) it incorrectly returns 0. What is the correct syntax?

Hi, I need my SQL results in a 12 hr clock, not 24 hr clock. I tried Hour( ) and Minute ( ) but there was not change. I had to change my "Time" field to a "GetAsText" field so I could see my time as 9:00 AM or 9:00 PM. But now I can not sort the SQL results. please see pic. Any assistance I would be grateful. Thank you. Tom :-) SQL 24Hr.pdf

Hi, I built a calendar for a client and initially the calendar was slow on the local computer, so I change the calendar to use ExecuteSQL "SELECT". And now the calendar is super fast on the local machine. Not even a blink and the calendar data is loaded. However, the data file also sits on a Shared Server and the calendar is now insanely slow. Over 30 seconds to display the calendar date. I replace the "From" Table Occurrence to a Table Occurrence that only allows for records to list within a date range rather than the entire table occurrence, but that had no effect. I would have thought that hence less records to query, the quicker but Nope! the time …

I ran into an odd problem today with ExecuteSQL(). I was trying to run a query that would count things that were NOT set, i.e.: ExecuteSQL ( "SELECT COUNT ( ID_pk ) FROM Updates WHERE ID_pk = ? AND Replaced ≠ ? " ; "" ; "" ; ParentID ; 1 ) Each parent could have a number of updates, but we only want one active at a time; so if the Update is marked as 'Replaced' then it is no longer active. So my query above was returning a dreaded "?". However, if I ran THIS query: ExecuteSQL ( "SELECT COUNT ( ID_pk ) FROM Updates WHERE ID_pk = ? AND Replaced = ? " ; "" ; "" ; ParentID ; "" ) I got the correct result. So it apparently doesn't wan…

Hello, Just wondering if it's possible to map each row returned by an ExecuteSQL statement into an individual repetition of a field. I'm only selecting a single field using ExecuteSQL. Other than scripting a loop to do the stuffing, I don't see any way to save the query results as myField[1], myField[2], myField[3], etc. Any ideas? Is it possible? Thanks, Jeff

I have three tables in my database that all use this ExecuteSQL statement so I thought that I would just put it into an Custom Function, but I seem to be doing something that violates the syntax rules. What am I doing wrong(pic)? http://pctechtv.com/show/exsqlgb.png

Hi I have been trying unsuccessfully to use the ExecuteSQL to select from a Microsoft SQL table added via ODBC. Should I be able to do this? or am I barking up the wrong tree!! Thanks in advance Stephen

HI there.. I am trying to adopt the portal sorting solution found elsewhere on this site. It uses this kind of SQL statement: ExecuteSQL ( "SELECT "Address ID" FROM "Addresses!Contact ID" Where "Contact ID" = ? ORDER BY " & $field & Case ( $$sort_desc; " DESC" ) ; "" ; "" ;Suppliers::Supplier) And it fails if what you are sorting by uses a mix of upper and lower case characters... how can I ignore the case in this situation? Matt

Hi everybody,  i'm going to calculate a percentage with ExecuteSQL, but i believe my syntax is wrong.  i've 2 multiple field which show type and sale (total for type) as list, as in the picture.  Now i'd like to calculate a percentage on the total for each type.  Table are 2, Customer where i've all customer information as name, address etc, and Type and Invoice...  for the multiple field called "Ricavi" (SALES in English) i used this:  ExecuteSQL(" SELECT SUM(A2.TOTAL) TOTAL FROM CUSTOMERS A1 LEFT OUTER JOIN INVOICE A2 ON A1.ID_CUSTOMER=A2.ID_CUSTOMER GROUP BY A1.TYPE "; "";"" )  If i write in the new calculated filed as follow it doesn't …