Integrating Google Translate with FileMaker

Integrating Google Translate with FileMaker

Recently we built a feature in a client’s FileMaker solution that involved sending written messages in both English and Spanish, starting by selecting a boilerplate template. The users would then edit the message as necessary before sending. We created a few test templates by composing a message in English, then we used Google Translate in a browser to get the Spanish version, and stored both versions in a record in the template table. Our initial understanding was that users of this solution speak and write both English and Spanish, so editing the boilerplate messages would not be a problem. But that turned out not to be the case.

Not only did the user not speak or write Spanish, he also didn’t read Spanish. So although the boilerplate Spanish version of the message was displayed on screen, he assumed it was an accurate translation of the message he had edited in English. Basically he assumed his message was being translated automatically in real time (never mind that the Spanish message did not change as he edited the English version).

We started thinking that instead of storing the Spanish version of the message in the templates table, maybe we could use Google Translate to translate the English text on the fly. It seemed like that would correct all of the problems we had encountered with the first draft and it would definitely make the user’s life easier.

We started by doing some Googling of our own and right away found a blog post and sample file Douglas Alder wrote on this subject in 2012.

Douglas’s sample file was almost exactly what we were looking for, we just wanted to simplify it a bit and make it more portable. So we did away with the ParseData custom function, and used Insert From URL instead of a web viewer.

Download our sample file to see how easy this is. You’ll have to get your own Google API Key.

And here are some tips about how to use the Google Translate API.

Cheers.

Create A Map Using FileMaker I

Today I was asked to create a map using FileMaker to show all the students that are accepted to my client’s school. The client said the Department of Education needs to map out the bus routed for the students. He said he DOE may not want to provide buses for a child if he/she lives too far, and we need to make an argument that the child should be bused to the school.”

So at first I thought about what software/plug-in I should use, then I realized I can create a map with Google Maps with layers. Especially, since this map we can’t just have in the database but rather we need to share it with the DOE. Turns out this was the easiest task, ever, so I thought I’d share the steps.

  1. Export the data you want to be mapped in CSV format. I wanted to see kids and their location (full name, address, city, zip);
  2. Create a Google Map here: https://www.google.com/maps/d/u/0/;
  3. Add a new layer, name it whatever you want to import your data into;
  4. Import the CSV file. It will ask you to dedicate the data for the pins (address) and the next step is to dedicate a column for your label (full name);
  5. Change the color of the pins;
  6. Add more layers with more data if needed (in my case my school is the other layer).

And here is the finished product:

 

Screen Shot 2015-07-15 at 1.12.44 PM

Clean Data in FileMaker

Achieving clean data in a FileMaker database is important

This is something I’ve bee dealing with for years. It hit me like a large hammer in the chest when one of my users replaced the contents of one field in about 5000 records with HTML she copied from the web. This was done accidentally, and clearly she felt so bad that she didn’t even tell me. This happened about 9 years ago.

I realized I had to put in some measures so this won’t happen EVER again. Now, you can think of every possible measure you can take and users can still surprise you. It’s similar to spam online. We keep getting smarter at how to deflect spam but spammers are always a step ahead.

Below are some things you can put in place to make sure your (their) data is as clean as it possibly can be.

Custom Menus

  1. Create a custom menu (or more) for certain situations (Exhibit A)
    1. One menu for everyday use (and different menus for different levels of users);
    2. Another menu for when printing;
    3. Another menu for a layout that handles sensitive data.
  2. On every custom menu
    1. Remove “Replace Field Contents” so users cannot accidentally replace records in fields
    2. Replace “Paste” with a script you write. The script should use Paste with remove style checked. (Exhibit B)

When you create a custom menu name it appropriately so you know what it is for.

