I FileMaker finns nedrullningsbara listor vars innehåll definieras med en värdelista. De ser alla likadana ut, varför inte göra en egen variant? I det här exemplet tänker jag kombinera Sök med SQL med en popover och några manus-triggers. Resultatet blir en nedrullningsbar lista (även kallat dropdown-meny) med eget utseende.

Jag utgår från exempeldatabasen från Sök med SQL och behåller både sök-manus och portal. I det här exemplet vill jag sätta in ett värde i fältet Bilmärke (ett nytt fält jämfört med exempeldatabasen), värdet väljer jag från en nedrullningsbar lista.

När jag skriver i fältet Bilmärke reduceras bilmärkena i listan precis som tidigare. Det nya är att jag kan trycka Pil ner respektive Pil upp för att markera ett bilmärke i listan. Vill jag välja det markerade bilmärket trycker jag på Retur. Manus-trigger med piltangenterna har jag tidigare skrivit om här.

Det första jag gör är en popover med portalen som visar resultatet av sökningen. Jag gör popoverns bakgrund genomskinlig och döljer knappen som öppnar popovern. Det gör jag med funktionen Dölj objekt i Granskaren. Jag markerar knappen och skriver “1″ i fältet för Dölj objekt. Det betyder att knappen alltid är dold i bearbetningsläget, men syns i layoutläget.

I popovern placerar jag också en kopia av fältet Bilmärke och ser till att de båda fälten Bilmärke placeras på exakt samma plats, dvs. både det som finns i popovern och det som visas på layouten utan popover. Planen är att det inte ska märkas att användaren byter från det ena fältet till fältet i popovern.

Jag gör en trigger som startar när jag klickar i fältet Bilmärke (utanför popovern), det startas VidObjektÖppna. Manuset ser ut så här:

Jag tar reda på var användaren klickar i fältet, utför sökningen så att listan visar de bilmärken som början med det som skrivits i fältet redan, går till objektet med namnet “bilmärke_fält” som är fältet i popovern samt sätter tillbaka markören på samma ställe. Jag namnger fältet i Granskaren och använder Gå till objekt istället för Gå till fält eftersom FileMaker då mycket väl kan gå till det andra bilmärkesfältet, det utanför popovern, och det vill jag inte. I och med att jag går till ett objekt som finns i en popover visas den.

Med två andra manus tar jag hand om piltangenterna. Det första ställer jag in så att det utförs VidObjektTangenttryckning på fältet Bilmärke (i popovern).

Om Pil ner markeras den första portalraden, om Retur stängs popovern. Alla övrig tangenter skickas vidare till fältet, t.ex. bokstäver.

Det andra ställer jag in så att det utförs VidObjektTangentTryckning på hela portalen i popovern.

Om Pil ner markeras nästa rad i portalen, om Pil upp markeras raden ovanför. Om första raden redan är markerad går FileMaker till sökfältet igen. Om användaren trycker Retur väljs bilmärket, det sätts in i fältet Bilmärke och popovern stängs.

Du kan ladda ner exempeldatabas här: Rullistasok.fmp12

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.

This is the first in our series on Web Services integration with FileMaker that I mentioned last week.

DropBox

The API we are going to look at today is DropBox. With this API a FileMaker solution can manage and share files, folders and Dropbox Paper documents.

As an example, when someone signs up to sponsor the BaseElements plugin, we share with a them a DropBox folder with every historical version of the plugin, as well as private beta versions with new functionality.

We do that with a FileMaker script that uses the BaseElements plugin to do HTTP calls to the Dropbox API. If you missed the first article of the series, it is an introduction to the world of web services and FileMaker.

Each interaction with the web service starts with a request from FileMaker sent to a specific url (or endpoint) along with the request parameters.

A request URL will look something like this:

https://api.dropboxapi.com/2/files/list_folder

This endpoint returns the contents of a folder.

The URL breaks down (reading backward) to: i want to use the list_folder function from the files category of the version 2 of the dropbox api. Makes sense? The first part will be the same in all our requests, the only parts that will change will be the function and the category.

Of course we also need to tell the Dropbox API what folder we are talking about and that is done through the parameters (or body) of the request, using JSON:

{
   "path": "/Goya/Secret_Projects"
}

One detail to remember about the Dropbox API is that it uses POST requests even for endpoints where we get data.

So the previous example called from FileMaker with the BaseElements plugin will be

BE_HTTP_POST ( "https://api.dropboxapi.com/2/files/list_folder" ; "{ \"path\": \"/Goya/Secret_Projects\"}" ; "" ; "")

If you go ahead and try that the API will send back an error (at least we know they answer!).

The request can't be processed because we haven't yet told the API what dropbox account we want to use.

So let's go back to our workflow for the Goya sponsors: we have an email address (we receive it from another API) and we want to share our folder with them.

Based on what we said, to successfully process the request Dropbox needs to know:

• that we are authorized to interact with that Dropbox account
• what folder do we want to share
• who are we sharing the folder with and what privileges will they have on the folder and files

Authorization

To identify what Dropbox account we want to work on we need to tell the API that we are authorised to work on it. You do that by getting a token.

The token can be obtained going to this address https://www.dropbox.com/developers/apps and following the steps to register an app and create a token for that app.

Once we have a token, we can add an Authorization HTTP header to the request, and again we can do this with the BaseElements plugin :

BE_HTTP_Set_Custom_Header ( "Authorization" ; "Bearer " & Table::API Token )

Another header we want to add is the language we are using to send the body of our request. In our case it is JSON :

BE_HTTP_Set_Custom_Header ( "Content-Type" ; "application/json" )

Get the Folder list

We need to know the ID of the shared folder so that we can add our new sponsor to it.

The endpoint https://api.dropboxapi.com/2/sharing/list_folders returns a list of all the shared folders from the Dropbox account.

In the body we can specify a limit for the number of items returned.

The following call will return up to 1000 shared folders

BE_HTTP_POST ( "https://api.dropboxapi.com/2/sharing/list_folders" ; "{ \"limit\": 1000 }" )

The API will have one "entry" node for each shared folder

{
    "entries": [
        {
            "access_type": {
                ".tag": "editor"
            },
            "path_lower": "/secret projects",
            "name": "Secret Projects",
            "permissions": [

            ],
            "policy": {
                "member_policy": {
                    ".tag": "anyone"
                },
                "resolved_member_policy": {
                    ".tag": "anyone"
                },
                "acl_update_policy": {
                    ".tag": "editors"
                },
                "shared_link_policy": {
                    ".tag": "anyone"
                },
                "viewer_info_policy": {
                    ".tag": "enabled"
                }
            },
            "preview_url": "https://www.dropbox.com/somelink",
            "shared_folder_id": "123456789"
        },
        ...
    ]
}

We can parse the JSON using the function BE_JSONPath. So to grab the ID of the first shared folder we'll write

BE_JSONPath ( $json ; "$.entries[0].shared_folder_id" )

Share the folder

Now that we know the token, the shared folder id and the email we want to share with, we are ready for the last call to the API.

The endpoint to add a new member to a folder is, surprisingly, https://api.dropboxapi.com/2/sharing/add_folder_member.

The body of the request will look like this

{
    "shared_folder_id": "123456789",
    "members": [
        {
            "member": {
                ".tag": "email",
                "email": "salvatore@goya.com.au"
            },
            "access_level": {
                ".tag": "viewer"
            }
        }
    ],
    "quiet": false
}

where we specify the folder id, the email of the new member and what access privileges they will have.

Calling our BE_HTTP_POST with this url and body will add the member to the folder.

Success! This call doesn't return anything if successful, so we'll have to check the HTTP response header to know if everything went well.

If BE_HTTP_Response_Code returns 200, we know the email was added.

These are only two of the actions available through the Dropbox API and they were all we needed for this workflow.

A complete example file is available to sponsors of the BaseElements plugin, and is sitting in their DropBox share folder already.

Up next in the series, integrating FileMaker and Zendesk.

Salvatore is our web services and integration expert, passionate about linking any API useful to our clients (or us!) to FileMaker. He loves to share ideas and a good story, and as a speaker at DevCon he manages to do both.

Like most modern companies, here at Goya we use web-based tools. Slack, Dropbox, Zendesk and Mailchimp are awesome platforms to organise our work and to share our ideas.

The best part though is that we can manage all these services from a FileMaker solution.

Which is exactly what happens when someone joins one of Goya's sponsor programs: their Dropbox, Zendesk and Mailchimp account details are auto created via FileMaker scripts.

Pretty neat, it saves us quite a few clicks!

We are able to call the web services from FileMaker using the HTTP functions from the BaseElements plugin, and then parse the results using the JSON ones.

Want to try it yourself? In the coming weeks we will publish articles here and add examples to the share folder available to all our sponsors. If you'd like copies of our example files, please consider signing up as a sponsor.

Next week FileMaker integration with Dropbox.

In the meanwhile, if you're not sure this should matter to a FileMaker developer or what web services are, you can download the slides of my Devcon 2015 session
or check out this excellent introduction to web services from Mason Stenquist at DB Services.

Update : Part 1 is up : FileMaker integration with Dropbox

Salvatore is our web services and integration expert, passionate about linking any API useful to our clients (or us!) to FileMaker. He loves to share ideas and a good story, and as a speaker at DevCon he manages to do both.

Databuzz might be based in Sydney, Australia but thanks to the wonder of the Internet we have customers all around the world. Managing a diverse customer base can be a challenge at times and we’ve become experts at time zone differences and knowing when is the best time to schedule a demo for customers in Europe or North America.

We love meeting up with our customers face-to-face at the FileMaker Developer Conference every year – it’s usually the only chance we get to meet our non Australian customers in person. Every 12 months or so I like to generate a simple Google Map showing where our customers are located for our various products – it’s quite pleasing to see the pins grow every year and new locations added to the map.

Our FileMaker Xero integration solution fmAccounting Link (Xero Edition) is now into it’s fourth year of development and in that time the number of Xero subscribers has grown exponentially. Xero is now reporting a total customer base of over 862,000 subscribers (up from 284,000 in 2014) and is on track to hit the magic million mark some time in 2017. It’s four main markets remain the same:

• Australia
• New Zealand
• UK
• North America

but it is now seeing growth in new markets such as South Africa, Hong Kong and Singapore. Australia remains the largest Xero market for now, but we can expect to see North America become the largest market over time. Here’s a map showing where fmAccounting Link (Xero Edition) customers are located:

As you can see it also reflects the main Xero markets – we’re starting to see more enquiries from Canada, Asia as well as Africa, so hopefully next time we generate this map there will be some red pins in some new countries as Xero grows in other parts of the world.

fmSMS, our oldest product is a truly international product and was designed from day one to work in as many countries around the world as possible – using fmSMS you can send and receive SMS messages via one of over 75 supported SMS Gateways reaching over 860 mobile networks in more than 220 countries. We continue to add additional SMS Gateways each year and now have customers spread out all over the world – here’s a map showing their locations:

We often speak to FileMaker customers who are considering purchasing one of our products from outside of Australia and are slightly concerned that they will be the only customer from their country and will have trouble getting support. We pride ourselves and providing the same great customer service to all our customers regardless of where they are located – and as these maps demonstrate there’s a good chance you’re not our first customer from your part of the world.

FileMaker and RESTful APIs

By now you’ve probably heard of REST API, but why should you care as a FileMaker developer? Well REST APIs are a universal standard for interacting with other programs and services, so by learning REST you can greatly expand the capabilities of what FileMaker can do, and the services it can interact with. If you’ve ever wanted to integrate FileMaker with Google Maps, Google Calendar, Microsoft SharePoint, or many other great services, you’ll want to learn what RESTful APIs are and how to use them.

REST Overview

REST stands for “REpresentational State Transfer”. Unfortunately, that doesn’t really tell us much. What this means is that REST is a standard for manipulating data in a standardized format that represents the data. The representational part of REST means that the URL or route you use to access or manipulate the data should correspond with the data, so accessing /users should show users, and /posts, should only show posts, and all actions will take place on those URLs. Another thing you’ll hear a lot when talking about REST is CRUD, which stands for Create, Read, Update, and Delete. With these four actions, you can do anything to a system using an easy-to-understand format.

In order to fully work with a REST API you must understand the different types of HTTP methods you can make. GET is the most common method, and happens anytime you browse the web, or type in a specific URL into the address bar and is what you would use for the “read” in CRUD. The rest of the HTTP methods like POST, PUT, and DELETE can only be performed by web forms or built-in functions provided by most libraries. POST is used to submit data to a server and is generally used to add a new record or pass more data than would reasonable to put in a URL. PUT and PATCH are used to update an existing record, and finally DELETE is obviously used to delete a record. Understanding the basic structure and actions of a RESTful API will make working with them so much easier.

Below are the standard routes used when working with a RESTful API. Go ahead and test the GET commands by clicking on the routes below to view some sample data.

Integrating with FileMaker

The main way to interact with a RESTful API from FileMaker is using the “Insert from URL” script step. The “Insert from URL” script step is capable of doing GET requests and POST requests. Whenever you insert from a URL that starts with ‘https’ or ‘http’ you are doing a standard GET request. FileMaker has a unique way of handling POST requests, to do one you would start your URL with either ‘httppost’ or ‘httpspost’. One limitation you will probably run into quickly is the inability to set custom HTTP headers before sending a request. This is sometimes necessary for authentication or telling the API what the data format being submitted is. If you run into this your best option is to use the excellent BaseElements Plugin by Goya, which has the capability to set HTTP headers using the BE_HTTP_Set_Custom_Header function, which works with the following functions: BE_HTTP_GET, BE_HTTP_POST, BE_HTTP_PUT_DATA, BE_HTTP_DELETE.

Download the sample file below to look under the hood.  In the sample file we have integrated with a simple Ruby on Rails app we spun up that manages usernames; while most services you’ll work with will be more complicated than this, it’s still a great example of how to work with a REST API.

Conclusion

REST is a great addition to any FileMakers developer’s toolkit, allowing FileMaker to integrate with some of the most used services on the web today and we’re only going to see more of it in the future. Because all REST APIs use the same conventions and principles, working with RESTful APIs will only get easier the more you work with them, and there is no time like now to start learning.

Did you know we are an authorized reseller for FileMaker Licensing?
Contact us to discuss upgrading your FileMaker software.

Download Integrating FileMaker With RESTful APIs

Please complete the form below to download your FREE FileMaker database file.

You asked. We listened.

The main driving forces behind your business are clients and revenue – so why not connect the two?

The FM Books Connector Online Edition plug-in connects FileMaker with QuickBooks Online and enables users to push and pull contacts, invoices, and other sales transactions.

