Clean Data in FileMaker

/3 Comments/in /by

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

/1 Comment/in , , , /by

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: