Jump to content
  • entries
    26
  • comments
    0
  • views
    4,134

Entries in this blog

 

Salvatore Colangelo, Goya – Insert from URL – Atlanta FileMaker Developers Group 10/3/18

Salvatore Colangelo from Goya presents on using cURL with FileMaker. He shows how to look at Insert From URL and the cURL options in a way that is applicable over and over again to different web services, like a framework. While looking at examples he also highlights some of the most common errors when trying to integrate.   The post Salvatore Colangelo, Goya – Insert from URL – Atlanta FileMaker Developers Group 10/3/18 appeared first on Blue Feather - FileMaker Developer, Android, Web.
View the full article

Smef

Smef

 

DevCon 2018 Recap – Atlanta FileMaker Developers Group 9/5/18

DevCon attendees talk about some interesting topics from DevCon 2018. You can see the videos from DevCon 2018 at the link below:
https://content.filemaker.com/e/526331/-devcon-session-materials-2018/8j2gcj/1043433363 The post DevCon 2018 Recap – Atlanta FileMaker Developers Group 9/5/18 appeared first on Blue Feather - FileMaker Developer, Android, Web.
View the full article

Smef

Smef

 

Jesse Barnum, 360Works – AWS Services with Filemaker. Atlanta Developers Group 8/1/18

This presentation is NOT about how to run FileMaker Server or FileMaker Cloud using Amazon Web Services (AWS). Instead, we will embark on a one hour tour of the incredibly powerful software as a service (SaaS) offerings from AWS. AWS is the largest cloud computing vendor in the world, with services such as real-time message queues, machine learning, and massive on-demand general computing services. You’ll learn how to interact directly with any of these services by sending AWS requests using the FileMaker Platforms Insert from URL, JSON, and encryption features. The post Jesse Barnum, 360Works – AWS Services with Filemaker. Atlanta Developers Group 8/1/18 appeared first on Blue Feather - FileMaker Developer, Android, Web.
View the full article

Smef

Smef

 

FileMaker 17 New Features – Atlanta FileMaker Developers Group 7/11/18

David Nahodyl from Blue Feather will be presenting on the new features in FileMaker 17. Come learn about the new scripting, behaviors, functions, and completely overhauled FileMaker Server interface which come as part of the latest version of FileMaker. The post FileMaker 17 New Features – Atlanta FileMaker Developers Group 7/11/18 appeared first on Blue Feather - FileMaker Developer, Android, Web.
View the full article

Smef

Smef

 

Atlanta FileMaker Developers Group 6/6/18 – 360Deploy 2

Joe Martin from 360Works demos their new product, 360Deploy 2. He shows how you can use 360Deploy 2 to automate deployment of your development files to your production servers with the click of a button. His demo also includes how to set up the 360Deploy 2. 360Deploy 2 can be purchased from 360Works. The post Atlanta FileMaker Developers Group 6/6/18 – 360Deploy 2 appeared first on Blue Feather - FileMaker Developer, Android, Web.
View the full article

Smef

Smef

 

Atlanta FileMaker Developers Group 5/2/18 – Web Scraping and Web Viewer Performance Issues

John Greene of United Acceptance Inc. presents on considerations regarding web scraping from FileMaker Web Viewer. Specifically, he addresses what happens when you loop through many hundreds or thousands of records and how to mitigate the risk of overwhelming the executing systems resources.   The post Atlanta FileMaker Developers Group 5/2/18 – Web Scraping and Web Viewer Performance Issues appeared first on Blue Feather - FileMaker Developer, Android, Web.
View the full article

Smef

Smef

 

Atlanta FileMaker Developers Group 4/4/18 – Relationship Rescue

Relationship Rescue What does it mean in the Set Field script step when it says “This calculation will be evaluated based on context determined at runtime.”? What is “context”, anyway? It turns out that there are different types of “context” that are relevant to FileMaker development. What do layouts have to do with the relationship graph? What do indexes have to do with relationships? How do the various types of relationships work?What’s the difference between defining a sort in the relationship graph and defining it in a portal? In this presentation Doug Gardner will discuss the answers to all these questions and talk about how to create a relational structure that scales to any size without becoming more difficult to read or use. Enjoy successful relationships without counseling.   The post Atlanta FileMaker Developers Group 4/4/18 – Relationship Rescue appeared first on Blue Feather - FileMaker Developer, Android, Web.
View the full article

Smef

Smef

 

FileMaker JSON, REST, and the Data API

Michael Layne from Gearbox Solutions presents on FileMaker’s new JSON functions. He does a deep dive into FileMaker’s new JSON functions and demos several examples of how they are using JSON for passing parameters, populating form data, validation and rollback, managing session info such as value lists as global. Michael also demos REST client and REST server with new data API.   The post FileMaker JSON, REST, and the Data API appeared first on Blue Feather - FileMaker Developer, Android, Web.
View the full article

