fmAccounting Link (MYOB AccountRight Edition) Now Supports Jobs

/0 Comments/in , , , , /by

We’ve just finished an update to fmAccounting Link (MYOB AccountRight Edition) which now includes examples for working with Jobs, one of the most requested features from our customers. fmAccounting Link shows you how you can use FileMaker to:

  • download Jobs from MYOB to FileMaker (all Jobs or filtered by Start Date)
  • create Jobs in FileMaker and upload them to MYOB
  • update a single Job from MYOB
  • enter a Job against an Invoice Line Item or a General Journal Item
  • download a Job Budget from MYOB to FileMaker and send an updated version back to MYOB

This is a free update for all existing customers. You can view the full release notes on our version history page. If there are other features you would like to see in the core fmAccounting Link (MYOB AccountRight Edition) file please get in touch and let us know.

fmAccounting Link (MYOB AccountRight Edition) Updates – my.MYOB Login and General Journals

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

We’ve been busy working on some updates to fmAccounting Link (MYOB AccountRight Edition) over the past few weeks. fmAccounting Link (MYOB AccountRight Edition) was a popular item during our recent End Of Year sale and we’ve had some great feedback from customers about features they would like to see in future releases.

We’ve just released an updated version of fmAccounting Link  – v1.0.8. As well as the usual bug fixes we’ve added examples for working with the General Journals API endpoint – you can now download, create and update General Journals from FileMaker using fmAccounting Link. We’ve also added support for users that have linked their MYOB Company File User ID to their my.MYOB login – typically this is set using either of the following dialogs in AccountRight:

We couldn’t find anything about working with linked my.MYOB logins in the API documentation and after raising a ticket with MYOB developer support we were informed about the changes you need to make when working with linked my.MYOB accounts. We’ve added a new checkbox to the MYOB Company screen that allows you to flag that you’ll be using a linked my.MYOB login:

which handles the required changes automatically. For existing customers using older versions of fmAccounting Link you can simply disable a single script step to handle working with linked my.MYOB logins – see our FAQ page for details.

We’ve started working on adding examples for working with the JOBS API endpoint and hope to have this completed in the next few weeks. If there are features you would like to se in the core fmAccounting Link (MYOB AccountRight Edition) file please get in touch and let us know. You can view the full release notes on our version history page.

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

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.

Databuzz releases fmAccounting Link (MYOB Essentials Edition) – Integrate FileMaker Pro and MYOB Essentials Accounting Software

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

Sydney, Australia – May 17, 2016 – Databuzz today announced fmAccounting Link (MYOB Essentials Edition), a FileMaker solution that integrates with the MYOB Essentials Accounting Software.

fmAccounting Link (MYOB Essentials Edition) allows you to upload and download data between your FileMaker solution and MYOB Essentials, the easy online accounting that has everything your clients need to take care of business, including payroll. fmAccounting Link (MYOB Essentials Edition) removes double data entry and human errors saving your company significant time, money and hassle by automating the exchange of data between FileMaker and MYOB Essentials.

fmAccounting Link (MYOB Essentials Edition) is completely unlocked allowing you to integrate it into your FileMaker solution. You can copy and paste examples showing you how to authenticate with the MYOB Essentials API and upload Contacts, Invoices, Payments and more at the click of a button.

fmAccounting Link (MYOB Essentials Edition) features include:

  • works with FileMaker Pro v12, v13, v14 and v15
  • completely unlocked
  • can be hosted by FileMaker Pro or FileMaker Server
  • works with Macintosh and Windows

“Many small businesses use FileMaker Pro to track their Contacts and generate Invoices and MYOB Essentials for accounting,” said Andrew Duncan, Director of Databuzz. “You no longer need to do any double data entry in both applications – with fmAccounting Link (MYOB Essentials Edition) you can push and pull data between FileMaker and MYOB Essentials at the click of a button.”

fmAccounting Link (MYOB Essentials Edition) includes examples for the following MYOB Essentials API endpoints:

  • Businesses: select from all available MYOB Essentials Businesses that you have access to
  • Contacts: download and upload Contacts (Customers and Suppliers)
  • Invoices: download and upload Invoices (including Invoice line items)
  • Items (Products): download and upload Items (Products price list)
  • Payments: upload Payments against an Invoice
  • Account Codes: download Account Codes from MYOB Essentials
  • Tax Types: download Tax Types from MYOB Essentials

 Availability, Pricing, and Compatibility

fmAccounting Link is available in a number of licenses: Company, Vertical Solution and Developer. It is available now from the Databuzz website at http://www.databuzz.com.au/fmaccounting-link-myob-essentials-edition/. Workgroup Licenses start at AUD $495.00. fmAccounting Link (MYOB Essentials Edition) requires FileMaker Pro v12, v13, v14 or v15 and a subscription to MYOB Essentials.

Media/Customer Contact:

Andrew Duncan

Phone: +61 418 468 103

sales@databuzz.com.au

About Databuzz: Databuzz is a long standing member of the FileMaker Business Alliance. We have 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 14 Developer. 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.

fmAccounting Link (MYOB AccountRight Edition) and FileMaker Pro v15

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

We’re pleased to report that fmAccounting Link (MYOB AccountRight Edition) v1 is compatible with FileMaker Pro/Pro Advanced v15 that was released today. We haven’t encountered any issues so far in our testing and have been able to authenticate, download from AccountRight to FileMaker and upload from FileMaker to AccountRight successfully.

If you encounter any issues with fmAccounting Link (MYOB AccountRight Edition) v1 and FileMaker Pro v15 please let us know.

fmAccounting Link (MYOB Essentials Edition) Preview Video

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

We’ve just uploaded our first preview video for fmAccounting Link (MYOB Essentials Edition). This video demonstrates the following:

  • authenticating against the MYOB Essentials API
  • downloading a list of available MYOB Essentials Businesses
  • downloading Inventory Items, Tax Types and Chart of Accounts from MYOB Essentials
  • uploading a Contact from FileMaker Pro to MYOB Essentials
  • uploading an Invoice from FileMaker Pro to MYOB Essentials
  • uploading a Payment from FileMaker Pro to MYOB Essentials

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

We should be releasing fmAccounting Link (MYOB Essentials Edition) in the next couple of weeks – please Contact Us if you have any questions in the meantime.

Databuzz releases fmAccounting Link (MYOB AccountRight Edition) – Integrate FileMaker Pro and MYOB AccountRight Accounting Software

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

Sydney, Australia – April 12, 2016 – Databuzz today announced fmAccounting Link (MYOB AccountRight Edition), a FileMaker solution that integrates with the MYOB AccountRight Accounting Software.

fmAccounting Link (MYOB AccountRight Edition) allows you to upload and download data between your FileMaker solution and MYOB AccountRight, the powerful accounting software with business management capabilities that allows you to work off or online. fmAccounting Link (MYOB AccountRight Edition) removes double data entry and human errors saving your company significant time, money and hassle by automating the exchange of data between FileMaker and MYOB AccountRight.

fmAccounting Link (MYOB AccountRight Edition) is completely unlocked allowing you to integrate it into your FileMaker solution. You can copy and paste examples showing you how to authenticate with the MYOB AccountRight API and upload Contacts, Invoices, Payments and more at the click of a button.

fmAccounting Link (MYOB AccountRight Edition) features include:

  • works with FileMaker Pro v12, v13 and v14
  • completely unlocked
  • can be hosted by FileMaker Pro or FileMaker Server
  • works with Macintosh and Windows
  • works with MYOB AccountRight running in the Cloud or on the Desktop (online and offline)
  • works with MYOB AccountRight Live 2013, 2014, 2015 and 2016

“Previous integrations between FileMaker and MYOB AccountRight have involved manual exports and imports of multiple text files or a Windows only ODBC connection,” said Andrew Duncan, Director of Databuzz. “These are now a thing of the past – with fmAccounting Link (MYOB AccountRight Edition) you can push and pull data between FileMaker and MYOB AccountRight at the click of a button.”

fmAccounting Link (MYOB AccountRight Edition) includes examples for the following MYOB AccountRight API endpoints:

  • Company Files: select from all available MYOB AccountRight Company Files that you have access to
  • Contacts: download and upload Contacts (Customers and Suppliers)
  • Invoices: download and upload Invoices (including Invoice line items)
  • Items (Products): download and upload Items (Products price list)
  • Payments: download and upload Payments against an Invoice
  • Employees: download and upload Employees
  • Account Codes: download Account Codes from MYOB AccountRight
  • Tax Codes: download Tax Codes from MYOB AccountRight
  • Categories: download Categories from MYOB AccountRight

Availability, Pricing, and Compatibility

fmAccounting Link is available in a number of licenses: Company, Vertical Solution and Developer. It is available now from the Databuzz website at http://www.databuzz.com.au/fmaccounting-link-myob-accountright-edition/. Company Licenses start at AUD $495.00. fmAccounting Link (MYOB AccountRight Edition) requires FileMaker Pro v12, v13 or v14 and MYOB AccountRight Live 2013, 2014, 2015 or 2016.

Media/Customer Contact:

Andrew Duncan

Phone: +61 418 468 103

sales@databuzz.com.au

http://www.databuzz.com.au

About Databuzz: Databuzz is a long standing member of the FileMaker Business Alliance. We have 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 14 Developer. 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.