FileMaker Custom Integrations

/0 Comments/in , , , /by

The Reconnect.Brisbane Claris FileMaker Developer conference gets underway tomorrow in Brisbane and it’s shaping up to be a great conference. With attendees from 9 countries and sponsors from 6 countries it’s going to have a real international flavour this year.

Databuzz is pleased to once again sponsor the Reconnect conference and I’ll be doing a short sponsor presentation where I’ll showcase our FileMaker integration solutions and also talk a little bit about some interesting custom integration projects we’ve worked on over the past year.

In addition to developing our integration solutions we work with customers all around the world helping them integrate their FileMaker solutions with a variety of platforms including:

Over the past 12 months we’ve worked on some interesting and challenging integration projects – here’s a summary of some of these:

Microsoft Office 365 – this integration involved working with the Microsoft Graph API to download emails from Office 365 so these could be viewed within FileMaker. We created a solution to download the emails from Office 365 and view them using a Web Viewer as well as any associated attachments. Webhooks were setup to receive notifications for new messages which triggered a FileMaker Server script to download the new messages.

GitHub – a client was using GitHub, the popular platform for software developers to manage code, version control etc, for tracking issues for their yacht including maintenance tasks. We helped integrate their customer FileMaker solution with GitHub so they could download issues from GitHub, edit these in FileMaker and upload edited issues and new ones with attachments to GitHub.

Amazon Business APIs – we worked with a charter school organisation in the USA to connect FileMaker with the Amazon Business APIs. This involved downloading Products from Amazon in bulk and creating Orders in Amazon and tracking these from FileMaker.

Macquarie Bank DEFT API – a client wanted to integrate with the Macquarie Bank DEFT API to generate welcome emails for DEFT registration, download Direct Debit Authorities, create new payments, search for transactions and more.

Cin7 – a client was using the Cin7 inventory management software and wanted to download sales orders and customers from Cin7 into their custom FileMaker solution for reporting. A FileMaker Server script schedule was created to download new orders from Cin7 weekly and upload those to their FileMaker solution.

If you’re attending Reconnect.Brisbane please come and say hello. Attendees will receive a discount coupon for 20% off all our integration solutions from our online store and one lucky attendee will win a free company license to one of our products as well.

I’ll also be co-presenting the Tips, Tricks, and Opinions you can’t ignore session after lunch on the 2nd day with Nicholas Orr from Goya where I’ll share some of my top time saving tips for faster FileMaker development.

Talking FileMaker Integrations on the FileMaker Talk Podcast

/0 Comments/in , , /by

I was recently a guest on the FileMaker Talk Podcast hosted by Matt Petrowsky & Matt Navarre talking about FileMaker integrations and connecting FileMaker to the world of APIs. We discussed how I first started working with APIs and how building solutions for Databuzz have turned into products for other small businesses using the Claris FileMaker Platform. You can listen on your podcast player of choice (search for episode 160) or via this Apple Podcasts link:

https://podcasts.apple.com/au/podcast/using-data-apis-with-andrew-duncan-of-databuzz/id294672686?i=1000563563921

Automating Shipments with the Australia Post API

/0 Comments/in , /by

Like most countries around the world Australia has seen a boom in the number of parcels being delivered over the past 2 years, driven by a surge in online shopping during the lockdowns. Last October Australia Post was delivering more than 10 million parcels across Australia every week and was struggling to keep up with demand, having seen a 76 per cent increase in online shopping purchases in the previous 12 months.

Things were so bad Australia Post suspended parcel collections from online retailers in NSW, Victoria and the ACT for three days in September in a bid to catch up on the backlog.

If you’re an Australian retailer using the Claris FileMaker Platform to run your business you can leverage the Australia Post API to automate many of the mundane tasks associated with creating and lodging shipments with Australia Post. Australia Post have a number of APIs to help automate many of the tasks associated with shipping and tracking, including:

  • creating shipments
  • validate postcodes and suburbs
  • printing labels
  • lodging orders
  • printing order summaries
  • tracking parcels

You can also use other techniques to help streamline the data entry process, such as scanning barcodes and using online address lookup tools to improve the accuracy of a shipping address. You’re probably familiar with having to enter your address and select the matching address from a filtered list that reduces the number of matches as you type. You might not be aware that you can also incorporate this into your FileMaker solution – here’s a screenshot showing a card window that allows the user to enter an address and select the matching result which will then be used to populate the address, suburb, state, postcode and country fields:

If you’re currently manually lodging shipments and downloading and printing labels and copying/pasting tracking codes integrating with the Australia Post API can save you and your business time and money. With the Australia Post API it’s simple to download a list of the Australia Post products for your account and associate these with line items on your order/invoice:

You can then create a shipment from your order/invoice, request the label (either as a PDF or ZPL format) and then create an order from one or more shipments and retrieve the order summary as a PDF.

Here’s a short video demonstrating how you can use a FileMaker solution to create shipments, print labels and lodge orders all from FileMaker (you can also view it directly on YouTube). If you would like to discuss integrating your FileMaker solution with the Australia Post API to further automate your business workflows please get in touch for a free initial consultation to discuss your requirements.

FileMaker and Zoom Integration

/0 Comments/in , /by

Like most of the world I have spent large chunks of the past 18 month living in lockdown and working remotely from home. I’m currently in week 7 of another lockdown here in Sydney, Australia with no firm end date in sight for when we will get of lockdown. As we’ve moved to remote working our meetings have moved online and the Zoom video conferencing platform in particular has boomed during the pandemic as many businesses scrambled to move to virtual meetings.

In an average week I’ll have at least one meeting on Zoom, Webex, Microsoft Teams and Google Meet. Databuzz, like hundreds of thousands of other companies around the world, has become a Zoom customer so we can host virtual meetings with our clients all around the world. In the last 12 months Zoom posted a massive 326% increase in revenue – in their most recent fiscal quarter the number of customers with more than 10 employees was up 87% from the same quarter last fiscal year.

As well as the standard video meetings Zoom also has a number of other products including Zoom Webinars where you can host online webinars/presentations to much larger audiences. This means you will also be generating a lot of new data, including:

  • Webinar Details
  • Lists of People who Registered
  • Lists of People who Attended

At some point you are going to want to get some of that Zoom data into your FileMaker business app so you can then follow up with registrants/attendees or update existing records with the details of the webinars they have attended. Luckily Zoom has an API that we can integrate the FileMaker Platform with, allowing us to automate the process of downloading Webinar lists, registrant lists and attendee lists. We’ve helped a number of customers over the past 12 months connect their FileMaker business apps with Zoom to automate this process so they are no longer dealing with export/import files and manually updating records.

Here’s a screenshot showing an example of the data you can download with the click of a button to get the Webinar Registrants into FileMaker:

List of Zoom Webinar Registrants

From here we can create or update records in your existing FileMaker solution, or simply create a link between the two so you can see at a glance which Webinars a contact in your FileMaker solution has registered for or attended. If you would like to discuss integrating Zoom with your FileMaker business app please get in touch for a free initial consultation to discuss your requirements.

Optimising Parsing JSON Arrays using the JSONGetElement Function

/0 Comments/in , , /by

Using the JSONGetElement function you can query JSON data for a specific element by an object name, an array index, or a path. For example you might be querying an API like Xero or Shopify for a set of recent Invoices or Orders which you would like to download into your FileMaker solution. Most APIs use pagination to limit the number of records that are returned in response to your query. The number of records returned might be set by the API vendor (e.g. you will always get 50 records) or you might be able to specify the number of records to return by using a query parameter.

The response from the API will typically be a JSON Array – Claris use the following example JSON data in their documentation:

{
    "bakery" : 
    {
        "product" : 
        [
            {
                "id" : "FB1",
                "name" : "Donuts",
                "price": 1.99,
                "stock" : 43,
                "category" : "Breads",
                "special" : true
            },
            {
                "id" : "FB2",
                "price": 22.5,
                "name" : "Chocolate Cake",
                "stock" : 23,
                "category" : "Cakes", 
                "special" : true
            },
            {
                "id" : "FB3",
                "price": 3.95,
                "name" : "Baguette",
                "stock" : 34,
                "category" : "Breads", 
                "special" : true
            }
        ]
    }
}

