Coming Soon – fmEcommerce Link (WooCommerce Edition)

/2 Comments/in , , , , , , , /by

Earlier this year we published a series of articles about FileMaker and eCommerce Integration, highlighting our use of External SQL Data Sources (ESS) to make our online store orders visible in our internal FileMaker CRM. We’ve been using this method of integration successfully for many years and it has saved us countless hours by not having to manually re-enter online orders in our main FileMaker business app and our accounting software (Xero).

That was until recently when we started getting this error message every time we navigated to the online orders layouts:odbc-error

Suddenly we could no longer see our online order records! Everything was still working on the website and orders were still being submitted successfully (we receive an email for each order) so we started troubleshooting this to try and get to the bottom of it. After many frustrating hours we still could not establish the ODBC connection to our server, so we made contact with our web host and opened a ticket explaining our issue and describing how this had previously worked fine for many years. We were then informed that they had disabled remote MySQL access for security reasons and there were no exceptions – talk about a great way to annoy your customers by switching off access and not informing your customers about this!

It was still possible to connect but you had to create an SSH tunnel first – we then wasted another couple of days setting up the SSH tunnel which would work but then drop out, and we had issues with automating this so it could run when the server wasn’t logged in. After many days of frustration and lost productivity I decided to abandon the SSH tunnel efforts as it was proving too unreliable and too look at other options. Databuzz specialises in FileMaker integration and having recently written an article on working with eCommerce APIs and Webhooks I revisited my article and knew what needed to be done.

Our website stores are built with WooCommerce, a popular plugin for WordPress that powers over 39% of all online stores. WooCommerce offer both a push and pull API so you can have new orders automatically pushed to a server, and also download new orders on demand (e.g. get all new Orders today). The push option is the more complicated of the two options as it requires FileMaker Server, Customer Web Publishing, PHP pages and Webhooks to be setup, so we decided to focus initially on the WooCommerce REST API as that allows us to query WooCommerce for any new Orders and download the data directly into FileMaker. This can be run regardless of whether the file is hosted with FileMaker Server or just using FileMaker Pro, and we can also setup server side schedules to run each night and download new data. The REST API also allows us to push data from FileMaker to WooCommerce, such as Product updates, which is something customers have requested in the past.

Fast forward a few months and we are in the final stages of development and testing of our latest product – fmEcommerce Link (WooCommerce Edition):

preview-home

fmEcommerce Link is our solution to connecting FileMaker to WooCommerce when you can’t make a direct ESS/ODBC connection – you use the fmEcommerce Link file to query your WooCommerce store for new orders, product inventory changes, new customers and also to push data back to WooCommerce such as new Products. You can link the fmEcommerce Link file to your existing FileMaker solution to push orders from WooCommerce into your main FileMaker business app, or recreate the same functionality inside your FileMaker business app as the fmEcommerce Link will be 100% unlocked for you to explore.

Here’s some screenshots showing data that we have downloaded from our test WooCommerce site:

preview-order-details

preview-orders-list

preview-products-list

I’ll be posting some videos demonstrating the core functionality of fmEcommerce Link over the coming days and would love to hear any feedback from existing FileMaker/WooCommerce users about any features you would like to see in the product – just leave a comment below.

fmAccounting Link (Xero Edition) Updates – Reports and Invoice Attachments

/0 Comments/in , , , , /by

We love hearing back from our customers about new features/examples they would like to see in the core fmAccounting Link (Xero Edition) file. We recently had some requests for examples showing how to upload files/attachments from FileMaker to an Invoice and the ability to download a Report from Xero to FileMaker.

We’ve had the Files API on our list of future enhancements as well as the Reports endpoint for a while, so we took this opportunity to dive in and get some of the basic functionality for these developed so our customers can start using them now.

For the Reports endpoint we’ve started with the Trial Balance report – we’ve created a new Reports module in the fmAccounting Link file that we will add to over time with additional reports that are available via the Xero API. The Trial Balance report lets you specify the date to run the report as at, and then the report is downloaded into the fmAccounting Link (Xero Edition) file and looks like this:

