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

Coding Principles And How They Apply In FileMaker

Coding Principles in general

When I dabbled in development first time I had no idea I actually was developing. I was just adding a few fields here and there. Mostly I just wanted a way to record information on tracking film materials, dubbing, etc. And for the next decade I did the same: I hacked my way to getting things done for the boss. And while I tried to protest, the boss always won and said “but I need it now.” That leaves zero time for planning or building architecture. So I was more of a firefighter than developer. The next decade I spent learning development from scratch and I keep learning.

Simplicity is the most important consideration in a design

Whether you’re a hard core coder who eats, sleeps and breathes code or a school teacher turned FileMaker developer, you sure have some principles that you abide by (and if you don’t you should). We live by principles, so why shouldn’t we code by principles? And when you code, you often ask yourself “is this the best possible way to solve the problem?” In FileMaker there are at least three ways to do the same things. And there’s a different

Since this blog is mostly dedicated to FileMaker development (at this point), I’ll just take a stab at some coding principles and see how they apply to us, FileMaker developers.

YAGNI (You Ain’t Gonna Need It)

The idea is that you should code with the goal in mind to program for what you need not what you might need. XP co-founder Ron Jeffries has written:

“Always implement things when you actually need them, never when you just foresee that you need them.”

The temptation—to create something—is large for a developer. It is like putting a knife in a surgeon’s hand or giving a pencil to the architect; they will want to do what they do best. We want to add bells and whistles and we want to blow the client away. But just like furniture shopping at IKEA, we can end up with a lot more than we can take home. It’s better to code for what the client needs than what the client wants. Personally, I’m an advocate for this and I always tell my clients: “I will give you what you need but not what you want.”

Worse Is Better

Aptly, also called also called “New Jersey style”.  [And if anyone ever wants to mock Jersey, again, they will meet my fist.] The idea behind it is that “quality does not necessarily increase with functionality”.  So what this does is it uses a scale to measure which one is heavier and says simple is heavier than correct. So to me this boils down to getting a solution off the ground and into the users’s hand rather than making it perfect.  You need to cover as many aspects as you can to make it practical. Your design needs to be consistent but simple. So, buttons, element placement and font sizing should be consistent from layout to layout. Luckily we have FileMaker 13 now so if you use a built-in template (or build your own)it’s hard to go wrong.

KISS (Keep It Simple Stupid) principle

This acronym is a design principle noted by the U.S. Navy. Achieving simplicity should be the goal at all times, and avoid unnecessary complexity. Design your layouts with fewer buttons and make sure they do the most important functions. Then you can take the user to a different tab or layout to give them further info. Give them a drop-down menu with further options if you must.

Don’t repeat yourself (DRY)

When FileMaker gave us variables, it became possible to start writing universal scripts. If you’re still not using variables you’re missing out on something great! They allow you to compact your code. You can write one script to create, delete, modify a record and give parameters to tell what layout you are coming from, what your table is, where you need to end up when you’re done. Of course, there will be variations which you can put in an if statement if you need it. But if you can create something once and reuse it, you’re golden. Below is a script we use to strip fields (after a user adds data that) from unnecessary garbage.



clean-field-script

Clean Field Universal Script



 

 

 

 

 

 

More useful coding principles: Wikipedia

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.

Converting Files to FileMaker Pro 12

As you may know by now FileMaker 12 is out. A lot of articles have been written about the various features, so I’m not going to bore you with that. FileMaker 12 for iOS is free, so if you haven’t grabbed a copy, go get it. It comes with some demo files to play with to learn about the new features.

With every new release the question comes up: should I wait or should I convert? I think this question should be answered separately whether you’re a developer or a user. Users tend to jump in a lot faster. I have users who just announced they converted their database. Once it’s done—and you start using the database— it’s hard to go back. Developers I talked to are wary about converting, so we have two different groups with one goal: data integrity, however, their patience levels are different.

FileMaker Pro 12 can directly convert your existing FileMaker Pro 11, 10, 9, 8 and 7 databases.  All other versions of FileMaker Pro will require a multiple product conversion.  Review the information below to determine whether your files will directly convert to FileMaker Pro 12, or if they will need to be converted multiple times.

More info can be found in the FileMaker KnowledgeBase.

So, before jumping in, let’s look at a couple of things.

If You Are A User

First and foremost discuss the conversion process with your developer, if you have one. It’s a new file format, you’ll need to upgrade the server, as well as the file(s). If you don’t have a developer, take a look at your dataset. I heard complaints about issues with scrolling in list view. This may not affect you if you don’t use list view, but it’s worth knowing. I, for one, never use list view in the solutions I develop. I use portals that can be filtered in many ways instead, so you don’t have to work with large sets of data at any given time.

