Jump to content


  • Content count

  • Joined

  • Last visited

  • Days Won


beverly last won the day on June 22 2016

beverly had the most liked content!

Community Reputation

22 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,096 profile views
  1. 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
  2. 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.
  3. 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
  4. Does FQL actually work?

    Use the files (builder or explorer) to help you write the queries. beverly
  5. 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
  6. 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
  7. 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.

    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

    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
  10. 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
  11. 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!
  12. SVG Icon Manager

    So this IS an old thread, but still valuable! Is there a way to IMPORT the SVG files (a folder) that we can find "out there", so we can study them and verify that they are FMP-icon-ready? Thanks!
  13. Completely lost with ExecuteSQL

    You cannot have spaces in names try \"Cost Price\"
  14. + wim and the third option by webko. ESS is a "two-way sync" if you will with FM and the MySQL. you can push or pull data between the two. If the data is in a FileMaker table (TO), then Set Field with it into the MySQL table (TO). ESS makes the data in the external SQL source behave much like any other "native" table in FileMaker. if you can move data from one native table to another in FileMaker, you can do the same with ESS (provided you have the proper permissions, of course!) NO SQL commands (via xDBC) or XML/PHP needed. in addition, should you wish, you can use Import and Execute SQL script steps to exchange data between FM tables and SQL tables (not ESS, but SQL queries) beverly
  15. Execute SQL Insert Into

    yep on webko and wim! as I said, a query is TEXT. however you can calculate (or script gather) the text to make the query work for single or multiple records is ok. I have used both methods (even before there was "ESS")! beverly

Important Information

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