Clean Your Field

  1. If you’re dealing with phone numbers you might want to format the phone number. You might even want to reject data that is not entered properly. So, e.g. phone numbers are 10 digits in the US, zip codes are always 5. So, the phone number field you can format to accept numbers and dashes only. The Filter function is great for this. The zip codes you need to lock down to allow 5 digits only (if the country is US) and no other characters but numbers.
  2. Use a custom function (or write it in a script but the custom function (Exhibit C) is easier) to strip off anything you don’t want, such as additional space,  carriage returns and formatting (Exhibit D). Users have the tendency to hit enter after filling out a field. This is probably just a habit they picked up when working with Excel.
  3. When you’re generating reports, badly formatted data can drop off or look really strange (e.g. large letters in red). So whether you format your field nicely to Helvetica 10 on a report, if the data is messed up in the fields you’re not getting the proper result. The best thing? You’ll only know about this months down the line when the user complains that the report looks messed up.

 

03-27-2015-exhibit-a

Exhibit A

03-27-2015-exhibit-b

Exhibit B

03-27-2015-exhibit-c

Exhibit C

03-27-2015-exhibit-d

Exhibit D

 

 

 

 

 

 

 

 

 

Exhibit D shows Ray Cologon’s Trim 4 Custom function.

Just to recap, clean data in FileMaker is as important as having a nice structure or a slick interface, if not more important, since we actually build the database for the users, not ourselves.

Getting Data From WordPress To FileMaker

How many times we find ourselves presented with a new challenge when working in FileMaker? I will say this: more often than not. I like challenges. They make you learn and keep you on your toes.

My client has a website for a new school which we built in WordPress: www.yalowcharter.org. He was interested in getting data from WordPress to FileMaker. The school is accepting applications for students. He needs the applications to be in a FileMaker database so we can keep their information (children names, parents, etc.)

Screen Shot 2015-02-26 at 11.18.51 AM

I’m using the ContactForm7 plug-in to collect the information. Visitors can fill out the form and submit the info. The great thing is the plug-in works well, it even has a CAPTCHA element (separate plug-in required) so you won’t get spammed by bots. It however can only email the data from the form collecting all the  data and dumping in the body of the email. That is as far from a relational database as it can be.

So after a little digging I found another plug-in (Contact Form DB) that can dump the collected data into a MySQL database. I was excited like a little kid. Then came the next hurdle: all the fields with their data created a new record. And the MySQL timestamp, of course is not oh so delightful. By the way, I use Navicat for working with SQL tables, but you can use PHPmySQL, and that will do the job, as well.

Screen Shot 2015-02-25 at 11.38.06 AM

Turns out all I had to do is write a SQL query to turn that into a nice VIEW and now I have columns and rows with a properly formatted timestamp. So here’s one query that can help you write one:

SELECT
DATE_FORMAT(FROM_UNIXTIME(submit_time), ‘%b %e, %Y %l:%i %p’) AS Submitted,
MAX(IF(field_name=’first_name’, field_value, NULL )) AS ‘first_name’,
MAX(IF(field_name=’last_name’, field_value, NULL )) AS ‘last_name’,
MAX(IF(field_name=’email’, field_value, NULL )) AS ’email’,
MAX(IF(field_name=’cell’, field_value, NULL )) AS ‘cell’,
MAX(IF(field_name=’website’, field_value, NULL )) AS ‘website’,
MAX(IF(field_name=’service_provided’, field_value, NULL )) AS ‘service_provided’,
MAX(IF(field_name=’address1′, field_value, NULL )) AS ‘address1’,
MAX(IF(field_name=’adress2’, field_value, NULL )) AS ‘address2′,
MAX(IF(field_name=’city’, field_value, NULL )) AS ‘city’,
MAX(IF(field_name=’state’, field_value, NULL )) AS ‘state’,
MAX(IF(field_name=’zip’, field_value, NULL )) AS ‘zip’
FROM wp_cf7dbplugin_submits
WHERE
form_name = ‘Individual Membership Form’
AND
form_name = ‘Student Membership Form’
GROUP BY submit_time
ORDER BY submit_time DESC