trial-balance-report

For the Invoice Attachments we’ve added a new TAB to the Invoice details layout allowing you to select a file to upload to Xero and attach it to the current Invoice:

invoice-attachments

You can upload up to 10 attachments (each up to 3mb in size) per invoice, once the invoice has been created in Xero. To keep things simple we’re not storing the selected file in a container field – we’re just storing the path to the file (you can change this behaviour in your version of the fmAccounting Link file). You can upload the common file formats, such as PDF, JPG, Word, Excel and PNG files, and you can specify for each attachment whether to include that in the Online Invoice so that the attachment appears when a user clicks on the online invoice link. A full list of file types that you can upload is available on the Xero website.

One limitation of the Attachments API at the moment is that you cannot currently delete an attachment in Xero from FileMaker – you will need to login to Xero to delete any attachments that have been uploaded to an Invoice.

We’re working on the Prepayments endpoint next and should have that finished in December, and then we’ll start adding more of the Reports that are available via the Xero API. If there’s any examples that we don’t currently have that you would like to see please get in touch and let us know. Further details on fmAccounting Link (Xero Edition) including the full list of examples are available on the fmAccounting Link (Xero Edition) product page.

FileMaker 15 Certified Developer

/0 Comments/in , , , , /by

Databuzz is pleased to announce that Andrew Duncan recently passed the Developer Essentials for FileMaker 15 Certification Exam and is now FileMaker Certified in v8, 9, 10, 11, 12, 13, 14 and 15. FileMaker 15 Certification is the official credential offered by FileMaker, Inc.

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. Being a certified developer demonstrates to clients, peers and management that you’ve achieved an essential level of knowledge, experience and skills in developing FileMaker solutions.

certified_15_logo_4clr

Credit Card Tokens and Payment Processing Video

/1 Comment/in , , , , , , /by

Recently we wrote an article about the benefits of automating the processing of credit card payments using your FileMaker solution and why you shouldn’t be storing unencrypted credit card numbers in your FileMaker database. We wanted to demonstrate how easy it is to tokenise a credit card number and then charge that token when processing a transaction so we put together a short video demonstrating this. We’re using the eWay Payment Gateway in this example.

You can watch the video below or directly on YouTube via this link.

fmSMS Now Supports 75 SMS Gateways Internationally

/0 Comments/in , , , /by

We’ve just added another SMS Gateway (Zipwhip) to the list of supported SMS Gateways for fmSMS, our award winning solution that lets you send and receive SMS messages using the FileMaker platform. This brings the total number of supported SMS Gateways to 75!

Zipwhip was a bit different to most SMS Gateways that fmSMS supports – they allow you to enable text messaging for your existing business phone number (Landline or Toll-Free Phone Number). This allows you to have one number for customers to call you and text you – normally you would need to get a dedicated virtual mobile number to handle the incoming text/SMS messages.

SMS is still an extremely effective form a communication – 80% of consumers prefer to be contacted by a business via SMS/texting over a phone call. 98% of consumer text messages are read – only 10% of emails are even opened.

You can download a trial version of fmSMS and setup a trial account with one of the SMS Gateways to see how you can use FileMaker to send SMS Messages to your customers, employees, prospects etc.

FileMaker and Xero Integration Webinar Recording

/3 Comments/in , , , , , , , /by
Read more

FileMaker and Xero Integration Webinar

/1 Comment/in , , , , , /by

Are you using FileMaker to run your business and also Xero for your accounting software? Would you like to see how they can be integrated together so you can streamline your workflow and protect your sensitive Xero data at the same time?

Join Andrew Duncan from Databuzz and David Borgnis, Business Development Manager, APAC at FileMaker Inc. for an informative and explorative 60 minute presentation. The webinar details are:

Date: Thursday, 3 November, 2016
Time: 12 noon (AEST)
Duration: 60 minutes
Register now

Integrating FileMaker and Xero allows you to remove double data entry and human errors saving your company significant time, money and hassle by automating the exchange of data between FileMaker and Xero. Xero was recently named the most-loved accounting software for a second year in a row, beating MYOB and Intuit Quickbooks, scoring five stars across seven criteria. It was named the preferred accounting software of Australian accountants – as you can see from the chart below it was by a significant margin:

afr-chart-1-1_800x480_acf_cropped

In the webinar will demonstrate:

  • how to setup the authentication between FileMaker and Xero without giving your staff direct access to Xero
  • how to download data from Xero into FileMaker (Chart of Accounts, Tax Rates, Inventory Items etc)
  • how to push a Sales Invoice from FileMaker to Xero (and create a Contact in Xero at the same time)
  • how to push a Payment for a Sales Invoice from FileMaker to Xero

We hope you can join us for the webinar – if you have anything you would like addressed in the webinar please leave a comment below. For further details on fmAccounting Link (Xero Edition), our FileMaker solution for integrating with Xero please visit the fmAccounting Link (Xero Edition) product page.

Update: thanks to everyone who was able to attend the webinar. If you weren’t able to make the live webinar the recording is now available here.

fmSMS and Emoji Support 😀

/2 Comments/in , , , , , , , /by

At the recent FileMaker Developer Conference I was doing one of my fmSMS demonstrations to an attendee which involved me sending a message from fmSMS to their mobile phone (showing how you can use FileMaker to send SMS messages) and then having them send a reply back to demonstrate how you can use fmSMS to receive incoming messages directly into FileMaker. Normally the reply is a simple text reply of a few words, but this one was a bit different as you can see here:

screen-shot-2016-10-21-at-10-42-30-pm

I was pleasantly surprised to see that the Emoji reply had made it all the way back from their phone to fmSMS via the SMS Gateway and the PHP page that is used to convert the incoming reply into a FileMaker record without me having to do anything to handle the Unicode characters – it just worked! I decided to do a bit more research into this to see how different SMS Gateways handled Emoji characters as a way of testing their Unicode support (there are now hundreds of Emoji characters encoded in the Unicode standards).

It’s important here to understand the history of SMS – back when the GSM standard was being adopted the mobile phone industry decided on a standard set of characters called GSM 03.38. Support for this character set became mandatory for all GSM handsets and network elements (carriers etc). The GSM character set includes the English alphabet ( A-Z ), numbers (0 – 9) and some special characters, and the size of a single SMS was limited to 160 characters. The 160 character maximum actually comes from the fact that you can encode 160 7-bit characters into 140 bytes – 140 bytes being the limit for the size of a message.

Unicode characters use several GSM characters to describe each Unicode character which means that you won’t be able to send as many characters in your SMS when include Unicode characters. Depending on which special characters you’re sending, you may only be able to send between 35 and 70 characters. To send a message that is longer than the 160 characters/140 bytes limit the message needs to use Concatenation, which involves breaking up the message over multiple SMS messages.

Thankfully most SMS Gateways and mobile phone handsets support concatenation where multiple messages are joined together to form a single message on the handset, even though that single message is greater than 160 characters (or 140 bytes). As each message needs to broken up into individual 140 byte messages the SMS Gateways will charge you for each individual message, even though they appear as a single message to the recipient.

Here’s an example of such a message using the GSM character set which is 312 characters but appears as a single received message on the phone:

long-message

SMS Gateways that handle the encoding of Unicode characters make life easy for us developers – we don’t have to do anything when sending or receiving messages from FileMaker. One such SMS Gateway is Twilio which I used in my tests and was able to handle the Emoji characters with ease – they have a number of articles on their website that go into the details of their support for Unicode. I created an outgoing message in fmSMS with some Emoji characters (FileMaker Pro 7 and later being a fully Unicode aware application):

