Coming Soon – fmEcommerce Link (Shopify Edition)

/0 Comments/in , , , , , , /by

Just over four months ago we released fmEcommerce Link (WooCommerce Edition), a FileMaker solution for integrating with WooCommerce. WooCommerce is a popular plugin for WordPress that powers over 39% of all online stores, including both the Databuzz Shop and the fmSMS Shop (you can read about how we came to develop fmEcommerce Link out of necessity here).

There are a number of other popular ecommerce platforms apart from WooCommerce, and we’ve had a number of requests to create a version of fmEcommerce Link for some of the more popular alternatives. Most of the requests were for a solution that integrates with Shopify, a leading cloud-based, multi-channel commerce platform designed for small and medium-sized businesses that is also popular with FileMaker users. Shopify currently powers over 400,000 businesses in approximately 175 countries and is trusted by brands such as Tesla, Red Bull, Nestle, GE, Kylie Cosmetics, and many more.

Shopify also has an established API that was suitable for integration with FileMaker, so it made sense for the next edition of fmEcommerce Link to work with Shopify. We’re in the final stages of development and testing but wanted to share some details of our latest product: fmEcommerce Link (Shopify Edition). You can subscribe to our newsletter to be notified when it’s released.

The initial release of fmEcommerce Link (Shopify Edition) will have the following features:

  • download Customers, Products, Orders, Shipping Countries, Locations and Discounts from Shopify
  • update existing Customer, Product and Order records from Shopify to FileMaker
  • download Orders between 2 dates
  • create/update a Customer record from FileMaker to Shopify
  • create/update a Product record from FileMaker to Shopify

Like our other products it will also have these features:

  • will work with FileMaker Pro v12, 13, 14, 15 and 16
  • can be hosted by FileMaker Pro or FileMaker Server
  • works with Macintosh and Windows
  • 100% unlocked for you to integrate into your own FileMaker solution

Here’s some more screenshots showing Customers and Orders downloaded from Shopify and the details for an individual Order:

We’ve put together a short preview video showing fmEcommerce Link (Shopify Edition) in action downloading Customers and Orders from Shopify to FileMaker. You can watch this below or via this link on YouTube:

PayPal launches PayPal Business in a Box (United States)

/0 Comments/in , , , , , , /by

As part of National Small Business Week in the United States PayPal today announced PayPal Business in a Box aimed at U.S. small business owners who would like to take the leap into online sales. You might be just starting a business for the first time but you don’t have web design capabilities to develop an online store or know how to set up online or point of sale payment methods, or an established offline business looking to move online.

PayPal have partnered with two of our favourite companies WooCommerce and Xero to deliver a simple, integrated solution that gives you the tools you need to start an online business. If you’re using the FileMaker platform to run your existing business you can also take advantage of our solutions that integrate WooCommerce and Xero with FileMaker:

FileMaker, WooCommerce and Xero make a great combination when it comes to running your business – we should know as we’ve been using these platforms for many years to manage our business, online store and accounting needs. We’ll be publishing a new article shortly about the benefits of integrating FileMaker with WooCommerce and Xero and how you can save time and money and avoid unnecessary double data entry.

Getting Ready for WooCommerce 2.7

/2 Comments/in , , , , /by

WooCommerce 2.7 is getting close to final candidate release – this promises to be a major release with lots of structural changes designed to improve performance. There will also be a number of changes to the WooCommerce REST API that our fmEcommerce Link (WooCommerce Edition) FileMaker solution integrates with.

You can read about all the changes in WooCommerce 2.7 on the WooCommerce blog – at this stage it looks like they will introduce a v2 of the REST API and keep the current v1 functionality so products like fmEcommerce Link will continue to work. We’ve reported a number of bugs with v1 of the REST API which will be fixed in the v2 API – we’ll keep monitoring the status of the changes to the REST API and incorporate the changes in the v2 API in a free update to fmEcommerce Link for all our existing customers.

There will be a number of changes to the REST API endpoints with the WooCommerce 2.7 release so we’ll need to make some associated changes and do a lot of testing to make sure all the existing functionality continues to work. Most of the changes related to Products and Variations which are the most complex endpoints to integrate with, so we’re looking forward to a more simplified way of creating and updating Product Variations.

The updated version of fmEcommerce Link will then only support WooCommerce 2.7 or later going forward (v2 of the REST API) – we’ll let everyone know when the new version is available once we’ve completed our development and testing.

fmEcommerce Link (WooCommerce Edition) Update

/0 Comments/in , , , , , /by

We’ve just released our first update to fmEcommerce Link (WooCommerce Edition) for 2017 – this release provides an alternative method for handling authentication for some servers that may not parse the Authorization header correctly, as well as making the process of working with Product Variations much easier.