Smef

Smef

 

FileMaker Project Planning & Online Support Resources – Atlanta FileMaker Developers Group

Todd Daniel present to the Atlanta FileMaker Developers Group this month. You can download a PDF of Todd’s presentation here. Learn “best practices” for planning and modeling your next FileMaker solution. In the second part of the presentation Toss talk about his favorite resources on the web. The idea is to not reinvent the wheel, but rather tap into existing assets to get your project off the ground faster. It is hoped that this presentation will lead into a lively discussion. The post FileMaker Project Planning & Online Support Resources – Atlanta FileMaker Developers Group appeared first on Blue Feather - FileMaker Developer, Android, Web.
View the full article

Smef

Smef

 

Amazon S3 and CloudFront integration – Atlanta FileMaker Developers Group 11/1/17

This month Earl Cook from Laser Solutions presented about his integration with FileMaker and Amazon S3 and CloudFront for managing access to video content in a FileMaker solution distributed worldwide.   The post Amazon S3 and CloudFront integration – Atlanta FileMaker Developers Group 11/1/17 appeared first on Blue Feather - FileMaker Developer, Android, Web.
View the full article

Smef

Smef

 

Atl Dev Group – FileMaker and Xero, Shopify, and WooCommerce integration

Andrew Duncan from Databuzz will be presenting on integrating FileMaker with Xero, Shopify, and WooCommerce using their products fmAccounting Link and fmEcommerce Link.   The post Atl Dev Group – FileMaker and Xero, Shopify, and WooCommerce integration appeared first on Blue Feather - FileMaker Developer, Android, Web.
View the full article

Smef

Smef

 

New Features in FileMaker 16 – Atl Dev Meeting 6/7/17

This month we covered some of the new features in FileMaker 16. Check out the recording of our monthly meeting below.   The sample files used in this demo are available for download from https://community.filemaker.com/docs/DOC-8138. This link is supposed to be available to download, but there’s been an error popping up on that page about restricted access which you may experience.
View the full article

Smef

Smef

 

Let’s Encrypt SSL Certificates for FileMaker Server for Mac

Looking for instructions for Windows? Click here! 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 Bash script and a LaunchDaemon 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! Requirements: FileMaker Server deployed on a Mac server – Tested with OSX 10.11 but may work with other versions. The “FileMaker Database Server Website” page must be reachable through the public internet using a web browser at the address you wish to get an SSL certificate for, such as http://myserver.mycompany.com. This means opening or forwarding port 80 in your router, firewall, or security groups. We use the Let’s Encrypt HTTP verification challenge, so we must make sure that Let’s Encrypt is able to reach our FileMaker server through HTTP. Here’s a summary of what we’re going to need to do: Install Homebrew Install Certbot Edit the GetSSL.sh file Run the Bash script Change the FileMaker Server SSL Connections settings Set up a schedule to renew the SSL certificate 1. Download the GetSSL.sh Bash script First, you’ll need a copy of the GetSSL Bash script. Download the file using the link below and save it on your server where you’ll want to get the SSL certificate. 2. Install Homebrew Homebrew is a package manager for Mac. It’s used to install tools and applications for your system. You can read more about Homebrew at the Homebrew website. We’re going to follow the instrucitions on there to install Homebrew on our Mac server. Run the following command in Terminal: /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)" 3. Edit the GetSSL.sh file The script file needs to be edited so that it know the address you wish to get an SSL certificate for. Open the script in a text editor like TextEdit, TextWrangler, Sublime Text, or through Terminal using an editor like Vim. 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. Run the Bash Script With Certbot installed, we’re now ready to run the Bash script. Make sure nobody is connected or using your FileMaker server and then run the GetSSL.sh Bash script by navigating to the directory you have it copied to in your Terminal window and entering: sudo ./GetSSL.sh A bunch of text will scroll by in the Terminal 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.   5. 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 whole FileMaker Server machine or just the FileMaker Server background service. 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.sh file to a relatively permanent location on your server. An example place for this might be /usr/local/bin/. You’ll need to create a LaunchDaemon schedule to run the script at the dates and times you want to do it. A LaunchDaemon will run your scheduled program or task at the dates and times, or at the interval, that you specify. These certificates are good for 90 days at a time, and the Certbot tool we’re using will only renew them once they’re at least 30 days out from renewal. You will want to create a .plist file to schedule this in a way which makes the most sense for you. You can have it run once per week, for example, and it will renew your certificate when it becomes eligible for renewal (It will still restart your FM Server at your scheduled time, even if there isn’t a new certificate – I’d like to update this in the future to only restart if there’s a new certificate.). You can use the example .plist file available at the download link below. You can download and edit this example file to run your GetSSL.sh script at the location you stored it at and on the days you want. This example one is scheduled to run once per week, on Saturdays at 6:21 pm. You can read more about writing your own LaunchDaemons from the Apple Developer site. Download example .plist As an alternative, there’s a great app called Lingon X which give you a really nice GUI for creating, editing, and installing LaunchDaemons. A demo is available, and a single-user license is $9.99, which may be worthwhile as it makes creating and scheduling LaunchDaemons very easy. Once you’ve finished setting your schedule, place your .plist file in /Library/LaunchDaemons/. With your LaunchDaemon installed you can tell your server to start the schedule by ( substituting your .plist file name and then ) running : sudo launchctl load /Library/LaunchDaeomns/my-script.plist 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. Do a test to make sure that it’s all working properly, that it gets a new certificate for you, and that your FileMaker Server service restarts after it has retrieved the certificate. If there is an issue, you may want to run the script manually in PowerShell or debug with the PowerShell ISE to locate any issues. 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

