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.

fmSMS now supports more than 40 SMS Gateways Internationally

Earlier this year we released fmSMS v3, a complete rewrite of fmSMS for FileMaker Pro v12. So far the feedback has been great and we have customers from all around the world using fmSMS to send and receive SMS/TXT messages everyday from their FileMaker solutions. We had a great time at the recent FileMaker Developer Conference in San Diego where we were able to demonstrate how you can send and receive SMS messages to attendees from many different countries.

Since the release of fmSMS v3 we’ve also been busy adding support for more SMS Gateway providers around the world. We now support over 40 SMS Gateway providers around the world, allowing our customers greater choice over which SMS Gateway provider to use in conjunction with fmSMS. Some of the more recent additions include:

  • Twilio – based in the USA, can deliver to over 1,000 carriers internationally
  • Nexmo – offices in San Francisco, London and Hong Kong. Can deliver to over 1,000 carriers around the world in over 200 countries
  • iTagg – based in the UK
  • HQSMS – based in Poland
  • Quiubas – based in USA/Mexico
  • Expert Texting – based in the USA, can delivery to 222+ countries
  • SMS Solutions Australia – based in Australia
  • Silverstreet – has offices all around the world, including The Netherlands, United Kingdom, Malaysia, Indonesia, Russian Federation, Australia, Poland and Thailand
  • CDYNE – based in the USA
  • txtNation – has offices all around the world, including USA, UK, Denmark, Sweden, Ireland, Spain and Finland
  • MobiWeb – has offices in UK, USA and Latin America

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/

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.

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.

Databuzz releases fmSMS v3 – rewritten for FileMaker Pro 12

Databuzz today announced fmSMS v3, an upgrade to their award winning solution that lets you send and receive SMS/TXT messages from within FileMaker Pro.

fmSMS allows you to send an SMS from FileMaker Pro 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. SMS is great means of direct communication with customers, staff, suppliers, and students.

fmSMS v3 was rewritten for FileMaker Pro v12 and includes support for sending messages from FileMaker Go running on the iPad or iPhone. Databuzz will be demonstrating fmSMS v3 at the 2013 FileMaker Developer Conference, to be held in San Diego, California from August 12-15.

What’s New in v3

–        rewritten for FileMaker Pro v12

–        reduced the number of files from 3 to 1

–        now uses the BaseElements plug-in

–        simplified licensing: no limit on the number of FileMaker Pro clients

–        includes a license for FileMaker Server

–        FileMaker Go support for sending messages from an iPad or iPhone*

SMS is perfect for appointment reminders, phone messages, promotions, segmented marketing, school absence alerts, and password confirmations. With SMS you can reduce your costs and play less “telephone tag”.

fmSMS works with multiple SMS Gateway providers internationally and supports the following features**:

–        Send single and bulk SMS messages (messages are typically delivered in under 15 seconds)

–        Send long messages (greater than 160 characters)

–        Works with over 30 SMS Gateway providers internationally

–        Delayed Delivery – send a message now for delivery at a future time

–        Alphanumeric Sender ID

–        Delivery Receipts – track the status of sent messages

–        Message Logging – track the history of all sent messages

–        Message templates – create an unlimited number of pro forma templates

–        2 Way SMS – allow recipients to reply to messages and have them appear in fmSMS

“fmSMS is now easier to integrate into a customer’s existing FileMaker solution than ever,” said Andrew Duncan, Director of Databuzz. “We’ve simplified the licensing and included support for the entire FileMaker platform – you can now send an SMS from FileMaker Pro, FileMaker Go or FileMaker Server.”

Availability, Pricing, and Compatibility

fmSMS v3 is available now from the fmSMS website at http://www.fmsms.com. A 14 day trial version is available for both Macintosh and Windows. Workgroup Licenses start at AUD $495.00. fmSMS requires FileMaker Pro v12.

Upgrade Information

fmSMS v3 is a free upgrade for owners of fmSMS v2.

* FileMaker Go support requires the fmSMS file to be hosted by FileMaker Server

** Not all SMS Gateways support all features of fmSMS. Some features might incur additional charges by your selected SMS Gateway. Some features require hosting by FileMaker Server v12 with Custom Web Publishing using the PHP API and a static IP address. See our website at http://www.fmsms.com for more details.

Media/Customer Contact:

Andrew Duncan

Phone: +61 418 468 103

sales@databuzz.com.au

http://www.fmsms.com

http://www.databuzz.com.au

 

About Databuzz: Databuzz is a long standing member is a member of the FileMaker Business Alliance and has been developing and deploying FileMaker solutions for clients in Australia and internationally since 1999. Our clients are individuals, small-medium businesses, government agencies and multi-national corporations. Databuzz was founded by Andrew Duncan, a Certified FileMaker 12 Developer. In 2010 Andrew presented a session at the annual FileMaker Developer Conference on integrating SMS/TXT Message Integration with FileMaker. For more information please visit our website at http://www.databuzz.com.au.

###

FileMaker is a trademark of FileMaker, Inc., registered in the U.S. and other countries. All other trademarks are the property of their respective owners.

Databuzz to Exhibit at the 2013 FileMaker Developer Conference

FileMaker Inc. have recently released the schedule for the 2013 FileMaker Developer Conference, to be held in San Diego, California from August 12-15. We’re excited to announce that we will be exhibiting at the conference for the first time, showcasing fmSMS v3, our 2 way SMS solution for FileMaker Pro.

 

banner__adhorz-copy

We’ve completely rewritten fmSMS from the ground up for FileMaker Pro v12, with a number of new features including support for FileMaker Go. 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.

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

fmSMS v3 Coming Soon

We’re putting together the final touches to v3 of fmSMS, our two way SMS FileMaker solution that allows you to send and receive SMS/txt messages from your FileMaker database. With the release of FileMaker Pro v12 we decided to take this opportunity to rewrite fmSMS from the ground up, which has meant the v3 release has taken us a bit longer than we expected. Version 1 of fmSMS was originally created with FileMaker Pro v6, and v2 was developed with FileMaker Pro 9, 10 and 11. With the new v12 file format, plugin installation updates and the ExecuteSQL function we decided that it was a good time to go back to the drawing board and start afresh.

We’re also changing the plug-in we use with fmSMS v3 to the free BaseElements plug-in. The BaseElements plug-in already had a number of HTTP functions which we were able to implement quickly, however it was lacking a function that a number of our customers rely: proxy support. We decided to sponsor the development of this function which was included in the recent v2 release of the BaseElements plugin. The function is called as follows:

BE_HTTP_Set_Proxy ( proxy {; port ; username ; password } )

You can supply the proxy server address, along with the three optional parameters for the port number, proxy server username and proxy server password.

We’ve also reduced the number of files down from 3 to a single file. Using the BaseElements plug-in also allows us to remove any licensing restrictions and to include support for FileMaker Server scripts as standard (previously this required customers to purchase an additional plug-in license). We’ve also included support for additional SMS Gateways around the world, including the USA.

We hope to have the new version ready in a few weeks.

FileMaker API for PHP Valuelists and FileMaker Server v12

When working with the FileMaker API for PHP there are a number of functions that are helpful when working with Value Lists. You can use the listValueLists function  to return the names of any value lists associated with the  layout you are referencing, and the getValueLists function to return a multi-level associative array of value lists (i.e. the contents of the value lists on the layout).

I noticed something strange today when working on a FileMaker v12 file hosted with FileMaker Server v12 – using these 2 functions I was seeing value lists that I was sure were not on the layout. After a bit of testing I found the source of the problem – some of the fields which were set to display as a simple “Edit box” had previously been set to display as a “Drop-down list”, and it was the value lists that were previously associated with these fields that were showing. Simply changing the field’s control style from “Drop-down list” to “Edit box” was not enough to remove it from the XML that is returned by the Web Publishing Engine – if you’ve used FileMaker Pro for a while you’ll have encountered this feature where FileMaker can “remember” previous settings for a field on a layout and when you switch it back from “Edit box” to  “Drop-down list” it will remember the value list you had previously selected.

