Jump to content


  • Posts

  • Joined

  • Last visited

  • Days Won


beverly last won the day on March 22 2019

beverly had the most liked content!

Profile Information

  • Title
    Web and Database Developer
  • Gender
    Not Telling
  • Location
    in the ether

FileMaker Experience

  • Skill Level
  • FM Application

Platform Environment

  • OS Platform
  • OS Version

Recent Profile Visitors

7,638 profile views

beverly's Achievements

Rising Star

Rising Star (9/14)

  • Conversation Starter
  • First Post
  • Collaborator
  • Week One Done
  • One Month Later

Recent Badges



  1. ID is a reserved word. Try escape quoting the "name": WHERE Daily_Totals_From_Date = ? ( AND \"ID\" = ? )
  2. The "grammar/schema/format" for the XML that FileMaker uses for import is FMPXMLRESULT. See this help topic for more information. You must use an XSLT that "maps" the XML source to the fields in FileMaker. https://fmhelp.filemaker.com/help/17/fmp/en/FMP_Help/xml-format.html Also check this "universal XSLT" for an example: http://jensteich.de/fmfaq/export/universal-xslt-fur-den-import-beliebiger-xml-daten/
  3. I have SQLEditor (MacOS) http://www.malcolmhardie.com/ You choose the dialect ("FileMaker"). The FileMaker Community Forum has people talking about Razor, tho I've not tested it. https://razorsql.com/ Beverly
  4. https://filemakerhacks.com/2012/11/02/pdf-version-of-the-missing-fm-12-executesql-reference/ See the above blog for some suggested "editors". There are many threads here, there are many threads on several forums (the above blog, included). Several developers got our hands dirty and tested what works. SeedCode has an update for (new clauses in 13): https://www.seedcode.com/filemaker-13-sql/ The SQL guide(s) in FM 12-17 are great, but goes into details that work with the SQL queries to other dbs and using FMServer as ODBC/SQL source. Since ExecuteSQL() only uses the SELECT statement, we've all narrowed down what does/doesn't work. Beverly
  5. Barbara and Kevin are correct. Note that Kevin did NOT use a JOIN construct. If you have the value of therapists::zk_TherapistID as the field directly or set as a variable before calling the query, then you don't actually need the JOIN. However if you are passing a direct value in the parameter, it must have context. So presumably you are IN thrapists table (or layout based on a TO with therapists as the base table) to make the query, even though you ask for a sum from another table. Passing the parameter as a variable, however, you can be in any context and the query will work. HTH, Beverly
  6. You can call me comment if that helps. LOL You are LOOPing the ROW/records in your XML. That works for 'normal rows' used where needed. However, you are NOT testing to see if the COL/field is your flag field. You don't want it in the "columns", but used as a trigger to make the style as desired. @comment Got you started with the xsl:If. I might use the xsl:choose, xsl:when, & xsl:otherwise is some places where the "if" is not sufficient. You are correct that XSLT is a different 'language' and getting it to work can be a challenge sometimes. This can give you a few pointers: https://www.w3schools.com/xml/xsl_intro.asp Using XSLT (version 1.0) with XML export (or import) and FileMaker can be narrower than you might find if you just search for how to do XSLT in other applications.
  7. comment is correct that this is a spreadsheet markup question. However the Export from FileMaker can be done very well with XML and XSLT that maps the fields exported into the elements needed for markup. I do it all the time. There will NOT be a template that one can use to make it any easier, however. Part of the issue comes from FileMaker where the data exported is not formatted text - the XML is plain text. There IS a work-around using calculated HTML (styled) which Excel can open: https://filemakerhacks.com/2017/04/04/user-friendly-excel-exports-part-7/ (and previous parts of this article may help as well). But if you want to export as XML and have the XSLT set the styles, you also have to export a field with the "flag" that says this data should be styled bold or this data should be Verdana 36, for example. Unless it's something like a header row that will always be a particular format - that can be "hard-coded" into the XSLT. It's the dynamic parts that may not be able to caluclate-on-the-fly and require a specific field of data to say what the formatting should be. That can be read in the XML by the XSLT and used in the <ss:Style> for particular Rows, Columns, or Cells. @fmdataweb what determines a data point to be a particular style? Beverly
  8. Use the files (builder or explorer) to help you write the queries. beverly
  9. Set up a View in the MySQL and narrow your set first? Is that what you mean by What about this does not work? beverly
  10. The fm_sql_reference (versions 13, 14, 15, & 16!) is only partially helpful. It applies more for using FM as an ODBC source. The "missing" reference is a compliation of many developers pounding (testing) the heck out of the ExecuteSQL function. What works, what doesn't. Get the PDF and the example files. Read the comments from others on the blog. Kevin (and others) have a ton more blogs on ExecuteSQL! And there are a few "helper" files that will guide you in writing the queries correctly (here are the current/correct links): SQL Builder (this forum) http://fmforums.com/forum/files/file/25-sql-builder/ SQL Explorer (seedcode) https://www.seedcode.com/filemaker-sql-explorer/ And if you need a Japanese version of the "missing" reference? http://notonlyfilemaker.com/2014/01/missing-executesql/ beverly
  11. the problem may well come from limitations in the ExecuteSQL function. Nested functions with the aggregates ( SUM, COUNT, etc.) don't always work as expected. You may well do the SUM as a caclulation in Clients (use ExecuteSQL) to get the time in the last 30 days. Or use a Let() statement to get the sum of the time (no join needed) in the last 30 days and push to a variable that can be then divided by the Seats. Use this field to FIND the Client with the "most". I probably would NOT use a calculation (unstored), but a looping script to Set Field.
  12. IMPORT does NOT require to know how many records are being imported (not for tab, not for csv, not for XML). Thanks for the alternatives, guys. OP still needs to learn the XPATH and XSLT to understand what we've advised. beverly
  13. 1. the elements in the xml source do NOT match the elements "called" in the XSLT what is current/id/@value ? when the source has data/post/id learn about the basics of XSLT and XPATH: https://www.w3schools.com/xml/xpath_intro.asp https://www.w3schools.com/xml/xsl_intro.asp <COL><DATA><xsl:value-of select="./id"/></COL></DATA> 2. to "loop" the xsl:for-each is used around the ROW elements in your XSLT and the select needs to be a valid XPATH, such as data/post so that you will get one row/record imported with everly post element in your XML source <xsl:for-each select="data/post"> <ROW> <COL><DATA><xsl:value-of select="./id"/></DATA></COL> ..... </ROW> </xsl:for-each> beverly
  • Create New...

Important Information

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