This sample JSON contains a bakery object with an array of three product objects. To get the value of a particular object you can use JSONGetElement function and specify the array index and object name. For example if you wanted to get the value of the name object of the second product object in the array you would use:

JSONGetElement ( $$JSON ; "bakery.product[1]name" )

which would return ‘Chocolate Cake’. You would typically have a counter that you would set in your FileMaker script to increment the array index – remember to start your counter at 0 as JSON Array indexes are zero based. Whilst this approach works reasonably well for small JSON Arrays once your JSON Array contains larger results (50, 100, 150 records etc) then you will get better performance by breaking out each individual record into it’s own FileMaker variable and then referencing that with your JSONGetElement functions. The benefits are even more noticeable when your JSON data contains multiple nested arrays.

FileMaker’s JSON parser is much slower when it has to continually parse the JSON array to target the 1st record, then the 2nd, 3rd and so on. If you have over a hundred records in your JSON array (as well as each record having their own arrays, such as an Order with line items, tax items, refund items etc) you will get much better performance if you break out each record into it’s own FileMaker variable and target that. You want to avoid parsing any large JSON arrays as much as possible.

In our FileMaker integration solutions we have changed from using this type of structure in a loop:

JSONGetElement ( $response ; "orders[" & $counter & "].id" )

to extracting each record from the JSON array in a loop into a $record variable:

JSONFormatElements ( JSONGetElement ( $response ; "orders[" & $counter & "]" ) )

then we can simply target each object like this:

JSONGetElement ( $record ; "id" )

We’ve seen performance improvements of over 80% when converting scripts to this new format when testing our fmEcommerce Link (Shopify Edition) solution by downloading all Orders and all Products from Shopify. If you’re parsing large JSON arrays we highly recommend moving away from continually having to parse the JSON array to extracting each record from the array in your loop and perform your JSONGetElement queries on that instead.

Here’s a short video that demonstrates the technique (you can also watch this on YouTube here):

You can also download the file we used in this demonstration here.

‘Automate What You Hate’

/1 Comment/in , , /by

With the move to remote working and the changes to social distancing since the onset of COVID-19 many businesses have been forced to evaluate their use of technology and quickly adopt solutions to fill in some gaps in their business processes that have changed under the coronavirus. Companies that never thought they could function with a remote workforce have been forced to adapt quickly – I’ve spoken to many employees at other companies who have helped their organisations transition to a remote workforce in under 2 weeks.

I’ve had a lot of conversations over the past 6 weeks with customers who have found themselves in two common situations:

  1. having been forced into working remotely they quickly need a new solution or integration (e.g. implementing eSignatures now that in person signing isn’t safe or transitioning from retail sales to online sales)
  2. they have been able to take the extra time they have to finally get around to some of those projects they have been putting off, such as getting an eCommerce store up and running or integrating two different software platforms to remove those repetitive manual tasks.

Earlier this week Shopify, a leading global commerce company, announced that the number of new stores created on Shopify grew 62% between March 13 and April 24, compared to the prior six weeks. In the 6 week period since March 13, local orders have more than doubled in English-speaking geographies. As retailers suspended their in-store operations retail merchants managed to replace 94% of lost point-of-sale orders with online sales over the same period.

Earlier this week I attended the DocuSign Momentum Live APAC which was streamed live where a number of DocuSign customers shared their experiences and best practice examples of how they’re keeping business ticking in the current climate. We saw examples of how businesses are using the DocuSign platform to complete JobKeeper application forms so they can access the Australian Federal Government JobKeeper scheme payments. This form can be bulk sent to employees and a complete audit trail can be maintained.

DocuSign have a dedicated page on how they are supporting the global response to COVID-19 in healthcare, government, education, and business. We’ve helped a number of businesses in the past 6 weeks quickly adopt DocuSign for sending out agreements to their customers which, for now at least, can no longer be signed in person. Using eSignature allows you to have agreements signed remotely anywhere from any device – using our fmESignature Link (DocuSign Edition) solution you can also integrate this into your existing FileMaker solution.

I came across an article recently on the Inside Small Business website titled Automate what you hate which talks about the benefits of automation and how small businesses can use technology to replace boring manual repetitive tasks. The article goes on to give four reasons to automate the repetitive tasks you hate.

