Getting the IDs for a Filtered Portal

One of my favourite features of FileMaker Pro v13 is the new “List of” summary type option, which is used for creating a return-delimited list of non-blank values in a field. For example if you wanted a list of all Customer IDs for your found set of records you can now simply reference a Summary field that uses the List of type option:

List of Summary Field Type

 

I’ve found the performance to be excellent and it can now replace the use of Custom Functions, looping scripts etc that were previously used to generate a list of IDs for a found set of records. We’ve also another great use for it – in conjunction with filtered portals it can be used to get the IDs for the currently filtered portal records. This makes it simply to filter a portal, get the list of filtered IDs which can then be used in a multi-key relationship to group those filtered records for further processing.

Portal filtering was introduced in FileMaker Pro 11 and lets developers easily filter a portal by simply editing the portal setup – you don’t need to add unnecessary clutter to your relationship graph to support portal filtering. However one of the limitations with portal filtering is that the results of calculations are based on the full set of related records, not just the records in the portal that are currently filtered. This means that you can’t simply use functions like the List function to return a list of all the Customer IDs that are currently filtered as it will return a list of all Customer IDs for the underlying relationship used by the portal.

However you can now use the new “List of” summary type field with a filtered portal to quickly get the list of filtered record IDs. You simply need to create a Summary field in your “child” table that you are filtering that is a List of the IDs, then create a script that retrieves this value.

Here’s a screenshot showing a simple filtered portal of Customers by State. When you select a State from the pop-up menu the list of customers changes to match the selected State:

 

Filtered Portals Demo

You’ll notice below the portal is another field that displays the list of Customer IDs for the currently filtered portal records. We’ve added a script trigger to the State pop-up menu filter that checks for any filtered records and retrieves the CustomerIDs for the filtered records by retrieving the value of the List of Customer IDs Summary field. The script has to go to the first row of the portal to establish the correct context first – here’s what the script looks like with the step that gets the filtered IDs highlighted:

Get Filtered Customer IDs

 

You can download this demo file to explore this technique – please post any comments below.

Databuzz to Exhibit at the 2014 FileMaker Developer Conference

FileMaker Inc. have recently released the schedule for the 2014 FileMaker Developer Conference, to be held in San Antonio, Texas from July 28-31, 2014. We’re excited to announce that we will be once again exhibiting at the conference, showcasing fmSMS v3, our 2 way SMS solution for FileMaker Pro, FileMaker Go and FileMaker Server.

devcon_14_banner_horz

fmSMS v3 was completely rewritten from the ground up for FileMaker Pro v12, and with the release of FileMaker Pro v13 we’ve also added support for some of the great new v13 features including Perform Script on Server and WebDirect. If you’re coming to the conference please stop by and say hello and we can demonstrate how you can send and receive SMS/TXT messages using FileMaker Pro, FileMaker Go and FileMaker Server.

We hope to see as many of you there as possible and look forward to answering your questions about FileMaker and SMS integration.

fmSMS Updated with Additional SMS Gateways Support

We’ve just released v3.3 of fmSMS which now supports some additional European based SMS Gateways. We’re working on adding a few USA based SMS Gateways but our most recent additions include:

  • fastsms – based in the UK and can delivery to more than 180 carriers in over 90 countries
  • Wireless Services – based in The Netherlands with coverage of more than 550 networks in 170 countries

Most SMS Gateways will delivery to international destinations so their location normally doesn’t limit the countries to which they can delivery messages. You can get the full list of supported SMS Gateways from the fmSMS website at:

http://www.fmsms.com/sms-gateways/

You can also download a trial version of fmSMS from the fmSMS website at:

http://www.fmsms.com/download/

Our Getting Started Guide will also help you get up and running with the trial version – most SMS Gateways offer a trial account which you can use with the trial version of fmSMS to send some free test messages.

If there are SMS Gateways that you would like us to support just let us know. We can normally add a new SMS Gateway in a few hours.

Where are fmSMS users located?

fmSMS allows you to send an SMS from FileMaker Pro and FileMaker Go to almost any mobile phone in the world via one of the supported SMS Gateways, reaching over 860 mobile networks in more than 220 countries. We thought it would be interesting to see where our customers are located (we’re based in Sydney, Australia) and created a simple map using a FileMaker database that plots the addresses on a Google Map:

 

Looks like we need to get some customers in China, South America, eastern Europe and Africa to complete the map!

FileMaker Pro 13 – HTTP POST and HTTP Headers

In FileMaker Pro v13 you can now perform an HTTP POST when importing XML data or using the “Insert from URL” script step. Both HTTP GET and HTTP/S POST are supported – GET and POST are generally the 2 most popular HTTP Request Methods when working with web services.

FileMaker Pro v12 already supported HTTP GET so nothing has changed here – you only need to specify the URL to perform an HTTP GET request. To perform an HTTP POST request you need to specify a URL starting with either:

  • httppost
  • httpspost

You then include a “?” character in the URL to separate the request URL from the POST data. Everything before the first “?” character is the request URL and everything after the first “?” character is the post data. For example to query a web service that converts Fahrenheit to Celsius you would use the following URL:

httppost://www.w3schools.com/webservices/tempconvert.asmx/FahrenheitToCelsius?Fahrenheit=100

FileMaker Pro recognises that you wish to perform an HTTP POST by the use of “httpost” at the beginning of the URL. The URL for the HTTP POST then becomes everything up to the first “?” character:

http://www.w3schools.com/webservices/tempconvert.asmx/FahrenheitToCelsius

and the POST data is everything after the first “?” character:

Fahrenheit=100

We get an XML result like this:

<?xml version=”1.0″ encoding=”utf-8″?><string xmlns=”http://www.w3schools.com/webservices/”>37.7777777777778</string>

which we could easily parse using FileMaker functions to get the actual Celsius value into a FileMaker field (see Demo File link at the bottom).

HTTP Headers

One of the main limitations when performing an HTTP POST is that we are unable to set the HTTP Headers. When working with web services – particular SOAP/XML based web services – you will often be required to set specific headers before sending the HTTP POST data. With FileMaker Pro 13.0v1 the following headers are set automatically and cannot be changed:

User-Agent: FileMaker/13.0

Content-Type: application/x-www-form-urlencoded

Hopefully a future update to FileMaker Pro will allow developers to set the HTTP Headers for each request – in the meantime you will have to continue using of the available FileMaker plugins (such as the BaseElements plug-in)

 

HTTP POST Demo.fmp12

Bookmarking the FileMaker Pro/Pro Advanced 13 Help File on Mac OS X

If you’re like me and you bookmark the FileMaker Pro/Pro Advanced Help so you can view it in your web browser instead of the Mac Help Centre you’ll find the v13 index here:

/Applications/FileMaker Pro 13 Advanced/Extensions/FileMaker Help/FileMaker Help.bundle/Contents/Resources/en.lproj/FileMaker Pro Help/index.html

(you need to right click on the FileMaker Help.bundle and select Show Package Contents to find the remaining folders)

I create a new folder in my bookmarks for “FileMaker 13” and then bookmark the following pages for easy reference:

  • index/home page
  • New features in FileMaker Pro 13
  • FileMaker Pro error codes
  • Script triggers reference
  • Script steps reference
  • Functions reference

fmSMS and FileMaker Pro v13

We’re pleased to report that fmSMS v3 is compatible with FileMaker Pro/Pro Advanced v13 that was released today. We haven’t encountered any issues so far in our testing and have been able to send single and bulk messages successfully.