And that produces something like this. If you have errors Navicat will let you know.

Note: Make sure you use straight quotes, aka not curly (or smart) quotes such as the ones text editors use.

Screen Shot 2015-02-25 at 11.38.39 AM

The next step is using the Actualtech plug-in (ODBC connector) on the FM server to set up a DSN so you can access this data. You’ll have to define your database (tables, view, username and password).

Note: Make sure you select “view”, as well because it is not an actual table you need but the view you created with the SQL query.

After that you create a new external data source in your FileMaker database and create your table occurrence from it.

Screen Shot 2015-03-03 at 4.33.45 PM

You can actually just display this data in your database but it’s much more sophisticated and safer to bring that data over to FileMaker. Of course you can do this in different ways. I have to parse the data into multiple tables because we are dealing with related data (kids to parents, phone numbers to parents). I will just run a server script that will check for new records and create them on the FileMaker side when new records show up.

One last important thing to pay attention to is that just because a form is submitted and a record is created in the MySQL database the record will not show up automatically in the FileMaker database. So you’ll have to refresh.

Now, of course when you’re dealing with data you’ll have to put in some checks and balances. Data can be submitted twice because of computer or human error into the MySQL database but we don’t need that twice in our FileMaker database.

I think this is a pretty simple and easy way to get data into FileMaker from a WordPress site.

Update: We’ve published a follow-up article, you can read here:

Upgrading to FileMaker Pro 13

The long-awaited FileMaker 13 family of products have arrived for Christmas.

Some of us FileMaker Business Alliance members or who attended the FileMaker Developer Conference in San Diego last August had gotten a preview of what the new offerings were, but, of course our non-disclosure agreements does not allow us to share the excitement until the product actually hits the market. Well, as usual we did get our hands on a pre-release version of the FileMaker 13 Pro/Advanced product and spent some time playing with it. You can see numerous people have written blog posts and about finding bugs (which is completely normal with new software release), FileMaker cloud hosts testing different servers and configurations, discussing their findings. This is all normal.

What’s not normal is clients upgrading their operation system overnight and locking themselves out of their solution because, well FileMaker 11 doesn’t run on Mavericks. Then me scrambling to get them updated on their licenses to FileMaker 12 on the day of 13 being released, therefore you can’t even download a FileMaker 12 trial and even FileMaker Go 12 has being pulled from the App Store. The good news is that FileMaker 13 can open FileMaker FileMaker 12 files. Any version below 12 it cannot open because we had a file format change at 12. FileMaker 12 can also open FIleMaker 13 files—but you can choose to not allow it, and I would highly recommend that after upgrading a solution.

Well, two months in we can say we learned to like FileMaker 13. I’m not going to spend a lot of time on the new features, they’re great and the upgrade is worth it even if only for the themes but then there’s that pesky web access. Since we also sell FileMaker hosting, we’ve gotten some FM13 servers ready as of January 1. This, of course was a great time to learn that FileMaker 13 Web Direct is a very powerful tool to bring your server down if used “incorrectly”. That being said, if you follow the instructions—whether ours or FMI’s—you should be alright and all of our servers will chug along just fine.

So, the question is still: should we upgrade or not? Well, I think it depends on how much time and money you have and when you want to spend them. You’ve surely heard that before. Time? Well, yes, it takes some time to create a theme (if we don’t want to use the built-in ones) and then it takes some time to apply it to the layouts. And then you have to make new layouts for Web Direct (highly recommended, lots of articles out there, a video from Richard Carlton, etc.), and then it’s possibly a good time to revise some layouts and possibly the workflow. Then test, test, test. Especially, if you want to use Web Direct. You will also have to do some pixel-pushing to make those pesky web layouts work both in FileMaker, as well as web. All the developers we’ve talked to have agreed, yes, it takes awhile to create a them and set it up for all the elements used, but once that’s done, it’s much easier to make universal changes. So, there, that brings us to the second question: money. Well, it takes some time to upgrade and test and that costs money.