screen-shot-2016-10-21-at-10-26-51-pm

and sent the message using a number of different SMS Gateways. Where the SMS Gateway supported Unicode the message appeared exactly as it was sent:

emoji-received

 

 

I tried with some SMS Gateways that don’t have native support for Unicode characters and on the handset here’s what the same message looked like:

no-emoji

I then sent some replies containing Emoji characters back to fmSMS which has a “chat view” that shows all the sent/received messages to an individual contact – here’s our Emoji conversation:

screen-shot-2016-10-21-at-10-33-36-pm

As you can see the incoming message containing Emoji characters was received successfully using the Twilio SMS Gateway once again.

Unicode support isn’t only important for Emojis – if you need to send accented characters or messages in Greek, Cyrillic, Hebrew, Arabic, Thai, Chinese, Japanese, Korean etc then you will be thankful for Unicode support as well. Just remember that when sending messages containing Unicode characters the standard 160 character test doesn’t apply – I’m yet to find a way to have a Unicode character check that converts back to the GSM character set in FileMaker so I can accurately count the number of credits required for each message, regardless of whether it contains GMS or Unicode characters). If anyone knows of a way please let me know in the comments below. 😃

Are you still processing credit card payments manually?

/3 Comments/in , , , , , , , /by

american-express-89024_640

Do you process credit card transactions manually and wonder if there is a better way? The good news is that there is a better more automated way that can integrate with your custom FileMaker solution and save your staff time and your business money.

Chances are if you’re a small business that sells goods or services your customers are going to want to pay by credit card, and having options and making it easy for customers to pay your invoices is a good thing for your business. Once you start accepting credit card payments however you need to comply with the PCI DSS (Payment Card Industry Data Security Standard) to help protect card data and prevent payment data theft. Small businesses are increasingly at risk for payment data theft – nearly half of cyberattacks worldwide in 2015 were against businesses with less than 250 workers according to cybersecurity firm Symantec.

The easiest way to protect against data breaches is to not store card data at all, however that isn’t always practical, especially if you’re selling an ongoing service that requires ongoing payments (e.g. a monthly subscription service). Whilst you definitely should not be storing unencrypted credit card data in your FileMaker solution that any employee can access, you can take advantage of encryption and tokenisation technologies that allow you to store an “alias” or token in your FileMaker solution and use that to processes future charges. Here’s how it works:

  1. customer provides credit card details to pay an Invoice
  2. you send an encrypted HTTPS request to a PCI DSS compliant credit card gateway who store the credit card details (as they are PCI DSS compliant) and send you back a token and a masked version of the credit card number (e.g. 512345…346)
  3. you store this token in your FileMaker transaction record. As this is a token and not a real credit card number it’s completely useless if stolen. You can also store the masked version of the card number in case you need to confirm with the customer which card number you are charging
  4. when you need to process a payment in the future you make another HTTPS request to the credit card gateway requesting a payment and referencing the token
  5. the credit card gateway returns a response indicating whether the transaction was processed successfully or if there was an error (e.g. declined, insufficient funds etc)

This can all be automated in a FileMaker solution allowing staff to process a payment or tokenise a card at the click of a button. We’ve worked with many PCI DSS compliant credit card gateways such as Stripe, eWay, BPOINT and Authorize.Net to help customers automate the process of processing credit card payments securely in their FileMaker solutions. If you’re currently storing credit card numbers in your FileMaker solution and would like to tokenise these we can also help you batch process these.

If you would like to discuss implementing a secure credit card processing system for your FileMaker solution plesae get in touch for a free initial consultation. For more information on how you can protect card data the Payment Card Industry has a number of guides for small businesses, including:

Update: we’ve published a short video demonstrating how you can use FileMaker to tokenise a credit card and then process a transaction by referencing that token.

FileMaker and eCommerce Integration – Part 3

/1 Comment/in , , , , , , , , , , /by

puzzle-1152793_1280

