Smef

Members
  • Content count

    1,053
  • Joined

  • Last visited

  • Days Won

    6

Smef last won the day on August 20 2011

Smef had the most liked content!

Community Reputation

13 Good

About Smef

  • Rank
    member
  • Birthday

Profile Information

  • Gender
    Male

Contact Methods

  • Website URL
    http://bluefeathergroup.com
  • Skype
    Smef42

FIleMaker Profile

  • FM Application
    15 Advanced
  • Platform
    Cross Platform
  • Skill Level
    Expert
  • Certification
    10
    11
    12
    13
    14
    15
  • Membership
    TechNet
    FileMaker Business Alliance
  1. HA! I forgot to put that in there. I'll update this. Thanks! The work is all out of the office, not travel. Clients are all over the country (and sometimes outside of it), but it's almost all remote to their servers and things.
  2. I've got this on my list of things to do as well. It should be a pretty similar process.
  3. Blue Feather (https://bluefeathergroup.com) in Johns Creek, GA is looking to hire a FileMaker database developer. This is a client facing position where you will be gathering requirements and developing solutions to meet client needs. We’re looking to find someone with some FileMaker experience that we can train to become a very advanced developer, becoming proficient in complex integration solutions and technologies outside of just FileMaker. Daily work will primarily consist of business application development and design using FileMaker scripting, calculations, and layout tools. Our FileMaker solutions are custom-made for each client to meet their specific needs, so there will be new programming challenges each day as we work with clients in many different industries with different technical requirements. Work is currently out of our home office. Be ok with going to someone’s house every day for work, though it is a reserved, nice, office-focused area. Business hours are 10am - 6pm Monday through Friday. A person in this position will be responsible for: Custom business application development and design from scratch using FileMaker scripting, calculations, and layout tools Design and layout of user interfaces for business applications such as CRM systems A little bit of web design and development Managing client projects, requests, and requirements Tracking time working on projects for clients Maintaining current FileMaker Developer certification Requirements: Strong math and logic skills FileMaker Development Experience Demonstrable understanding of general software development and computer programming Desire to learn advanced development techniques with FileMaker and other platforms Nice to Have: PHP Java HTML / CSS / Javascript C++ Compensation Pay will range from $30k - $70k DOE and ability to earn bonuses by taking on projects Lots of one-on-one training to develop new skills Total of 15 paid sick/vacation days to use as you please Paid holidays Company cell phone, laptop Full-time hours, but flexible schedule available after you're able to work on projects on your own Please send your resume to jobs@bluefeathergroup.com
  4. Will this Run as Administrator? I don't think FileMaker Server normally has permission to edit the CStore folder, which is why "Run as Administrator" is required.
  5. It's definitely doable in a similar fashion, I just haven't gotten to doing it yet. I may have a Mac server available soon I can do testing on, and will definitely post when I get a script available for Mac.
  6. The warning FMI gives about using non-supported SSL certificates is that FileMaker Pro and Go clients won't be able to verify the SSL certificate. I believe we've solved this issue with this solution, and FileMaker clients as early as 13 are able to successfully verify the SSL certificate. There could be other issues lurking somewhere, but we've got this deployed in a number of live servers with no issues so far. It's definitely a good idea to do some testing after deploying this, all the same. If anyone does encounter any issues, please post them here, on the article's comment section, or send a private message or email to me.
  7. Hello Everyone, I've written a PowerShell script to allow you to get a free SSL certificate from Let's Encrypt to use with FileMaker Server. You can schedule this to run every few months and renew your certificate automatically. Now there's no reason to keep using that default certificate. Check out the post for instructions! How to Use Let’s Encrypt SSL Certificates with FileMaker Server | Blue Feather - FileMaker Developer, Android, Web EDIT: One compatibility note for everyone - While it looks like it's all compatible with FileMaker Pro 13-15, only FileMaker Go 15 is compatible. FileMaker Go 14 is unable to connect with these certificates installed. I'd recommend using FM Go 15 anyway, but it's something to be aware of if you're still using FM Go 14.
  8. Let’s Encrypt is a non-profit certificate authority with the mission of spreading the SSL love across the internet. Though they’re not officially supported, we can use Let’s Encrypt to get free SSL certificates to use with FileMaker Server. We will use a PowerShell script and the Windows Task Scheduler on Windows Server 2012 R2 to retrieve and automatically renew SSL certificates through Let’s Encrypt to make sure our connections to FileMaker Server are secure. With this, there’s no reason anyone should have an invalid SSL certificate on their FileMaker Server deployment! WARNING: FileMaker does not list Let’s Encrypt as a supported SSL vendor. We’ve had no problems with using these SSL certificates, but can’t make any guarantees for you. This is an experimental script and procedure. Please proceed with the use of this PowerShell script and Let’s Encrypt SSL certificates at your own risk. Here’s a summary of what we’re going to need to do: Download the GetSSL.ps1 PowerShell script Install the Microsoft PowerShell Package Manager Edit the GetSSL.ps1 file Change Windows security to allow PowerShell Scripts to run Install ACMESharp Run the PowerShell Script Change the FileMaker Server SSL Connections settings Set up a schedule to renew the SSL certificate Check out the video below for a walkthrough and continue reading for additional instructions. 1. Download the GetSSL.ps1 PowerShell script First, you’ll need a copy of the GetSSL PowerShell script. Download the file using the link below and save it on your server where you’ll want to get the SSL certificate. WARNING: This is an experimental script and procedure, and SSL certificates from Let’s Encrypt are not officially supported by FileMaker, Inc. Please download and use this script with the understanding that it comes with no guarantees or warranties, and that you are doing so at your own risk. Blue Feather, Let’s Encrypt, nor anyone else are responsible for what happens to your server or systems when using this script. Download the GetSSL PowerShell script 2. Install the Microsoft PowerShell Package Manager Windows Server 2012 R2 does not have the PowerShellGet module installed by default, and so we must download it from Microsoft. Visit Microsoft’s download page or PowerShell Gallery to get the latest version of PowerShell for PS 3 and 4. Download and install the very small file. This will allow us to more easily install the modules we need to make this work. 3. Edit the GetSSL.ps1 file The script file needs to be edited so that it know the address you wish to get an SSL certificate for. Right-click on the ps1 file and select edit to open a text editor. Change the address, email address, and (if necessary) the FileMaker Server install path variables to reflect your server’s information and your contact information. Let’s Encrypt will use this contact information to reach out to you if there is a problem with the SSL certificate that they have issued to you. 4. Change Windows security to allow PowerShell Scripts to run Windows Server will not allow you to run PowerShell scripts by default, so you’ll need to modify your security settings to allow this. Open PowerShell or PowerShell ISE as Administrator using the “Run as Administrator” option and enter the command: Set-ExecutionPolicy -Scope LocalMachine Unrestricted Enter “y” and press enter to accept the security warnings that appear. Note: PowerShell must be Run as Administrator for this step and all subsequent steps, or you will receive errors. Be sure you are running PowerShell or the PowerShell ISE as Administrator using the “Run as Administrator” option, not just a user named Administrator. 5. Install ACMESharp We’ll be using the ACMESharp PowerShell module to communicate with Let’s Encrypt to get our SSL certificate. Install the ACMESharp PowerShell module using the command: Install-Module -Name ACMESharp Enter “y” and press enter to accept the security warnings that appear. 6. Run the PowerShell Script WARNING: Running this PowerShell script will restart your FileMaker Server service, abruptly disconnecting any active users. Make sure that nobody is connected to your server before you run this script. With ACMESharp installed and our security settings adjusted, we’re now ready to run the PowerShell script. Make sure nobody is connected or using your FileMaker server and then run the GetSSL.ps1 PowerShell script by navigating to the directory you have it copied to in your PowerShell window and entering: .\GetSSL.ps1 A bunch of text will scroll by in the PowerShell window as the script requests, fetches, and installs your SSL certificate. Your FileMaker Server service will then be stopped and started again automatically. Your SSL certificate should now be installed! Go to your FileMaker Server admin console to make sure you’re seeing the new SSL certificate. You may need to close and re-open your browser if you had the page open already. 7. Change the FileMaker Server SSL Connections settings The SSL certificate is installed, but we want to force FileMaker Pro and Go clients to connect securely to our server. Log in to your newly secured FileMaker Server admin console. Select the Database Server options from the list on the left and then the Security tab at the top of the page. Check the “Use SSL for database connections” option (as well as “Use SSL for progressive downloading” if you would like) to force FileMaker Pro and Go clients to use a secure connection when connecting to this server. Save your changes and then restart your FileMaker Server service on your server machine. Your FileMaker Pro clients should now show the green lock icon when logging in to this server, indicating that the connection is secure. 8. Set up a schedule to renew the SSL certificate SSL Certificates from Let’s Encrypt are only valid for 90 days and must be renewed before that time. Let’s Encrypt does this purposefully to encourage automation and increase security. In that spirit, we should set up an automatic renewal for our SSL certificates so that we don’t need to manually re-run this every couple of months. This process is similar to setting up a scheduled script in FileMaker Server. Move the GetSSL.ps1 file to a relatively permanent location on your server and then open the Task Scheduler, which we will use to set up a new scheduled task. Once you have the Task Scheduler open, right-click on the Task Scheduler Library icon on the left side of the window and select the “Create Basic Task” option. Give your task a name and description so that you can recognize what is is and then press Next. Select a frequency for this task to run, such as Monthly and enter the times you wish the schedule to run on the next window. Enter “PowerShell” in the “Program/script:” field. Enter the path to the GetSSL.ps1 script in the “Add arguements (optional)” field. This should be a full path like C:\GetSSL.ps1. Click the next button to review, and select the “Open Properties” checkbox. Complete the setup and the properties window will open for you to make final adjustments to this schedule. You can edit the triggers and scheduling here, but the important thing we need to do is change the security options. Select the “Run whether user is logged o nor not” radio button and enter your password to allow the script to run even if you’re not logged into the machine. Also be sure to check the “Run with highest privileges” option to make the script Run as Adminstrator, which is required for the script to work properly. Done! That’s all that you need to do! Your script should run automatically at your scheduled time to renew your SSL certificate with Let’s Encrypt. Keep in mind that your FileMaker Server service will be restarted after getting the new SSL certificate, so be sure to schedule it for a time when people will not be active in your system. This is an early version of this script and there is quite surely room for improvement. Please let me know if you have any suggestions or run into any issues using this scripting. Let’s make the FileMaker community a secure one! View the full article
  9. We had a special presentation at the Atlanta FileMaker Developers group meeting last night. Andy LeCates and Darrin Quick from FileMaker, Inc. came by to present some of the new features in FileMaker 14 and answer questions from the attendees. We live-streamed the event on YouTube and Hangouts On Air, where we also had some remote participants watching and asking questions. The recording of the live-stream is available on YouTube below. View the full article
  10. http://bluefeathergroup.com/blog/importing-xml-data-into-filemaker-using-xslt-xsl-style-sheets/ I've written a new post about how to create XSL Style Sheets so that you can import data from XML data sources into FileMaker. Please let me know if you have any comments.
  11. I've just written a brief guide on importing data and creating XSL stylesheets for FileMaker. Hopefully this can be helpful for anyone else who comes across this. http://bluefeathergroup.com/blog/importing-xml-data-into-filemaker-using-xslt-xsl-style-sheets/
  12. FileMaker is capable of importing data from many different sources. One of the most useful is importing from XML data sources, which can be a great way to get data from non-FileMaker resources into your database. XML is used by all sorts of programs and applications, and so you may encounter it when working with many non-filemaker services or data sources. About XML and XSLTXML is a standard way of making writing annotations about data, but each XML document can be formatted in completely different ways. This is similar to how how filing cabinets usually store files using hanging folders which may contain folders, which may contain paper documents, but the way a particular person or company organizes files and folders in a cabinet can be very different from company to company and person to person. An XSL style sheet and XSL Transformations (XSLT) provide instructions for applications about how to rewrite and XML file into a format the application can understand. The FileMaker XML Data FormatFileMaker has a defined XML style of what “record data” looks like. It only knows how to import records as XML in this one particular style of XML formatting, called a Document Type Definition (DTD). This specific XML DTD which FileMaker understands is named FMPXMLRESULT. Any data that you want FileMaker to import must match the FMPXMLRESULT DTD, or else FileMaker can’t read it and won’t know what to do with the information. We can use XSL Transformations (XSLT) to convert (transform) non-FileMaker XML data into a FileMaker-friendly FMPXMLRESULT format. FileMaker provides a description of the FMPXMLRESULT format at http://www.filemaker.com/help/13/fmp/en/html/import_export.17.33.html This page describes which tags and attributes are required for FileMaker to successfully read an XML document in the FMPXMLRESULT format. Let’s take a look at an example of FMPXMLRESULT-styled XML data. For this tutorial I’ve created a simple FileMaker weather database with one table with _kp_ID, City, Temperature, Humidity, Wind Speed, Wind Direction, and Clouds fields. I’ve exported these fields (other than the _kp_ID field) using FileMaker’s Export Records function to export the data as XML to get a sample for us to reference. Download this sample file and we’ll use it to test our XML transformation and see the exported XML. Download <?xml version="1.0" encoding="UTF-8" ?> <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"> <ERRORCODE>0</ERRORCODE> <PRODUCT BUILD="04-17-2014" NAME="FileMaker" VERSION="ProAdvanced 13.0v3"/> <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="Weather.fmp12" RECORDS="1" TIMEFORMAT="h:mm:ss a"/> <METADATA> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="City" TYPE="TEXT"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Temperature" TYPE="NUMBER"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Humidity" TYPE="NUMBER"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Wind Speed" TYPE="NUMBER"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Wind Direction" TYPE="NUMBER"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Clouds" TYPE="TEXT"/> </METADATA> <RESULTSET FOUND="1"> <ROW MODID="1" RECORDID="1"> <COL> <DATA>Alpharetta</DATA> </COL> <COL> <DATA>303.69</DATA> </COL> <COL> <DATA>40.2</DATA> </COL> <COL> <DATA>6.7</DATA> </COL> <COL> <DATA>170</DATA> </COL> <COL> <DATA>few clouds</DATA> </COL> </ROW> </RESULTSET> </FMPXMLRESULT>This is data that was exported from a FileMaker database. It shows the format that we’re going to need to match for FileMaker to be able to read our XML. Conveniently, Filemaker does not require all of the XML attributes that it export to be present when importing data, which makes our job easier. In the FMPXMLRESULT format there are two areas we need to pay particular attention to. The first part is inside the tags. The <FIELD /> tags inside this section define the fields that each record contains. The attributes of each <FIELD /> show the name of the field and the type of data (text, number, etc). The other attribute data of each <FIELD /> is useful when bring data from FileMaker to other systems, but not necessary for us to use when importing data, so we’ll ignore it for now. The order of the <FIELD /> tags is important, as the data values further down are read and matched with the <FIELD /> tags to determine what the data is. The <FIELD /> tags are essentially the column headers or column titles. The first <FIELD /> is matched with the first piece of column data, the second <FIELD /> is matched with the second piece of column data, etc. Lower down we have the <RESULTSET> </RESULTSET> tags which contain the records and the field data for each record. Records (rows) are indicated by <ROW></ROW> and each field (column) in the record is indicated with <COL></COL>. The data contained within each field for each record is contained in <DATA></DATA> tags. We’ll have one set of <ROW></ROW> tags for each record we’re importing, and each one of those should have the same number of <COL></COL> and <DATA></DATA> tags as we have <FIELD /> tags in the <METADATA></METADATA> section at the top. This means we’ll have one column for each field that we’re importing. Let’s get some XML data to import! Openweathermap.org provides XML weather data which we can use as a source for our import. XML weather data for Alpharetta is available at http://api.openweathermap.org/data/2.5/weather?q=Alpharetta,us&mode=xml which we can use for this example. <?xml version="1.0" encoding="utf-8"?> <current> <city id="4179574" name="Alpharetta"> <coord lon="-84.29" lat="34.08"/> <country>US</country> <sun rise="2014-09-02T11:11:50" set="2014-09-03T00:01:23"/> </city> <temperature value="303.69" min="301.48" max="305.93" unit="kelvin"/> <humidity value="40" unit="%"/> <pressure value="1020" unit="hPa"/> <wind> <speed value="1.62" name="Light breeze"/> <direction value="279.503" code="W" name="West"/> </wind> <clouds value="40" name="scattered clouds"/> <visibility/> <precipitation mode="no"/> <weather number="211" value="proximity thunderstorm" icon="11d"/> <lastupdate value="2014-09-02T18:11:15"/> </current>This XML source provides all of the values we want to bring into the weather database, but the XML is formatted very differently than the FMPXMLRESULT format Filemaker requires. An XML transformation though the use of XSLT will solve that problem! Transform the XMLCreate a new text file on your computer and name it with a weather-transform.xslt. This file will provide FileMaker with the instructions on how to transform the XML data to get it into the FMPXMLRESULT format before trying to import it. Paste the code below into this document. This will be the base framework for building our XML transformation upon, and is required in an xslt document. <?xml version='1.0' encoding='UTF-8'?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> </xsl:template> </xsl:stylesheet>Our transform instructions are going to go inside the <xsl:template></xsl:template> tags. <xsl:template></xsl:template> tells FileMaker to start re-writing this xml document as soon as it hits the value set in the “match” attribute. In this case it’s set to match “/” which means “root” similar to a folder structure on Mac or Linux. Since we’re matching root the original XML is essentially going to be completely replaced with whatever we write. The hierarchy of tags within other tags are indicated using XPath, a notation format which is similar to how you would write out paths for files and folder in a computer filesystem. A lot of this document is going to be exactly the same as the data that we exported from FileMaker before, so copy and paste that XML inside the <xsl:template></xsl:template> tags. Delete the <?xml version=’1.0′ encoding=’UTF-8′?>, <ERRORCODE></ERRORCODE>, <PRODUCT />, and <DATABASE /> tags, as those are not required tags and will be ignored by FileMaker. Also remove EMPTYOK, MAXREPEAT attributes from the <FIELD /> tags, FOUND=”1″ from <RESULTSET>, MODID=”1″ RECORDID=”1″ from <ROW>. None of these attributes are required, so we can remove them to keep our document simpler and not lead to any confusion if the values don’t match our data. You document should now look like this: <?xml version='1.0' encoding='UTF-8'?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"> <METADATA> <FIELD NAME="City" TYPE="TEXT"/> <FIELD NAME="Temperature" TYPE="NUMBER"/> <FIELD NAME="Humidity" TYPE="NUMBER"/> <FIELD NAME="Wind Speed" TYPE="NUMBER"/> <FIELD NAME="Wind Direction" TYPE="NUMBER"/> <FIELD NAME="Clouds" TYPE="TEXT"/> </METADATA> <RESULTSET> <ROW> <COL> <DATA>Alpharetta</DATA> </COL> <COL> <DATA>303.69</DATA> </COL> <COL> <DATA>40.2</DATA> </COL> <COL> <DATA>6.7</DATA> </COL> <COL> <DATA>170</DATA> </COL> <COL> <DATA>few clouds</DATA> </COL> </ROW> </RESULTSET> </FMPXMLRESULT> </xsl:template> </xsl:stylesheet> This would technically work, but it would be bringing in the same data each time, and wouldn’t actually be reading the data in from the XML source. We’d be re-writing the XML data source with static data, so we need to replace the values with the actual data values from our source. The <xsl:value-of /> tag will tell FileMaker to read a specific value from the original XML to replace when writing out the transformed XML data. This is the key part that lets us transform the data into the new format we’ve written into the XML transformation. Starting with the city “Alpharetta” we want to replace the text with the actual city name from the XML we’re importing. This value is located in the “name” attribute of the <city> tag, which is inside the <current> tag. The XPath expression “current/city/@name” will get us this value. Putting this value in the <xsl:value-of /> tag will tell FileMaker to replace the tag with the actual city name. The tag would be <xsl:value-of select=”current/city/@name”/>. Replace the text “Alpharetta” with that tag in the xslt document. <?xml version='1.0' encoding='UTF-8'?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"> <METADATA> <FIELD NAME="City" TYPE="TEXT"/> <FIELD NAME="Temperature" TYPE="NUMBER"/> <FIELD NAME="Humidity" TYPE="NUMBER"/> <FIELD NAME="Wind Speed" TYPE="NUMBER"/> <FIELD NAME="Wind Direction" TYPE="NUMBER"/> <FIELD NAME="Clouds" TYPE="TEXT"/> </METADATA> <RESULTSET> <ROW> <COL> <DATA><xsl:value-of select="current/city/@name"/></DATA> </COL> <COL> <DATA>303.69</DATA> </COL> <COL> <DATA>40.2</DATA> </COL> <COL> <DATA>6.7</DATA> </COL> <COL> <DATA>170</DATA> </COL> <COL> <DATA>few clouds</DATA> </COL> </ROW> </RESULTSET> </FMPXMLRESULT> </xsl:template> </xsl:stylesheet>In this state FileMaker would use the .xslt to read the original XML and then re-write it in the new format, replacing the city name with the actual data from the source XML. The rest of the data would still be the static numbers and text inside the <DATA></DATA> tags. Let’s do the same thing for the other values that we did for the city name, replacing each value with a <xsl:value-of select=”” /> tag with select attribute matching the source of the data we want for the field. <?xml version='1.0' encoding='UTF-8'?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"> <METADATA> <FIELD NAME="City" TYPE="TEXT"/> <FIELD NAME="Temperature" TYPE="NUMBER"/> <FIELD NAME="Humidity" TYPE="NUMBER"/> <FIELD NAME="Wind Speed" TYPE="NUMBER"/> <FIELD NAME="Wind Direction" TYPE="NUMBER"/> <FIELD NAME="Clouds" TYPE="TEXT"/> </METADATA> <RESULTSET> <ROW> <COL> <DATA><xsl:value-of select="current/city/@name"/></DATA> </COL> <COL> <DATA><xsl:value-of select="current/temperature/@value"/></DATA> </COL> <COL> <DATA><xsl:value-of select="current/humidity/@value"/></DATA> </COL> <COL> <DATA><xsl:value-of select="current/wind/speed/@value"/></DATA> </COL> <COL> <DATA><xsl:value-of select="current/wind/direction/@value"/></DATA> </COL> <COL> <DATA><xsl:value-of select="current/clouds/@name"/></DATA> </COL> </ROW> </RESULTSET> </FMPXMLRESULT> </xsl:template> </xsl:stylesheet> This is a finished XSLT document! We can now use this xslt file to transform our data from the http data source into a format FileMaker can understand for importing. Run the ImportOpen Weather.fmp12 and select File->Import Records->XML Data Source… Specify http://api.openweathermap.org/data/2.5/weather?q=Alpharetta,us&mode=xml as the data source and weather-transform.xslt as the XSL Style Sheet. Press continue and you should be taken through the field-matching screen and be able to directly import your data from this XML data source. You can read more about XSLT at W3 Schools, which has lots more information about the different XSL transformation functions, such as filtering, for-each loops, ifs, and embedded templates. View the full article
  13. FileMaker is capable of importing data from many different sources. One of the most useful is importing from XML data sources, which can be a great way to get data from non-FileMaker resources into your database. XML is used by all sorts of programs and applications, and so you may encounter it when working with many non-filemaker services or data sources. About XML and XSLT XML is a standard way of making writing annotations about data, but each XML document can be formatted in completely different ways. This is similar to how how filing cabinets usually store files using hanging folders which may contain folders, which may contain paper documents, but the way a particular person or company organizes files and folders in a cabinet can be very different from company to company and person to person. An XSL style sheet and XSL Transformations (XSLT) provide instructions for applications about how to rewrite and XML file into a format the application can understand. The FileMaker XML Data Format FileMaker has a defined XML style of what “record data” looks like. It only knows how to import records as XML in this one particular style of XML formatting, called a Document Type Definition (DTD). This specific XML DTD which FileMaker understands is named FMPXMLRESULT. Any data that you want FileMaker to import must match the FMPXMLRESULT DTD, or else FileMaker can’t read it and won’t know what to do with the information. We can use XSL Transformations (XSLT) to convert (transform) non-FileMaker XML data into a FileMaker-friendly FMPXMLRESULT format. FileMaker provides a description of the FMPXMLRESULT format at http://www.filemaker.com/help/html/import_export.16.30.html. This page describes which tags and attributes are required for FileMaker to successfully read an XML document in the FMPXMLRESULT format. Let’s take a look at an example of FMPXMLRESULT-styled XML data. For this tutorial I’ve created a simple FileMaker weather database with one table with _kp_ID, City, Temperature, Humidity, Wind Speed, Wind Direction, and Clouds fields. I’ve exported these fields (other than the _kp_ID field) using FileMaker’s Export Records function to export the data as XML to get a sample for us to reference. Download this sample file and we’ll use it to test our XML transformation and see the exported XML. Download <?xml version="1.0" encoding="UTF-8" ?> <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"> <ERRORCODE>0</ERRORCODE> <PRODUCT BUILD="04-17-2014" NAME="FileMaker" VERSION="ProAdvanced 13.0v3"/> <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="Weather.fmp12" RECORDS="1" TIMEFORMAT="h:mm:ss a"/> <METADATA> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="City" TYPE="TEXT"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Temperature" TYPE="NUMBER"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Humidity" TYPE="NUMBER"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Wind Speed" TYPE="NUMBER"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Wind Direction" TYPE="NUMBER"/> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Clouds" TYPE="TEXT"/> </METADATA> <RESULTSET FOUND="1"> <ROW MODID="1" RECORDID="1"> <COL> <DATA>Alpharetta</DATA> </COL> <COL> <DATA>303.69</DATA> </COL> <COL> <DATA>40.2</DATA> </COL> <COL> <DATA>6.7</DATA> </COL> <COL> <DATA>170</DATA> </COL> <COL> <DATA>few clouds</DATA> </COL> </ROW> </RESULTSET> </FMPXMLRESULT> This is data that was exported from a FileMaker database. It shows the format that we’re going to need to match for FileMaker to be able to read our XML. Conveniently, Filemaker does not require all of the XML attributes that it export to be present when importing data, which makes our job easier. In the FMPXMLRESULT format there are two areas we need to pay particular attention to. The first part is inside the tags. The <FIELD /> tags inside this section define the fields that each record contains. The attributes of each <FIELD /> show the name of the field and the type of data (text, number, etc). The other attribute data of each <FIELD /> is useful when bring data from FileMaker to other systems, but not necessary for us to use when importing data, so we’ll ignore it for now. The order of the <FIELD /> tags is important, as the data values further down are read and matched with the <FIELD /> tags to determine what the data is. The <FIELD /> tags are essentially the column headers or column titles. The first <FIELD /> is matched with the first piece of column data, the second <FIELD /> is matched with the second piece of column data, etc. Lower down we have the <RESULTSET> </RESULTSET> tags which contain the records and the field data for each record. Records (rows) are indicated by <ROW></ROW> and each field (column) in the record is indicated with <COL></COL>. The data contained within each field for each record is contained in <DATA></DATA> tags. We’ll have one set of <ROW></ROW> tags for each record we’re importing, and each one of those should have the same number of <COL></COL> and <DATA></DATA> tags as we have <FIELD /> tags in the <METADATA></METADATA> section at the top. This means we’ll have one column for each field that we’re importing. Let’s get some XML data to import! Openweathermap.org provides XML weather data which we can use as a source for our import. XML weather data for Alpharetta is available at http://api.openweathermap.org/data/2.5/weather?q=Alpharetta,us&mode=xml which we can use for this example. <?xml version="1.0" encoding="utf-8"?> <current> <city id="4179574" name="Alpharetta"> <coord lon="-84.29" lat="34.08"/> <country>US</country> <sun rise="2014-09-02T11:11:50" set="2014-09-03T00:01:23"/> </city> <temperature value="303.69" min="301.48" max="305.93" unit="kelvin"/> <humidity value="40" unit="%"/> <pressure value="1020" unit="hPa"/> <wind> <speed value="1.62" name="Light breeze"/> <direction value="279.503" code="W" name="West"/> </wind> <clouds value="40" name="scattered clouds"/> <visibility/> <precipitation mode="no"/> <weather number="211" value="proximity thunderstorm" icon="11d"/> <lastupdate value="2014-09-02T18:11:15"/> </current> This XML source provides all of the values we want to bring into the weather database, but the XML is formatted very differently than the FMPXMLRESULT format Filemaker requires. An XML transformation though the use of XSLT will solve that problem! Transform the XML Create a new text file on your computer and name it with a weather-transform.xslt. This file will provide FileMaker with the instructions on how to transform the XML data to get it into the FMPXMLRESULT format before trying to import it. Paste the code below into this document. This will be the base framework for building our XML transformation upon, and is required in an xslt document. <?xml version='1.0' encoding='UTF-8'?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> </xsl:template> </xsl:stylesheet> Our transform instructions are going to go inside the <xsl:template></xsl:template> tags. <xsl:template></xsl:template> tells FileMaker to start re-writing this xml document as soon as it hits the value set in the “match” attribute. In this case it’s set to match “/” which means “root” similar to a folder structure on Mac or Linux. Since we’re matching root the original XML is essentially going to be completely replaced with whatever we write. The hierarchy of tags within other tags are indicated using XPath, a notation format which is similar to how you would write out paths for files and folder in a computer filesystem. A lot of this document is going to be exactly the same as the data that we exported from FileMaker before, so copy and paste that XML inside the <xsl:template></xsl:template> tags. delete the <?xml version=’1.0′ encoding=’UTF-8’?>, <ERRORCODE></ERRORCODE>, <PRODUCT />, and <DATABASE /> tags, as those are not required tags and will be ignored by FileMaker. Also remove EMPTYOK, MAXREPEAT attributes from the <FIELD /> tags, FOUND=”1″ from <RESULTSET>, MODID=”1″ RECORDID=”1″ from <ROW>. None of these attributes are required, so we can remove them to keep our document simpler and not lead to any confusion if the values don’t match our data. You document should now look like this: <?xml version='1.0' encoding='UTF-8'?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"> <METADATA> <FIELD NAME="City" TYPE="TEXT"/> <FIELD NAME="Temperature" TYPE="NUMBER"/> <FIELD NAME="Humidity" TYPE="NUMBER"/> <FIELD NAME="Wind Speed" TYPE="NUMBER"/> <FIELD NAME="Wind Direction" TYPE="NUMBER"/> <FIELD NAME="Clouds" TYPE="TEXT"/> </METADATA> <RESULTSET> <ROW> <COL> <DATA>Alpharetta</DATA> </COL> <COL> <DATA>303.69</DATA> </COL> <COL> <DATA>40.2</DATA> </COL> <COL> <DATA>6.7</DATA> </COL> <COL> <DATA>170</DATA> </COL> <COL> <DATA>few clouds</DATA> </COL> </ROW> </RESULTSET> </FMPXMLRESULT> </xsl:template> </xsl:stylesheet> This would technically work, but it would be bringing in the same data each time, and wouldn’t actually be reading the data in from the XML source. We’d be re-writing the XML data source with static data, so we need to replace the values with the actual data values from our source. The <xsl:value-of /> tag will tell FileMaker to read a specific value from the original XML to replace when writing out the transformed XML data. This is the key part that lets us transform the data into the new format we’ve written into the XML transformation. Starting with the city “Alpharetta” we want to replace the text with the actual city name from the XML we’re importing. This value is located in the “name” attribute of the <city> tag, which is inside the <current> tag. The XPath expression “current/city/@name” will get us this value. Putting this value in the <xsl:value-of /> tag will tell FileMaker to replace the tag with the actual city name. The tag would be <xsl:value-of select=”current/city/@name”/>. Replace the text “Alpharetta” with that tag in the xslt document. <?xml version='1.0' encoding='UTF-8'?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"> <METADATA> <FIELD NAME="City" TYPE="TEXT"/> <FIELD NAME="Temperature" TYPE="NUMBER"/> <FIELD NAME="Humidity" TYPE="NUMBER"/> <FIELD NAME="Wind Speed" TYPE="NUMBER"/> <FIELD NAME="Wind Direction" TYPE="NUMBER"/> <FIELD NAME="Clouds" TYPE="TEXT"/> </METADATA> <RESULTSET> <ROW> <COL> <DATA><xsl:value-of select="current/city/@name"/></DATA> </COL> <COL> <DATA>303.69</DATA> </COL> <COL> <DATA>40.2</DATA> </COL> <COL> <DATA>6.7</DATA> </COL> <COL> <DATA>170</DATA> </COL> <COL> <DATA>few clouds</DATA> </COL> </ROW> </RESULTSET> </FMPXMLRESULT> </xsl:template> </xsl:stylesheet> In this state FileMaker would use the .xslt to read the original XML and then re-write it in the new format, replacing the city name with the actual data from the source XML. The rest of the data would still be the static numbers and text inside the <DATA></DATA> tags. Let’s do the same thing for the other values that we did for the city name, replacing each value with a <xsl:value-of select=”” /> tag with select attribute matching the source of the data we want for the field. <?xml version='1.0' encoding='UTF-8'?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"> <METADATA> <FIELD NAME="City" TYPE="TEXT"/> <FIELD NAME="Temperature" TYPE="NUMBER"/> <FIELD NAME="Humidity" TYPE="NUMBER"/> <FIELD NAME="Wind Speed" TYPE="NUMBER"/> <FIELD NAME="Wind Direction" TYPE="NUMBER"/> <FIELD NAME="Clouds" TYPE="TEXT"/> </METADATA> <RESULTSET> <ROW> <COL> <DATA><xsl:value-of select="current/city/@name"/></DATA> </COL> <COL> <DATA><xsl:value-of select="current/temperature/@value"/></DATA> </COL> <COL> <DATA><xsl:value-of select="current/humidity/@value"/></DATA> </COL> <COL> <DATA><xsl:value-of select="current/wind/speed/@value"/></DATA> </COL> <COL> <DATA><xsl:value-of select="current/wind/direction/@value"/></DATA> </COL> <COL> <DATA><xsl:value-of select="current/clouds/@name"/></DATA> </COL> </ROW> </RESULTSET> </FMPXMLRESULT> </xsl:template> </xsl:stylesheet> This is a finished XSLT document! We can now use this xslt file to transform our data from the http data source into a format FileMaker can understand for importing. Run the Import Open Weather.fmp12 and select File->Import Records->XML Data Source… Specify http://api.openweathermap.org/data/2.5/weather?q=Alpharetta,us&mode=xml as the data source and weather-transform.xslt as the XSL Style Sheet. Press continue and you should be taken through the field-matching screen and be able to directly import your data from this XML data source. You can read more about XSLT at W3 Schools, which has lots more information about the different XSL transformation functions, such as filtering, for-each loops, ifs, and embedded templates. Source
  14. I had to do this recently for a client, and thought I'd write an article about my solution to this problem. Hopefully this will be helpful if anyone else needs to make a chart with a fixed number of x-axis labels with any number of records. http://bluefeathergroup.com/blog/fixed-number-of-x-axis-labels-in-a-chart-for-any-number-of-records/
  15. FileMaker has extremely functional charts, but one tricky part I’ve found is making the x-axis display in an appealing format. The values along the x-axis can overlapIf you have a lot of records. Summaries can help with this, but what if you don’t know how many points you’re going to have to plot, or the range which would need to be summarized? For example: A client of mine has an environment monitor that monitors temperature in a manufacturing facility to make sure it doesn’t get too warm for either people or machines. The manufacturing managers need to be able to view current temperatures, as well as historical temperatures over a time range to figure out work schedules. This time range could be a few hours, a few days, or a few weeks. There could only be a few data points which need to be charted, or hundreds. We can’t summarize the data by day or hour because of the potential ranges they could look at, which could be whatever they feel like. This leads to charts that look like this: So how can this be cleaned up? Really we only need to show x values at the beginning, end, and a few points evenly distributed between them. Well here’s a calculation to help you do that! If (Get(RecordNumber) = 1 or Let([$pointCount = 8; $fc = Get(FoundCount); $rn = Get(RecordNumber)]; $rn = (Round( $fc/$pointCount * Round($rn*$pointCount/$fc; 0); 0))) ; Environment::Read Timestamp ) Plug this calculation into the x-axis data field, replacing Environment::Read Timestamp with the data you want to show on the axis, and set the $pointCount value to be the number of points past the first data point that you want to show. You will always have this number of values displayed on the x-axis, no matter how many points you have in your found set. Much better! UPDATE: By popular demand, here’s the calculation and strategy for related records. You will need two unstored calcs for the record numbers where related::_c_RecordNum = get(RecordNumber) and MainTable::_c_RelatedRecordCount = Last(related::_c_RecordNum) If (related::_c_RecordNum = 1 or Let([$pointCount = 8; $fc = MainTable::_c_RelatedRecordCount; $rn = related::_c_RecordNum]; $rn = (Round( $fc/$pointCount * Round($rn*$pointCount/$fc; 0); 0))) ; related::xLabelVal ) Count(related::_kp_ID) can be used instead of MainTable::_c_RelatedRecordCount = Last(related::_c_RecordNum) if you really don’t want to make a new field, though that would be slower to calculate. View the full article