Take a moment to consider how mission-critical your solution is. If you’re a small shop (couple of people) with a one-file solution you may be able to just jump in and bite the bullet. You may never have any issues. And you may love a lot of the new features FileMaker 12 offers including the new layout themes. I’d still make a copy of the new database and run FileMaker 12 parallel to your FileMaker 11 (yes you can) and test the new database for a good couple of days. Run scripts, create records, check the mission-critical processes.

If you’re a larger shop or your database is mission critical, I’d take a copy of the file, convert it, put it in a test environment. Put in on a new server (different machine than your FileMaker 11 server). Test your new database thoroughly for about a week. Test systematically. Test for as many processes you can.

If You Are A Developer

Then it is your duty and responsibility to manage client expectations and not let them commit suicide. The same applies to you as the large shops who have a test environment and can perform lots of tests. But you also need to consider that FieMaker 12 has changes that affect the developer, as well. One example the new layout tools. Test the development features, as well, to make sure you will be comfortable developing on the new version.

We’ve been waiting awhile for FileMaker 12 and we are all excited. I love the new Insert from URL function, for example, but  when I developed a database for the iPad for a client, I noticed that getting XML takes 5 minutes on an iPad2 in FileMaker 12. This is not a concern for my client, I believe, but it can be a concern for more mission-critical environments.

It’s possibly best to convert, while your users are not using the database. A weekend might be a good time if you can’t set up a test environment. But as you know, we have 4 weekends in a month, so don’t schedule more conversions than you can handle.

Talk to us, if you need help converting your FileMaker files or have questions about development.

FileMaker Image Management – Container Field Behavior and Much More

One of my clients called this week and told me his logo got inverted when printing to a PDF. Also, the PDF file size doubled. Previously it did not occur to me to check the file he received from his designer, because I wrongfully assumed the designer gave him the appropriate file. But this brings up some interesting points about what files we should use with FileMaker and how we should use them. So, I gathered some useful information. Hope this helps you with handing your images in FileMaker and FileMaker Go.

General Image Handling

There are several different ways you can show images on a FileMaker layout:

  • Container field (Insert/Picture)
  • Place image on the layout (copy and paste from another source)
  • Show from another source in a webviewer (i.e. using SuperContainer or DocuBin)

1. Container Field

Advantages:

  1. Quick and easy. You insert an image, and voila it’s right there. If you store the file as a reference only  (suggested use if you have constant access to the hard drive the files reside on and do not plan to move or delete them) you will not bloat your database;
  2. Anyone can export it from there by right-clicking on the image and choosing “Export Field Contents”;
  3. The image can be swapped out just by inserting another image;
  4. Scan directly into a container field (by using a plug-in);
  5. You can export or email the image (file) as an attachment;
  6. Images can be cropped or resized (pay attention to maintaining proportion).

Disadvantages:

  1. You cannot do too much manipulation without a plug-in;
  2. Your database file size gets large by every image you inserted (unless stored as a reference only), therefore eventually your database will get slower;
  3. Global container fields save on close, so your file must be accessed locally, if you’d like to save a change;
  4. You can’t find or sort records, so you need to set up a calc field. See Exhibit A;
  5. You can only print the image as it is on the layout.

2. Place Image on Layout: Placing an image on the layout is not generally a good idea, unless it is your logo or  layout design (i.e. you are creating a header), but even so it’s best to use a global container field in an interface table or an image table where you load the images from when your database starts. Although, I have to mention that I see image quality differences between a referenced image in a field vs. an image pasted on the layout: in my opinion, image quality of the pasted image will be superior.

3 . Web Viewer

  • Web viewers are great for images. You can show images from a website or SuperContainer. PDF (and certain image*) files can be viewed from a web viewer. PDFs can even be manipulated (zoom, next page, etc.) on Macs. Files are not stored in the FileMaker database.
  • You cannot print from the web viewer.  You can print the layout, but that’s agains, any decent designer’s instincts.
  • There’s a great article by Geoff Coffey on Scaling Images in a Web Viewer.

Instant Web Publishing

  • Container field data cannot be entered or modified from IWP.
  • Complex or layered pictures are not rendered properly.
  • SuperContainer works with IWP.

Image Management Helpers (Plug-Ins and Tools)

  • SuperContainer (File and image management. The images are NOT stored in the FileMaker database, rather stored on a file server (can be your local computer). Has the ability to upload/download, version files.
  • CNS Image (An all-round plug-in for image handling.)
  • InsideScan (Scan directly into a container field.)
  • ExifPlugIn (Get EXIF data from pictures taken with a camera.)
  • ScriptMaster (Has basic functions for cropping, rotating, watermarking, etc.)
  • Theme Library (Themes, buttons, image manipulation)

Supported Formats for Container Fields

FileMaker Pro supports the following picture, QuickTime, and sound formats (Exhibit B)

FileMaker Supported Image Formats

General Image Suggestions

Print Quality: 300 DPI CMYK or Grayscale JPG or TIFF (if you need large, good-quality photos)

Screen Quality: 75 DPI RGB or B&W

Transparency: If you plan to use transparency, FileMaker suggests using TIFF, PNG, or JPG images. FileMaker does not recommend using the PICT (.pct) format with transparency.)