The WooCommerce REST API docs mention that some servers may not parse the Authorization header correctly – we’ve never encountered this during the testing and development of fmEcommerce Link but recently a customer contacted us after having trouble getting started with fmEcommerce Link (WooCommerce Edition). Upon further investigation we noticed that were receiving the same error each time they attempted to download data from WooCommerce:

{
 "code": "woocommerce_rest_cannot_view", 
 "data": {
 "status": 401
 }, 
 "message": "Sorry, you cannot list resources."
}

After many hours of frustration we stumbled across the suggestion to switch from using HTTP Basic Auth and implemented a change to test this and had immediate success. We’ve decided to add a toggle that users can switch on/off in case anyone else in the future encounters this issue:

We’ve also made it easier to download, update, create and view Product Variations in the fmEcommerce Link (WooCommerce Edition) file:

and also show which variations were selected when viewing an Order:

This is a free update to all our existing customers – we have a number of items on the list to add, including Refunds (create a Refund in FileMaker and upload to WooCommerce) and download Reviews. 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.

fmEcommerce Link (WooCommerce Edition) and Webhooks

/1 Comment/in , , , , /by

When we released fmEcommerce Link (WooCommerce Edition) last week there was one feature that we wanted to include that wasn’t quite ready: Order Webhooks. The initial release of fmEcommerce Link lets you download orders from WooCommerce, either all at once or within a specified date range. For many users that will be sufficient as they might only need to download all orders from the previous day.

Some customers would prefer to be notified immediately the order was placed and have it automatically download into FileMaker – you could setup a server side schedule that ran every x minutes that checked for new orders since a specified timestamp, but it’s much more efficient to leverage Webhooks if they are available. A Webhook is simply a way of having WooCommerce send a notification to another service at a nominated URL when a particular event is triggered.

Webhooks were introduced in WooCommerce 2.2 and can trigger events each time you add, edit or delete orders, products, coupons or customers. New orders seemed the logical place to start – wouldn’t it be great if upon receiving a new order in WooCommerce it could be pushed to the fmAccounting Link FileMaker file automatically without you having to do anything?

We’ll be releasing a free update to fmEcommerce Link (WooCommerce Edition) in the next couple of days which will include support for Webhooks when a new Order is created. You will need to setup a Webhook and install the supplied PHP file on your FileMaker Server (you will need to enable Custom Web Publishing/PHP in your FileMaker Server Admin Console), and once this in place anytime a new Order is received in WooCommerce it will automatically be pushed to the fmEcommerce Link file, typically within a few seconds.

This was a feature that we wanted for ourselves to completely streamline the order entry process for our business – there’s nothing better than waking up in the morning and seeing new overnight orders already in the fmEcommerce Link file. Here’s a short video demonstrating this in action (you can also watch this on YouTube here):

 

 

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.

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.

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

FileMaker and eCommerce Integration – Part 2

/6 Comments/in , , , , , , , , , , , /by

shopping-cart-3-1546160-1280x960

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.

As a small business ourselves we also experienced this pain when we went live with our own online stores. Our first online store was for our oldest product fmSMS which allows you to send/receive SMS messages from the FileMaker platform – this has always had it’s own dedicated website/domain so it made sense for the store to live on the same site:

http://www.fmsms.com/shop/

A few years ago we also started selling the first of our fmAccounting Link products for the Xero accounting platform and it made sense to sell this via a store on our main Databuzz website:

http://www.databuzz.com.au/shop

So we now currently have 2 online stores located at different domains, but we will eventually merge these together to simplify things. As both stores were built using the WooCommerce plugin for WordPress and hosted with the same web hosting provider, we knew that any integration solution for one of the stores would work for both stores.

For each order that came through the store we need to perform the following actions:

  • check for an existing customer in our company FileMaker CRM and if no match is found create a new Contact record
  • create a new Invoice and associated Invoice Items
  • create a Payment record against the Invoice
  • push a copy of the Invoice to our accounting software (Xero in our case)
  • add the purchaser to a mailing list in MailChimp for future email newsletters/updates

The process starts with an email from the online store letting us know a new order has arrived:

New Customer Order

We would then have to copy and paste all the details into our FileMaker CRM, push the Invoice to Xero using our fmAccounting Link (Xero Edition) integration, then add the customer to the appropriate mailing list in in our MailChimp account. When you’re only dealing with a couple of orders a month you can probably cope with doing things manually, but once you start to get several orders a day you are then impacted by the time it takes to do all of these takes which are also prone to data entry errors. Like us you probably start wondering if there is a better way and can this process be automated.

The good new is that it can and having helped tens of customers in the past overcome similar FileMaker/eCommerce integration challenges so we knew where to start – ESS. ESS is the External SQL Data Sources feature that was first introduced way back with FileMaker Pro v9 and allows you to establish a live two-way connection between FileMaker Pro and the top SQL data sources. ESS originally supported these SQL data sources:

  • Microsoft  SQL Server
  • MySQL
  • Oracle

