Jump to content

Search the Community

Showing results for tags 'sql'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


  • Claris Engage Vendor Discounts

Community Forums

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


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


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

Product Groups

  • Workplace Innovation Platform
  • Site Advertising
  • Development & Hosting

Find results in...

Find results that contain...

Date Created

  • Start


Last Updated

  • Start


Filter by number of...


  • Start







Website URL




OS Version

  1. Hi, I have been using Base Elements command BE_FileMakerSQL in FileMaker script to SQL select data, and return with '<c>' as field(column) delimiter, and '<r>' as the row delimiter. The below line worked before, I dumped a whole FM database with these delimiters showing in text files. But somehow, now if I run below line, it seems to have changed to ?only allow? 1 character for the delimiter, which of course doesn't make sense anyway with varied characters in the data: BE_FileMakerSQL ( $sql_select; "<c>" ; "<r>" ; $fm_file ) The string this returns now looks li
  2. Hi, I am using the Base Elements plugin Execute SQL command to select SQL on a table in another FileMaker file. For a file with many fields and quite a lot data in some fields, this times as about 1 minute just for SQL SELECT command for 1,000 rows. I haven't used MBS (monkeybread software) plugin yet, but I wonder if anyone knows if it is significantly faster? I.e. BE_FileMakerSQL vs. MBS(FM.SQL.Execute..) - is one significantly faster? Here's the code to select 1,000 rows for reference, $timer_seconds comes as about 60 seconds here. If I export same table internally in FileMaker us
  3. 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 th
  4. Hi all Apologies in advance if this is a vague question, or if it's been extensively documented, but I could do with some basic advice on moving filemaker tables to SQL. In terms of database development, I have only really known FileMaker. We have a 60 table, 25 user FileMaker database which takes care of most if not all of the company's operations. There is now talk of allowing other platforms (such as PowerApps/PowerBI) access to the data. One such reason is other people are interested in making apps/applets that serve specific functions. This would be simple if the data were
  5. 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 ; ""
  6. Looking for someone to help setup a simple connection to a sql database on cpanel for our Ranch to connect to cattle listings on our website.. I'll be using fmp12 server or content for now just establishing a connection via fmp12 client on windows 7 pro machines. Specifically I am having trouble establishing a DSN connection from any of my machines and have opened ports / given access in cpanel to my static IP. I have also updated MS ODBC drivers on one of the machines to see if that made a difference. It may be something quick for an expert with experience or it might end up being
  7. I am in the process of connecting an existing FileMaker database to a SQL server that is behind a web interface. I wanted to know if there is a way to send a command from the web interface to kick off a sync. For example, a user opens a record on the web, makes some changes, hits a save button. How do I get that "Save" to trigger a sync? And can I get it to sync that single record or do I need it to sync all records that have changed? Thanks in advance, Mike
  8. Hi all I'm trying to export my filemaker records to my external sql database via the execute sql script step but am getting a number of errors. I can see my external ODBC source and can generally write to it (I can go to the respective layout and click 'new record'). My query basically as follows: "INSERT INTO dbo.Log ( dbo.Log.log_id, dbo.Log.log_case_idf, dbo.Log.Labour_TOTALS_Labour_Cost, dbo.Log.Labour_TOTALS_Item_Sale) SELECT Log_Log.log_id, Log_Log.log_case_idf, Log_Log.Labour_TOTALS_Labour_Cost, Log_Log.Labour_TOTALS_Item_Sale, FROM Log WHERE Log_Log.Log_Type = '2'" M
  9. 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 = Ex
  10. Hi all I'm trying to connect to an Azure SQL database from FileMaker 16. I've added the DSN via ODBC 32bit (see pic). However, when I go to File>Manage>External Data Sources, it doesn't show the DSN. Where am I going wrong? Thanks in advance
  11. 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
  12. 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 ( "S
  13. 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 & ", '" &
  14. 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
  15. 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
  16. 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
  17. 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
  18. 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 ( "
  19. 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_Expi
  20. 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 b
  21. Hi all Can someone tell me if this query is correct? I'm trying to find all currently open Cases, but only those that belong to a personnel whose 'exclude_from_stats' field is null. So far this does seem to give me the correct results, however I am a bit uncertain as I have been having problems recently defining criteria in related records. It seems that you sometimes have to resort to different types of joins or you run the risk of excluding/including too many records. SELECT COUNT (*) FROM Cases C JOIN Personnel P ON p.personnel_ID = c.User_IDF WHERE c.Closed IS NULL AND p.e
  22. 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
  23. 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
  24. 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_
  25. 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
  • Create New...

Important Information

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