Join Tables, Many-To-Many solutions

Posted on February 17, 2011 by Dirk Reynolds

In Filemaker, it’s all about relationships. In order to make your data fly, it’s a good idea to break out the data into appropriate tables. For example, if you’re running a small business, instead of putting all your data into one massive table (or spreadsheet), you may want to create a few basic tables, and then relate them all to each other. Common tables would be: Clients, Projects, People, Invoices, Line Items, Payments, etc.

That way, if you were able to attach People to a Project via a relationship, you would be able to see all the People that belong to a Project when viewing that Project. Better yet, if you happened to change some data in the Project record, it would be reflected in their related People records right away too. This is because you’re not duplicating any data. The Project information exists only in one place, in one record (the Project record), and is simply displayed in any other area of the database that is related to that Project record — such as the People involved with that Project.

One-To-Many Relationship

So far, so easy. The above scenario is called a One-To-Many relationship, because it’s set up so that one Project can be related to many People. But what if somebody in the People table is involved in more than one Project? The One-To-Many relationship will not allow that.

This is a problem, because in real life, Larry may very well be in charge of Project A, and Project B, and perhaps also involved with Projects T, U, and V in various other roles. Since we cannot set up a many-to-many relationship directly between two tables, we need to basically add another table in-between the original two. This new table (we’ll call it Roles for this example) joins the two original tables, and because of its one-to-many hookups to both People and Projects, it becomes a de facto Many-To-Many conduit between People and Projects.

Many-To-Many Relationship, via a join table

This is because each record in the Roles table represents an instance of some Person fulfilling a certain Role for a Project. Since we can add as many Role records as we like, as long as each one’s linked to both a Person and a Project, we can put as many people on any project as we like, and we can also link as many projects to any one person as we like.

Also, since they’re linked, and because Filemaker’s relationships are both deep and bi-directional, this means that we don’t wind up duping Project or People info anywhere, yet we can display it appropriately on any and all People or Projects reports.

Posted in Data Wrangling | Tagged , , | Comments Off

Filemaker “On the Go” Kit

Posted on February 16, 2011 by Dirk Reynolds

Filemaker Go on the iPhone and iPadFilemaker’s been around for a long time, but one of its most exciting developments was when they released a full Filemaker client for both the iPhone and the iPad last summer (2010). With the release of Filemaker Go, anyone with a Filemaker solution could take their entire database with them on the road. No programming edits needed. Filemaker Go works seamlessly with existing Filemaker solutions, leveraging all the existing layouts, scripts, tables, and functions. In one shot, Filemaker allowed all of its customers to take their solutions out of the office, and keep their business data at their finger tips no matter where they are.

Better yet, if you share your Filemaker solution with colleagues, you can continue to do that on your iPhone or iPad too. Not only can you take the data with you no matter where you are; the data you’re viewing (or editing) is live, real-time, and always accurate. You’ll know of the latest sales, inventories, tasks, etc — and anything you enter on the road is immediately available to your team too, no matter where they are.

It’s not surprising that pretty much all Filemaker developers, including ZWARM, are spending a good deal of time now hooking up their clients with Filemaker Go so that they can all simply grab their iPhone or iPad to get to work, and stay on top of things easily.

Even better. The new Filemaker Go clients are very reasonably priced. For the iPhone it’s $19.99, and for the iPad it’s $39.99.

Want to learn more? Here’s a good page on the Filemaker site with a quick overview for Filemaker Go, and even a short video demo of how one company is using Filemaker Go already.

They also have a free “On the Go” kit, a 30-day free trial of Filemaker Pro, Filemaker Go demos, Filemaker Go datasheets,  Filemaker Go, sample files, and a Filemaker Go overview demo. You can find the “On the Go” kit on the same page as above, or just go here to get it.

Posted in Data Wrangling | Tagged , , , | Comments Off

Script Trick – Set Last Page

Posted on February 15, 2011 by Dirk Reynolds
Better Paging through science

Better Paging through Science

Going from meh to awesome is all in the details. Sometimes the little things are what make a tool truly great. One of the many small scripts I carry along with me in every solution I build is a Set LastPage script. It’s small, simple, and makes multi-page reports better by simply indicating on every page how long the entire document is.

So, instead of the footer just saying ‘Page xx’, your reports will now say ‘Page xx of yy’, with yy being the total amount of pages of the report. That way nobody ever needs to wonder how long the report is, or more importantly, whether or not they have all the pages. It’s easy to set up. Here’s how.

First off, before creating the script, create a global field in your solution. We will use this field to hold the yy value. Since it’s a global field, you can do this in any table, but just for organization’s sake, I usually like to actually have a dedicated table for system data like this, usually a table called Globals, or Settings. We’ll call the field LastPage.

Now all we need is the script, called Set LastPage. It looks like this.