We’re planning a small update to fmSMS to take advantage of the new Perform Script On Server script step, which will remove the need to have a FileMaker Server scheduled script running every x minutes on the server to send messages that were generated on FileMaker Go v13 clients. Using the Perform Script On Server script for FileMaker Go v13 clients will allow them to compose an SMS and have it send immediately, rather than waiting for the scheduled server side script to send it on it’s next scheduled run.

If you encounter any issues with fmSMS v3 and FileMaker Pro v13 please let us know.

Creating Panic Status Boards using FileMaker Server

As long time users of Panic applications we were very interested when they released Status Board earlier this year. Status Board is an iPad app designed to help you display your data. You can view the data on the iPad using the app, but where it gets interesting is the in-app purchase that lets you connect it a giant display screen/TV with HDMI out.

The app has some great standard display panels:

  • Clock — Analog or digital, anywhere in the world.
  • Weather — Temperature and four-day forecast.
  • Calendar — Pick a calendar and see your appointments at a glance.
  • Mail — Get messages counts, or see the latest Subject lines.
  • Twitter — See the latest Tweets or the volume of tweets.
  • Feeds — The latest headlines from your favorite sites

and also 3 Pro panels that let you customise your own content to display:

  • Graph — You provide the JSON or CSV data source, we make it beautiful.
  • Table — Toss us some HTML or CSV data and we’ll make a nice looking table.
  • Do-It-Yourself — Design your own panels using HTML

It’s these 3 Pro panels that let you display data directly from your FileMaker Pro databases hosted on FileMaker Server. We’ve used the FileMaker API for PHP to query hosted databases, return the result and then format the data into the required format. Here’s an example we did for a real estate client:

FileMaker Status Board

FileMaker Status Board

This Status Board is displayed on a TV in their office that all staff can and is updated every 5 minutes automatically. It allows sales staff to quickly see how they are tracking in terms of number of property listings for the month and sales for the month. They can also view historical data for the last 6 months. It includes a table of their current properties for sale – we’re only showing 4 at any one time but the table scrolls automatically on the external display. We even included some green/red icons for the admin staff to quickly see the status of their FileMaker Server and Web Servers so they can tell if there’s any issues.

If you have any questions about integrating your FileMaker data into a custom Status Board please contact us.

Databuzz at FileMaker DevCon 2013

We are exhibiting for the first time at the FileMaker Developer Conference 2013 where will be showing fmSMS v3 and how easy it is to send and receive SMS/TXT messages with FileMaker Pro, FileMaker Server and FileMaker Go. If you’re attending DevCon make sure you stop by our booth for a demo of how fmSMS works or if have any questions about integrating SMS functionality into your existing FileMaker solution.

We’re also offering a special DevCon discount from now until the 16th August, 2013 – you can get 10% off fmSMS using our special DevCon purchase link at:

http://www.fmsms.com/devcon/

Even if you can’t make it to DevCon in person you can still take advantage of this offer. I look forward to catching up with as many attendees as possible – please stop by our booth and say hello.

Databuzz SMS Solution featured in FileMaker Asia Pacific Newsletter

A 2 way SMS solution we built for a client is featured in the latest FileMaker Asia Pacific Newsletter:

http://www.filemaker.com/au/newsletter/archives/2013/q4/FM_pages/page_02/FM-News_AU_02.html?#CD

The solution allows people interested in a property for sale with a real estate agency to “subscribe” to that property by sending an SMS with the property code to a dedicated virtual number that they see advertised on a billboard or website. For example if you were interested in a property you would send an SMS with the property code (e.g. L1234) to the advertised number.

This incoming message – MO (Mobile Originated) – is then pushed to a webpage hosted as part of the real estate agency’s FileMaker Server Custom Web Publishing deployment. The php page processes the incoming request, creates a record in the Subscriptions table linked to the property they are interested in, the sends back a custom message with details about the property and the agent who is handling the listing.

Follow up messages can be sent to everyone that has subscribed to the property – for example details about open house times, price changes or upcoming auction details.

For more information about 2 way SMS and FileMaker please visit the fmSMS website.