Search the Community

Showing results for tags 'sql'.

More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


  • Custom Function Library


  • Journal

Community Forums

  • Community Resources
    • FileMaker Pro 16 Discussions
    • FileMaker Pro 15 Discussions
    • Community Articles, Tips, & Techniques
    • FileMaker Marketplace Discussions
  • FileMaker Security Management
    • Security Concepts
    • Intellectual Property
  • FileMaker Server Administration
    • FileMaker Server 16
    • FileMaker Server 15
    • FileMaker Custom SSL Certificates
    • External Server Authentication
    • Stand by Server Deployment
    • Legacy FileMaker Server Discussions
  • FileMaker Platform
    • FileMaker Interface Features
    • FileMaker Schema & Logical Functions
    • FileMaker Go for iPhone & iPad
    • iBeacon Support
    • FileMaker IOS App SDK
    • FileMaker Discussions
    • Brain Food
  • FileMaker and the Internet
    • FileMaker Cloud
    • FileMaker WebDirect
    • Custom Web Publishing
    • Other Internet Technologies
  • Geist Interactive Product Support Forums
    • Visit Geist Interactive
    • Visit Modular FileMaker
    • FMPerception
    • Generator
    • fmQBO
  • 360 Works Official Product Support Forums
    • 360 Works General Support
    • MirrorSync by 360Works
    • SuperContainer by 360 Works
    • ScriptMaster by 360 Works
    • FTPeek by 360 Works
    • 360Works Email Plugin
    • DocuBin by 360 Works
    • Zulu – FileMaker, iCal & Google Calendar.
  • FM Forums Affiliate Sponsors
    • SyncServer Pro by LinearBlue
    • Open Source Frameworks
    • Monkey Bread Software (MBS Plugin)
    • FileMaker Plug-Ins
    • ISO FileMaker Magazine
    • User Group Central - Sponsored by
  • FM Starting Point - By Richard Carlton Consulting
    • Visit FM Starting Point
    • FM Starting Point - General Discussions
  • FileMaker Classifieds
    • FileMaker Product & Service Announcements
    • Professional FileMaker Training
    • Services for Hire
    • Services Wanted
    • Solutions Wanted
    • Tools Of The Trade
  • The Water Cooler
    • Member Lounge
    • Wants & Wishes
  • FM Forums Operations
    • FM Forums Feedback & Site News
    • Site Instructions


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


  • Samples
  • Solutions
  • White Papers
  • Plug-Ins
  • FMGo

Found 61 results

  1. 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 sure I can do this with a calculation, but it's entirely possible. I'm just trying to get a SUM on a certain set of records to display SOMEWHERE in a layout. The fields are from a related table, but have nothing to do with the related records. It's just a way to SUM the objects in a portal by "filtering" the amount.
  2. 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_IDF = c.Client_ID WHERE l.Log_date >= ?"; ""; ""; $date_30_days_ago) But no joy in either case when it comes to actually performing calculations within SQL. Is this possible in FM SQL? Many thanks
  3. Greetings All I am looking to add a line into the following code into the 'prepare payload for client' section to select only records that match the following criteria I have a variable set called: $additional_settings The field that needs to match with $additional_settings is called _kf_uuid_companys the code that i have already is as follows, i need to add a line into this code to pick records where the field _kf_uuid_companys matches $additional Settings. Any help would be greatly appreciated: "SELECT " & $dyn_sql & ", '" & $$record_delimiter & "'" & " FROM \\"" & $sync_table & "\\"" & " WHERE " & // Exclude records flagged for sync exclusion. "( COALESCE ( BS_Exclude, 0 ) = 0 ) " & // If the client is merging new/updated data with data already on the device... // Only include records that have been added/updated since the last pull... // And only include data that they did not just push (i.e. no "round tripping!")... If ( ( $$sync_method = "Merge" ) and ( $last_pull_utc > 0 ) ; "AND ( BS_UTC_Time > " & $last_pull_utc & " ) " & "AND ( COALESCE ( BS_Device_ID, 'X' ) <> '" & $client_persistent_id & "') "; "" )
  4. 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 instead: Let ( [ ids = "281,282,283"; sq = "SELECT ID_fk, sum(Amount) FROM Payments WHERE ID_fk IN ( ? ) GROUP BY ID_fk" ] ; ExecuteSQL ( sq ; "" ; "" ; ids ) ) I found some custom functions from folks that help format this correctly, but I would rather not use a custom function for just this one need (only doing this once). Thanks, -- Justin
  5. Hello We are going to take the plunge and setup a sync between our Oracle tables and 'shadow' FileMaker tables and we're seeking input. One thing we are wondering about is a 'one-way' sync. We don't want changes to be sync'd back to Oracle. The bigger issue is that we don't want deletions in the Oracle table to be deleted in the FileMaker table. There often are children to these records we don't want deleted or orphaned. As well, are there any gotchas we should consider? I've read the Advanced topics and feel mostly comfortable but you never know what you don't know! Thanks, Mark
  6. 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. This statement works: ExecuteSQL ( "SELECT COUNT (ID_ESTIMATES) FROM TO40_ESTIMATES WHERE ID_SALES = ? “ ; "" ; "" ; TO13_sales_ESTIMATES||id_sales::id_sales ) --- This statement does not work: ExecuteSQL ( " SELECT COUNT (ID_PROPOSAL) FROM TO26_PROPOSALS WHERE ID_SALES = ? "; "" ; "" ; TO13_sales_PROPOSALS||id_sales::id_sales) Any help would be appreciated. SC
  7. I am trying to create a list of dates from multiple tables to create a gantt chart. My projects have Shipping dates, Installation dates and task item dates. Each of which come from their own tables. Is there a good way to import all of these dates into one table related to the project, each with it's own record of Start date and end date. I'd rather have: Project | Item | Start_Date | End_Date| than: Project | Install_Start_Date | Install_End_Date| Ship_date | Ship_Arrive_Date | Task_Start_Date | Task_End_date
  8. 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 server (because I can check manually, and the query works on a different client). Does anyone know how to check or change this within Filemaker? Otherwise, does anyone have any ideas as to how I might work around this? Unfortunately I can't call a stored procedure on the server because the query relies on variables. My understanding is that you can't use variables in a LOAD DATA INFILE statement on the server, and you can't use LOAD DATA INFILE in a prepared statement. Does anyone know how to change the local_infile variable within the Filemaker mysql client, or failing that, does anyone have a good workaround for the restriction on variables with the LOAD DATA INFILE statement if I run it on the server? In case it's not already blatantly obvious, I don't really know what I'm doing, so any help (especially help that is dumbed down) would be hugely appreciated. Thanks, John
  9. 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 LEFT JOIN \"INV\" aON b.\"Order\" = a.\"DateMonth\"AND b.\"CurYear\" = a.\"DateYear\"GROUP BY b.\"Order\""; " | " ; "0¶" ) My current result as above SQL request 726.605050 818.7150 739.067850 0 703.7210 620.805350 599.986160 506.9230 597.8370 801.78250 1014.11510 567.64575 Below is what I require (a Running Total)726.605051545.320052284.38792284.38792988.10893608.914254208.900414715.823415313.660416115.442917129.558017697.20376 I can do this without SQL, but I want to do using SQL and use it for my Charts. Would appreciate any help. My SQL understanding is basic and I am using FileMaker 14 Adv
  10. I created a database to track permits by building. Each permit type is stored in its own table. The only link between tables is the Building Name which is the physical location where the permit exists. Tables: v_Buildings (fields include building name, ID, building status, address, city, state, zipcode) Air (ID, Building Name, Expiration Date, Days_to_Expiration, Type, etc...) Water Food Elevator ...9 permit types in all, and I will add more in the future Each of these tables has the same fields in common: BuildingName PermitExpirationDate PermitOwner Days_to_Expiration I created a Dashboard layout with the idea that users could select a building name from a drop-down menu and then see all of the permits for that particular building along with their expiration date and the permit owner's name. I am just baffled as to how to do this across multiple tables. I have looked at SQL, Join tables, portals etc...., and I cannot figure out how to aggregate all of this information into one view for users. I cannot even seem to figure out what table should be used for the Dashboard layout. I would love to hear from the community the best and hopefully scalable approach for designing this layout. Thanks in advance from a novice user.
  11. 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 layout.. Thank you
  12. 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
  13. 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 query will read from Entitlements, insert a row for every entitlement for this user and, based on data in Entitlements take, will mark some of the entitlements as 'active'. Ideally, every time I created a new user this SQL would be called (I'd have it as a trigger in a SQL or Oracle database, or I'd have a batch process that ran frequently looking for new users). Basically, as a new user is created I want to create their entitlements profile and activate standard entitlements. I don't need/expect a full answer - was just hoping I could get pointed in the right direction: is 'scripts' the area I need to explore? Is there a SQL capability to FM? I have not found it if there is. Thank you for any assistance you can provide, -Neil
  14. 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 me stumped! Let me know what information would be helpful for those out there who might help me! Many thanks in advance!
  15. 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 dataset of: 68|-3600|3|-7200| <<< as an example for this customer with 68 and 3 being the required group 'names' or headers. This data is passed via scripting into a field on the scratch table called ListData which has several fieldsdedicated to particular values, two of which are dedicated to Group68 values and Group3 values. The formula in each is: Get Value(ScratchTable::ListData;2)<<<For Group 68, -3600 Get Value(ScratchTable::ListData;4)<<<For Group 3. -7200 However, there may be cases where a customer does not have any values associated to either 68 or 3 which obviously reduces the return on the dataset and makes the parse section inaccurate. On my scratch table, I have several fields If a particular customer has NO value for group 68, the returned data set instead looks like: 3|-7200 and this will break all downstream GetValue(Field;x) calculations. So, what I really need to have is a reliable way of ALWAYS returning 4 pipe separated results in the situation that a customer has data missing from one or other of the groups, and that's where I'm stuck. If I could get to: 68|0|3|-7200 or 68|-3600|3|0 as an example, this would enable me to parse the 0 values to where they should be. Any help on this one greatly appreciated. Thanks
  16. 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 kind of straightforward behavior?
  17. Hi all, This is my first post in the forum - one of no doubt many to come! I'm working on my first solution and as part of that I'm pulling in data from a SQL Server 2008 database which will be augmented by file maker tables. I have been able to locate the DSN and setup the External Data Source, however when I try to add the table in the relationships screen I can only see the system tables in the SQL database - not the views and tables that I have setup. Does anyone have an idea of a possible solution for this? Running FileMaker Pro Advanced 14 on Windows 7. Many thanks, Tim
  18. 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 what confuses me is it will work when input through a query field. Query field; SELECT SUM(Total) FROM financials Where "Cost Code" = 'FA' and EID = '982 000147908607' Please find attached images highlighting and showing everything relevant. Kind regards Pat
  19. 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 most common expression is a simple field name, such as calc or Sales_Data.Invoice_ID." (thanks!) It should't make any difference, but I am using the BaseElement plugin 1.2.1 to send the query to FileMaker. Any help would be gratefully appreciated. Thank you in advance. Regards, Chong-Yee
  20. 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 would be appreciated.
  21. Hi I have a variable that contains multiple lines, each with fields separated by commas. Can I call up this variable in the SQL INSERT function in order to create new records (based on the variable)? Thanks
  22. 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 the system. I want to use these columns now to generate a percentage on a chart. I need to somehow get that into a SQL statement - the statement below doesn't work (I believe because I'm querying the derived fields of INC and COST which seems ridiculous that you cannot do this, so probably it must be me making an error). If I were to do Select SUM(Amount), this works fine as Amount is simply a number field? Thanks in advance for any help Let ([ CUST = Customer_Home::Cust_PK; SQL = "Select (SUM(INC)-SUM(COST))/Sum(INC) from DataTable where CustID_FK=? and GroupID = ? and PeriodNumber=?"]; ExecuteSQL(SQL;"";"";CUST;1;5) & "¶" & ExecuteSQL(SQL;"";"";CUST;1;4) & "¶" & ExecuteSQL(SQL;"";"";CUST;1;3) & "¶" & ExecuteSQL(SQL;"";"";CUST;1;2) & "¶" & ExecuteSQL(SQL;"";"";CUST;1;1) & "¶" )
  23. Please Excuse the Minor Audio feedback issue at the beginning. We were unkinking out new studio. FMPug Without Borders | Execute SQL with Todd Dignan Download the FileMaker Pro 14 & FileMaker 14 for Mobile Devices Training Videos at Please Visit Our Channel: Please Subscribe While There. Richard Carlton Consulting, Inc. provides customized database development services for business, government, and non-profit organizations. Our core competency is FileMaker Pro, where we hold FileMaker's highest certifications. With a team of 27 staff, we are capable of deploying both small and large solutions for a wide variety of customers. We service all modern versions of FileMaker Pro, FileMaker Server, and FileMaker Go (for the iPad and iPhone). We also deploy FileMaker databases to the web, and offer PHP/Web Development, iOS Programming, FileMaker Server Support, and Hosting Services. Please feel free to contact us at Looking for FM Starting Point free software download: For More Free FileMaker Videos Check out Http:// Please Comment, Like & Share All of Our Videos. Feel Free to Embed any of Our Videos on Your Blog or Website. Watch a FileMaker Training Review Video Here: Follow Us on Your Favorite Social Media
  24. Hi - I've been trying a number of approaches to get a calculated value list using ExecuteSQL code. I initially tried putting the SQL code directly into the Value List definition but that didn't work. Right now, I've done this: I created a UnusedClaims field in a Visit table, whose value is calculated by this: ExecuteSQL(" Select \"_k1_xSAClaim_ID\" FROM \"xSA Claim Status:xSAClaim\" WHERE \"_k1_xSAClaim_ID\" NOT IN ( Select \"_k2_ClaimID\" FROM \"xSA Claim Status:VisitClaims\" WHERE \"_k2_VisitID\" = ? ) " ;""; "" ; Visits::${Visit ID} ) I then put a Claim TO in and related its primariy ID variable to the UnusedClaims field in the visit table. Then I created a ValueList, using the _k1_xsaClaim_ID value from that related Claim TO, and in the value list definition clicked Use only related values from the Visits table. Sometimes this works - but other times it includes values that are in the list that should be NOT IN. The ExecuteSQL code works perfectly in the data viewer, but the Unused Claims field, which has the exact same code in it, includes values it should not include. What am I missing?
  25. 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