The only way to completely refresh the layout so that “remembered” value lists were not returned by the listValueLists and getValueLists functions was to remove the fields from the layout and manually add them again, ensuring that the control style was set to “Edit box” to start with (this is the default style when adding new fields to a layout).

I quickly checked a v11 solution running under FileMaker Server v11 and I wasn’t able to reproduce this issue, so it looks like a new issue with FileMaker Server v12 only. I’ll report it to FileMaker Inc in case it’s considered a bug, but it’s definitely a change from previous versions of FileMaker Server.

Databuzz now FileMaker 12 Certified

Databuzz is pleased to announce that Andrew Duncan recently passed the FileMaker 12 Certification Exam and is now FileMaker Certified in v8, 9, 10, 11 and 12. FileMaker Certification is your validation that you are hiring an experienced FileMaker professional who has technical knowledge of the complete FileMaker product line and has passed the “Developer Essentials for FileMaker” certification exam.

 

FileMaker 12 Certified Developer

Using ExecuteSQL to Query the Virtual Schema/System Tables

FileMaker Pro v12 introduced the new ExecuteSQL function, which allows you to perform an SQL query on your FileMaker database. The query can can combine the results of two queries, include dynamic parameters, and lets you specify relationships independent of the relationships graph. It is presently limited to the SELECT statement, which is similar to doing a find using the native FileMaker Pro commands, but is not tied to a particular layout or relationship.

I’m going to discuss one particular use of the ExecuteSQL function in this post: the ability to query 2 “virtual tables” for information about the schema of your FileMaker Pro v12 files. The ability to execute SQL statements has been available via the external plug-in API since FileMaker Pro v7.0v3 was released, and many plug-ins have included this functionality, including MMQuery and the BaseElements plugins. Plug-ins that utilised the SQL functionality have also had the ability to query 2 hidden “virtual tables” for information about the schema of your FileMaker database, including Table Occurrences and Field Names. Now that we have the ability to perform native SQL queries using the ExecuteSQL function in FileMaker Pro v12 we can also query these “virtual tables”.

The 2 “virtual tables” that you can query are FileMaker_Tables and FileMaker_Fields.

FileMaker_Tables returns the following columns:

1. TableName
2. TableID
3. BaseTableName
4. BaseFileName
5. ModCount

Here’s some ExecuteSQL function examples that illustrate how you can query FileMaker_Tables (note that the table occurrences also work with external file references that appear on the graph of the current file. I’m using the Event Management starter solution from FileMaker Pro v12 to illustrate what data is returned as well):

ExecuteSQL ( “SELECT * FROM FileMaker_Tables” ; “” ; “”  )
// Returns TableName, TableID, BaseTableName, BaseFileName, and ModCount  for all Table Occurrences in the current FileMaker Pro v12 file

returns:

Agenda,1065093,Agenda,Event Management,46
Assignees | Contributors,1065094,Contributors,Event Management,115
Contacts,1065098,Contacts,Event Management,140
Contributors,1065097,Contributors,Event Management,115
Events,1065089,Events,Event Management,137
Guests,1065092,Guests,Event Management,62
Guests | Contacts,1065095,Contacts,Event Management,140
Tasks,1065091,Tasks,Event Management,89
Venue Looked Up,1065107,Events,Event Management,137

ExecuteSQL ( “SELECT BaseTableName FROM FileMaker_Tables” ; “” ; “”  )
// Returns list of all underlying Base Tables for all Table Occurrences in the current FileMaker Pro v12 file

returns:

Agenda
Contributors
Contacts
Contributors
Events
Guests
Contacts
Tasks
Events