Some clients we have already upgraded and some we’re in the process of and some we will upgrade later. Every situation is different and we need to carefully examine all the factors that come into play. One client I recommended upgrading a copy of the database, play with it, get it to “perfection”, then import the new data in and port it over a weekend.

And then there’s the topic of licensing and hosting and concurrent access. FileMaker, Inc. has changed the pricing model. FileMaker Go is free. Web Direct you do not need a client for but they charge for the concurrent access to FileMaker Go, as well as Web Direct, and it comes in 5-user packs. So we sell hosting in 5-user packs on top of the 1-file hosting. So there’s this additional cost to factor in when upgrading.

Everyone seems eager, new features are always exciting, unless they are scary. I recommend talking this over. Let’s make a decision together, make a plan then upgrade.

Any questions about upgrading or licensing, feel free to drop us a line.

FileMaker on Android and Web

A lot of people ask: can we run FileMaker on Android?

Well, not natively, but that doesn’t mean you cannot get access to FileMaker data.

I’m going to list a few options. Of course, you can always run down to your local Apple Store and grab an iPad Mini and then all your problems will be solved. Plus you get to use Messages and talk to all your iPhone owner friends for free. If this doesn’t tickle your fancy, here are your options:

Option 1: Set up a 2X Server. I know just the guy for that. Then you can access your database (or someone else’s) through a Windows and a real FileMaker Pro install. Will it be readable? Well, that depends on your screen size. We tested this on an Android Asus tablet some time ago. See the video:


Option 2: Have a web developer (e.g. us) create a web application that gets your data posted from FM to the web and load that in a browser. We can even sync your data with MirrorSync from 360works.com.

Option 3: Upgrade to FileMaker 13 and use WebDirect to show your data in a browser. Now, I know what you’re thinking. This product is brand new. Is it buggy? Well, it might need more beefier web server than what you might have at the moment and being new we will discover bugs as we use it, but it’s a viable option.

But then again choosing the right option is choosing the right city to raise your kids in: requires some research and conversation.

Contact us if you need help deciding or setting things up. We’re here to help you.

New Development vs. Phase II or Maintenance

New development

The majority of the client work we do is new development. New development is when a client does not have a FileMaker database (or has a very old database that does not fit their process) so we create them a solution from scratch. The process for that is outlined in this article, so I’m not going to detail that. The bottom line is that we start in a sandbox and we build them a castle of some sort. We start with discussing the possibilities,  we plan, we create mind maps, mockups, all good, creative stuff that has tangible results. The client is super-excited, they come up with a long laundry list of requests that we sift through and  narrow down until we establish what will be in Phase I. Everyone loves you, the boss, the secretary, even the boss’ wife. You get taken out for lunch, they talk to you often, with a lot of excitement in their voice. You are their hero. You are the person who will bring them to the 21st Century, you are Superman who can fly across the sky and can’t do wrong. And you can’t do wrong. You give them what they need, if you can guide them well, as opposed to what they might want. The only tiny thing that can cloud the whole process is that they actually will have to pay for the work, but since they have realistic expectations of what they are getting, that’s not really an issue. Very few clients actually bargain once they get a proposal, because by then they have an approximate idea of how much Phase I will cost and what they will be getting for that money. Now, all we have is the anticipation and the development work. Ad if we did our job well, the client is extremely happy with the result of our hard work (which they don’t see, of course, but everyone knows Rome wasn’t built in a day and you have to move a lot of bricks to get anything erect.)

The client starts using the database

At first there’s overwhelming happiness from half the employees, while the other half won’t even touch it cause the database must have the plague. Slowly but surely the head of the firm will get everyone using it. That’s when the chaos hits. Requests come in from left and right and of course, you still have some bugs to iron out. We generally do free bug fixes for 3 months after the release date. That is the industry standard. Of course, we tend to be more lenient than not, ssh…