A FileMaker and QuickBooks integration not only eliminates double entry but also provides your staff with quick access to pertinent financial data (like customer balances and invoice information) without multiple people accessing your sensitive QuickBooks company file. Check out the video below for an overview of the FM Books Connector Online Edition plug-in features and benefits.
 

Click here to view the embedded video.

1. It is compatible with FileMaker Server 14 -15, Mac and Windows.

2. Now, multiple users can post to QuickBooks from multiple devices. Your front office staff can connect on their desktops with FileMaker client, your accountant can update items in FileMaker from her house – using a web browser and FileMaker WebDirect, and your sales reps can update client information or push invoices to QuickBooks on the road – from an iPhone or iPad via FileMaker Go. A server-side plug-in allows users to truly capitalize on the mobility of QuickBooks Online.

3. Faster load times than client-side plug-ins, meaning you get more work done in less time.

4. The convenience of only needing to install the plug-in one time in one location, versus having to install it on every users’ computer (and no more having to deal with reinstallations or transferring licenses from machine to machine).

5. For just $450 a year, you can have unlimited users per server – no more need for five, ten, or 20 users licenses. Now it is painless and easier than ever to add new users as your company grows.

6. Ability to connect with multiple QuickBooks Online company files. This is especially helpful if you are an accounting agency that is contracted to manage multiple clients or your company recently acquired a second firm and is undergoing a merger, or if you are a corporation that has multiple divisions that require their own dedicated company files.

The possibilities are endless.

This version release (2.0.1.0) includes:

– An updated demo file to introduce and familiarize users and developers with the new server functionality.
– New video for step by step instructions on how to install the plug-in on the server.
– Updated documentation for easier development and integration.

FM Books Connector Online Edition is available for an annual rate of $200 for a single user (Client license – no server-side functionality) and $450 for unlimited users (Server license). Every Server license includes a client license for developmental purposes.

For more information on the FM Books Connector Online Edition plug-in, visit www.fmbooksconnectoronline.com or call 760-510-1200.

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

We’ve just uploaded a couple of new videos demonstrating how you can work with Prepayments and Overpayments using fmAccounting Link (Xero Edition). In each video we cover:

• creating a new Prepayment and Overpayment from a Customer
• allocating the Prepayment/Overpayment to an Invoice
• refunding the balance of the Prepayment/Overpayment

You can watch these videos on the Videos page or here are the direct links:

Prepayments in fmAccounting Link

Overpayments in fmAccounting Link

TowBoatU.S. San Diego, founded in 1987, is the most established Towboat company in San Diego. Their experienced staff assists thousands of boats a year off the coast of Southern California and Baja, Mexico. Rob Butler’s company is ranked number four in the U.S. in terms of caseload volume. TowBoatU.S. San Diego is 100% mobile and 100% paperless.

Q: What challenges were you experiencing prior to purchasing VSS?
A: While we’re at sea, it’s hard to keep track of new service requests coming in by phone and it is difficult getting invoice details back to the main office. Imagine those busy days when you barely get a chance to write up an order or the paperwork gets lost. It’s easy to misplace, lose the invoice or forget to write up the order. How do you follow up with a client weeks or months after the service was done? We knew we needed to change something to become more organized and remain competitive and responsive in the marketplace.

Q: What led you to decide to use Vessel Service Solutions?
A: We needed a way to streamline our method of receiving and processing orders to improve business, turnaround time, and efficiency of service. Before VSS, we were using a work order software that allowed us to dispatch orders to a captain’s iPad, but since the majority of our orders come from BoatU.S., we wanted something that would actually import the BoatU.S. order information directly into a form in the system. With the old solution, our staff had to copy and paste the email into the database, which was labor intensive and slow.

We were also using a completely separate software to track all fleet maintenance, but the solution was too complex for our needs. The Fuel Log and Maintenance modules in VSS are very easy to use and perfect for our needs – we only record the information we want to track.

Q: What goals were you hoping to achieve with a new solution?
A: We wanted to provide our captains with a simple, paperless, easy-to-use system. We needed the ability to quickly update the data needed for an order (times, services, etc.) and get the customer’s signature all while assisting a boater. Making sure we could do this without an internet connection was very important. VSS did all of this and it has an incredibly short learning curve. The captains can pick up an iPad and start using VSS confidently in a day or two.

Q: How has VSS helped your business?
A: It’s been great. We’ve been using VSS since 2014. And since then we’ve seen an improvement in payment processing and an increase in customer satisfaction. Now all towing orders go in and out through our iPads – keeping everything up to date and our office completely paperless. My boat captains are able to receive an order on their iPad, edit and update the order at sea, and send their orders back to the main office for invoicing. Paper forms are completely eliminated, customers in distress are assisted faster, and the payment process with BoatU.S. or non-member customers has been greatly improved. The system tracks all orders, so it’s easy to follow up and collect payment. I would estimate that VSS saved us at least $60K in 2016.

Q: What about VSS do you use the most?
Probably the remote dispatching feature. With VSS, I – or anyone else – can dispatch tow orders on the road. No one has to be sitting down at a computer to send out a tow dispatch. With VSS, it’s easy to work on the go – we don’t really have an office anymore. Our entire operation is mobile.

Q: What is your favorite feature of VSS?
A: My office manager really appreciates the fact that you can instantly push invoices from VSS to your QuickBooks file and send completed orders directly to BoatU.S. This ensures that the main office stays up-to-date and completely eliminates manual re-entry of data. Sending orders directly to BoatU.S. without having to scan or fax anything has helped reduce the time spent in the office and helps us get paid faster. I’d estimate we’ve reduced our office work by 90%

I also really appreciate the maintenance message pop-ups that remind staff when a service is due on a boat.

Q: You’ve been using VSS since 2014, is there anything you’re excited about with the release?
This solution has just continued to get better and better. There is a lot to be excited about, but if I had to pick one new feature, I would say it is the fact that captains can create an order on the iPad while at sea. We used to send captains blank order from the central dispatch file. Now, when my captains come across a stranded boater in the middle of the ocean, they can create a new order themselves, instead of having to call into dispatch to get a blank order.

For more information on Vessel Service Solutions please visit: www.vesselservicesolutions.com

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.

Integrating FileMaker and SharePoint Using Microsoft Graph

Often times documents are commonly needed in multiple programs, but the lack of integration can result in having to enter in the same files in different applications. Microsoft Graph is a powerful tool that provides a unified API interface for many of Microsoft’s most popular Cloud programs, including the popular web-based application SharePoint.

Integration between SharePoint and FileMaker is even possible using Microsoft Graph. In this example I will demonstrate how to upload files from a FileMaker solution into your SharePoint site.

There are three main steps to the integration. Allowing access to your Microsoft Application, setting up OAuth 2.0 authentication, and the actual uploading of documents to Sharepoint.

Watch on Youtube: Integrating FileMaker and SharePoint Using Microsoft Graph

Setting Up Your Microsoft Application
In order to allow FileMaker to talk to your SharePoint solution using Microsoft Graph, you will first need to set up a Microsoft Application to allow access using OAuth 2.0. If you go to Microsoft’s My Applications page you can either edit an existing application or create a new one. (If you don’t have a site already, you will need to create a site in SharePoint prior to this). Make note of the Application ID (sometimes called the Client ID), you’ll need this in your FileMaker solution.

Next you’ll need to create a new Application Secret (again, sometimes called Client Secret). Generate and copy the password. This is the only time you will be able to copy this password, so be sure to document it somewhere or else you will have to generate a new password.

Now add a new web platform, and allow implicit flow and enter in the Redirect URI. The Redirect URI is just the web page the user will go to after they allow access to the SharePoint application.

Next you will need to allow Microsoft Graph Delegated Permissions. For the purposes of this demo you will only need the ones referenced in the image or in our Demo file.

Setting Up OAuth 2.0 Connection
Now that we have our Microsoft Application set up, using the Application ID, Application Secret, Redirect URI, and Site Name we can talk to our SharePoint solution! The first step is getting the access code. You will need to call this in a web browser or Web Viewer in FileMaker using a URL.

Once you accept, you can grab the access code in the URL. Then using this, you can request a token to access your SharePoint site. A JSON-encoded response will bring you the account and refresh tokens, which you can then use to talk to your SharePoint solution.

For more about how OAuth 2.0 works, reference the OAuth 2.0 Authorization Code Flow.

Uploading Documents to SharePoint through FileMaker
With everything set in place, we can now upload documents through FileMaker into your SharePoint site.  In order to upload files, we will need to use them to grab your Site ID in SharePoint.  Below is the URL template.

https://graph.microsoft.com/beta/sharepoint:/YOUR_SITE_NAME_HERE

Be sure to set your authorization headers using the BE_HTTP_Set_Custom_Header and the access token before performing your GET call.

Next, use your Site ID to grab the drive ID where the document will be uploaded.

https://graph.microsoft.com/beta/sharepoint/sites/YOUR_SITE_ID_HERE/drive

Again be sure to set your authorization header with the access token.  Then using the drive ID, you can use a PUT call to create the file in SharePoint! If it is successfully created, you will receive a JSON-response code with the information about the newly created item.

And in the documents in SharePoint, the new document is created!

The potential of SharePoint and FileMaker integration using Microsoft Graph goes beyond this.  You can reference all the functionality on their API Documentation.  There are even more Microsoft programs that can be integrated with FileMaker using Microsoft Graph, such as Excel, Outlook, and Webhooks.

Conclusion
With those three steps, documents added into FileMaker can easily be uploaded automatically into SharePoint.  This integration allows documents to quickly and easily be uploaded and viewable in both applications.  Microsoft Graph’s ever-expanding capabilities continue to allow for further integrations with Microsoft and other programs using a unified API interface.

Did you know we are an authorized reseller for FileMaker Licensing?
Contact us to discuss upgrading your FileMaker software.

Download Integrating FileMaker And SharePoint

Please complete the form below to download your FREE FileMaker database file.

What is Vessel Service Solutions (VSS)? It is a solution designed for the boat assistance and towing industry. Simply stated, your business is assisting boaters and managing a fleet – and VSS is here to help you efficiently manage every aspect in between.

You’re asking, as the owner of a boat towing company, how can VSS help me?

First we’ll ask you a couple questions:

How do you currently handle your order processing? When you receive an order from BoatU.S. do you copy and paste, re-type, or hand write information before you dispatch your captain? What if, with the touch of a button, you could populate a form with the BoatU.S. order details and then send it to a captain?

How do you submit orders for payment? Do you use a two-part carbon copy form that you have to collect from your captains before you can scan and email or fax to BoatU.S.? Ever lose one overboard with a gust of wind? Do you find this process to be time consuming and results in payment delays? With VSS you don’t have to hassle with paper. Your clients can digitally sign a tow order and your dispatcher can email it to BoatU.S. the same day.

Still curious how VSS can assist you in streamlining your business operations?

– Reduce paperwork or actually go paperless: Make your dispatcher’s life easier, reduce costs and waste. With VSS all order processing, log tracking, and timesheets are handled on your computer or iPad. There is no need to print forms.

–Assist boaters faster: When BoatU.S. sends a tow order to your dispatcher, VSS imports the order email directly into a standardized form. Dispatchers can add additional information and assign the order to a captain’s iPad – all with just a few clicks.

– Send texts to alert captains of new orders: Once an order is assigned, VSS can send the assigned captain a text about the pending order – no need for additional phone calls.

– Captains can receive orders at sea: When an order has been assigned, captains can retrieve the orders on their iPads – there is no need to come back to the office or spend time on the phone discussing the new tow order with the dispatcher. iPads must have access to the internet to retrieve pending orders.

– Easily update orders: While at sea, captains can update and edit orders on their iPads (no internet connection needed). Adding service items, updating time tracking, and getting customer signatures is quick and easy.

– Quickly sync between central and remote: After the captain is done assisting the boater and has an internet connection, he can send the order back to dispatch for review and final processing.

– Get paid faster: With VSS, you can create, edit, sign, and submit an order for payment all from your computer or iPad – there is no need to print or fax documents. When you’re ready to invoice BoatU.S., simply click a button and an email will automatically be generated with the invoice attached and the required BoatU.S. subject line: Tower ID – Member ID.

– Analyze your business’s success: Pull reports on orders, boats, and time sheets to review data, trends, performance, and profitability.

– Process payroll faster: The Timesheet’s module handles commission percentages, straight hourly rates, and day and night rates.

– Protect sensitive information: VSS has robust privilege settings, allowing management to dictate what information and modules employees can view, edit, and delete.

– Maintain Employee Records: Store employee information in the system with the ability to easily and quickly update pay rates and contact details.

– Maintain fuel logs: Tracking fuel use not only helps you monitor boat expenses, but also helps you reconcile your monthly fueling expenses. Users can add fuel logs on the iPad and record the date, engine hours, cost per gallon, number of gallons, total cost, and fueling location.

– Ensure all boat maintenance is performed on time: With maintenance schedules, you can ensure you never miss an oil change again. By creating schedules, you can track mileage and plan for boat maintenance, ensuring your fleet is always in top shape and you’re never down a boat on a busy weekend.

– Log maintenance performed: With the Maintenance Log you can track all maintenance on all boats, log the date, engine hours, services performed, and the total cost.

Baltimore Marine Recovery, LLC provides emergency towing, salvage, refloat, charter, and launch services to the Chesapeake Bay area. Dale and Christine Plummer have been using Vessel Service Solutions (VSS) for three years. In 2015, they won the BoatU.S. Smooth Sailing Award for “Superior Operational and Organizational Standards” – with help from VSS.

Q: Why did you decide to start using Vessel Service Solutions?
A: We have 10 boats, about 14 staff, and we work out of 3 ports (Annapolis, Baltimore, and Middle River). On top of being busy all year-round, there is a lot of paperwork to deal with on a daily basis. We wanted something to make our lives easier and enable us to continue to deliver great customer service. Plus, VSS allows our captains to handle dispatch orders, fuel and maintenance logs on one iPad application.

Q: What goals were you hoping to achieve with a new solution?
A: Reduce paperwork! Before VSS, we used the standard issue 2-part carbonless TowBoatU.S. invoice. Forms were lost overboard, misplaced, or just were not returned by the captains in a timely manner. With three ports, it was hard to manage and the captains complained that managing paper in a windy, wet environment sucked!

Q: How has VSS helped your business?
A: In addition to allowing us to go virtually paperless, we’ve seen an increase in boaters assisted and faster turnaround time on payments from BoatU.S. Being able to email a completed, signed order to BoatU.S. directly from VSS is great. I can also share the completed invoice with the boat owner by email.

The system has also made it is easier to process and track orders. I have an accurate and extensive historical record of all orders processed, that I can sort, filter, and search through. I no longer have to flip through old orders in a filing cabinet when BoatU.S. has a question on an invoice. VSS orders can also be submitted directly to my QuickBooks file. Three years ago, I hand entered every order into QuickBooks after I scanned and emailed it to BoatUS. Today, I push a couple of buttons and it is done.

The Time Sheets module has made payroll much easier, as well. Instead of calling the captains for their hours, I run a simple report and see all timer entries. If something looks off, I can ask the captain to review and update the timer entries. Once that is done, the updated entries are immediately available to process payroll using the Time Sheet reports. I can run one report and have all employees’ time sheets for any time period complete with details for easy reference.

Q: What is your favorite feature of VSS?
A: I have a couple favorite features. The first is remote dispatching. It is a total game changer. I can take my entire office with me. I have the ability to handle calls and dispatch orders from my son’s football game or from my home in the middle of the night – without getting out of bed.

The second would be ease of use. The system is very straightforward and user-friendly. Even those captains who aren’t tech savvy have found the system easy to use. Now that they know the VSS system, my Captains can’t stand using paper. If they manage to leave without an iPad aboard, they always come back and get it!

And third would be the text notifications that the captains receive when they have been dispatched an order – my guys use that as the basis for their timers. Each email has the invoice number and every invoice needs a timer so at the end of the day they have a running list to use to create or review their timers.

FileMaker Barcode Techniques

Using barcodes in FileMaker is a very easy and useful way to extend your database. Whether you are selling tickets, managing assets, creating name tags, or any number of other use cases, barcodes can easily be used to provide an instant way to locate a record in your database system and much more. Barcodes can be generated and scanned for iPads, iPhones, Windows, and Macs.

Watch on Youtube: FileMaker Barcode Techniques

How To Generate

There are many different methods that can be used to generate barcodes in FileMaker and there are many different types of barcodes that you can create. The 2 main types are QR codes and regular barcodes like you would see on millions of products. A QR code is basically a barcode with another dimension allowing it to store more information than what a 1D barcode can store before becoming unreadable by the scanner. This is why URL’s are often shared using QR codes instead of standard barcodes. Do note that QR codes cannot be scanned by USB or BlueTooth scanners, but work great with camera-based scanners like a mobile device. The 3 ways we’ll be talking about are through using web services like Google’s Chart API, using 3rd party plugins and products like FileMaker Barcode Creator, or using barcode fonts. Below we’ll go over the pros and cons for each method.

Web Services

There are plenty of great web services out there like barcode-generator.org and Google’s chart API for generating QR Codes. This is the method we chose to use in our demo file as it is very flexible and free. To generate a QR code using Google’s API, all you have to do is create a simple URL in FileMaker and then use the Insert from URL script step to download the image to a container field. Below is how we accomplished this in our demo file.

One thing to be aware of when using this method is that the container must be on the layout and be editable when you use the Insert from URL script step. This might not be the best method to use if you have hundreds of thousands of barcodes that need to be generated, as most free web services will block you from making too many calls, and depending on internet speeds, this method could be slow.

FileMaker Barcode Creator

We’re big fans of Geist Interactive’s FileMaker Barcode Creator. It’s easy to integrate with, has 19 different barcode types including QR Codes, is native FileMaker that works with Pro, Go, WebDirect, and server side scripts. To integrate with Barcode Creator you need to copy a modules script folder to your solution, and then you just need to call one script, passing the serial number as a parameter. The script returns a Base64 encoded string which can then be converted to an image using FileMakers Base64Decode function. FileMaker Barcode Creator is a paid product, but in our opinion is well worth the money, and is what we use in our solutions.

Barcode Fonts

Barcode fonts is another way you can create barcodes in FileMaker. This method has been around for a long time and used to be the only way to create barcodes without the use of plugins. The only reason I mention it in this article is to tell you NOT to use this method! This method has many limitations like only being able to generate and view barcodes on the desktop, with no support for FileMaker Go, or WebDirect. It also requires that all computers have the correct barcode font installed, which depending on how many users you have, can be a challenge by itself.

How To Scan

There are 2 ways to scan a barcode/QR code using FileMaker. The easiest way is to leverage FileMaker Go’s ability to integrate with the iPad or iPhone’s camera and allow for easy and instant barcode scanning using the Insert From Device script step. This works great as long as you have an iOS device. If you don’t have an apple device you can always buy a USB or Bluetooth laser scanner. These scanning devices are interpreted as virtual keyboards when connected, so in order to scan and capture the scanner’s input, you must have the cursor in a field or Show Custom Dialog input field. They work especially well with the Show Custom Dialog script step as they will trigger the OK button after scanning.

Using a Bluetooth or USB scanner also allows for the ability to scan multiple barcodes one after another using a field with an On Exit script trigger, that goes back into the field after running so you are ready to scan again. Laser scanners are definitely quicker and preferred for scanning multiple items quickly, but they do have limitations like only being able to scan traditional barcodes (No QR codes) and they can’t scan a screen. Below are a few Bluetooth scanners we have tested with FileMaker and recommend:

• Wasp Cordless Pocket Barcode Scanner
• KDC200i 1D Laser Barcode Scanner with Bluetooth

Conclusion

Creating and scanning barcodes in FileMaker is straightforward and makes a great extension of FileMaker’s already great toolset. Contact us if you’d like to discuss barcoding and how you can add to your solution and improve your workflow.

Did you know we are an authorized reseller for FileMaker Licensing?
Contact us to discuss upgrading your FileMaker software.

Download FileMaker Barcoding

Please complete the form below to download your FREE FileMaker database file.

Here at Productive Computing, we talk a lot about FileMaker to QuickBooks integrations. We even did a webinar about it. This is because: a) FileMaker is a powerful and versatile database platform with (almost) unlimited possibilities to help you organize and analyze your data, and b) QuickBooks is the most popular accounting software for small and medium businesses in the United States.

That being said, what makes more sense than to have these two applications communicate and share information? Connecting these two applications via a plug-in (FM Books Connector and FM Books Connector Online) allows users to seamlessly exchange data between the two applications, saving time and money and reducing data entry errors.

A FileMaker and QuickBooks integration can be accomplished three different ways:
1. Custom integration with a plug-in
2. Purchase a CRM that is preconfigured to integrate with QuickBooks (i.e. Core5 Starter Edition)
3. Integration package

In this blog we’re going to talk about option number 3. Specifically, the 123sync™ Accounting Integrator.

The 123sync Accounting Integrator allows you to connect your FileMaker Solution with a variety of QuickBooks’ lists and transactions. 123sync, like other integration packages, offers clients a faster and more affordable option to the traditional custom integration. With two integration packages available (Basic and Advanced), you are able to achieve a virtually custom integration at a fixed price.

123sync is ideal for clients who:
1) Have an existing FileMaker solution
2) A well-structured QuickBooks file
3) A workflow that ensures all transactions begin in the FileMaker solution
4) Do not have an in-house developer familiar with plug-ins and QuickBooks or do not want to hire someone to do a custom integration

This application was built to communicate with one QuickBooks file and is compatible with QuickBooks Desktop for Windows, meaning Mac integrations require one instance of QuickBooks for Windows and FileMaker for Windows.

123sync gives the user the power to push, pull, modify, and delete various QuickBooks Lists and Transactions. The chart below reflects the functionality of 123sync version 9. Functionality is subject to change and we recommend you check out this functionality chart for the latest updates.

 
With the release of version 9, users can take advantage of the updates listed below:
– Added compatibility for QuickBooks 2017 Pro, Premier, and Enterprise US, Canadian, and UK editions.
– Added compatibility for FileMaker 64-bit.
– Added new module to the Advanced Integration package to allow users to push and pull Vendor Bill Payments. 123sync will post new or modified check payments to Vendor Bills and pull check payments applied to Vendor Bills in QuickBooks. Credit card payments are not posted.
– Added ability to use multiple currencies to the Invoice and Payments module.
– Enhanced the Customer list to pull additional Customer information from QuickBooks.
– Enhanced the Vendor list to pull additional Vendor information from QuickBooks.
– Enhanced the Payments module to retrieve posted customer payments.
– Added visual indicators to each module to notify users when there are items ready to post.
– Added ability to pull the Currency list to the Basic and Advanced Integration packages
– This plug-in is verified compatible with FileMaker® Pro 13-15 for Windows (32-bit) and FileMaker Pro 14 -15 for Windows (64-bit)

As mentioned earlier, there are two integrations packages available. Depending upon your QuickBooks needs, you can either purchase the Basic Integration Package or Advanced Integration Package. 123sync requires a $600 yearly subscription that covers unlimited users and phone technical support. Your subscription qualifies you for free upgrades to the latest versions of 123sync and FM Books Connector plug-in – and ensures your 123sync file is always compatible with the latest versions of FileMaker and QuickBooks.

For more information please visit www.fm2qb.com.

FileMaker Cloud Overview

FileMaker came out with a new way to deploy your app with FileMaker Cloud! This new product is available through Amazon Web Services (AWS) which gives you FileMaker Server in minutes.

FileMaker Cloud is ideal for remote work groups/companies that don’t have an IT company or someone in-house that can manage the time and effort involved in running a dedicated FileMaker Server machine.

Watch on Youtube: FileMaker Cloud Setup Demo

Jag använder alltid mitt favoritprogram FileMaker Pro för alla möjliga ändamål. Häromdagen fattades det lappar med veckoschema som vi använder för att boka tvättid i vår lilla bostadsrättsförenings tvättstuga och den gamla mallen hade försvunnit. Tio minuter med FileMaker blev räddningen och vi kan tvätta igen!

Den blankett jag ska göra är ganska enkel, några linjer, klockslag och datum. Datumen ska automatiskt beräknas så att varje ny post visar en ny vecka. Så här kommer det att se ut:

Jag skapar en ny databas med namnet Veckoschema och ändrar temat på layouten som skapas. Högerklicka på layouten i layoutläget och välj Ändra tema: Eftersom det är en utskrift väljer jag första bästa tema lämpat för utskrift vilket är “Upplyst utskrift”. Temat innehåller inga bakgrundsfärger.

För att se hur stor yta jag kan använda för veckoschemat väljer jag att visa Sidbrytningar i menyn Visa och liggande utskriftsformat med menyvalet Utskriftsformat i menyn Arkiv. FileMaker lägger alltid till lite marginaler för utskriften, jag har för vana att ta bort dem Väljer därför kommandot Tillval för layout i menyn Layouter, går till fliken Skriva ut i dialogrutan som visas, kryssar i rutan Använd fasta marginaler och anger 0 i samtliga fält. Jag struntar i varningen som kommer när jag klickar OK.

Dra sedan ut layouten så att sidbrytningen nedtill inte syns. Till höger om sidbrytningsmarkeringen kan det finnas vit layoutyta, den kommer aldrig att skrivas ut, och kan vara bra att ha kvar.

Jag börjar med att rita en ram med rektangelverktyget som är så stor som jag vill att schemat ska bli. Jag använder alltid Hårfin linje i helt svart, men inställningen beror på vilken skrivare du har. Välj det som passar dig bäst! Du väljer genom att först välja Formateringslisten i menyn Visa, för att visa formateringslisten, och sedan i menyerna till höger på formateringslisten.

Nästa steg är lite matematik för att få linjerna med lika långt mellanrum. Jag visar Granskaren (välj Granskare i menyn Visa) och visar fliken med positionsmåtten. Jag arbetar nästan alltid med enheten pt (punkter), klicka på enheten bredvid någon av positionsmåtten för att byta enhet (där kan stå cm eller tum, klicka till det står pt). I mitt veckoschema ska det finnas 1 rad i huvudet med datum och sedan 4 rader, 1 kolumn längst till vänster med klockslag och sedan 7 kolumner, 1 för varje dag. För enkelhetens skull flyttar jag min rektangel högst upp till vänster, då blir det lättare att räkna eftersom måtten då börjar på 0.

Höjden på rektangeln gör jag till 510 punkter, den första horisontella linjen ritar jag 50 punkter från ovankanten. Jag tar fram kalkylatorn på datorn och räknar ut att för att få 4 rader ska det finnas 3 linjer med 115 punkters mellanrum. Jag kopierar den första linjen och placerar kopiorna på rätt ställen. FileMaker hjälper till att placera dem rakt under varandra.

Bredden på rektangeln blev 735 punkter, för att få jämna siffror gör jag första kolumnen till 49 punkter och resten med 98 punkters mellanrum. jag drar en lodrät linje som jag sedan kopierar. FileMaker hjälper till att placera linjerna. (Siffrorna kan säkert göras enklare och jämnare, det blev så när jag ritade rektangeln som sedan avgör måtten.)

I kolumnen till vänster skriver jag några klockslag med textverktyget och placerar dem på “fri hand”.

Som rubrik för varje dag vill jag ha veckodag och datum, en vecka för varje post. Jag behöver två beräkningsfält, ett beräkningsfält som räknar fram datum för måndagen i veckan som ska visas och ett fält som räknar fram datum för alla veckans dagar. För det använder jag ett repeterat fält med 7 repetitioner.

Det första fältet kallar jag DatumMåndag och har följande formel:

GetAsDate ( "2016-10-31") + 7*(Get (Postnummer) - 1)

Jag börjar veckoschemat på 31 oktober 2016 (som är en måndag, det är viktigt) och för varje ny post lägger jag till 7 dagar. Jag använder funktionen Get (Postnummer) för att få reda på postens nummer i tabellen, multiplicerar med 7 för att få datum för nästa måndag. Fältet får ett datum på en måndag för varje post i databasen.

Det andra beräkningsfältet är ett repeterat fält, kallat DatumDagar, med formeln:

Extend(DatumMåndag) - 1 + Get ( BeräknatRepetitionsnummer )

Funktionen Extend behövs för att fältet DatumMåndag ska användas för alla repetitionerna och med funktionen Get ( BeräknatRepetitionsnummer ) blir det olika datum i alla repetitionerna. Eftersom jag minskar med 1 läggs det till 0, 1, 2 osv. till 6 till måndagsdatumet, dvs. jag får ett datum för veckans alla dagar från måndag till söndag. Tänk på att fältet ska repeteras 7 gånger, vilket anges längst ner till vänster i dialogrutan för beräkningsfält.

Fältet DatumMåndag placerar jag i layoututrymmet till höger om den yta som skrivs ut så att jag ser att det blir rätt. Fältet ska aldrig skrivas ut.

Jag vill ha både veckodag och datum i varje kolumn, på två olika rader. Enklast är att lägga in fältet DatumDagar två gånger ovanför varandra, det översta fältet utformar jag så att endast veckodagens namn visas och det andra utformar jag till att visa datumet.

Se till att samtliga 7 repetitioner visas horisontellt, det väljer du i Granskarens fjärde flik. Centrera innehållet i fältet med kommandot Justera Centrerad i menyn Utforma och gör det lika brett som kolumnerna med dagarna. Markera allt på layouten och flytta veckoschemat så det blir centrerat på sidan.

Sen är det bara att gå till bearbetningsläget och skapa så många poster som du vill ha veckoschema och skriva ut.

Ladda ner exempeldatabas: Veckoschema.fmp12

FileMaker är känt för dess snabba och flexibla sökning. Det är inte många databasprogram som söker i alla ord i alla fält med ett en knapptryckning (snabbsök)! Sökning bland miljontals poster går på ett kick. Men ibland vill man visa sökresultatet i en portal och då är det inte lika enkelt, men inte heller så svårt .

I exemplet har jag en tabell med bilmärken som jag vill söka bland. Resultatet, som kan vara en eller flera bilmärken, vill jag visa i en portal som visas i en layout baserad på en annan tabell.

När jag skriver en bokstav i sökfältet ovanför portalen vill jag att portalen omedelbart ska visa aktuellt resultat. I bilden har jag skrivit “ma” och portalen visar alla bilmärken som börjar med dessa bokstäver. Jag använder en manus-trigger som kör ett manus varje gång fältet ändras. Högerklicka på fältet i layoutläget och välj Ange scripttrigger i menyn. Jag har gjort ett manus som gör sökningen, i brist på fantasi har jag kallat det för Sök, som ska köras varje gång fältet ändras (VidObjektÄndra).

Jag har tidigare skrivit om hur du kan söka medan du skriver med fördröjning så att du hinner skriva några bokstäver innan FileMaker behöver göra en sökning, allt för att göra det “snabbare” för användaren och låta FileMaker slippa göra så mycket jobb. Denna gång görs sökningen direkt.

Sökningen gör jag med manuset Sök som faktiskt bara består av en enda rad. Det finns flera olika sätt att göra sökningen på, t.ex. att gå till en annan layout och söka med “FileMaker-sökning” och därefter gå tillbaka till ursprunglig layout, men denna gång använder jag en enkel SQL-fråga. Alla bilmärken finns i tabellen Bilmärken med fälten id_bilmärke (ett unikt id-nummer för varje post), Bilmärke och i vissa fall även Land. Från tabellen “Portalsök” vill jag söka början på alla bilmärken.

Jag använder en SQL-fråga som ser ut så här:

SELECT id_bilmärke FROM Bilmärken WHERE LOWER(Bilmärke) LIKE ?

Sökning görs på fältet Bilmärke i tabellen Bilmärken. För att kunna söka på både små och stora bokstäver omvandlar jag först innehållet till små bokstäver med SQL-funktionen LOWER. Som resultat returnerar jag id-numret för alla bilmärken som hittas. FileMaker har en funktion för att köra SQL-frågor, funktionen ser ut så här:

ExecuteSQL ("SELECT \"id_bilmärke\" FROM \"Bilmärken\" 
WHERE LOWER(\"Bilmärke\") LIKE ?";"";"¶";Lower(Portalsök::gSök) & "%")

Alla fält som innehåller “konstiga” bokstäver (ofta åäö) måste finnas inom citat-tecken för att FileMaker ska kunna förstå dem, därav \” före och efter varje fält- och databasnamn i SQL-frågan.

Den andra parametern till funktionen är skiljetecknet mellan de fält som frågan ger som resultat. Eftersom frågan endast returnerar ett fält (id_bilmärke) så anger jag en tom sträng.

Den tredje parametern är skiljetecknet mellan olika poster. Jag anger en radmatning för jag vill att varje id-nummer ska hamna på en egen rad i resultatet.

Den fjärde parametern är det jag vill söka efter, den ersätter frågetecknet i SQL-frågan. Jag har kallat fältet för Portalsök::gSök och lägger till ett procenttecken som talar om för SQL att söka på början av fältet. Även sökfältet omvandlar jag till små bokstäver med FileMaker-funktionen Lower.

Resultatet, som är ett eller flera id-nummer på lika många rader, tilldelar jag ett annat variabel-fält av typen Text i tabellen Portalsök. Portalen som ska visa resultatet är relaterad med tabellen Bilmärken, variabel-fältet och id_bilmärke är nyckel.

SQL-frågan körs varje gång du skriver ett tecken i sök-fältet, variabel-fältet i Portalsök tilldelas med de hittade id-numren och portalen uppdateras med de bilmärken som hittas. Enkelt, eller hur?

Du kan ladda ner exempel-databas här: Portalsok.fmp12

Payment Processing in FileMaker using Stripe

Imagine a company called Larry’s Landscapers. It’s a landscaping company that uses FileMaker in variety of ways as part of its day to day operations and has a group of customers for which it maintains lawns and driveways on an as needed basis. Wouldn’t it be great if Larry could provide these customers with the same sort of payment convenience that we’ve all become used to when paying recurring bills? That is, give them the ability to provide payment information just once and then reuse that information, in an easy and secure way, for any and all future payments? Well, Larry, or any business for that matter, can do just that by integrating the Stripe payment platform with their FileMaker solution.

Watch on Youtube: FileMaker and Stripe Integration

Creating a Stripe account

First, you’ll need a basic Stripe account. They are free, can be created with just an email address and by default are set up for testing.

Once you have an account, the only piece of information you will need for testing is your Test Secret Key. It can be found in the API Keys tab of your Account Settings via the Stripe dashboard. This key will be passed to Stripe as a means of account authentication.

Processing a test payment

Processing a payment via Stripe requires two steps and both steps entail POST requests, which are accomplish via the BaseElements Plugin and its BE_HTTP_POST custom function.

Creating a customer

Step one is a request for a customer object and is made via BE_HTTP_POST with the following parameters:

• stripe customer url
• payment source, e.g. if a credit card, then “card”
• customer name
• credit card number
• credit card expiration month & year
• credit card security code
• stripe account test secret key

An example of the BE_HTTP_POST function call would look like this:

BE_HTTP_POST ( “https://api.stripe.com/v1/customers” ; “source[object]=card” & “&source[number]=” & $cardNum & “&source[exp_month]=” & $expMonth & “&source[exp_year]=” & $expYear & “&source[cvc]=” & $CVC & “&source[name]=” & $customerName ; $testSecretKey ; “” )

The parameters passed in this example represent the standard minimal amount of customer information for a credit card. Additional information, like the cardholder’s address, can also be passed along as part of the customer request. You can refer to Stripe’s API documentation for more details.

A successful customer request results in Stripe creating a customer and returning a customer object, which, like all objects returned by Stripe, is in JSON format.

A customer is Stripe’s secure way of allowing users to create a reusable means by which to charge the same payment source multiple times. It’s secure because all the customer data is stored on their servers. (Servers which have been audited by a PCI-certified auditor and are certified to PCI Service Provider Level 1.) It’s easy because, once a customer is created, all that’s needed for processing a payment is the id of the customer object because this id now represents the customer and all their information.

Processing a payment

Step two processes the payment by requesting a charge object and is made via BE_HTTP_POST with the following parameters:

• stripe charge url
• charge amount as total amount of the smallest unit of the charge currency, e.g. if USD, then 1 cent
• currency of the charge, e.g. if USD, then “usd”
• customer object ID
• stripe account test secret key

An example of the BE_HTTP_POST function call would look like this:

BE_HTTP_POST ( “https://api.stripe.com/v1/charges” ; “amount=” & $amount & “&currency=” & $currency & “&customer=” & $tokenID ; $testSecretKey ; “” )

As with the request for a customer object, the parameters passed in this example represent the standard minimal amount of charge information. Additional information, like a description of the charge or an email address to send a receipt to, can also be passed along. As before, you can refer to Stripe’s API documentation for more details.

A successful charge request results in Stripe returning a charge object. It contains various details about the charge, like if the charge itself was successful, the type of charge it was and how it was verified.

Failed requests and error objects

Stripe has extensive error handling and will return an error object whenever a requests fails.

Going live

Moving beyond testing and processing real payments with Stripe requires the activation of your Stripe account via an account application. Then, once live, you just use your Live Secret Key in place of your Test Secret Key.

Conclusion

Integrating a FileMaker solution and Stripe is both straightforward and simple, and the result is an efficient and secure way for any business to process a wide variety of payments.

Did you know we are an authorized reseller for FileMaker Licensing?
Contact us to discuss upgrading your FileMaker software.

Download FileMaker and Stripe Integration

Please complete the form below to download your FREE FileMaker database file.

Det finns många tävlingar på hösten och en del arrangörer använder FileMaker för administration av tävlande och redovisning av resultat. FileMaker fungerar såklart alldeles strålande även för denna uppgift!

Det som kan ställa till en del huvudbry är hur man hanterar om två eller flera deltagare hamnar på samma resultat, dvs. samma tid eller samma poäng. Vem av dem kommer först? Eller ska alla med samma resultat dela en placering? Om det inte går att skilja dem åt är det vanligt att de får samma placering.

Jag har gjort ett enkelt exempel.

I exemplet är det en post per tävling/gren och en portal med de tävlande. I den vänstra kolumnen finns radnummer och sedan platsnummer, namn och tid (i exemplet vinner deltagaren med kortast tid). Databasen innehåller 2 tabeller, en med tävlingar/grenar och en med resultaten. Portalen är sorterad efter tid i stigande ordning.

Radnumret fås genom att sätta in variabeln Postnummer ({{Postnummer}}) på portalraden. Placera insättningspunkten där du vill att den ska hamna och välj Postnummer i menyn Sätt in. I bearbetningsläget visas nu radnummer som ökar med 1 för varje portalrad. Samma variabel kan användas utanför portalen och visar då postens nummer i aktuell sorteringsordning. Jag har placerat ut variabeln Postnummer även upptill till höger på layouten.

Platsnumret är lite svårare och kräver en beräkning. Jag har räknat antal deltagare med ett bättre resultat och adderat 1 för att få platsnummer på respektive deltagare. För det har jag gjort en s.k. själv-relation, en relation mellan samma tabell.

Relationen är mellan Resultat och ResultatFöre (som alltså båda är samma tabell). Tanken är att relationen ska visa alla deltagare i samma tävling/gren med bättre resultat än aktuell deltagare. Relationen ser ut så här:

Det är viktigt att jämförelsen görs i samma tävling/gren (därav fältet id_tävling i relationen) och sedan görs en jämförelse med fältet Tid så att relationen får fram alla med bättre tid i samma tävling/gren.

Beräkningsfältet Platsnummer ser ut så här:

Count(ResultatFöre::id_resultat) + 1

Beräkningen räknar antalet resultat som är bättre och lägger till 1.

Beräkningen kan också göras med funktionen ExecuteSQL och ett SQL-kommando, då behövs inte själv-relationen. Det finns exempel på SQL-beräkning i exemplet också, den ser ut så här:

ExecuteSQL ("SELECT COUNT(id_resultat) FROM Resultat 
WHERE \"id_tävling\" = ? and Tid < ?"; ""; ""; 
Resultat::id_tävling; Resultat::Tid)

Ladda ner exempelfil: Radnummer.fmp12

There are a lot of good-looking and useful Javascript libraries. The good thing is they can be integrated into a web viewer in FileMaker. It is even possible to interact with the web viewer, display more information or navigate to a certain FileMaker record using the fmp protocol. A couple of years ago (times goes by..) I wrote about an interactive map of Sweden.

Lately I was in need to show events in a timeline. It might be made in FileMaker, but it is a lot easier using one of the Javascript libraries easily found on the internet. Without much consideration I choosed a library called VisJS.

The events are all in one table with an id, a date, a name, and a little bit longer comment. In this sample I have entered information about FileMaker versions (the dates are approximated) in an Events table. The information is from Wikipedia, and this blog.

The VisJS library needs an array in JSON format for the data to be displayed. I added a calculation field, Info, in the Events table for the JSON representation of one event record, it looks like this:

"{ id: " & id_event & ", start: new Date(" & Year(EventDate) & ", " & 
Month(EventDate) & ", " & Day (EventDate) & "), content: '" & Content & "'}"

A JSON record begins with { and ends with } and it is all text concatenated with the & operator. In the JSON record I have 3 fields: id, start and content. The “start” field is a Javascript date field, and the “content” field is the text which is going to be displayed for each event in the timeline. I need the “id” to make the timeline interactive.

I have an interface table, TimeLine, with a global text field, gHTML, where I have copied the HTML code from “basic example” found at the VisJS web page. The links to the library itself and its CSS are changed to a CDN, which means you will need internet access when you open the database in order to show the timeline.

In the HTML template I have a placeholder (<!TIMELINEDATA!>) for the JSON timeline data, like this:

...
var items = [
<!TIMELINEDATA!>
 ];
....

At the interface layout I have a web viewer with the following formula:

"data:text/html, " & Substitute(Timeline::gHTML;"<!TIMELINEDATA!>";Substitute( List ( Events::Info ); "¶";","))

The formula substitutes my placeholder in the HTML template with real data. All the JSON events for all event records are assembled with the List function. I use a cartesian relation to get all the events from the Events table. All new lines are also replaced by a comma (you can’t have new lines in the content field) just to make it valid JSON.

To make the timeline interactive I added one Javascript event to the HTML template, a function I found in VisJS called “timeline.on (‘select’)”, which means it will be executed when I click (select) on one of the events. The Javascript does only one thing, it calls a script in the FileMaker database using the fmp protocol with the id of the event as parameter. It calls the FileMaker script ViewEvent which does a single Set field script step, only to show more information about the selected event below the timeline.

Information about the selected “FileMaker Pro 15″ event in yellow is displayed below the timeline.

Please feel free to download a sample database and add whatever events you like.
Download Timeline.fmp12

Update 2016-07-19: Thanks for all the positive feedback! I did a new version of the sample file with tabs for the timeline and the HTML template. Actually I also got rid of a global field, gHTML, at the same time. It is now a layout object, a technique I have mentioned before. It is also possible to rename the sample file and maintain the functionality,

Update 2016-07-20: Adjusted month number by -1, in Javascript month is between 0 and 11 instead of 1-12. I seem to forget that all the time The sample file nr 2 is updated accordingly. Thanks for noticing James!

Download Timeline2.fmp12

When the first FileMaker DevCon Developer Cup took place at FileMaker DevCon 2014 many people asked me why I was not participating. So I applied for the next one in 2015. I was selected out of the over 1500 attendees to be one of 12 actually competing, and I won both the separately evaluated design challenge and the whole contest as well, to become a proud FileMaker DevCon 2015 Developer Cup Champion.

Now, 3 weeks before the next DevCon 2016, when people are thinking of participating in the new FileMaker Developer Challenge, I am going to reveal, why I decided to participate in the Developer Cup last year, what I did to win it, and what it has given me in the end.

Continue reading: How and why I won the FileMaker DevCon Developer Cup

[…] https://www.gomainspring.com/blog/ […]