ExecuteSQL ( “SELECT DISTINCT BaseTableName FROM FileMaker_Tables” ; “” ; “”  )
// Returns a unique list of all underlying Base Tables for all Table Occurrences in the current FileMaker Pro v12 file

returns:

Agenda
Contacts
Contributors
Events
Guests
Tasks

FileMaker_Fields returns the following columns:

1. TableName
2. FieldName
3. FieldType (the SQL data type, not the FileMaker data type)
4. FieldID
5. FieldClass (Normal, Summary, Calculated)
6. FieldReps
7. ModCount

Here’s some ExecuteSQL function examples that illustrate how you can query FileMaker_Fields (once again using the Event Management starter solution from FileMaker Pro v12 to illustrate what data is returned):

ExecuteSQL ( “SELECT * FROM FileMaker_Fields” ; “” ; “”  )
// Returns TableName, FieldName, FieldType, FieldID, FieldClass, FieldReps and ModCount  for all Table Occurrences in the current FileMaker Pro v12 file

returns:

Agenda,EVENT ID MATCH FIELD,decimal,2,Normal,1,4
Agenda,Agenda Item,varchar,3,Normal,1,2
Agenda,Date,date,4,Normal,1,9
Agenda,Explanation,varchar,5,Normal,1,5
Agenda,Time,time,6,Normal,1,7
Agenda,Time Frame Order,decimal,8,Calculated,1,16
Agenda,Time Frame,varchar,9,Calculated,1,12
Assignees | Contributors,EVENT ID MATCH FIELD,decimal,1,Normal,1,5
Assignees | Contributors,Email,varchar,2,Normal,1,19
Assignees | Contributors,Phone,varchar,3,Normal,1,10
Assignees | Contributors,Contributor | MATCH FIELD,varchar,4,Normal,1,26
Assignees | Contributors,Role,varchar,5,Normal,1,1
Assignees | Contributors,Assignment,varchar,13,Calculated,1,19
Assignees | Contributors,Photo | Container,binary,14,Normal,1,5
Assignees | Contributors,Assignment Status,decimal,16,Calculated,1,3
Assignees | Contributors,Photo Placeholder,varchar,18,Calculated,1,3
Contacts,First,varchar,2,Normal,1,2
Contacts,Last,varchar,3,Normal,1,2
Contacts,Contacts | MATCH FIELD,varchar,4,Calculated,1,26
Contacts,Job Title,varchar,5,Normal,1,2
Contacts,Company,varchar,6,Normal,1,5
Contacts,Work Email,varchar,7,Normal,1,4
Contacts,Work Phone,varchar,8,Normal,1,2
Contacts,Mobile Phone,varchar,9,Normal,1,2
Contacts,Fax,varchar,10,Normal,1,1
Contacts,Website,varchar,11,Normal,1,1
Contacts,Address 1,varchar,12,Normal,1,3
Contacts,City,varchar,13,Normal,1,3
Contacts,State,varchar,14,Normal,1,3
Contacts,Postal Code,varchar,15,Normal,1,3
Contacts,Country,varchar,16,Normal,1,3
Contacts,Address 2,varchar,17,Normal,1,4
Contacts,CONTACT ID MATCH FIELD,decimal,33,Normal,1,7
Contacts,Initial,varchar,34,Calculated,1,32
Contacts,Sort Selection,global varchar,39,Normal,1,4
Contacts,Sort List Key,varchar,40,Calculated,1,4
Contacts,Photo | Container,binary,41,Normal,1,4
Contacts,Title,varchar,42,Normal,1,1
Contacts,Home Phone,varchar,43,Normal,1,1
Contacts,Home Email,varchar,44,Normal,1,5
Contacts,Notes,varchar,46,Normal,1,2
Contacts,Address Short,varchar,48,Calculated,1,8
Contacts,File Name Placeholder,varchar,49,Calculated,1,4
Contacts,Result Label Plural,varchar,50,Calculated,1,2
Contributors,EVENT ID MATCH FIELD,decimal,1,Normal,1,5
Contributors,Email,varchar,2,Normal,1,19
Contributors,Phone,varchar,3,Normal,1,10
Contributors,Contributor | MATCH FIELD,varchar,4,Normal,1,26
Contributors,Role,varchar,5,Normal,1,1
Contributors,Assignment,varchar,13,Calculated,1,19
Contributors,Photo | Container,binary,14,Normal,1,5
Contributors,Assignment Status,decimal,16,Calculated,1,3
Contributors,Photo Placeholder,varchar,18,Calculated,1,3
Events,EVENT ID MATCH FIELD,decimal,1,Normal,1,11
Events,Event,varchar,2,Normal,1,10
Events,Date,date,3,Normal,1,2
etc . . .

ExecuteSQL ( “SELECT * FROM FileMaker_Fields WHERE TableName=’Tasks'” ; “” ; “”  )
// Returns TableName, FieldName, FieldType, FieldID, FieldClass, FieldReps and ModCount  for the “Tasks” table occurrence in the current FileMaker Pro v12 file

returns:

Tasks,EVENT ID MATCH FIELD,decimal,2,Normal,1,5
Tasks,Due Date,date,5,Normal,1,3
Tasks,Task,varchar,6,Normal,1,11
Tasks,Status,decimal,7,Calculated,1,21
Tasks,Assignee | MATCH FIELD,varchar,11,Normal,1,4
Tasks,Status Sort Order,decimal,12,Calculated,1,32
Tasks,Completed,varchar,14,Normal,1,2
Tasks,To Do,decimal,17,Calculated,1,5
Tasks,Overdue,decimal,18,Calculated,1,6
Tasks,Event,varchar,20,Calculated,1,1

ExecuteSQL ( “SELECT FieldName FROM FileMaker_Fields WHERE TableName=’Tasks'” ; “” ; “”  )
// Returns a list of FieldNames   for the “Tasks” table occurrence in the current FileMaker Pro v12 file.The list of field names is in field creation order

returns:

EVENT ID MATCH FIELD
Due Date
Task
Status
Assignee | MATCH FIELD
Status Sort Order
Completed
To Do
Overdue
Event

ExecuteSQL ( “SELECT FieldName FROM FileMaker_Fields WHERE TableName=’Tasks’ ORDER BY FieldName” ; “” ; “”  )
// Returns a sorted list of FieldNames for the “Tasks” table occurrence in the current FileMaker Pro v12 file

returns:

Assignee | MATCH FIELD
Completed
Due Date
EVENT ID MATCH FIELD
Event
Overdue
Status
Status Sort Order
Task
To Do

ExecuteSQL ( “SELECT FieldName FROM FileMaker_Fields WHERE TableName='” & Get ( LayoutTableName ) & “‘ ORDER BY FieldName” ; “” ; “”  )
// Returns a sorted list of FieldNames for the table occurrence of the current layout in the current FileMaker Pro v12 file. I’m on the “Events” layout in this example

returns:

Address
Agenda Item Label Plural
Agenda Item Number | All
Agenda Item Number | Current
City
Contributor Number | All
Contributor Number | Assigned
Country
Date
Description
EVENT ID MATCH FIELD
Email
Event
Guest Label Plural
Guest Number | All
Guest Number | Attending
Layout Selector
Notes
Phone
Postal Code
QuickFind | iOS
Result Label Plural
Sort List Key
Sort Selection
State
Task Label Plural
Task Number | All
Task Number | To Do
Time Frame
Time Frame | Sort Order
Type
Venue

There’s very little official documentation about these virtual tables that I could find – if I’ve made any errors please let me know and I’ll update this post. I’ve created a couple of custom functions for 2 of the queries that I use the most:

ListBaseTables
ListFieldsFromTable