Here come the modifications

We recommend putting those on a project named Phase II, so the client has a clear understanding that A) those are not bugs (something that was programmed faulty), B) what needs to get added to the database. Since we charge for estimating (because it is a lot of man hours to provide with a somewhat accurate account of how long the work will take, therefore how much it will cost), people elect to just pay up front, get a discount and have us work the time off. This seems to only work for awhile. At some point or another every clients starts questioning the funds put towards continuous development. I have a client whose database was developed by them and we just came in to make modifications, to make the database more modern, better-functioning, convert it to FM12 and add bells and whistles. After working together for about a year I get the question: we’ve spent over x amount of dollars and we still don’t have a working database. The client is regularly unhappy and not the kind of client I was talking about in the beginning of the article. Why is that? Let’s see. From the client’s standpoint, the requests are simple, I just want a new shipment section added to my database. Sounds simple, right? Yeah, it’s as simple for the trained developer who receives clear instruction as it is for the NASA to launch another rocket. Every adult brushes their teeth every morning (one would hope) and none of us think about whether we pick up the toothbrush with our left hand or right, how much toothpaste to squeeze out and how long and with what motion we’re supposed to brush. Now, try to have someone stand next to you, who says, wait, you left out top-left-3, and you have a speck in between 4 and 5. Oh, and you need to massage your gums, too. And what about flossing? Look what you’re doing, you ran out of toothpaste, and you’re dripping on the floor, go get the mop! At some point you’d put the brush down and walk out of the room. And the 3-minute task certainly ended up being half an hour. Hope my example illustrates my point: without clear direction it’s impossible to perform any task. Now, let’s add to the mix that people actually pay for your time, therefore you are bound to them.

When you perform modifications on a database—whether you built it or anyone else—any little change you make affects previous development, can cause bugs that you’ll need to fix that can cause other bugs, etc. It can get really hairy, even if the client has a clear vision and realistic expectations.

Here are the caveats to implementing changes:

  • Changes take a lot longer to implement than original, similar features;
  • Often there is no visual gratification;
  • Bugs keep popping up that further delay the work;
  • Client is thoroughly unhappy waiting for the change;
  • You end up billing 20 hours, even though you worked 30, because the client thinks it was a quick change that should’ve taken 5.

The bottom line is: nobody’s happy throughout the process even though the end result gets to be what the client wants.

The solution?

I think my solution will be to treat every modification/change as new development. We will create a mockup, estimate the work and charge accordingly. Then we’ll handle the next batch of changes. It takes finesse to build a database on a solid foundation so you can add features easily later. We aim to do just that, so we’ll have the best chance to keep our clients satisfied. Another thing to pay attention to is mapping out phase II while working on phase I, so we can accommodate those changes  much easier when it is time to do so. You know, a stitch in time saves nine. Still you can have misunderstandings, but at the end of the day you’re still better off because both parties agree to what’s going to be done.

Comments are welcome both from developers or clients.

 

 

FileMaker Go and Barcode Scanning – A Workaround

We all know that there are differences when dealing with FileMaker Go vs. FileMaker on a desktop. I think I can speak for all of us when I say we’re grateful that FileMaker Go exists, but we have to admit it has caveats. But we’re resourceful developers, right? We will sit and try ti tackle any given problem, because that’s what out clients/bosses pay us for. Most importantly, we cannot sleep until we figure the issues out because it just bothers us when we’re presented with a problem that we cannot find a solution for.

We sell barcode scanners. We make sure all of our scanners work with FileMaker, and all of our Bluetooth scanners work with FileMaker Go (and, of course, Android and other devices that sport Bluetooth connectivity). So far so good, right?

I’ve been getting complains that it’s impossible to scan into FileMaker Go. When things dont work like they’re supposed to out of the box.

The Problem