Here at Databuzz we’re big fans of using technology to automate repetitive manual tasks and leveraging the power of APIs to integrate various systems, something we’ve written about many times before. If you find yourself with some extra time during the COVID-19 shutdown we would encourage you to use this opportunity to look at ways you can streamline your current workflows and remove any unnecessary manual tasks, particular those that involve double data entry. If you’ve been planning to integrate your FileMaker solution with another platform now is a great time to make these system changes to your business and automate what you hate.

Here’s some ideas based on conversations and work we’ve done over the past few months helping customers on ways you can change the way you communicate with customers or integrate your FileMaker solution with other platforms:

  • SMS is a great alternative replacement for certain communications, such as appointment reminders, special offers, school alerts to parents. We saw a great example recently with a doctor in the USA using our fmSMS solution to implement a “Doctor by Text” service
  • If you have an online store and find yourself manually entering orders into your FileMaker solution look at whether you can integrate the two platforms. Most online stores have APIs that you can use to integrate with your FileMaker solution – if you’re using either WooCommerce or Shopify we have integration solutions already built that you can use to get up and running quickly.
  • eSignatures are becoming more popular with the social distancing rules and can help you remove paper and establish a digital audit trail and allow your customers, staff etc to sign documents on their own device at their convenience. Our fmESignature Link (DocuSign Edition) solution is proving very popular at the moment as it can help you get up and running with integrating eSignatures into your existing FileMaker solution
  • Accounting software integration: see our earlier article about the benefits of integrating your FileMaker solution with your accounting software. If you find yourself manually entering the same invoice, payment, purchase order etc in both your FileMaker solution and your accounting software there is a better way! If you’re using either Xero or MYOB AccountRight we have integration solutions already built to help you fastback an integration.

fmAccounting Link (Xero Edition) Now Supports OAuth 2.0

/4 Comments/in , , , , /by

Earlier this month Xero said goodbye to OAuth 1.0a and moved to OAuth 2.0 for a simpler, faster and more secure authentication experience (see our earlier post about this). We’ve been busy working on an update to fmAccounting Link (Xero Edition), our solution for integrating the FileMaker Platform with Xero, which we previewed in an previous post where we discussed the changes and the updates we plan to release as a result of the move to OAuth 2.0.

We’re pleased to announce that v1.97 of fmAccounting Link (Xero Edition) now supports OAuth 2.0, so new customers can now create OAuth 2.0 apps for the Xero API and connect them to the FileMaker Platform using fmAccounting Link (Xero Edition). We’ve updated our integration guide and posted a new video showing you how to setup an OAuth 2.0 app. Setting up an OAuth 2.0 app in Xero and connecting this to the fmAccounting Link (Xero Edition) solution only takes a few minutes and once you’ve completed the initial authentication everything works the same as before.

We’re working on documenting the process if you wish to convert your existing fmAccounting Link (Xero Edition) solution from OAuth 1.0a to OAuth 2.0. There’s no immediate rush as you have until December 2020 to update existing OAuth 1.0a integrations to OAuth 2.0. We’re also working on v2.0 of fmAccounting Link (Xero Edition) which will only support OAuth 2.0 and not require a FileMaker plug-in – it will use native FileMaker functions and scripts and will require FileMaker Pro v16 or later.

If you have any questions about fmAccounting Link (Xero Edition) and OAuth 2.0 please get in touch or post a comment below.

FileMaker Admin API Authentication Issue

/5 Comments/in , , , /by

Now that the FileMaker Admin API is a standard feature in FileMaker Cloud for AWS 1.18 and FileMaker Server 18 and no longer a trial* I’ve been incorporating certain parts of the Admin API into client solutions. For example it is often helpful to see how many users are logged into your solution and get a list of their Account Names without having to use the Admin Console, or provide non admin users with access to the Admin Console.

I recently encountered an issue when calling the List Clients endpoint to see who was currently connected to a particular FileMaker solution. It was generating a 1627 error (Authentication failed) when performing the Insert from URL script step. This was strange as the same script had been working successfully earlier in the day and in previous days. I double checked the credentials for the server that were being used in the API request and they were correct, and verified these by logging into the Admin Console.

I was a bit puzzled at this stage and ran through the script once more with the Script Debugger. I was saving the response from the Insert from URL script step into a local variable, and upon further examination I noticed this was recording the following response from the Admin API:

{
"response": {},
"messages": [
{
"code": "956",
"text": "Maximum number of Admin API sessions exceeded"
}
]
}

There were no other Admin API sessions in use at this point – the Admin Console wasn’t being used – which further confused things. Logging in to the Admin Console again I could see that the list of connected clients wasn’t correct. If you’ve been using the FileMaker Server Admin Console for a few years now you’re probably familiar with this situation and the need to periodically restart the Admin Server via the following command line interface command:

fmsadmin restart adminserver

Once the Admin Server had restarted I tried the Admin API request to list the currently connected clients and it was successfully this time, so the authentication error was misleading in this case. If you encounter this error and you’ve verified the credentials are correct I would just go and restart the Admin Server on the FileMaker Server using the command line option above.

* the FileMaker Admin API was a trial feature in FileMaker Cloud 1.17 and FileMaker Server 17 and that trial period will expire on September 27, 2019 after which the API will cease to operate

Create Order Fulfillments in Shopify with fmEcommerce Link (Shopify Edition)

/0 Comments/in , , , , /by

We’re pleased to announce that you can now create Order Fulfillments in Shopify in the latest release of fmEcommerce Link (Shopify Edition). We’ve added the ability to create a Fulfillment record in fmEcommerce Link for one or more Order Items in v1.4 which was released today:

You can enter the Tracking Company (select from a list of known Tracking Companies), Tracking Number and optional Tracking URL and select the Location that the Fulfillment will ship from. Clicking the Upload to Shopify button on this window will create a new Fulfillment in Shopify or update an existing one if you’re making a change to a previously uploaded Fulfillment.

The Fulfillment will be created in Shopify and save you from having to update Orders in both Shopify and FileMaker when it comes to managing Order Fulfillments:

Once again this is a this free update to all our existing customers who can download this version from the link on your original order email (contact us if you need the link to be reset etc). Here’s a video that demonstrates uploading an Order Fulfillment from fmEcommerce Link (Shopify Edition) – you can also watch it on YouTube here:

Sending Messages from FileMaker to WhatsApp Users with Twilio

/2 Comments/in , , , , /by

 

Last month I attended the Twilio Engage + Superclass events in Melbourne, Australia. This was my second Twilio Engage event after attending last year’s Sydney event, but my first Superclass session. Superclass is hands-on training for developers to learn how to use all things Twilio, with Twilio engineers on hand to help as you work through code challenges in TwilioQuest, their self-paced training video game. The use of TwilioQuest makes the day lots of fun with your awards flashing up on the big screen every time you compete a new challenge.

Even though I’ve been developing with the Twilio APIs for many years ( see my article from last year about how we’ve integrated FileMaker with Twilio for two-way messaging, voice calling, number lookups and polling for incoming messages) it’s always great to spend a day away from work with other developers and the Twilio staff to learn new skills and immerse yourself in the content without distractions.

My aim for this year’s Superclass was to spend time learning some of the other Twilio APIs that I wasn’t very familiar with, with a particular focus on the Twilio API for WhatsApp. I was aware that Twilio had recently launched the API for WhatsApp which allows you to send SMS messages directly to WhatsApp users – there are over 1.5 billion people using WhatsApp – and I was keen to see if I could get this working from fmSMS, our FileMaker solution for sending and receiving SMS messages from the FileMaker platform.

The Twilio API for WhatsApp is currently in beta and you can use their Sandbox to setup access from a Twilio number and start sending messages using their sample templates. The API works in a similar fashion to their standard Message API and I was able to get up and running quickly and send my first message from fmSMS to my WhatsApp account. Here’s a screenshot showing the message I sent from fmSMS:

and here’s how it appears in the WhatsApp app on my iPhone:

I then enabled a Webhooks for incoming messages and was then able to receive incoming messages from WhatsApp back in to fmSMS. You can learn more about pricing for the Twilio API for WhatsApp here and API documentation here.

Once the Twilio API for WhatsApp is out of beta and you can easily request your own numbers we’ll definitely be adding this as another supported Gateway for fmSMS. We’ve put together a short video showing sending and receiving messages between FileMaker and WhatsApp in action: