FileMaker Inc. Releases Xero eBook

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

filemaker-xero-ebook

FileMaker Inc. have put together a number of resources outlining the benefits of Xero integration with a FileMaker custom app, focussing on how to eliminate duplicate data entry between FileMaker and Xero and centralise your Customer, Inventory and Invoice data whilst protecting your Xero financial data. I’m pleased to report that our recent Xero Webinar and our fmAccounting Link (Xero Edition) solution are both featured.

You can access the eBook and other resources from the FileMaker Inc website. If you would like to discuss integrating Xero with your FileMaker business app please get in touch.

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

fmAccounting Link (Xero Edition) Update

/2 Comments/in , , , , /by

Over the past few months we’ve been busy adding new examples and features to our fmAccounting Link (Xero Edition) solution based on customer requests and our own list of features we’d like to see. We now have new examples for the following Xero API endpoints:

  • Purchase Orders
  • Receipts
  • Expense Claims
  • Users
  • Currencies
  • Branding Themes
  • Credit Notes

We’ve also added the ability to download the Xero Invoice PDF as well as the Invoice Online URL – this allows you to include these in emails that you send to your customers (e.g. a ‘pay now’ link). We’re currently working on Overpayments and then will tackle Prepayments. 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.

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

Databuzz showcases SMS and Accounting Integration Solutions at 2016 FileMaker Developer Conference

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

Databuzz will be demonstrating their SMS and Accounting integration solutions at the 2016 FileMaker Developer Conference this week in Las Vegas:

  • fmSMS: our award winning 2 way SMS solution for FileMaker Pro, FileMaker Go, FileMaker WebDirect and FileMaker Server. fmSMS allows you to send an SMS from FileMaker Pro, FileMaker Go and FileMaker Server to almost any mobile phone in the world, reaching over 800 networks in more than 200 countries. You can get more information about fmSMS from the fmSMS website at http://fmsms.com
  • fmAccounting Link (Xero Edition): our solution that integrates between FileMaker Pro and the Xero Accounting Software. Xero is the market-leading small business cloud accounting software in Australia, New Zealand and the United Kingdom and is making great progress in the United States market. fmAccounting Link (Xero Edition) lets you upload and download Contacts, Invoices, Payments, Bills, Timesheets and more between FileMaker and Xero. You can get more information from the fmAccounting Link (Xero Edition) product page at http://www.databuzz.com.au/fmaccounting-link-xero-edition/
  • fmAccounting Link (MYOB AccountRight Edition): our solution that integrates between FileMaker Pro and the MYOB AccountRight Accounting Software (Australia and New Zealand). MYOB AccountRight runs either in the Cloud or on the Desktop, and fmAccounting Link (MYOB AccountRight Edition) allows you to upload Contacts, Invoices, Payments and more at the click of a button. You can get more information from the fmAccounting Link (MYOB AccountRight Edition) product page at http://www.databuzz.com.au/fmaccounting-link-myob-accountright- edition/
  • fmAccounting Link (MYOB Essentials Edition): our latest solution that integrates between FileMaker Pro and the MYOB Essentials Accounting Software, the easy online accounting software from MYOB (Australia and New Zealand). fmAccounting Link (MYOB Essentials Edition) allows you to upload Contacts, Invoices, Payments and more at the click of a button. You can get more information at the fmAccounting Link (MYOB Essentials Edition) product page at http://www.databuzz.com.au/fmaccounting-link-myob-essentials-edition/

    Databuzz will also be offering a DevCon show special for the entire week – all products will be discounted by 10% when you purchase via our online stores at:

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

    http://fmsms.com/shop

Wells Fargo Now Sharing Data with Xero

/1 Comment/in , , , , /by

Last week Xero announced that it had entered into a data sharing agreement with Wells Fargo, one of the top US banks. Xero users will now be able to get bank feeds directly from Wells Fargo into Xero without having to manually export and import files. Xero is the first major technology company to partner with Wells Fargo to help their common customers share data between their two platforms.

Xero recently surpassed more than 700,000 subscribers around the world and North America is their fastest growing market with more than 62,000 subscribers. In the UK, with the addition of Barclays earlier this year, Xero now has five of the top six banks already running direct feeds to Xero giving them coverage for over 90% of UK’s small businesses.

We’ve noticed an increase in the number of enquires about FileMaker and Xero integration from North America over the past 12 months, particularly from customers looking for an alternative to QuickBooks. We can expect more major US banking integrations in the coming months, similar to what we have seen in Australia, New Zealand and the UK.

If you’re looking for a “do it yourself” option for FileMaker Xero integration check out fmAccounting Link (Xero Edition) – it includes examples for authenticating, uploading and downloading Contacts, Invoices, Payments and more. It’s 100% unlocked allowing you to integrate the functionality into your existing FileMaker solution, helping your company save time and money by removing the double data entry between FileMaker and Xero.

This article in Inc. magazine has an interesting perspective on what the Wells Fargo Xero agreement will mean for small business customers in the US.

fmAccounting Link (Xero Edition) Update

/1 Comment/in , , , , /by

We’ve just released an update to fmAccounting Link (Xero Edition) – v1.86. You can read about the changes in the version history notes but here are some of the highlights:

  • added a <Reference> field for Invoices POST calc so you can set the customer reference number
  • updated the uploading of an Invoice to Xero to display any ApiException errors if present (more meaningful error messages)
  • Accounts: added portals to Accounts Form layout showing related Invoice Items, Bill Items and Payments
  • Invoices: added script to GET the Xero online invoice url (the URL that customers can click on the Xero Invoice to make an online payment)
  • Invoices: added script/button to update the status from Xero for the current Invoice. This allows you to periodically check the status of any outstanding invoices from FileMaker to see if they have been paid in Xero, which is particularly helpful when you only record payments in Xero
  • Invoices: added script to GET the Xero Invoice PDF. This lets you download the same PDF that Xero generates to a FileMaker container field, allowing you to file it or email it etc.
  • Invoices: added a Mark as Sent flag for the Invoice and added SentToContact to XeroInvoicePOSTCalc field. If you’re using Xero’s new email reminders feature this is required to let Xero know the Invoice has been sent to the customer

This update is once again free to all existing customers – just download the latest version from the same link from your order email. We’re working on the next round of changes which will include examples for the following Xero API endpoints:

  • Expense Claims
  • Purchase Orders

The Benefits of FileMaker Integration with your Accounting Software

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

integration

The start of a new financial year is a great time to assess how your business has performed over the previous 12 months and start making plans for the next financial year. It’s also a good time to assess your business processes and put in place systems that help streamline the way your business works and contribute to the bottom line in the next fiscal year.

For Australian businesses the new financial year starts in a few weeks (1 July), for New Zealand and the United Kingdom is was on 1 April, for Canada it was 1 January and for the United States it was 1 October.

If you’re planning to make any changes to your accounting software and systems the start of a new financial year is the perfect time to implement these changes. You can use the weeks (and months) leading up to the new financial year to decide on any changes you wish to make, then develop and test these changes during the end of the current financial year before going live at the start of the new financial year. If you can’t wait for the start of a new financial year the start of a new quarter is usually the next best time.

Some of the questions you might wish to consider include:

  • does my Customer Relationship Management (CRM) software “talk” to my accounting software?
  • are my staff doing double data entry of Customers, Invoices, Payments etc in my CRM and my accounting software?
  • if I could eliminate any double data entry would that free up my staff to work on more productive tasks?

The ability to integrate CRM software with Accounting software has become much easier thanks to the rise of APIs. An API is an Application Programming Interface that a company makes available to other software developers – essentially it is a series of instructions for what you need to do to get your software talking to their software. In the small business space accounting software vendors such as Xero, MYOB and Saasu all provide well documented and reliable APIs that you can use to integrate your CRM software with their accounting software.

Having an API allows the accounting software applications to create an ecosystem of “add ons” that extend the functionality of usefulness of the accounting software, for example having transactions from a point of sale system automatically uploaded to the accounting software at the end of the day or being able to accept online payments for an invoice.

If you’re using FileMaker as your CRM application to track your customers, sales, invoices and payments there are a number of advantages of integrating it with your accounting software, including:

  • no more double data entry in FileMaker and the accounting software. Enter the invoice once in FileMaker, click a button and the invoice is pushed to your accounting software in a few seconds
  • less errors between your CRM system and your accounting system. Being able to push invoices, payments etc from your main system to your accounting system means there is less likelihood of data entry errors between the 2 systems
  • free up staff time for more productive work. Rather than having staff spend countless hours each week doing double data entry they can be freed up for more productive (and enjoyable) work, such as following up on sales leads, customer service, marketing etc.
  • it also allows staff to push data to the accounting software without giving them direct access to the accounting software

Having implemented a number of FileMaker/CRM and accounting integrations over the past 3 years we’ve seen the benefits first hand. One customer that processed hundreds of sales every fortnight has now freed up 1-2 hours of time each day for staff that were responsible for the double data entry of invoices. Now they enter the sale once into FileMaker, click a button and a few seconds later get confirmation that the invoice has been uploaded.

We’ve also integrated FileMaker with our accounting software in our business. Before the integration the workflow for processing an online sale was something like this:

  1. customer makes an online purchase from our webstore and a notification is sent us via email with the order details
  2. Databuzz staff manually create a new order in our FileMaker CRM system
  3. Databuzz staff then manually create the same invoice in our accounting software
  4. Once the invoice has been created we then manually add the payment details

Now that we have integrated our FileMaker CRM with our online webstore and our accounting software the process is this:

  1. customer makes an online purchase from our webstore and a notification is sent us via email with the order details
  2. Databuzz staff review the order in our FileMaker CRM which can show webstore orders live, then click a button to push that sale to our CRM and online accounting software, including the Customer, Invoice and Payment details

That saves around 5-10 minutes of data entry time per order – multiple this by tens or hundreds of orders and you can see the time savings add up quickly.

If you would like to discuss integrating your FileMaker solution with either Xero, MYOB AccountRight or MYOB Essentials please contact us for a free initial consultation to discuss your requirements. We also have a number of products which allow you or your in-house/external FileMaker developer to do the integration yourself:

A typical integration usually takes around 4-8 hours – in that time we can have your FileMaker system authenticating with your accounting software, uploading Contacts, Invoices, Payments and more. Imagine how much time and money your business could save by integrating your CRM with your accounting software.