FileMaker Query Language or FQL

Howdy, all; I think there's something amiss with the MAIN::CityIDsMultiKey__lct field's calculation: Case ( // If Country, State and County have not been selected, show ID's for all City records IsEmpty ( id_Country ) and IsEmpty ( id_State ) and IsEmpty ( id_County ) ; ExecuteSQL ( "SELECT id FROM \"ESQL_Cities|Sel\" " ; "" ; ¶ ) ; // If a State has been entered but not a County, show all City ID's for the State IsEmpty ( id_County ) ; ExecuteSQL ( "SELECT \"id\" FROM \"ESQL_Cities|Sel\" INNER JOIN \"ESQL_Counties|Sel\" ON \"ESQL_Cities|Sel\".\"id_County\" = \"ESQL_Counties|Sel\".\"id\" WHERE \"ESQL_Coun…

Greets, all: I have a calculation that works as is: ExecuteSQL ( " SELECT Department__lxt, COUNT ( * ) FROM ESU7_STAFF WHERE Status__lxt = 'Active' GROUP BY Department__lxt UNION ALL SELECT '¶Total' Department__lxt, COUNT (Department__lxt) FROM ESU7_STAFF WHERE Status__lxt = 'Active' " ; Char (9) ; "" ) ...but the output of departments is unsorted. (See below.) I'd like to have the departments in ascending order, but I just can't get the syntax right using ORDER BY; how/where should it go? As always, TIA for your help!

If there's a native way to do this in FileMaker Pro (v20.x) I'm all for it, otherwise I need a little help with an ESQL calc. Please refer to the attached. Total is a calculated field that looks refers to Pages or # Originals and QTY or # Copies. (It has a bunch of different calculations based on Product Code, so that's why the one for W11 looks funky.) Within the child table, I'd like to automatically copy/calculate the value from the Total field of records that have a Product Code of W11, into those records that have a Product Code of L8511; so the calculated value in Total for W11 is also in Total field in records where the Product Code is L8511. I…

I was quite disappointed when I found out that writing to the FileMaker_Fields table doesn't work (using SQL-Runner): UPDATE FileMaker_Fields SET FieldID = 24 WHERE TableName = 'VL_Test' AND FieldName = 'Enum' I get the following error: failed with FileMaker error code 8309: ERROR: FQL0048/(1:10): This statement contains an invalid operation on FileMaker system table "FileMaker_Fields". Is there any workaround? FileMaker mixed up my remote ODBC tables and I want to try to fix it, inserting the «correct» FieldID’s. Thanks a lot! Gary

With executesql script, date Is in format MM-DD-YYYY... How May I convert It to DD/MM/YYYY?

Okay I can't find an example now. But I see where SQL looks like this: Select * FROM t2.table 2 ... I cannot find where the t2. comes from. Is this an alias, or is this saying "I am naming this table t2 so when I talk about it and say t2 you should know what I mean. oh can someone just point me to something which explains what this thing is even called? I can't look it up because a. and b. and t2. were not the names of the tables in the examples I looked at. t2. isn't a real thing, is it? I think I could have used this idea recently on something I couldn't figure out if only I knew what it meant. Know what I mean? I don't think it is called alias bec…

I have Product records that have two fields: SupplierID number field Last_Updated date field I want to group this data by the Supplier and a set of Age categories depending on how long ago the Product was last updated. This will allow me to chart each supplier to see where data needs updating. For the non-working example, I'll only include two age categories for clarity. SELECT SupplierID, CASE WHEN Last_Update < ? THEN 1 WHEN Last_Update > ? AND Last_Update < ? THEN 2 ELSE 0 END As Age, Count(*) FROM Products WHERE SupplierID IN (SELECT DISTINCT SupplierID FROM Suppliers) GROUP BY SupplierID,Age"; ""; ""; Get(CurrentDate)-7); Get…

I'm attempting to perform an SQL query to find out how much has been paid in rent for a given time frame for all rent payments in a given property. I have created extra fields to convert dates to numerical values. DateNo is a date > numerical field in the Payment table (Payment::Date). DateStart1 is a date > numerical field in the Property table (Properties::DateStart), as is DateEnd1 (DateEnd). With a date range entered in DateStart and DateEnd (numerically DateStart1 and DateEnd1, respectively) the following statement gives me a blank result. If I remove the date parameters I get a result, obviously for all payments ever entered. My error seems to b…

Howdy, howdy: I found a neat way of performing a sort within a portal (in the attached file), but I'm stuck trying to amend one of its scripts' steps: in the setPortal_refresh [field] script, line 7, it has: ExecuteSQL( "SELECT ID from Contacts WHERE State = ? ORDER BY " & $field & Case ( $$sort_desc; " DESC" ); ""; ""; States::abbreviation ) The thing is, I want to exclude referencing a state--I just want to be able to sort each column's contents, state notwithstanding. I tried removing the WHERE part of the code but I'm just not getting the syntax right. How would I tweak the syntax to accomplish my goal...or by tweaking the syntax it would…

Gents, I am stuck with ExecuteSQL. The problem: I have 5 shipping containers with distinct numbers. But there are 6 records in the table. I need to calculate the total weight for all 5 containers ( select distinct containers only). Thanks for your help! SQL_containers.fmp12

Hi all I'm attempting to modify an existing (working) Execute SQL calculation to further include another step. The calculation field is inside my Projects table (Unstored, From Projects). The SQL code references data inside the Assets table to give me a count of the applicable records. This is my current code: ExecuteSQL ( "SELECT Count (*) FROM Assets WHERE EntryModeCheck = 1" ; "" ; "" ) I need the SQL query to reference an additional field where I need the EntryModeCheck to still execute, however Projects::ProjectID must equal Assets::ProjectID to only count the records inside the Project parent record. I figure an INNER JOIN is needed, but all I can g…

What is the consensus for doing foreach record in FQL? Do I do n queries using some sort of iterator? Or do I do 1 query and iterate through its results? I'm setting one global variable for each record, there is about 100 global variables to be set from records.

Hi, I'd like your opinion and advice regarding the following situation. I have a database with several (related tables). Most of the layouts are created with portals which get populated by an SQL search function based on the Soliant tutorial. I have very limited knowledge of SQL programming. The ExecuteSQL script searches all the fields in the current table and the portal then shows the found records. However, I also want records to show up if the entered search value matches a related field. Eg. table 1= patients and table 2= prescriptions. When searching in the prescription overview, I also want to be able to enter the patient name so all the prescription…

Hi all FM Genius, I have a table where I have as columns: Purchase_Month, p_SupplierName and c_Total from table::Purchases. I wrote an Executesql formula as follows: ExecuteSQL ( "SELECT p_SupplierName , Purchase_Month, SUM(c_Total) FROM Purchases WHERE Purchase_Year = ? GROUP BY p_SupplierName, Purchase_Month" ; ":" ; "" ; Purchases::Purchase_Year; Purchases::Purchase_Month ) and am getting the result as Results: Acrux Ltd:November:39371.0976694915254236 All Goods Ltd:October:7328.3071186440677962 Anichem Pharmacy:February:724.08 Anichem Pharmacy:January:16678.2529661016949151 IBL HEALTHCARE:January:822.76271186440677…

Hi folks. I'm attempting to run an SQL query that references a table with a space in its name. ExecuteSQL ("SELECT SUM (\"Quantity removed from inventory\") FROM \"Trade Record Ledger\" JOIN \"Trade Records\" ON Trade Record Ledger.ID_Record = "\Trade Records\".\"Trade Record ID\" WHERE \"ID_Quantity\" = ? AND \"ID_SubRecord\" LIKE ?" AND "Trade Records".\"Action Date\" = ? ; "" ; ""; "QUN5", "INV%", "08/12/2020") This returns a number, text constant, field name, or "(" is expected here - highlighting the beginning of "\Trade Records\" on the third line. The following, simpler, query executed with no issues: ExecuteSQL ("SELECT SUM (\"Quantity remove…

Hi, I'd like to INSERT new records, but only, when they not already exist. Otherwise I‘d need to update existing records. There is a handy command in MySQL and I wonder if there’s a workaround for our SQL92 engine? INSERT INTO table (column_list) VALUES (value_list) ON DUPLICATE KEY UPDATE c1 = v1, c2 = v2, ...; Thanks a lot for your comments! Gary

Hi, I have a table Beleg:: that I can query like this SELECT Beleg.id_Konto, COUNT ( Beleg.id_Konto ) FROM Beleg WHERE Beleg.Year = 2020 GROUP BY id_Konto To get the COUNT of each id_Konto in Beleg: 2e974529-5bf6-47e1-b1a7-05b3b2582aea 397 cb54987b-1eec-4868-bcb5-14e2efccc1be 83 ea461038-43f1-43b8-8d68-a2f357f7f41f 27 And I have a table Konto:: that has the names for the id_Konto = Konto.id values: SELECT Konto.Kontonr , Konto.Name_short FROM Konto WHERE Konto.id IN ( SELECT ( Beleg.id_Konto ) FROM Beleg WHERE Beleg.Year = 2020 ) ORDER BY Konto.Kontonr The Name_short that I need: 1000 Kasse 1210 Bank1 1240 Bank4 However, I c…

Hello, first, I'm a newbie. SELECT t.fk_ProductID, MAX(t.EffectiveDate) AS latestDate FROM Transactions t GROUP BY t.fk_ProductID HAVING t.fk_ContactID = '9EE273FD-A35C-4177-8E2F-8ABE6F368286' The green part of the query above works well, however when I add the HAVING clause, it breaks with the following error: "All non-aggregated column references in the SELECT list and HAVING clause must be in the GROUP BY clause." How do I overcome this apparent limitation? Looking forward to your feedback. thanks a lot.

I have an ExecuteSQL question that I'm hoping you could help solve for me [FMP18A on MAC]. The best way for me to outline my question is to show the process I took to get to where I am currently. [Example data only] I have the following TABLE [Sales_Database] and COLUMN [FRUIT] setup, and my SQL calculation gives the following result: ------------------ ExecuteSQL ( "SELECT Fruit, COUNT(*) FROM Sales_Database GROUP by Fruit ORDER by 2 DESC " ; "" ; "") Apple, 77 Oranges, 67 Grapes, 57 Pears, 47 …

Good morning. Despite there being numerous examples out there (the following being just one of-), for the life of me I am unable to get a specific calculation to work. https://fmforums.com/topic/93770-sum-function-with-execute-sql/ Here's a simplified version of my query: I have a table called DL_AT. In this table I have three fields Date: (date), Service (text), Amount (number). I have a second table called Invoices In this table I have three fields: Date (date), Service (text), Total (number) The relationship between the two tables is DL_AT::Date = Invoices::Date AND DL_AT::Service = Invoices::Service I have a portal setup within Inv…

Is there any way to make this statement without using IFs? Let( [ $country = "IT"; $region = "Toscana"; $subregion = ""; $town = ""; $result = ExecuteSQL ( "SELECT Ref FROM RealEstate WHERE Country = ? " & If(IsEmpty($region);" AND (Region is null)";" AND (Region = '" & $region & "')") & If(IsEmpty($subregion);" AND (SubRegion is null OR SubRegion is not null)";" AND (SubRegion = '" & $subregion & "')") & If(IsEmpty($town);" AND (Town is null OR Town is not null)";" AND (Town = '" & $town & "')"); "" ; "" )]; $result ) I use a tbl.Criteria table to store multiple records of rules, that I then use to select various…

I am trying to use a start date and and end date in an executesql calculation and the result keeps coming back with 0. I can get it to work by a direct executesql using the dates, but would like the option to use an already defined field in the layout (dropdown calendar) like this: Let ( [ begin= GetField("Search Start Date" ) ; end = GetField("Search Stop Date" ) ; $query = " select count(*) from "Office" where qualified='Yes' and "Client Flow Sales Agent"='Sales Guy' and "Client Flow Date In" BETWEEN ? AND ? "; $result = ExecuteSQL ( $query ; "" ; "" ; begin ; end) ]; $result ) Does anyone have any recommendations for a better way to do this or how to fix the …

Well, I'm new to SQL and have used it in it's basic select mode. Now I have been asked too create a dashboard using data from different tables to display. Which on the most part is OK and working great. I have had a request for a top customer list for the month and previous month (say best 5 ranked from 1-5.) and not together so 2 seperate views I am able to pull customers and SUM totals (seperately)for the month using SELECT DISTINCT I am able to get a list of the customers for the month. How would you then get a total or SUM of the invoice amounts for the given month with the customer name and their total ? also is there a similar function to FRACTION i…

Hi again, very grateful for help on last question with this Base Elements function. Now I am trying to cycle through all tables in an external .fmp12 file, and SQL select all from each table. I want to get external data from tables in an application built in FileMaker, which disables the script space in that database. As mentioned in my other topic 105263 here, this involves opening the tables with Open File step, which will then ask me for user and password which I have. Once the files are open, script can access tables with BE_FileMaker SQL function (but not with ExecuteSQL), as my script is running from a different file. For a static table name, this code works …

Hello, I have developed a FM database over the last 6 years and this is the first time I have not been able to solve a problem searching the forums. Yet seemingly simple, I have not been able to create a working SELECT COUNT () statement (or alternative) to count the subquery listed below. There is many references to MySQL; however, they fail with FM execute SQL. Any help would be appreciated Let ( [ hospital = Dashboard::g~SQL~Hospital ; year = Dashboard::g~SQL~Year ; month = Dashboard::g~SQL~Month ; $$YearList = Dashboard::g~SQL~Year ] ; ExecuteSQL ( "SELECT COUNT (b.cEncURN) FROM Encounters b WHERE b.cInitialHospitalNameShort = ? AND b.Initi…

Hi, I'm new to FileMaker scripting, and trying to basically select all data from another database created with a 3rd party application which is itself made with FileMaker. This means Script Workspace is disabled in that database. So I can open an .fmp12 file from that database with script in blank FileMaker file: Open File [Open hidden:Off; "Provenance"] And the following line works fine to get FileMaker tables from my blank database: Set Variable [$sql_test; Value: BE_FileMakerSQL ( "SELECT * From FileMaker_Tables" ) ] Using the DatabaseNames function, I get following as names of the open databases: TestScript (the one I'm running the script…

Greets, all: I know FSQL isn't for creating documents, but tables, but this should be do-able: I have a single table (OJI) where I'm only concerned with two out of its many fields, Injury_Root_Cause and zz_RecordID. What I want to do is sub-summarize by Injury_Root_Cause and list each's respective (unique) zz_RecordID's under each heading--sorted in ascending order--horizontally listed and each zz_RecordID separated by a comma. So far, I have some of the calculation working: ExecuteSQL ( " SELECT zz_RecordID FROM OJI WHERE Injury_Root_Cause = 'Carelessness' ORDER BY zz_RecordID " ; "" ; ", " ) That works great for listing the zz_Rec…

I'm having a syntax problem getting the 'IN' clause to work in FM's ExecuteSQL statement. I would like to use a '?' placeholder in the statement (just looks cleaner), but when I do it doesn't work. I already have a string that contains the list of numbers (this is all numbers...except the fact that it is a string of numbers :) ). I want to use that variable in this statement. Here's a WORKING example: Let ( [ ids = "281,282,283"; sq = "SELECT ID_fk, sum(Amount) FROM Payments WHERE ID_fk IN ( " & ids & " ) GROUP BY ID_fk" ] ; ExecuteSQL ( sq ; "" ; "" ) ) But it's not using the '?' placeholder. What I would like to do ins…

Howdy, all: Once again, I know I'm close but the syntax is getting the better of me. My goal is to use a global variable ($$_ProposedDate) to test against a date field (Date__lxd) in the same table (RESERVATION_PARENT) to see if a date has already been used. Here's what I have so far: Let ( TheDate = $$_ProposedDate ; ExecuteSQL ( " SELECT COUNT ( * ) FROM RESERVATON_PARENT WHERE Date__lxd = ? " ; "" ; "" ; TheDate ) ) If the count > 0, then I know the date has been used. Obviously, I'm missing something, but what? Along this calculation, I could swear there's a native FileMaker Pro way of doing the sa…

Greets, all: I'm trying to present the total of all the unpaid invoices in a report that's generated in a join table (ACTIVITY_PtoC_JOIN) with ACTIVITY_PARENT joined with ACTIVITY_CHILD. I stripped down the SQL calc that's used in FileMaker Pro's native Inventory database (that's created when you choose, "Create New"). The fields I'm using for the SQL calc are Total__lcn (which captures the total of an invoice in ACTIVITY_PARENT), Paymount_Amount__lxn (used to capture how much was paid on the invoice (if anything), and of course the account number (AccountNumber__lxt); they're all in ACTIVITY_PARENT. Let ( [ …

I feel like the answer is right in front of me, but I can't seem to nail it. I keep getting a syntax error. Here is my calc (testing in the data view for now): Let ([ theDate = Totals_Daily::Daily_Totals_From_Date ; ID = Totals_Daily::ID ; query = "SELECT SUM (Daily_Qty) FROM Totals_Daily WHERE Daily_Totals_From_Date=? AND ID=? GROUP BY Daily_Totals_From_Date,ID" ]; ExecuteSQL ( query ; "" ; "" ; theDate ; ID ) ) If I remove the second parameter (ID) and simply sum by the date, I get a result. As soon as I add the "AND ID=?" I get a syntax error.

Hi Gang, I am reaching out to see if anyone can assist (or maybe point me in the right direction) with some ExecuteSQL() issues I am having in my my next version of my product. I want to execute a query like the following: SELECT code FROM Appts WHERE appt_id IN (’14’,’10’,’8’,’9') ORDER BY FIELD(appt_id, ‘9', ‘10', ‘8', ‘14’) I have tried several times for this to return a working result with NO LUCK. Do you know if FMP allows this sort of query or does it fall short. Appreciate any help. Thanks!

Greets, all: I _thought_ this would be simple: signal users that the value they're inputting into a field--in a portal--is a dupe. (For the incredibly lazy who won't scroll through the portal records, first, to see if the value's already there.) I figure a counting function ought to do it--with a count of greater than one, that would set a message window fired by a script trigger. I know I'm close but for some reason I just can't nail it: The calc is in the child table, JOB_KEYWORD_JOB ; the value field to test is Keyword__lxt; the foreign key is _kflt__JobID. I figure I'd count those values with the same foreign keys and keyword and that would give me the …

There's a bit of strangeness going on with this calculation between using it in a Calculation (text) field and populating a text field with a Set Field script step: ExecuteSQL ( "SELECT DISTINCT ( Cad_Type__lxt ) FROM VALUE_LIST WHERE Cad_FullName__lxt = \"_g_CadFullName__gxt\" AND CountryAbbreviated__lxt = \"_g_CountryAbbreviated__gxt\" " ; "" ; "" ; "" ) The calculation works fine by itself in the Calculation (text) field but when used in a Set Field script step, a line feed (or carriage return--I can't tell which) is inserted in the text field before the calculated result. Am I missing something? I could add a subsequent script step that w…

Trying to pull data (list of DataIDs) where they have null Notes, see image. I want to see the ids from the related layout table Master. Any help in the right direction will be great. thanks.

Hi everyone, i'm looking for a sql client able to connect ti FILEMAKER and could displya query result like sql oracle developper in Windows and Oracle Database. The soft must work on mac, is there any solution like this ? I want to make query and access to result with odbc Thanks Tom

I am wanting to learn to use the ExecuteSQL() function. I find I seem to take quite naturally to SQL when working through the examples on w3schools but am quickly lost in a maze when I try to translate this learning to use in Filemaker. I have seen the introductory articles but have rather a lazy mind. Are there a set of concise rules to translate from SQL to FQL? In passing, as this has become an important feature in Filemaker, would it not be reasonable to expect Filemaker to provide an SQL editor where one could enter standard SQL, use field and table references, and then give us the FQL version of the query?

Hello i really need some help, the step by step kind. i have a table with a bunch of fields the two I care about are site_id and a plot. I want to know the number of records in the table for each combination of site_ id and plot. I have tried executeSQL but it crashes the table has to many records. if one of you kind experts could please send me an example of what fields I need to add and the summary/calculations in need to set up. i will even give you the messy table if it will help. https://drive.google.com/open?id=1qapEEbztUGaol8opO8iSC51GQkLyIS1L Thanks

I read the FM v16 SQL Reference (Page 9) but I don't think it gave me the whole skinny on using non-alphanumeric field names so I'm stuck. Using this formula works: ExecuteSQL ( "SELECT COUNT ( k_StudentID ) FROM EXPERIENCE WHERE k_StudentID = ? " ; "" ; "" ; k_StudentID ) ...but the real name of the field is _k_StudentID, so the leading underscore character is the troublemaker. In the Guide it says to use double-quote marks but FileMaker Pro squawks with an error message when I use them so I'm missing something. What's the correct syntax to make this formula work? Thanks in advance for your help.

Having trouble with an ExecuteSQL calculation. I used SeedCode's SQL Explorer to build the calculation. It works fine if I use literal values from a specific record; but if I instead change it to a field reference, the calculation fails. I get the message <unrelated table> in the Data Viewer. The code is below. Am I misusing the field names somehow? // Build SQL Query q = "SELECT SUM ( " & ainterestMineralDecimal & " ) FROM " & a@LINEITEMS & " WHERE " & a_id_lease & " = ? AND " & a_id_tract & " = ? AND " & a_id_unit & " = ? " ; // Run SQL Query …

Hi all, I'm trying to use execute sql to populate a field with the sum of all the values in a field of related records. All I get is a ? so obviously something is wrong with my syntax but I cannot figure it out. Any help much appreciated! This calculation is occurring within the 'Collections' table. Let ( [ query = " SELECT SUM(S.Volume.used) FROM \"Straws_for.entry\" AS S JOIN \"Animals.to.Collections_join\" AS A ON \"S.fk_a.to.c.join\" = \"A.pk_join\" WHERE \"A.fk_collection\" = ? " ; collection = Collections::pk_collection ; result = ExecuteSQL ( query ; "" ; "" ; collection) ] ; result ) Also, aft…

I have a Db with 265,000 records. I am attempting to find the Unique Value count of a field called PRODUCT SKU in a table called SALES. ExecuteSQL ( "SELECT DISTINCT \"PRODUCT SKU\" FROM \"SALES\""; ""; "" ) I created a calculation field called SKU COUNT with the above SQL. At the moment the query is running at a pace of about 6 seconds per record. I'm obviously doing something wrong.

This seems so straightforward, and I keep ending up in the same place. I have 2 tables, "therapists" and "years". They are joined in the relationship graph by years::therapistid = therapists::zk_TherapistID The years table has the numeric fields "year" and "expectedrefsind" I have a database field in therapists::TotalExpectedRefs, it's defined by this calculation . . . ExecuteSQL ( " SELECT SUM(Y.expectedrefsind) from years Y JOIN therapists T WHERE Y.therapistid = ? AND year > ? " ; "" ; "" ; "T.zk_TherapistID" ; 2014 ) Expecting this to return a Number instead of a ?. Any help would be really appreciated. …

Happy holidays, everybody! Simple request: I want to set a calculated flag in a parent table's field so if any of that parent record's children has the word, Yes, in a specific text field it would set a value in the flag field. I tried the following but the syntax is getting the better of me; as always, your help is greatly appreciated! The flag calc is easy; the ExecuteSQL syntax is hard. Of course, if there's a native way in FileMaker Pro to do it I'm all for it! Parent table name: TEST Child table name: STANDARD_1 Fieldname in TEST: YesFlag__lxt Fieldname in RESULT: PassedYN__lxt ExecuteSQL ( "SELECT COUNT ( STANDARD_1::PassedYN__lxt ) FROM RESULT W…

I am still learning SQL and I was hoping for some help to add another relationship to my equation. I would like the SQL to show me all the ItemsIDs that are assigned to selected years (via key) that are originated from Details table layout. This is working good, but it's showing my ALL ItemsID meeting that year criteria, but I would like to only see __ItemsID based off the layout I am on, which is Details. I think I need to add FROM "\Details\" and WHERE _fkDetailsID = ? ; Details::__DetailsID, but I am struggling to add in another table!! Any help would be greatly appreciated to get where I am going or simplify!! Details::__DetailsID…

I have used the combination of ExecuteSQL and ValueCount quite a few times throughout my databases before, with almost no issues. Generally what I will do is set a variable by running an ExecuteSQL, then take a ValueCount of that variable. Then with that ValueCount I will loop through my original ExecuteSQL variable. However, I am now trying to do something different with this combination. Instead of using the ValueCount variable for a loop, I'm trying to use it as a Global Variable that will be displayed on the main menu. Here is an example of the code I'm trying to use: Set Variable [$source; Value: ExecuteSQL ( "SELECT UUID FROM Inspections WHERE Inspector = ?…

I'm having trouble using a variable with WHERE IN in FileMaker ExecuteSQL. A bit about what I am trying to accomplish and my environment. My company has heaps of content from hundreds of providers. We use FileMaker to store the bibliographic data in an easy to use layout for internal end-users. The data in FileMaker is imported from a csv file that is an export of a view from Microsoft SQL database. The view contains all biblio data and relevant record IDs and is stored in a table called 'FM_EBookDataFields_And_Shadow_Fields' . Sometimes ContentProviderA and ContentProviderX send the same content (there could be dozens of ContentProviders for X.) I want to …

I've been working with SQL databases for about 12 years now, and have stumbled across this monstrosity in a new position - and I really don't get the translation between Filemaker and SQL. This should work according to the documentation: ExecuteSQL ( " SELECT Customer_EVENT::Inv_dollars ) FROM Customer_Event WHERE Customer_EVENT::FlagReview = \" " ; "" ; "" ) ...or something. I really can't even get a simple SQL Statement just pulling anything more than 1 field to work. Is there a reference out there (not Filemaker or FM14 Bible) that actually explains in detail WHY FQL statements are made the way they are and how each piece interacts? I'm not …

I can't seem to get any ExecuteSQL calculation working that used GROUP BY. Here is a very simple example that I just tried. It wont work unless I take out the GROUP BY statement: ExecuteSQL (" SELECT * FROM citystatezip WHERE city='Wilmington' ORDER BY citystatezip_id GROUP BY zip ";",";¶) I've tried everything I can think of, indexing all the fields, rearranging the order by and group by statements, etc. Again, it works perfect as soon as I take out the 'GROUP BY zip' line

I have created a "viewer" using a virtual list technique and ExecuteSQL. The "viewer" is a portal that contains one field - a unstored calc text field from the virtual list table. This allows me to run a script for a "report" and have the results appear in this field. I can run a different script and have different data appear in that field. Everything works great, except column widths. I understand how to adjust Tabs, alignment, etc using FM>>Line Spacing>>Tabs. However, that sticks to the field regardless of the data it is showing. I can use hard tabs in the script to place tabs where I want but only for FM fields or text (for column header…

I've been using Daniel Smith's JSON scripts from (http://www.modularfilemaker.org/module/json/) to parse JSON. It's worked quite reliably (thanks Daniel!), but I'm wondering if anyone has compared its parsing speed to MBS's JSON plugin or the new native Filemaker 16 JSON functions? I'm still on 15. I'm working with some larger JSON files that are taking a long time to parse and hoping for a faster solution. Any data on which option parses JSON fastest? Any disadvantages/advantages to each option? Thanks!

This is my first time posting, and I apologize if this is the wrong place. I've only been filemakering for a few months, so again, apologies if this is a stupid problem. I could use some advice. I'm working on a solution that must create monthly reports in the following form: http://i.imgur.com/Cf4Fjic.png I've given up trying to bend the summary fields to my will, and began using the following approach: Make two new tables - Queries and Reports. In queries table, I have fields for M1, M2, M3, Region, Category, Subcategory and four result fields (M1, M2, M3 and YTD.) I also have foreign key into the reports table. …

Hi all, Hope an FM SQL guru can help. I am accessing a large list of tables and one table has ~500,000 records ~30 fields. Using 3 or more key fields to create a smaller subset fails to show more than a few records either in a portal or gttr. Other smaller dataset tables ~5000 return complete sets in the ~100's or less and very quickly! I do have a built in report on the SQL side that returns subsets quickly but is in-flexible as to key fields and output format. I see two ways forward I haven't tried. 1. Try a scripted find then copy to a native fm table. 2. Try using the same key fields as the built-in report and see if that provid…

Trying to integrate FMP solution with Shippo using the Shippo API. When using Insert from URL step, response comes back with this error. {"detail": "JSON parse error - Unterminated string starting at: line 1 column 25 (char 24)"} Here's what Shippo specifies in their API, and below that, what FMP is sending (per Data Viewer). Any help resolving this issue is greatly appreciated. From Shippo API: '{ "address_from":{ "name":"Mr. Hippo", "street1":"215 Clayton St.", "city":"San Francisco", "state":"CA", "zip":"94117", "country":"US", "phone":"+1 555 341 9393", …

Trying to get FMP 16 solution to integrate with Shippo to get shipping rates but keep getting an authentication error. I suspect my syntax is wrong in the cURL component of the Insert from URL step. I've checked the URL ($url) and token ($token) and they are correct values. Any help is greatly appreciated. Shippo API: curl https://api.goshippo.com/shipments/ \ -H "Authorization: ShippoToken shippo_test_4f4d4302e76f97f922d6fb957a9c156712bec772" \ -H "Content-Type: application/json" \ -d '{ "address_from":{ "name":"Mr. Hippo", "street1":"215 Clayton St.", "city":"San Francisco", "state":"CA", "…

With the introduction of cURL options in FM16's Insert from URL script step I've been investigating the possibility of being able to save a copy of my FM solution directly to my Dropbox folder using the https://content.dropboxapi.com/2/files/upload element from Dropbox V2 API. I've successfully created the access token to pass through as part of this step and the Insert from URL script step successfully creates the stated folder and file in my Dropbox. However, the file size is always 0 bytes. I've tried 2 different methods METHOD 1 #Export a compacted copy of this file to Temporary Folder Set Variable [ $file_path ; Get ( TemporaryPath…

Hi all I'm trying to find the Client that has the most Job time over the last 30 days, divided by the number of seats that they have. The tables are: Clients Client_ID, Seats Jobs Client_IDF, Job_time, Job_Date The best I've managed so far is to find the distinct Client_IDF from all Jobs: ExecuteSQL ( "SELECT DISTINCT j.log_client_IDF FROM Jobs J WHERE j.Log_date >= ?"; ""; ""; $date_30_days_ago) And then loop through the list and perform the 'divide by seats' calculation for each line Or to do something similar but from the Clients table: ExecuteSQL ( "SELECT SUM (j.labour_units), c.Name FROM Clients C JOIN Jobs J ON j.log_client_ID…

Hello I need help understanding FQL. I thought I did Please download my file for testing Query I highlighted all differences between the first and the other statements. The value "aha" is inside the second repetition of the field 'test::name' Result of evaluation Question Why won't the SQL statement sql02 return anything. Looking at statement sql03, it looks like only the last statement inside the brackets is being evaluated. Looks like a BUG to me. Thank you

I am dealing with a very weird condition in my solution where an ExecuteSQL statement was failing to return a matching record. My solution tracks documents such as deeds and leases, where a document can have N grantors and N grantees. So I have a Parties table, a Documents table, and identical join tables, Grantors and Grantees. I noticed the problem when printing out a report, and a particular document didn't have any grantees. The report uses the executeSQL query in question. So I looked for that document in the database, and the particular grantee appeared as expected in the portal. I looked in the Grantee join table, and there was a matching record, comple…

I have a table enlist_load_dtl, I want to use the Execute SQL script step to push data into a MySQL database through ESS. using values from a global variable. Can anyone help me, if my query is correct, if not appreciate the help . INSERT INTO enlist_load_dtl (studid, schedcrsid, coursecode, fromtime, totime, days, room) values ($$StudID, $$schedcrsid, $$Coursecode, $$Fromtime, $$Totime, $$Day, $$Room); Thanks in advance, Pele

HI Guys and all the best for 2017… Could I please ask for advice on a SQL query i require as part of an availabilities system I am building for a rental database where rental businesses rent inventory to customers for a time period. When a rental job is created it has a start & end date also a start & end time. When I go to my rental item list to add a rental item, next to each inventory item I want to see how many in total I have (own) and how many are available for rent for the date/time range added to the job. Tables Rentals Line_items Inventory Each lineitem added to a Rental has a start/end timestamp so I need a SQL query that will…

Hi, i am a novice when it comes to anything more than basic SQL and am stuck trying to get an average (using SQL) of grouped query result. My first query is: SELECT SUM(diffDateTime) as theDiffSum FROM Briefs WHERE diffDateTime>4 AND Group1 = 'ABC' AND Status = '1' GROUP BY ows_ID HAVING theDiffSum>99 ORDER BY theDiffSum ASC this gives me a result like: 100 120 123 123 290 Ideally, i would like to form a query on this result set to give an average - a single number, using SQL (not FMP functions). I've looked around but have been unable to find anything that works (or understand). e.g. SELECT AVG( SELE…

Just trying to learn this executeSQL and can not seem to get sensible results. I have a table which represents any cost prices changes that occur with a product. If I use a simple statement eg ExecuteSQL ( "SELECT ProductDescription FROM CostPriceChanges" ;"";",") i get a nice list of products show up eg Sweet Potatoes, Limes, Limes, Lettuce, etc etc However when i add where criteria I get the dreaded ? ExecuteSQL ( "SELECT ProductDescription FROM CostPriceChanges WHERE Cost Price >= .01" ;"";",") I am thinking, it is something in the syntax of the where that i am not getting into the grey matter but that is where i am …

I need some help with ExecuteSQL. I have a TO “Sales” that is related to Estimates, Inspections, and Proposals via ID_SALES:id_sales. I created some simple ExecuteSQL calculations that work on Estimates and Inspections, but the same calculations will not work with the related Proposals TO. · I know that the relationship is good because I can get the SUM and COUNT from Proposals with a basic calculation (not ExecuteSQL). · I know there is not a problem with the name on the related Proposals TO (like a space between words). I've copied and pasted the working calculations and I have started from scratch, and neither works. …

Hi all, new user on the block Anyhow, I want to be able to search a table from another table. I have tried using ExecuteSQL, but I seem to not be able to make it so I can for an example type "89547" instead off "895476", using the wildcard %.It only returns ?. In the example file I attached I think I have been able to explain it more clearly. It has 1 000 randomly generated data to test on. It does not however contain any scripts. If anyone could help me get on track I would be very grateful :=) Regards JohanA Example.fmp12

Hi, I'm trying to use Filemaker to manage a mysql database on Amazon RDS. I'm trying to set up an import layout that will allow users to load data into into the RDS database. Mostly, Filemaker and mysql are playing well together. But I'm getting a problem when I try import data using LOAD DATA LOCAL INFILE . I've run the query successfully with a different client on the same database (Sequel Pro) but when I run it using the Execute SQL script step in Filemaker I get an error, with the message "Load data local infile forbidden". I've looked around online, and it seems like I need the server and client to both enable 'local_infile'. I know it's enabled on the…

I'm trying to wrap my head around using ExecuteSQL() to produce the following results but I could use some help. I have a table of projects with a field for status. I would like to query the table to get a list of all the statuses and how many projects contain each status. Active,10 Inactive,15 Cancelled,2 Completed,25

Hi, I need help with SQL and Running Total (instead of Total). TABLES INV = Invoice table - INV::Amount ( Amount of Invoice) - INV::DateMonth ( Month of InvoiceDate) - INV::DateYear ( Year of InvoiceDate) DTE = Months table (fixed 12 records) - DTE:Order=1...12) - DTE::CurYear = 2016 Below SQL request works, see my result! But what I would require is a running total, adding each month sales total, see my "Required result" ExecuteSQL ( " SELECT SUM(a.\"Amount\"/1000) FROM \"DTE\" b …

This is just about as simple as an ExecuteSQL gets, but I can't figure out why it generates a "?". ExecuteSQL ( "SELECT Count (\"voteYes\") FROM \"MotionVote\" WHERE \"motionYear\" = '2016' "; "" ; "" ) MotionVote is a TO; voteYes and motionYear are fields therein; voteYes can either be 1 or blank. The objective is to display a count of "yes" votes. What did I miss?

I'm using ExecuteSQL() to get a list of fields in a table. What I would like to do is be able to use my naming convention to include or exclude certain fields. For example I name certain utility field starting with a lower case "z" followed by an upper case letter as in Notes::zUtility. So I would like to exclude those fields from the list. ExecuteSQL ( " SELECT FieldName FROM FileMaker_Fields WHERE TableName = ? AND FieldName NOT LIKE ? " ; "" ; ""; "Notes" ; "z%" <-- this is not correct, what would this need to be to specify a lowercase z followed by any upper case letter? ) Any help would be greatly appreciated. Thanks

Can anyone see whats wrong here: Let ( [ invoiceID = id ; decimal = Left ( Evaluate ( 1/2 ) ; 1 ) ; SQL = ExecuteSQL ( " SELECT SUM ( b.\"Amount\" ) FROM \"Invoice Data\" b WHERE b.\"id_Invoice\" = ? AND b.\"Type\" = Rental " ; "" ; "" ; invoiceID ) ] ; If ( SQL ; Substitute ( SQL; "." ; decimal ) ; 0 ) ) There are 2 tables Invoice and Invoice Data. The items added to the invoice can either be a rental, Sale or service item and want to total each type on the invoice layo…

HI Guys I am new to SQL in filemaker and am trying to get SQL Data into variables and hope you can help please. I am running a script trigger after a user exits the "sku" field in a sku added to an invoice. I set the sku into $id and I want to get itemName and price from the products table for that sku and add the result to the variables $itemName and $price. Could I ask what the code would look like?

Is it possible to use Execute SQL across databases not just tables but another database that exists on the same server. If so how would I reference it in an SQL statement. Any help or advice would be much appreciated. Best,

Hi all Can someone tell me if this query is correct? I'm trying to find all currently open Cases, but only those that belong to a personnel whose 'exclude_from_stats' field is null. So far this does seem to give me the correct results, however I am a bit uncertain as I have been having problems recently defining criteria in related records. It seems that you sometimes have to resort to different types of joins or you run the risk of excluding/including too many records. SELECT COUNT (*) FROM Cases C JOIN Personnel P ON p.personnel_ID = c.User_IDF WHERE c.Closed IS NULL AND p.exclude_stats IS NULL Many thanks

Hi all, Curious about ExecuteSQL this works... ExecuteSQL ( "SELECT myfield FROM myTable WHERE myTable.color = ? and myTable.style = ? and myTable.id = ?" ; "" ; "" ; "black" ; "test" ; "3" ) but this fails... when I replace myTable.color selection from "black" to empty string "" then the result fails (when there are rows with this criteria)... ExecuteSQL ( "SELECT myfield FROM myTable WHERE myTable.color = ? and myTable.style = ? and myTable.id = ?" ; "" ; "" ; "" ; "test" ; "3" ) Q: any reason it fails? is it a syntax thing? how do I search for field = "" ?

Développeur Support 21 juin 2016 08:53 I am working on a search query with "Like ?" The query give me no result when I put the search string in a field but If I try with the same value in a constant I have my result. Here are the two query: ExecuterSQL ( "SELECT \"idDossier\" , \"Demandeur\" , \"Historique\" , \"TravailDemandé\" FROM DemandeAssistance WHERE \"Historique\" LIKE ? OR \"TravailDemandé\" LIKE ?" ; $$separateurChamp ; $$separateurLigne ; RechercheGlobale::chaineComposée ; RechercheGlobale::chaineComposée ) Executer…

Hi, I need control of my column width. The following SQL select is perfect with the results I want except I have been unsuccessful in finding a way to set my column width. All I can do is to set my delimiter by 15 - 20 spaces which pushes the results apart but they are staggered depending on the field data's length. Filemaker's SQL pdf reference doesn't seem to reference a way to control the "Width" of a column within an SQL statement. I tried the "Right( )" but it only threw a "?" in the results and I had to remove them for "Quantity and Item_Cost_Plus_MU" Does anybody have a suggestion. I would be grateful. Thank you. …

Has anyone come across a way to have filemaker execute an online trade with a brokerage account? I want a DB to place a trade buy or sell with a brokerage house - probably thru an API Thanks in advance J

I'm a complete newbie with SQL, just exploring now. This is probably a stupid simple question but here goes. I have a table I want to query to extract a virtual value list. I want the WHERE condition to come from a value in a global table. I've tried lots of things but just don't have the knowledge of the syntax in SQL. cList1 = ExecuteSQL ( "SELECT List FROM ListsCustom JOIN Global ON _pk_1 = _pk_1 WHERE Label = Cust_List " ; "" ; "¶" ) cList2 = ExecuteSQL ( "SELECT List FROM ListsCustom JOIN Global ON _pk_1 = _pk_1 WHERE Label = Cust_List[2] " ; "" ; "¶" ) cList3 = ExecuteSQL ( "SELECT List FROM ListsCustom JOIN Global ON _pk_1 = _pk_1 WHERE Label = …

I'm attempting to do a search for a value that was entered by a user. They want to be able to enter in part of a project number and have the system look for it. I understand the LIKE operator and I can get it to work if I pass the argument of something like %12345% but I want to pass in something that looks like the following: wildcard plus value entered by user plus wildcard. I've tried the following (let me try this again with the right code): Let ( [ project_id = "%" & d_tmd_tmh::zd_time_dtl_prj_num & "%" ; SQL = ExecuteSQL ( " SELECT a.\"__project_id\" FROM …

I am new to FM, coming to it from a SQL Server/Oracle/Access background. I have created a database with layouts and while there is certainly much more for me to learn it generally is working as I desire it. However, I need to perform a task that I do not understand how it would be done in FM. The example below is not my actual problem but does exemplify my core issue and hopefully is easier to understand than if I described my project. I have a table of Users, a table of Entitlements, and a join table of UserEntitlements. I want to execute SQL that, for a given User, will read from the Entitlements table and insert rows into UserEntitlements for this user. The…

I have a database that accepts first and last name entries for a primary person and a secondary person. However, there is a problem that sometimes the secondary person comes back looking to be entered as a separate primary person, which is not permitted. However, I do not have any data validation or verification to prevent this. When a new record is created, would I be able to use SQL to check the first and last name combinations entered for the primary and the secondary persons against the names previously entered in the database and create a dismissible dialog popup giving a warning when duplicate names are entered? Ideally the dialog box would also give a link to g…

Hi All. I've been learning and utilizing ExecuteSQL all over the place and here's my latest endeavor. I'm trying to use ExecuteSQL to grab a list of record IDs based on selections in global fields to be used as a multikey for a relationship . So in a global field called gEmpIDs which is related to the ID field of my Employees table I have the following calc Let ([ ~status = Case ( gEmpStatus = "Active" ; 1 ; 0 ) ]; ExecuteSQL ( " SELECT ID FROM Employees WHERE status = ? " ;"";""; ~status ) ) gEmpStatus is a dropdown with selections for "Active" and "Inactive" status is a fie…

I'm tying to write an ExecuteSQL for charting. I can get it to work with out selecting a Date Filed (OrderDate). What I want to do in the end is do a "Where OrderDate = mm/dd/yyyy" to filter out by date. But in testing I'm not even able to even get SELECT to work. ================================= ExecuteSQL (" SELECT SKU, SUM(Quantity) as ProductTotal FROM LineItems GROUP BY SKU ORDER BY ProductTotal DESC FETCH FIRST 20 ROWS ONLY " ; "|" ; ¶ ) =================================

I'm working on a cross-tab report using ExecuteSQL. I'd like to try to do it as a single eSQL statement but I'm not sure if its possible or not. I'm still a bit of an eSQL newbie. Background, the database manages work done in a condominium building. Each apartment in the building is identified by a floor and a location in the building; 1-9 for the floors and N, NE, SE, W for the location. For the report, the floor is on the vertical axis and location is the horizontal axis. Each cell in the report is a sum of apartments where the work has been completed. I have the first part of the statement working, I can grab a total number of apartments completed on each flo…

I am relatively new to sql queries in Filemaker, and I'm receiving the ? in response to the following queries. Because I don't have the advanced version of FMP, I used Evaluate() to return the error code 8310 (syntax error). However I can't figure out where my syntax error is. Hopefully more experienced eyes will spot my problem(s). The following is entered in a calculated field that returns text: Let( [ $initials=ExecuteSQL("SELECT TaskAssignedTo FROM workflow WHERE" & Quote("_IDDocLog") & "=workflow::" & Quote("_IDDocLog") & "AND workflow::TaskNumber=9"; "" ; "" ); $name=ExecuteSQL ("SELECT FullName FROM staff WHERE AccountName= $init…

Hi, I'm attempting to return a literal out of an ExecuteSQL() statement so I can test and develop another SQL query but I cannot get it to return successfully (it reports SQL Syntax error). I used verbatim this model example from Beverly on the FMForums only changing the "myTableRef" reference to a valid TO name in my solution but no results are returned. Ultimately I want to coalesce or cast a text field to an integer so I can use the value in a "WHERE ... IN()" SQL statement expecting integers. I had no success using CAST, COALESCE or NUMVAL SQL functions doing this which lead me to this use of literals which I was hoping to use to debug my casting problem. …

Hi All, Need some help and I *think* that ExecuteSQL will do it but i'm not sure how: I have a portal which lists 4 fields in a tabe called Related Job Items. The fields are Detail, Notes, Compound and Sales. With multiple records there is a potential that the portal may list duplicates which is undesired - For example 2 records could have all 4 fields with the same information as it is based on a lookup from all jobs. What i want to do is ensure that when all 4 fields match a previous record it only shows it once in the portal. Any Thoughts? Thanks,

Hi all, I'm relatively new to the ExecuteSQL statement but I want to learn more about it in order to create a dashboard of charts without having to worry about the underlying layout's base table. I am a lab manager who oversees 4 funds each with different fiscal years. I'd like to see real time totals for each fund. I'm stuck with how to incorporate the various fiscal years to the total spend for each fund. Do I need a statement for each fund? I have tables for Orders, Funds and a Dashboard. I was able to successfully write an SQL statement that allows me to chart how much each lab user is spending each calendar year (via a User table). But this has …

Hi all Hopefully this is in the right place, apologies if not. I'm using the technique found in the FM 13 advanced training series to generate virtual lists. I'm using virtual lists to quickly transfer data to a scratch table used for reporting. I have an ExecuteSQL formula to gather some of the data in question: ExecuteSQL ( " SELECT GroupID, SUM(RoundedValue) FROM DataTable_Home WHERE CustID_FK = ? and Code < ? and PeriodNumber = 2 and (GroupID = 68 or GroupID = 3) GROUP BY GroupID "; "|" ; "|" ; Cust_Home::CustID_PK; "500" ) This generates in most cases a da…

I have a table that contains items sold over the last 20 years, I would like to run a query where I can see a comparison of sales by year. Customer# Name Year1Sum Year2Sum 1 ABC Company 5000.00 1000.00 2 BAC Company 2500.00 7500.00 I can get the first column SUM to work ok - how do I add the second column in the same results from different records.?

A simple project wants to start with several people out in the field doing data entry; in the interest of uniformity, I set up a simple one-table web-based SQL table with a one-way PHP interface. That's working fine (and is about the limit of my facility with PHP). My intent was to set up an automated recurring import into a FMP database, which I thought would consist of entering credentials and an IP address into the ExecuteSQL script step. I find the learning curve a little more steep. In this instance, it's really not hard for me to export the contents to my desktop once a day and then hoover them up into FMP. But can you recommend a one-stop tutorial for this kin…

I'm relatively new to the ExecuteSQL statement but I want to learn more about it in order to create a dashboard of charts without having to worry about the underlying layout's base table. I have a simple projects table that contains ID_Project and a field called "Project Category". I'd like to create a pie chart that represents a count of each category but I can't even get the base select statement correct. Here's what I've done already ExecuteSQL ("SELECT ID_Projects, \"Project Category\" FROM Projects GROUP BY \"Project Category\"" ; "" ; "" ) All I get is the dreaded "?" I've tried a million variations of this and still cannot get it. Once I figure out…

LS, I am puzzled. I have this simple sql : Let ([ key = FORR::k_order_line_id ; SQL = "Select sum(amount) " & " from journal_line jl" & " where jl.k_order_line_id = ?" & " and jl.ledger = '2053' " ] ; ExecuteSQL ( SQL ; "" ; "" ; key ) ) Data viewer returns : 1707.5 ( 1895 - 187.50 ) Calculated field returns : 170750 <- wrong. Calculation is number, unstored. When I don't have decimals the sum result of both are the same. Any ideas as to why ? TIA, Joost

Howdy, all: Please refer to the attached. Background: This is a "flat" file that, unfortunately, should've been created using a parent-child relationship where the StudentID number should've been a primary/foreign key, but alas, this is what I inherited to work on; there are multiple records for the same student. I don't know how to easily create a parent-child relationship where all you have a children-type records so I thought rather than going through creative T.O. mapping and calcs, SQL might handle the job neater and more elegantly. The report will be sub-summarized by student. In this form, there are 17 fields where users can choose a rating of 1, 2, …

Hello, I'm trying to create a virtual list ( 70 Columns X 300 Rows ) with the below codes. But it fails after the 30th SELECT clause. I cannot figure out, please help. Thanks, Recep Let ([ @x1 = GetValue ( $$YemekIDList ; 1 ); @x2 = GetValue ( $$YemekIDList ; 2 ); @x3 = GetValue ( $$YemekIDList ; 3 ); @x4 = GetValue ( $$YemekIDList ; 4 ); @x5 = GetValue ( $$YemekIDList ; 5 ); @x6 = GetValue ( $$YemekIDList ; 6 ); @x7 = GetValue ( $$YemekIDList ; 7 ); @x8 = GetValue ( $$YemekIDList ; 8 ); @x9 = GetValue ( $$YemekIDList ; 9 ); @x10 = GetValue ( $$YemekIDList ; 10 ); @x11 = GetValue ( $$YemekIDList ; 11 ); @x12 …

Hi all I have a list of services with different dates. e.g. Broadband, £10.00, 27/09/2015 Domain, £15.00, 27/09/2015 Broadband, £10.00, 27/10/2015 Domain, £15.00, 27/10/2015 Broadband, £10.00, 27/10/2015 Domain, £17.00, 27/10/2015 What is the best way of creating a report that shows totals by service and month? e.g. Service September 2015 October 2015 Broadband £10.00 £20.00 Domain £15.00 £32.00 It can be any kind of report, e.g. Excel or csv. Any suggestions appreciated. Thanks

Hello, I am extremely new to working with SQL, so i am currently fumbling my way through achieving my required outcomes. To explain further explain my issue, i created a table/layout on one of my databases that i could use to practice and visualize the outcomes of ExecuteSQL. I type into my "query" field and that drives a separate ExecuteSQL calculation. I have been able to achieve the required outcome in this field, however when i transfer the query directly into a ExecuteSQL calculation i get errors. These relate to the field in financials called "Cost Code" If i attempt the same calculation without it, i can run it without an issue. However…

I am getting a strange result, I am attempting to use the ExecuteSQL command to avoid using multiple Table Occurrences. However I am stumped when the field I need does not work on ExecuteSQL for an odd reason. ItemNo and Level are both Indexed Number Fields with the EXACT SAME SETTINGS, however when performing these commands, ItemNo gives me a result, when Level does not. They both have data inside, etc. Any thoughts or ideas why I am receiving a "?" on the second ExecuteSQL("SELECT c.ItemNo FROM Test_C_any c WHERE c.TestID = 'AA'";"";"";"") ExecuteSQL("SELECT c.Level FROM Test_C_any c WHERE c.TestID = 'AA'";"";"";"")

Hi all I'm trying to utilise the NOT EXISTS command: I need to find records in the Cases table that do not have matching records in the Originals table. So far I have: SELECT a.Case_ID FROM Cases a WHERE a.Closed IS NULL AND NOT EXISTS (SELECT * FROM Originals b WHERE b.Case_IDF = a.Case_ID) It just freezes when I try this though. Could it be that because there are a lot of records in each table? I will be using additional criteria - not sure if this would improve performance if this is the case. Any help appreciated. Thanks