In Part 1 of our series on FileMaker and eCommerce Integration we outlined the challenges many small businesses face when they go live with an online store and the new workflow challenges that can create, leading to the prospect of having to do double data entry in multiple places. In Part 2 we showed how you eliminate any double data entry by making your online store visible to your FileMaker solution by using the ESS (External SQL Data Sources) feature of FileMaker Pro/Server, allowing users to see online orders on a normal FileMaker layout.

Not all FileMaker Pro solutions will be able to take advantage of the ESS feature however for a variety of reasons, including:

  • your online store doesn’t use a supported ESS data source
  • your online store hosting provider doesn’t allow remote SQL access
  • your company firewall won’t allow ESS access to the hosting provider
  • you wish to avoid the expense of purchasing ODBC drivers

There are a number of alternatives to having a “live” view of your online orders using ESS which can be broadly defined as either a push or pull approach, whereby data is either pushed from the online store to your FileMaker solution or pulled/downloaded from your online store by your FileMaker solution. Like all solutions there are pros and cons to each approach and the particulars of how your FileMaker solution is hosted will determine which options are available to you.

In the following examples we’re going to be using the WooCommerce plugin for WordPress as it offers both a push and pull API and is a popular eCommerce store, powering over 37% of all online stores at the time of writing.

Push Online Orders to FileMaker – the push approach is usually considered the most optimal solution as it is only invoked when there is new data to transfer, thus reducing the number of unnecessary requests to the online store to check for new orders. In WooCommerce you implement a push solution through the use of Webhooks – Webhooks are are very common form of server event notifications which trigger an action by sending a request to a URL that you specify. WooCommerce has a number of Webhooks that you can activate, for example each time a new order is created.

We’ve helped many customers over the years implement a Webhook solution that works as follows:

  • a new order is created on the customer’s online store
  • a Webhook is triggered which sends the details of the new order as JSON encoded data to a URL (a PHP page) on the customer’s FileMaker Server
  • the PHP file uses the FileMaker PHP API to convert the JSON encoded data into a new customer record, order record and order line items

The customer also receives an email for each new order, which prompts them to open their FileMaker database and review the order details and ship any required products then push the invoice to their accounting software (Xero, MYOB etc). The customer hasn’t had to do any double data entry or query the online store for new orders – everything is pushed through as it happens. It does require the customer to have FileMaker Server with Custom Web Publishing enabled and allow external access to the PHP file hosted on their server.

Pull Online Orders to FileMaker – if the push approach is not a viable option WooCommerce also offers a REST API that you can also use with FileMaker Pro. The WooCommerce REST API allows you to query your WooCommerce online store and retrieve details about customers, orders, products etc, as well as being able to push data from FileMaker to WooCommerce if necessary. A typical solution using the WooCommerce REST API works as follows:

  • customer receives an email notification from the WooCommerce store about a new order
  • customer then clicks a button in their FileMaker solution to query the online store for any new orders since a timestamp (typically the last time they checked for new orders)
  • FileMaker sends a REST API request for any new order details and receives a JSON encoded response from the WooCommerce REST API with details about each order
  • the response is parsed out to create new customer, order and order item records

Once again the customer has been able to eliminate any double data entry and simply has to click a button in FileMaker to get all the new order details.

As we have illustrated in this series there are typically a number of options available when it comes to integrating your online store with your FileMaker CRM, whether that’s a direct live view using ESS or having new orders pushed or pulled into your FileMaker solution. With an integration into your accounting software such as Xero, MYOB AccountRight or MYOB Essentials you can completely eliminate any double data entry for the entire order and sit back and watch as the data flows from your online store to FileMaker and then to your accounting software.

If you would like to discuss integrating your online store with your FileMaker CRM please get in touch for a free initial consultation.

FileMaker and eCommerce Integration – Part 1

FileMaker and eCommerce Integration – Part 2

FileMaker and eCommerce Integration – Part 3