FileMaker Pro v15 introduced 2 new data sources:

  • IBM DB2
  • PostgreSQL

Most of the popular eCommerce stores are using one of the following backend databases to drive the store:

  • MySQL (used by WordPress/WooCommerce)
  • SQL Server
  • PostgreSQL

These are also supported ESS data sources so you can use the ESS feature to  get your FileMaker CRM talking to your online store. ESS allows you to view your SQL data from within FileMaker – it appears just like normal FileMaker tables. You can create new layouts to view the data, create relationships from your FileMaker tables to your ESS tables, access the SQL data from FileMaker scripts and more (there are some limitations and it does require setting up ODBC drivers – see the Accessing External SQL Data Sources (ESS) Overview and Troubleshooting for more details.

Once you have installed the appropriate ODBC driver and setup the System DSN you can then add an ESS table occurrence to your FileMaker relationships graph, just like you would for your normal FileMaker tables:

ESS Table Occurrences

You will need to have a basic understanding of your external SQL data source structure so you know which tables to add to your FileMaker graph and how they relate – details about WooCommerce can be found here. Once you’ve added your ESS table occurrences you can create new layouts based on each of these and start to view your online store data live in your FileMaker CRM. Here’s some examples showing some of the WooCommerce/WordPress tables that store online order details:

ESS Orders

ESS Orders Meta

ESS Order Items

ESS Order Item Meta

The above screenshots are showing data from 4 of the main tables that are used by WooCommerce to store order details:

  1. posts: this creates a record for each online order. This table is also used to store Product details
  2. postmeta: this stores a number of records related to each order, such as the billing/shipping and currency/tax details
  3. woocommerce_order_items: this stores line item details for each order
  4. woocommerce_order_itemmeta: this stores meta data about each order line item

As you can see by looking at these ESS tables in FileMaker we can see all the data about each order but it is located in at least 4 different tables, making aggregating the details each order so we can easily view the complete order challenging. We could create a number of FileMaker calculation fields to extra details about each order based on the meta_key for Orders and Order Line Items, but that would end up adding a lot of table occurrences and relationships to the graph and create another layout of unnecessary complexity.

There is a better way however that avoids all that unnecessary clutter on the graph – we can use SQL Views to create a more structured view of the SQL data we require. ESS fortunately also supports SQL Views which allow us to create a predefined SQL query that we then add to the relationship graph. We created 2 SQL views for Orders and Order Line Items to gather all the related meta data about each order and order item. When we add these to the graph and view them from a FileMaker layout here’s what we see for Orders:

ESS Orders View

and this for Order Items:

ESS Order Items View

Much better! For each Order we now get 1 record showing all the Order/Customer details, and for each Order Line Item we now get 1 record showing all the details about the Order Line Item, including the Product Price and SKU (the SKU is the same one used in Xero so it’s important that we can pass that through to Xero). We can then create a relationship between these 2 ESS table occurrences to relate an Order to its Order Items by the order_id value:

ESS Relationship

and be able to view a complete WooCommerce online order in FileMaker:

WC ESS Order

We now have a FileMaker layout showing all the details for a single WooCommerce/online order, including Customer Details, Line Item Details and related Product Details. From here’s a simple case of FileMaker scripting to move the data from the ESS tables to the native FileMaker tables (first checking for any existing Customers with the same name) and from there into Xero. We add a button to the Online Order layout to push the online order into out FileMaker CRM which handles all of these tasks, saving us around 15 minutes per online order (we have customers that are getting tens of orders every day so they time savings really start to add up).

If you’re not familiar with ESS it’s important to be aware of the following:

  • you will need to install ODBC drivers
  • if you’re hosting your file with FileMaker Server you can install the ODBC driver once on the FileMaker Server machine for all FileMaker Pro clients to use, which makes deployment a breeze
  • depending on your ESS data source and whether you are on Mac or Windows you may need to purchase the ODBC driver. There’s a full list of compatible ODBC drivers in the FileMaker Knowledge Base
  • you will need to get some documentation that explains how your SQL data source tables are structured so you know which tables to add to the relationship graph
  • when working with ESS tables it’s best to use a “read only” account that won’t let you edit any of the SQL data in case you accidentally edit/delete any of the online order records
  • your company firewall will need to allow access to the ODBC data source port
  • if you’re accessing a MySQL data source you will typically have to setup Remote Access to the MySQL database via your web hosting company (e.g. via cPanel).

In Part 3 of this series we’ll look into the options when you can’t use ESS and how you can still go about integrating your online shop with your FileMaker CRM. In the meantime if you would like to discuss integrating your online store with your FileMaker CRM please contact us.

FileMaker and eCommerce Integration – Part 1

FileMaker and eCommerce Integration – Part 3