Jump to content


  • Content Count

  • Joined

  • Last visited

  • Days Won


beverly last won the day on July 8

beverly had the most liked content!

Community Reputation

24 Excellent

About beverly

  • Rank

Profile Information

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

FileMaker Experience

  • Skill Level
  • FM Application
    16 Advanced

Platform Environment

  • OS Platform
  • OS Version

Recent Profile Visitors

6,615 profile views
  1. beverly

    XML with XSLT, but error on import

    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/
  2. beverly

    Query data

    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
  3. beverly

    SQL to FQL rules

    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
  4. beverly

    Simple Join

    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
  5. beverly

    Export to Excel with XSL

    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.
  6. beverly

    Export to Excel with XSL

    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
  7. beverly

    Does FQL actually work?

    Use the files (builder or explorer) to help you write the queries. beverly
  8. beverly

    Large MSSQL data set error

    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
  9. beverly

    Does FQL actually work?

    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
  10. 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.
  11. beverly


    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
  12. beverly


    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
  13. beverly

    Is anyone making a living off FM?

    due to FM being one way to connect otherwise unconnected systems, many of us are versed in other systems as well. I speak web, I speak SQL. Of my job, 50% is FileMaker projects (web and non-web). But surprise, SURPRISE! many of the 'non-FileMaker' jobs/work may get some FM just because it's easy to use. Perhaps planning the next big web project is done with FM. Or 'the book" - a whole bunch was stored in FM for sorting searching, etc. Short answer: yes
  14. This is a X-post on FM Community: https://community.filemaker.com/message/658922 Please see all replies there before duplicating here. While there may be people here that have answers different, it's important to let everyone know you have cross-posted to more than one forum. Thanks!

Important Information

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