Smef

Smef

 

Live Stream: Integrating FileMaker and Quickbooks with FMBooks Connector

Marc Larochelle from Productive Computing presents about QuickBooks and FileMaker integration at our monthly meeting for May 2017. You’ll learn more about the world of QuickBooks Accounting, the various flavors available today and why customers are interested in connecting QuickBooks with FileMaker.  We’ll explore the tools and skills needed to accomplish this as well as common integration strategies in use today.  Then, we’ll take a deeper dive under the hood to see what a typical integration script looks like and how it all works from A to Z with FM Books Connector.  After watching this webinar, you’ll know a lot more about how to get started with these integrations on your own and the right questions to ask a customer interested in making a connection between FileMaker and QuickBooks.   You can purchase FM Books Connector from http://www.fmbooksconnector.com/.
View the full article

Smef

Smef

 

Live Stream: Nicholas Orr presents BaseElements

Nicholas Orr from Goya presented at our developer meeting this month. Watch the recording of the live-stream to see him demonstrate BaseElements and give a run down on the benefits of developer analysis tools in general, and BaseElements in particular.  He’ll show you how to get the best solution possible from FileMaker using these tools, as well a quick demo of their update builder product : RefreshFM, Web Services server : RESTfm and the free BaseElements plugin.  
View the full article

Smef

Smef

 

How to Use Let’s Encrypt SSL Certificates with FileMaker Server

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

Smef

Smef

 

FileMaker 14 New Features Live-Stream Recording

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

Smef

Smef

 

Importing XML data into FileMaker using XSLT (XSL Style Sheets)

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

Smef

Smef

 

Importing XML data into FileMaker using XSLT (XSL Transformations)

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/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 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.  
View the full article

Smef

Smef

 

Fixed number of x-axis labels in a chart for any number of records

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! Source

Smef

Smef

 

Fixed number of x-axis labels in a chart for any number of records

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

Smef

Smef

 

Installing and updating FileMaker Pro automatically with Active Directory Group Policy Objects (GPO)