Caveats: For proper image handling, QuickTime installation is suggested on Windows.

Image Resizing and Cropping

The Preview app on Mac OS X can manipulate images: you can crop, resize, even convert them to a different format (JPG, GIF, PNG, TIFF, etc.) (Exhibit C).  Some plug-ins and the Theme Library can enhance your image manipulation experience.

Image Resizing in Preview

FileMaker Go

FileMaker Go has now the following functions for container fields (Exhibit D):

  • Choose From Library
  • Take Photo  (iOS device with camera required)
  • Get Signature
  • Paste
  • Open
  • Email

Container Field Options on FileMaker Go

SuperContainer works with FileMaker Go without programming changes. Because SuperContainer thumbnail file sizes are a tiny fraction of the full-size images, they load much faster, especially over a 3G connection.

* You can view any image that your browser (Safari on Mac and Internet Explorer on Windows) supports.

 

Top 10 Suggestions If You Want To Hack At A Database Yourself – For In-house FileMaker Developers

All FileMaker developers start somewhere, and that place is definitely not being a certified independent developer. I started by being an in-house developer. Working in a busy environment, all you have time for is cranking out the features that your boss needs, right away. Nobody has time to think about consequences. My job was to get that report done, no matter how many calculations I had to add so I can produce the results. Or getting that extra layout in so we can show the data on a different list. All of these things add up, and eventually they make your file larger and/or slow your database down.

As an independent developer, you start from a different perspective: data and system integrity are most important. Over the last 4 years since I’ve been developing independently I have learned a lot from fellow FileMaker developers. I had learn to to rethink how I develop and incorporate planning, design, testing and other steps to my routine. It wasn’t so much reinventing the wheel so much as making it better.

One great thing about FileMaker (aside from it being agile) is that anyone can become a developer. If you feel like you have enough skills to create what you need, feel free to get started. You can purchase volume licenses with great discounts even for a small office from us. And if you get in trouble, you can always consult with us during your project or even years later, when you realize that it’s actually far from where it could be.

Here are some conventions to follow if you start on your own. These will help you greatly down the line. Follow @zerobluetech on Twitter to ask questions.

  1. Be minimalistic:

    Create what’s needed, not more but not less. A lot of times we don’t think about the effects of what we do; what’s another calculation, right? Think again. The more calculation fields you have on a layout, the slower the layout loads. We’ve all seen layouts that said “summarizing” for over minutes. This is easily avoidable by storing data in indexed fields.

  2. Break your scripts into smaller chunks.

    Don’t write scripts that would print on several pages. By the time you write it you’ll forget what the beginning was all about.

  3. Follow a naming schema.

    There are a lot of them out there, but you can certainly invent your own. Stick to it.

  4. Comment your code.

    Comment calculations, scripts and even layouts if needed. You (and others) need to know what’s what at a later time, too.

  5. Implement strict security features.

    Steven Blackwell (@filemakersecure) cannot say this enough but people are still not listening. 🙂 If you have strict control over who can access your data, you have a much better chance of avoiding disasters.

  6. Use custom menus for controlling access.

    Take away “delete all records” from all users–then you won’t have to restore thousands of records (and lose the ones that weren’t captured in the last backup).

  7. Talking about backups:

    Back up, back up, back up.  Every 30 minutes or ever hour (depending on your storage space), but at least once a day, week and month. Save clones of your working database. In case database corruption, you can export the data and get it imported to an earlier clone and you’re back in business. Compact the clones to save disk space.

  8. Do not store images in FileMaker container fields.

    Contrary to popular belief, your database will get large and clumsy over time. Use SuperContainer and now DocuBin to manage files.

  9. Stay away from a lot of graphics.

    Do not paste large images on your layouts: they will just slow your database down. Create 75 dpi PNGs if you can. They will also preserve transparency. Or put them into container fields so the dataabse will have to load them once, as opposed to on any layout. Here I’m talking about your layout design (header, buttons), not your records!

  10. Use forums, mailing lists and friends.

    Don’t spend time reinventing the wheel. Someone already did it.

Hope this will save you guys a lot of time. Had anyone broken things down into digestible chunks I certainly would’ve not spent a lot of my evenings at work recovering data other people lost accidentally or rebuilding features because my database got corrupted. Glitches can still happen and they will happen. You can, however reduce the opportunity for things going bad.

You will run into walls, we all do. But don’t be afraid. Start working on something or modify any of the FileMaker starter files.

If you need help, feel free to contact us; we provide consulting services in the New York, New Jersey area, or to any region where screen sharing apps are accepted. First hour of consulting is free.

Also, check out our services, portfolio and products.