In FileMaker Pro you can script the scanning process to show a dialog that you scan into. This is great, because all the scanners you can program (which is usually the default anyway) to send a carriage return (enter) at the end of scanning. Which means that when the scanner is done scanning your code, the “Ok” button will be pressed automatically and your script can continue what it’s supposed to do, whether that is the bring up the dialog to scan another code or do some crazy magic and analyze data. You, however cannot achieve the same result in FileMaker Go, because you cannot get the cursor into the dialog. Now, we can ask questions like ‘why’ and spend hours and days cracking our heads open and even email FileMaker Inc. to see why this is not working. Or we can simply create a workaround.

The Solution

You can download a simple file that demonstrates how you can get around the problem with barcode scanning in FileMaker Go. I used a field to scan into instead of the dialogue and two triggers so you can create records and keep on scanning. There’s one field and one button on this layout. Obviously, you will have to scrip the rest of your process.

We can take the load off of you by doing the heavy lifting. Scripting can be cumbersome, when all you want to do is just scan an item and manage your inventory. Let us help.

We sell a wide selection of Bluetooth Laser Barcode Scanners that work with FileMaker Go out of the box . Pair, connect and scan right into a field. Want to use the iOS keyboard? No problem, push the small button and the keyboard will pop up.

Script Writing Lesson 1: Compacting FileMaker Scripts – Making Your Code More Readable By Humans

We could discuss at length what we should and what we should not do while writing a FileMaker script. If you’re an expert you have your own opinion on this, if you’re a newbie, you can be overwhelmed with all the opinions the experts have. Not to fear, I’m not here to argue points or overwhelm the newbies (which I was at some point and I did find a lot of things overwhelming). This post is simply there to remind you of some best practices in script writing and help you get your scripts:

  1. a little more compact
  2. more readable and
  3. easier to debug (if you have FileMaker Pro Advanced, you can use the debugger and data viewer to see what your script does).

The script below uses functions from the 24U SimpleDialog Plug-in, which I use often to produce dialogs, because it extends dialog capabilities in FileMaker beyond the basics. First and foremost, pay attention to the first couple of lines. For your own, but especially for other developer’s sake, use some space at the top to introduce your script and let people know who wrote it and when. If you make significant cahnges later, it might help to fill out the “modified by” section, as well. I capture the errors, so in case I need to do some error logging, I can do that. I perhaps will talk in anther post about error logging. Every section in the script has a note on it, so I can tell later what exactly each section is for. This is not a crazy long script. I’ve seen scripts that took literally so long to debug that I forgot what it did by the time I got to the end. BAD, BAD, BAD. Ok, so I’ll let you read through the script and see you when you’re done.  (It’s only 4 pages; hopefully it won’t take you too long.)

So, what do you think? My brother and I threw this script together for one of our clients. The goal was to be able to guide users to generate a list based on certain criteria and then let then choose an output method, then generate a file. Simple, right? I don’t believe the above is a bad script. Not because I’m bias, but rather because I’ve written and seen long scripts. A long script is also not necessarily a bad script. A bad script is one that doesn’t function well or leaves room for error. A long script is just hard to read, decode and hard to debug. Imagine a 1000-line script where you just want to test something on line 900. You’d have to either ignore the first 899 lines and put a marker in (in which case you don’t know if things are happening as they supposed to be.)

Agnes Riley Won The “Mad Dog” Award 2 Years In A Row

Miami, FL July 18, 2012

FileMaker Public Relations awarded one of this year’s Mad Dog Awards to Agnes Riley of ZeroBlue at the FileMaker Developers Conference in 2012. This is the second Mad Dog Award for Agnes Riley two years in a row. The winners, representing the U.S., Europe, and Australia were acknowledged for their contributions in promoting awareness of the FileMaker name in the media and social media.

Agnes Riley has been at the forefront of communicating FileMaker news, writing blog posts and promoting the FileMaker name. Winning this award again is a recognition by the industry and FileMaker, Inc. of our hard work.

Thank you, Everyone for reading the blog and Facebook posts and tweets.