Set LastPage ScriptPretty straightforward. First, go to Preview mode so that Filemaker formats your report exactly as it will be printed. Then while in Preview mode, go to the last page of the report. Third step, set your global field (LastPage) to the current page number value, and finally, return to browse mode.

This scriplet can be dropped into any print script you put together, and should be inserted somewhere towards the end.

Last Page setup in layout modeThe final bit of using this script, is to make sure you have the correct text on the layout itself. The page number itself is displayed by using a Page Number Symbol (##), and the last page number is displayed by showing that global field we set up earlier (LastPage). The example shown in the image here, Page ## of <<GLOBALS::LastPage>> is how I usually set it up in the footer, right aligned.

One of the keys of a better solution, is to have a good set of ‘admin’ scripts like this one, all pre-built and ready to drop into larger scripts when and as needed. Scripts to manage your window sizes, window placements, print setups, etc. As we move forward, we’ll keep on adding more of these admin script tricks to the blog, so stay tuned.

Posted in Data Wrangling | Tagged , , , | Comments Off

JC Penney Receives a Google Penalty for Using Black Hat SEO Techniques

Posted on February 14, 2011 by Chris Kramer

The New York Times took an inside look at JC Penney and found that they were engaging in less than ethical SEO tactics. Specifically, they were purchasing thousands of low quality links in an effort to rank higher for very general and competitive terms. Did it work? Yes, for a while – and over the holidays, arguably the most important time of the year. However, once Matt Cutts from Google got wind of it – Google demoted their site by a ‘manual’ penalty. Former #1 organic rankings on Google are now showing up around #71.

The moral of the story- buying links is bad. It works, but only for a short time. And then you pay the price.

Read the full article here.

Posted in Search Engine Optimization | Tagged , , | Comments Off

Basic Layout Categories

Posted on February 13, 2011 by Dirk Reynolds

When building any database, but in this case a Filemaker Pro database, it helps to organize the layouts into categories. Typically within a Filemaker solution, the layouts will fall into to one of the three following categories:

  1. Detail
  2. List
  3. Print

Obviously, you can also come up with all sorts of other ‘categories’ too. For example, some of the more complex solutions may also have ‘Console’ layouts, essentially a layout that’s separated from the other data tables. Kind of like creating a platform from where you can view and manipulate all the data in the system, regardless of where it is (ie, which table it’s in). We’ll handle that whole separation model some other time though.

Let’s talk a little but more about the three main categories listed above, and how building along these lines when can help make your database easier to use, and easier to program.

Detail Layouts
Detail LayoutFor most records, it’s nice to have an entire layout dedicated to that particular record, and perhaps all of the data that relates to it. For example, in an Address Book, it’s handy to have a Detail layout so that you can see (or edit) anything to do with any given person’s record. Kind of like an index card. You can still flip through the records, but as a rule you’ll be viewing one record at a time. This layout is typically called a Detail layout.

List Layouts
List LayoutOther times, it’s great to be able to not view just one record, but indeed, a whole bunch of them together. This is normally done in a List View. Oftentimes, it’s ideal to have both a Detail layout and a List layout, and the ability to easily toggle between the two. Perhaps obvious, but I’m still surprised by how many solutions do not have the detail/list toggle functionality built in from the get-go. A good rule of thumb is that you should have a useful working List view for every table in the system, and for the more important tables, also a Detail view.

Print Layouts
Print LayoutAlthough you can print any layout in Filemaker (just hit the Print command), it’s best to differentiate between layouts optimized for on-screen viewing and editing (Detail and List layouts for instance), and other layouts dedicated specifically to look great when you run the print command. There is after all quite a difference between what looks good on screen and what looks good in print. Going the extra mile and setting up a group of Print layouts in your solution will be the difference between an OK system, and a very useful (and professional) one. Basically, if any of the users would ever want their data to be on paper, set up an appropriate Print layout for that type of sheet or report, and allow the user to initiate that printing process from their Detail or List layouts.

Filemaker’s print and reporting options are legendary, but sometimes they’re best suited to print scenarios only. Splitting your layouts between on-screen (Detail/List) and on-paper or on-PDF (Print) is a good idea.

Posted in Data Wrangling | Tagged , , | Comments Off

Simple VCF to CSV converter. Online & Free

Posted on February 11, 2011 by Dirk Reynolds
VCF to CSV Converter

Convert your vCards to CSV in no time flat.

The bane of all data geeks’ existence? Converting data from one format to another. In a perfect world, all data would always simply be rows and columns with no additional anything. This is however, not the case. For example, a widely used data format for address books is the vCard, where the data is stored in .vcf files. If you open one of these up in a word processor, it’s all just gobblygook. In order to clean it up, (so that you can dump it cleanly into your own rows and columns) you need to do some fancy parsing footwork.

Or…. just go to the web.

There are many VCF converters out there. Many of them even specifically for Filemaker. But although they all kind of work, and many of them are even free, none is as easy as this online converter site.

VCF to CSV converter site

Just go to this site listed abover, tag the vcf file you want to convert, and presto. It ain’t fancy, but it sure works. Better yet, it’s fast and free.

Posted in Data Wrangling | Tagged , , , , , | Comments Off

Filemaker PDF Printing Trick

Posted on February 10, 2011 by Dirk Reynolds

PDF Printing TrickPrinting on Filemaker is easy. You can even automate the printing of a PDF file via Filemaker’s own scripting tools (Scriptstep: Save Record as PDF). That’s all fine and well, but the best little PDF printing trick is to also automate within the script itself the place where you want the file saved, and its name too.

That way you can run the script on anyone’s machine, and when you generate any PDF, it can have a more descriptive name than simply ‘untitled’. Especially handy when you’re running a batch of them, and you don’t want each new ‘untitled’ PDF wiping out its immediate predecessor.

This first little demo, PDF Printing, shows how, in the Save Record as PDF script step, you can manipulate the Output File parameters with a customized path name that tells the script both where to put the file, and what to call it.

PDF Printing Tip

In this demo, that data is first assembled into a variable called $PDFPathName, and then that variable is used within the Save Record as PDF script. This way, the various letters in the demo all get printed to the Desktop, and with each recipient’s first name placed into the new PDF file name. The script also automatically opens the PDF too at the end, just because it can.

Click here to download the ZIP file with the new demo. You’ll need Filemaker Pro of course. Version 11 recommended, but it might also work with FM10.

Posted in Data Wrangling | Tagged , , , | Comments Off

Google Webmaster Tools and Google Analytics now Linked

Posted on February 10, 2011 by Chris Kramer

Welcome news for those of us who pore over stats all day long. Google Webmaster Tools, which has become a more important tool over the years for all webmasters, is now allowing the ability to link an account to Analytics. Vanessa Fox lays out the details here, from an original post by Istavan Turk. Google has also just added some comments here.

Both of these tools are indispensable in their own right for any SEO, but having them finally talk to each other is a great development.

Posted in Search Engine Optimization | Tagged , | 1 Comment

Don’t Enter, Just Scan

Posted on February 9, 2011 by Dirk Reynolds

A frequently requested feature in Filemaker databases is some sort of inventory management function. An easy thing to program, but a hard thing to maintain due to the tedious nature of inventory itself. Counting and entering what’s in stock is time consuming, and worse, fraught with possibilities for errors. Anything that can help speed the data entry along, and keep it more precise, is just what the doctor ordered.

Intelliscanner, Soho Black

Barcode scanner, small enough to fit in your pocket.

This of course is what bar codes, and bar scanners, were made for. Bar scanning plug-ins for Filemaker have been around forever, but nowadays they’re getting smaller and easier than ever to incorporate into your Filemaker solution. Here’s one company, Intelliscanner, that has a small handheld scanner that seems perfect for small businesses, and works seamlessly with Filemaker. Go check it out here.

Posted in Data Wrangling | Tagged , , | Comments Off

Go Easy on the Calcs

Posted on February 7, 2011 by Dirk Reynolds
Filemaker Calcs

Great data, poor performance. Go easy on the calcs.

For the gazillion Excel users out there, Filemaker is an extremely attractive alternative. Essentially, Filemaker is a spreadsheet on steroids. Complete with a cool layout functionality that allows you to create your very own look and feel. You can take all your ‘what-if’ spreadsheets, complete with your various cell calcs and dump it right into Filemaker (or at the very least, rebuild it calc for calc).

This way of handling data, by using calcs, is phenomenal. So easy, and powerful. And if your database has a limited data set, most definitely the way to go. The only real drawback to using calc fields is that they can drag the system speed down. Especially if you’re using a lot of calc fields, or have a lot of records, or if you are accessing the data remotely.

So, if you like managing your 934,155 invoices, each with on average 9-20 calculated line items, displaying them in gorgeous list views with multiple aging columns, and share this with your 30 accounting co-workers spread out over the US… well… then you’re all going to be groaning any time you do a search, run a report, or god forbid, decide to view some summarized data for those cal’d columns.

The way around this is to go cold turkey and break your calc field habits. Instead of spiking your invoice and line item tables with calc fields, use number fields instead, and leverage Filemaker’s powerful scripts to calc the data for you on the fly — on an as needed basis. Build up your invoice sub-totals via scripts rather than field calcs. Numbers field will sort, search, and summarize with blazing speed, even with millions or records, even over the net. Calc fields… not so much.

Posted in Data Wrangling | Tagged , , | Comments Off