If you’re working in an office with Active Directory and you have more than a couple of computers to install FileMaker Pro on it can be very convenient to use a Group Policy Object to deploy FileMaker to different computers on your domain. This prevents you from having to go to each machine and run the setup program. A GPO can also be used to install updates or uninstall FileMake Pro to different machines on your network. This guide will show you how to set up a GPO to deploy FileMaker Pro to your domain. Objectives Deploy FileMaker Pro to a group of computers using an Active Directory Group Policy Object (GPO) Bulk update FileMaker Pro to the latest version using a GPO Requirements Volume License of FileMaker Pro A Windows Active Directory server Familiarity with Active Directory A Windows target computer (this will not work for Macs) Administrator access to make changes to Active Directory Administrator access on a machine you can store and share installer files from using a windows share Set up an Active Directory group for computers GPOs can deploy software to either users or computers. I think it’s best for FileMaker to be deployed to a computer to make it easier to track how many machines you have it installed on. To do this, you will need to create a group in Active Directory and place each of the computers you wish to install FileMaker on inside this group. Connect to your Active Directory server and open the Active Directory Users and Computers console. Create a new security group in the Groups folder for the computers you’ll be installing FMP on. In this example I’m going to name my group FileMaker Computers to indicate that computers in this group should have FileMaker installed. Double-click on the new group you just created and add in the computers you wish to install FileMaker on by clicking the Add button in the Members tab. The window that opens will allow you to search for a computer to add, but computers may not be in the search results, and it may not find them normally. Click the Object Types button and check the Computers checkbox to make sure it’s also searching for computers in Active Directory. Once the GPO is set up you’ll be able to add and remove computers from this group to install or uninstall FileMaker Pro as needed. Share the installer files For GPO to work properly the files for the application you wish to install must be available to the target computer through a network share. Pick a server on your network where you will be hosting the FileMaker Pro installer files. You’ll need to make sure this server is left on so that the target computers will be able to reach the share when they need to install FileMaker. Download or copy the FileMaker Pro setup executable onto your server you’ll be using for sharing. Run the fmp_XX.X.X.X.exe setup file, but do not go through the setup and install FileMaker. Running this file will extract a folder named FileMaker Pro XX, which is the directory that we’ll need to share with our clients. Cancel the install process at the setup language dialog after the extraction is finished. Create a folder on your server from where you’ll be sharing these files. I know I’ll have other installers to share and use with GPOs, so I’m going to make a folder called Installers on a storage drive on my server and will use this as the share. Right-click on the folder (Installers, in my case), select properties, click on the sharing tab, and click the share button. Add in whichever group of computers is appropriate for the security at your office. You can use the FileMaker Computers group or another group on your domain, but it’s important that the computers that you want to install FileMaker Pro on will have read permission for this share. At my company I want to make this share available to all computers on my domain since I’ll be using this share for many different programs, so I’m going to add the Domain Computers group and give the group Read access to the share. Click ok and close down the windows to share the folder on your local network. Download and copy the latest FileMaker Pro update from the FileMaker website and do the same thing to this as you did for FileMaker Pro. I’ve made a folder for FileMaker 13 files on my server to keep things organized. The FileMaker Pro 13 and FileMaker Pro 13.0v3 Updater folders are the folders which were extracted from the .exe files which were downloaded from FileMaker’s website. Configure the FileMaker Pro installer and updater for silent install For the installer to be able to install FileMaker Pro automatically it needs to be able to run without user input. FileMaker provides two text files we can modify to allow this to happen. Open the FileMaker Pro 13/Files/Assisted Install.txt file in a text editor. You’ll need to change the lines to the following: AI_ORGANIZATION=Your Company Name AI_LICENSEKEY=YOUR-LICENSE-KEY AI_REGISTRATION=0 AI_SKIPDIALOG=1 Save and close the file when you are finished. There are other configurations you can make to this file to change how the install works and how FileMaker behaves after the install. You can read about the values in the official documentation. Next, we’ll need to configure the updater to also perform a silent install. Open the FileMaker Pro 13.0v3 Updater/Files/Setup.ini file in a text editor and change the following line: CmdLine=/q+LanguagePackInstaller=1 Save and close the file when you are finished. With these two files configured, our GPO will be able to tell the workstations to install and update FileMaker Pro without any user interaction. Create the Group Policy Object We’re now ready to create our Group Policy Object, which will tell our workstations to install FileMaker Pro. Open the Group Policy Management console on your Active Directory server. Right-click on your domain and select the “Create a GPO in this domain, and Link it here…” option. I’m going to name my new policy FileMaker Install. You should see the new GPO created in the Group Policy Object folder with a shortcut at the domain level linking back to it. Select your new policy from the Group Policy Object folder and remove any items in the Security Filtering section. Add in the Group that we created earlier to show that this policy should apply to any computers in that group. Right-click on the GPO object and select Edit. This will open the Group Policy Management Editor, which will allow us to configure what this GPO does. GPOs can operate at the user level or at the computer level. We’re configuring this GPO to install on a particular piece of hardware, not just any computer that a user logs in to, so we’re going to be making our changes in the Computer Configuration section. Expand Computer Configuration->Policies->Software Settings and right-click Software Installation and select New->Package… This dialog allows you to select the .msi file that you want to use for the deployment. The path and file that you select here will be used by all of the workstations you want to install FileMaker on, so it is important that you select the network share path rather than a local path. Select the computer you shared your files on from the Network option and navigate to the FileMaker Pro installer MSI. You can also type the server and share in the address bar at the top to get started using ServerShare notation. For me, this is ABCDATAInstallersFileMaker 13FileMaker Pro 13Files Select “Assigned” In the dialog box that pops up. This tells the computers that they should install the software without asking the users. The Published option allows users to select if they want to install the software, but since we’re assigning this to a machine and not a user it is not an available option. Do the same thing for the upgrade MSI, which is located at ServerShareNameFileMaker 13FileMaker Pro 13.0v3 UpdaterFiles, but this time select the “Advanced” option instead of “Assigned”. Rename the object to FileMaker Pro 13v3 just so we know which is which, and switch to the Upgrades tab. You should see “Upgrade FileMaker Pro 13” in the list on this tab. If you don’t, go back a few steps and make sure you didn’t miss anything. Click OK to close the window. This GPO is all configured and should show an up arrow on the v3 update package to show that it is upgrading another package. Close the Group Policy Management Editor and you’re all done! The GPO should take effect the next time your workstations check for updates to GPO settings, but you can force it to run immediately if you want to check and see if it worked. Go to one of the workstations and open the Command Prompt or Windows PowerShell and run gpupdate /force /boot /logoff to force a Group Policy refresh. You’ll need to restart the computer afterwards, and when you log back in you should see the FileMaker Pro icon on your desktop, with the application installed, updated, and ready to go! Source

Smef

Smef

 

Installing and updating FileMaker Pro automatically with Active Directory Group Policy Objects (GPO)

If you’re working in an office with Active Directory and you have more than a couple of computers to install FileMaker Pro on it can be very convenient to use a Group Policy Object to deploy FileMaker to different computers on your domain. This prevents you from having to go to each machine and run the setup program. A GPO can also be used to install updates or uninstall FileMake Pro to different machines on your network. This guide will show you how to set up a GPO to deploy FileMaker Pro to your domain. Objectives Deploy FileMaker Pro to a group of computers using an Active Directory Group Policy Object (GPO) Bulk update FileMaker Pro to the latest version using a GPO Requirements Volume License of FileMaker Pro A Windows Active Directory server Familiarity with Active Directory A Windows target computer (this will not work for Macs) Administrator access to make changes to Active Directory Administrator access on a machine you can store and share installer files from using a windows share Set up an Active Directory group for computers GPOs can deploy software to either users or computers. I think it’s best for FileMaker to be deployed to a computer to make it easier to track how many machines you have it installed on. To do this, you will need to create a group in Active Directory and place each of the computers you wish to install FileMaker on inside this group. Connect to your Active Directory server and open the Active Directory Users and Computers console. Create a new security group in the Groups folder for the computers you’ll be installing FMP on. In this example I’m going to name my group FileMaker Computers to indicate that computers in this group should have FileMaker installed. Double-click on the new group you just created and add in the computers you wish to install FileMaker on by clicking the Add button in the Members tab. The window that opens will allow you to search for a computer to add, but computers may not be in the search results, and it may not find them normally. Click the Object Types button and check the Computers checkbox to make sure it’s also searching for computers in Active Directory. Once the GPO is set up you’ll be able to add and remove computers from this group to install or uninstall FileMaker Pro as needed. Share the installer files For GPO to work properly the files for the application you wish to install must be available to the target computer through a network share. Pick a server on your network where you will be hosting the FileMaker Pro installer files. You’ll need to make sure this server is left on so that the target computers will be able to reach the share when they need to install FileMaker. Download or copy the FileMaker Pro setup executable onto your server you’ll be using for sharing. Run the fmp_XX.X.X.X.exe setup file, but do not go through the setup and install FileMaker. Running this file will extract a folder named FileMaker Pro XX, which is the directory that we’ll need to share with our clients. Cancel the install process at the setup language dialog after the extraction is finished. Create a folder on your server from where you’ll be sharing these files. I know I’ll have other installers to share and use with GPOs, so I’m going to make a folder called Installers on a storage drive on my server and will use this as the share. Right-click on the folder (Installers, in my case), select properties, click on the sharing tab, and click the share button. Add in whichever group of computers is appropriate for the security at your office. You can use the FileMaker Computers group or another group on your domain, but it’s important that the computers that you want to install FileMaker Pro on will have read permission for this share. At my company I want to make this share available to all computers on my domain since I’ll be using this share for many different programs, so I’m going to add the Domain Computers group and give the group Read access to the share. Click ok and close down the windows to share the folder on your local network. Download and copy the latest FileMaker Pro update from the FileMaker website and do the same thing to this as you did for FileMaker Pro. I’ve made a folder for FileMaker 13 files on my server to keep things organized. The FileMaker Pro 13 and FileMaker Pro 13.0v3 Updater folders are the folders which were extracted from the .exe files which were downloaded from FileMaker’s website. Configure the FileMaker Pro installer and updater for silent install For the installer to be able to install FileMaker Pro automatically it needs to be able to run without user input. FileMaker provides two text files we can modify to allow this to happen. Open the FileMaker Pro 13/Files/Assisted Install.txt file in a text editor. You’ll need to change the lines to the following:
AI_ORGANIZATION=Your Company Name

AI_LICENSEKEY=YOUR-LICENSE-KEY

AI_LANGUAGE=English

AI_REGISTRATION=0

AI_SKIPDIALOG=1

Save and close the file when you are finished. There are other configurations you can make to this file to change how the install works and how FileMaker behaves after the install. You can read about the values in the official documentation. Next, we’ll need to configure the updater to also perform a silent install. Open the FileMaker Pro 13.0v3 Updater/Files/Setup.ini file in a text editor and change the following line: CmdLine=/q+LanguagePackInstaller=1

Save and close the file when you are finished. With these two files configured, our GPO will be able to tell the workstations to install and update FileMaker Pro without any user interaction. Create the Group Policy Object We’re now ready to create our Group Policy Object, which will tell our workstations to install FileMaker Pro. Open the Group Policy Management console on your Active Directory server. Right-click on your domain and select the “Create a GPO in this domain, and Link it here…” option. I’m going to name my new policy FileMaker Install. You should see the new GPO created in the Group Policy Object folder with a shortcut at the domain level linking back to it. Select your new policy from the Group Policy Object folder and remove any items in the Security Filtering section. Add in the Group that we created earlier to show that this policy should apply to any computers in that group. Right-click on the GPO object and select Edit. This will open the Group Policy Management Editor, which will allow us to configure what this GPO does. GPOs can operate at the user level or at the computer level. We’re configuring this GPO to install on a particular piece of hardware, not just any computer that a user logs in to, so we’re going to be making our changes in the Computer Configuration section. Expand Computer Configuration->Policies->Software Settings and right-click Software Installation and select New->Package… This dialog allows you to select the .msi file that you want to use for the deployment. The path and file that you select here will be used by all of the workstations you want to install FileMaker on, so it is important that you select the network share path rather than a local path. Select the computer you shared your files on from the Network option and navigate to the FileMaker Pro installer MSI. You can also type the server and share in the address bar at the top to get started using \\Server\Share notation. For me, this is \\ABCDATA\Installers\FileMaker 13\FileMaker Pro 13\Files\ Select “Assigned” In the dialog box that pops up. This tells the computers that they should install the software without asking the users. The Published option allows users to select if they want to install the software, but since we’re assigning this to a machine and not a user it is not an available option. Do the same thing for the upgrade MSI, which is located at \\Server\ShareName\FileMaker 13\FileMaker Pro 13.0v3 Updater\Files, but this time select the “Advanced” option instead of “Assigned”. Rename the object to FileMaker Pro 13v3 just so we know which is which, and switch to the Upgrades tab. You should see “Upgrade FileMaker Pro 13” in the list on this tab. If you don’t, go back a few steps and make sure you didn’t miss anything. Click OK to close the window. This GPO is all configured and should show an up arrow on the v3 update package to show that it is upgrading another package. Close the Group Policy Management Editor and you’re all done! The GPO should take effect the next time your workstations check for updates to GPO settings, but you can force it to run immediately if you want to check and see if it worked. Go to one of the workstations and open the Command Prompt or Windows PowerShell and run gpupdate /force /boot /logoff to force a Group Policy refresh. You’ll need to restart the computer afterwards, and when you log back in you should see the FileMaker Pro icon on your desktop, with the application installed, updated, and ready to go!
View the full article

Smef

Smef

 

Introduction to FileMaker Custom Web Publishing (CWP) with PHP

The purpose of this lesson is to create a simple PHP page that will make a connection to a FileMaker database hosted on FileMaker Server and pull information that will be displayed as HTML on a webpage.  This will help you get started with the basics of using FileMaker Custom Web Publishing Objectives: Configure FileMaker Server for CWP access Configure the database for CWP access Create a PHP file which will read data from a database and display the result on a web page Search for a record and display data from the found record Mix HTML and PHP Requirements: Admin access to FileMaker Server Text editor or a PHP IDE (Integrated Development Environment) Web server with PHP installed Configuring FileMaker Server The first step to getting started with CWP is to make sure your FileMaker Server is properly configured.  To do this, you’ll need to log in to your FileMaker Server admin console. First, make sure that the Web Publishing Engine is enabled.  Scroll down on the Status page and look for the Web Publishing engine option.  Click the slider button to the right of the Web Publishing Engine to enable it.  Look for the green checkmark circle which will indicate if the WPE is successfully running. From there, select “Web Publishing” from the menu on the left.  Select the PHP tab and check the “Enable PHP Publishing” checkbox. This will enable your PHP application to connect and communicate with FileMaker Server. With the server properly configured for CWP we’re ready to move on to the FileMaker database Configure the FileMaker Database In addition to configuring the FileMaker Server for CWP we also need to make sure that our FileMaker Database is ready for access through CWP.  Create a new database and upload it to your FileMaker Server.  I’m going to name my database “Test Database” for this example. Open the Manage Security dialog and create a user to be used for accessing the database through CWP.  For this example I’m going to create a user named “PHP User” which will be used only for accessing the database through CWP and give this user the [Data Entry Only] privilege set. Go to the privilege sets tab of the Manage Security window and select the privilege set assigned to your newly created user.  Click Edit and enable the Access via PHP Web Publishing extended privilege set by clicking the checkbox next to it.  Press OK and close the Manage Security window to save your changes. After making this change you should be able to see that PHP access is enabled for your database from the FileMaker Server admin console.  Check the activity tab and you will see a dot in under the PHP column for your database if you have correctly followed the previous.  This dot indicates that at least one privilege set for the database has the fmphp extended privilege set enabled and that we’re ready to connect to it using PHP. Create a table called “People” in this new database with _kp_ID (primary key), Name, and Favorite Color fields.  Also create layout called “PHP Test” with these fields on the layout.  Create some records and set some values in the name and color fields for testing your work. Setting up the web server With HTML you’re able to write some code in a text file, save is as an .html file, and then open it in a web browser and see the result.  PHP is different from HTML in that it is code that needs to be executed, rather than just markup that gets read by your browser and displayed as a webpage.  Because of this difference we need to run the code on a web server and can’t just double-click the file to see our work. You can use any web server with PHP installed to run your code.  Some options you may want to consider are: • FileMaker Server with Web Publishing enabled • A server provided by a web hosting company where you can upload files • On your machine by running a web server For this tutorial I’ll be using the third option.  I like to use an AMP (Apache MySQL PHP) program for development and testing, as it makes it easy to manage the server.  MAMP (Mac Apache MySQL PHP) or WAMP (Windows Apache MySQL PHP) are free and easy to setup and configure on your local machine. These programs run a web server, php, and a MySQL database server on your computer with minimal setup and configuration, which for our purposes makes it so that you don’t have to worry about uploading or transferring files after you make a change in your code before you can see what the change does. Download and install MAMP/WAMP (xAMP) and follow the setup options and start the Apache web server through the xAMP console.  Find the web root directory for the new web server running on your machine, which will probably be called htdocs will be located in your xAMP installation directory.  This is where we’ll be putting our files so that they show up on our new web server.  If you’re running xAMP on your local machine you should be able to see the start page by going to http://localhost:8888/ in your browser (this can also be changed to use port 80 in the xAMP console). Put the FileMaker API on the server Now that we have our server set up we’re ready to install the FileMaker API.  The first thing we’re going to need to do is grab the FileMaker PHP API (Application Programming Interface) from our FileMaker Server installation.  The PHP API is a set of PHP files that we will use with our code to make the connection to FileMaker Server. The PHP API is located at: Windws – drive:\Program Files\FileMaker\FileMaker Server\Web Publishing\FM_API_for_PHP_Standalone.zip Mac – /Library/FileMaker Server/Web Publishing/FM_API_for_PHP_Standalone.zip Copy the zip file to your computer, extract it, and put the extracted files in your web root.  The files should look like this: Pick your editor Writing PHP code can be done in any text editing application, such as Notepad or TextEdit, but there are lots of applications that will help you write your code with features like automatic code completion or code highlighting to help you better see the organization of your code. There are also more advanced programs which will help you manage files, offer version control, and will spot errors and make recommendations about best practices while you’re writing your code.  They can even help you debug your code in a way similar to the script debugger and data viewer in FileMaker.  These programs are great for coding, but they take a bit of setup work to get everything running. I recommend installing one of these code editor programs to help make your job easier while writing code:

Simple, free code editors Mac: TextWrangler Windows: Notepad++ Integrated Development Environment (IDE) programs IntelliJ (free for personal use) PHPStorm (paid, almost exactly the same as IntelliJ for PHP but cheaper to buy) Let’s start coding! With everything in place we’re ready to start writing our code.  Create a new file in editor and save it in your web root as fmdemo.php (in the same directory as filemaker.php. You can name your file anything you like, but give it the .php extension to help the server see that we’re going to be using PHP code in this file. The first thing to do is to show our web server that the code we’re writing is PHP code.  This is done using the PHP tags. <?php ?> Any code we write in-between these tags will be executed as PHP code on the server and won’t be seen by the client’s browser. Now, tell the server we’re going to be using the FileMaker PHP API and let it know where to find the classes we’ll be referencing.  This sets us up for making calls to it in the next few steps <?php include("filemaker.php"); ?> Now, we can set up our connection to FileMaker Server.  The four parameters for the function we’re going to call are in order: Database name Server address Username password <?php include("filemaker.php"); $fm = new FileMaker("Test Database", "fms.mycompany.com", "PHP User", "password"); ?> Let’s prepare to get a random record from our People table.  To do this we’ll use the findAnyCommand method, which does exactly that.  This method takes a single parameter, which is the name layout to pull the record from. <?php include("filemaker.php"); $fm = new FileMaker("Test Database", "fms.mycompany.com", "PHP User", "password"); $findCommand = $fm->newFindAnyCommand("PHP Test"); ?> Now we can execute the find we’re prepared and set the result to a variable <?php include("filemaker.php"); $fm = new FileMaker("Test Database", "fms.mycompany.com", "PHP User", "password"); $findCommand = $fm->newFindAnyCommand("PHP Test"); $result = $findCommand->execute(); ?> A result from the FileMaker PHP Api will either return a FileMaker_Error object if there is an error or an FileMaker_Result object, which contains an array of records (even if there’s really only one in the result).  It’s important to always check for errors when there is a possibility of getting one, and then handling the error appropriately.  We’ll do this by adding in an if statement to check to see if our result is an error, and then write out the error message before exiting our script since we can’t do anything else useful right now to better handle the error. <?php include("filemaker.php"); $fm = new FileMaker("Test Database", "fms.mycompanydomain.com", "PHP User", "password"); $findCommand = $fm->newFindAnyCommand("PHP Test"); $result = $findCommand->execute(); if(FileMaker::isError($result)){ echo($result->getMessage()); return; } ?> If we didn’t get an error we’re able to view our results and print out some of our fields.  Use the getFirstRecord and getFieldName methods to store the first record in a variable and then read a field value from the record.  I’ll retrieve the Name field from the People record that was returned and write it out using the echo function. <?php include("filemaker.php"); $fm = new FileMaker("Test Database", "fms.mycompany.com", "PHP User", "password"); $findCommand = $fm->newFindCommand("PHP Test"); $findCommand->addFindCriterion("name", "Adam"); $findCommand->setRange(0, 1); $result = $findCommand->execute(); if (FileMaker::isError($result)) { echo($result->getMessage()); return; } $record = $result->getFirstRecord(); $name = $record->getField("Name"); $color = $record->getField("Favorite Color"); echo("$name - $color"); ?> Try loading this page in your browser now by going to http://localhost:8888/fmdemo.php (use the correct address of your server if it isn’t localhost:8888).  If everything was done correctly you should see one of the values from the Name field that you entered in your database.  Since we’re using the findAnyCommand method the value will be randomly selected from one of the records in your database, so it will change every time you reload the page. Find a specific record Let’s modify our code a little bit to find a specific person and display their name and favorite color.  We can use the findCommand method instead of findAnyCommand to create a find request just like we would in FileMaker.  We’ll also add a addFindCriterion call to the $findCommand object to specify the field we’re going to search on, also very similar to a FileMaker script. It’s important to set a “range” value here, which will limit the maximum number of records returned.  If you don’t limit the range and your found set is very large it could make your code very slow or even possibly cause your script to crash due to your server running out of memory.  Set a range using the setRange method, which takes two parameters – a number of records to skip and a number of records to return in the result.  Set these to 0 and 1 for now to not skip any records and only return one record in your result. Finally, let’s show both the person’s name and their favorite color at the same time by changing our echo at the bottom of our code.  I’m also going to assign the values to a variable before echoing them out to make it a bit easier to read what the echo statement should be writing.  PHP will actually evaluate variables that are placed inside double quotes, so you don’t have to worry about concatenation in this case. <?php include("filemaker.php"); $fm = new FileMaker("Test Database", "fms.mycompany.com", "PHP User", "password"); $findCommand = $fm->newFindCommand("PHP Test"); $findCommand->addFindCriterion("name", "Adam"); $findCommand->setRange(0, 1); $result = $findCommand->execute(); if (FileMaker::isError($result)) { echo($result->getMessage()); return; } $record = $result->getFirstRecord(); $name = $record->getField("Name"); $color = $record->getField("Favorite Color"); echo("$name - $color"); ?> Mix your HTML and PHP You can mix HTML and PHP on a page to make a webpage with dynamic data from your database.  HTML can be placed outside of the <?PHP ?> tags and will be sent to the browser like normal.  You can also have multiple PHP tags on the page to mix your code and your HTML as needed. <html> <body> <?php $name = "Adam" ?> <h1>Favorite Color of <?php echo($name) ?></h1> <?php include("filemaker.php"); $fm = new FileMaker("Test Database", "fms.mycompany.com", "PHP User", "password"); $findCommand = $fm->newFindCommand("PHP Test"); $findCommand->addFindCriterion("name", $name); $findCommand->setRange(0, 1); $result = $findCommand->execute(); if (FileMaker::isError($result)) { echo($result->getMessage()); return; } $record = $result->getFirstRecord(); $color = $record->getField("Favorite Color"); echo("We searched for $name and found the favorite color to be $color"); ?> </body> </html> That should cover the basics for using FileMaker CWP.  Let me know if this has been useful for you, or what you would like to see from me in the future.  Be sure to share this article using the buttons below if you think this would be helpful for other people as well.
View the full article

Smef

Smef

×

Important Information

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