FileMaker Posts @agiriley - Tumblr Blog

Adding Weather and Air Quality Data in FileMaker

With the current COVID-19 epidemic, life as we knew it has changed. You know what that means for you. Some inconvenient but some changes are probably good and maybe they are here to stay. Like washing your hands every time you enter a household or office.

Also, in California, we have been weathering fires ignited by dry-lightning and other issues. And of course, that brings a lot of smoke, ash, and soot. It helps to know when to stay indoors or whether going home from work you will need a mask with a filter for smoke.

I thought it would be apropos to add some data to our splash page. Luckily www.purpleair.com provides all of what I needed, and it was super easy to implement.

If you go to their website (https://www.purpleair.com/map), you need to find the closest sensor to your location. If you click on “Get this widget” you can get the sensor’s ID number. Then you go to https://www.purpleair.com/json?show= and append the aforementioned ID. It should load the JSON with the data relevant to that particular sensor.

Now you can use the Insert From URL script step and insert the JSON from the URL. (If you’re working with FileMaker 17 or higher you can insert the data into a variable.) So now that you have inserted the JSON into a variable named $json, you’ll just have to parse it out. You would use JSONGetElement and get whatever element you desire. E.g. JSONGetElement ( $json ; “temp_f” ) will give you the temperature value. I got the temperature, humidity, air quality index, and pressure just for fun. If you want to take this a step further you can add conditional formatting to show the color Purple Air is showing for the AQI, aka green for healthy, yellow, or red or purple for higher levels.

Happy FileMaking!

Adding Weather and Air Quality Data in FileMaker was originally published on ZeroBlue

New Support Portal

We have a new Zendesk-based support portal: for now email-only. For support requests, please send an email to support (at) zerobluetech (dot) com or click on the Support button on the menu. The support portal is for hosting and hardware sales customers so we can track and manage responses better.

New Support Portal was originally published on ZeroBlue

New ZeroBlue Shop

We are happy to announce that we have a new snazzy shop. Now with more shipping and payment options. We are the leader in barcoding and RFID technologies supporting the FileMaker/Claris community.

The new shop is getting expanded so check back for new products soon.

Our favorite product, the Scanfob 2006 is on sale for 229.00. It’s the best in class small Bluetooth Barcode scanner to use with any device.

New ZeroBlue Shop was originally published on ZeroBlue

What We've Been Up To

Hello All,

We’ve gone through some transformation. We moved to California! We still strive to continue to serve the entire country.

We are still doing most everything we’ve done before: hosting, development, and sales. We take on special clients now mostly for maintenance work and web integration, but if you have a project in mind feel free to talk to us, and given we capacity we’d be happy to work with you.

Agnes has acquired an adorable Australian Cattle Dog Mix puppy, who has become an integral part of the team.

We have been quite busy lately (lots of web integration with FMBetterForms), hence the lack of blog posts, but due to popular demand, we promise to do better in the future.

We are also working on catching up on some certifications, but we’ve been learning Javascript and working with Postman for web integrations. Talk to us about it!

We’ve been staying safe practicing good social and hygiene habits. Hope you’ve been, too!

What We’ve Been Up To was originally published on ZeroBlue

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.

Integrating Google Translate with FileMaker was originally published on ZeroBlue

10% Off On All Hardware for FileMaker Devcon

[av_heading tag=’h3′ padding=’10’ heading=’THE BIGGEST SALE OF THE YEAR!’ color=” style=’blockquote modern-quote modern-centered’ custom_font=” size=” subheading_active=” subheading_size=’15’ custom_class=”][/av_heading] [av_textblock size=” font_color=” color=”] We are happy to announce that we have secured a great discount on our products for you for the time of the FileMaker Developer Conference.

From today till July 25th you can get ALL of our HARDWARE items 10% off. Scanner, RFID readers, and even accessories. It’s a great time to try a Bluetooth Barcode Scanner or an RFID reader with FileMaker. [/av_textblock] [av_notification title=’Note’ color=’custom’ border=” custom_bg=’#de003f’ custom_font=’#ffffff’ size=’large’ icon_select=’yes’ icon=’ue859′ font=’entypo-fontello’] Use coupon code DEVCON15! [/av_notification] [av_promobox button=’yes’ label=’Let’s Go Shopping!’ link=’manually,http://store.zerobluetech.com’ link_target=’_blank’ color=’theme-color’ custom_bg=’#444444′ custom_font=’#ffffff’ size=’large’ icon_select=’no’ icon=’ue800′ font=’entypo-fontello’ box_color=” box_custom_font=’#ffffff’ box_custom_bg=’#444444′ box_custom_border=’#333333′] Get me some discount! [/av_promobox]

10% Off On All Hardware for FileMaker Devcon was originally published on ZeroBlue

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.

Export the data you want to be mapped in CSV format. I wanted to see kids and their location (full name, address, city, zip);

Create a Google Map here: https://www.google.com/maps/d/u/0/;

Add a new layer, name it whatever you want to import your data into;

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);

Change the color of the pins;

Add more layers with more data if needed (in my case my school is the other layer).

And here is the finished product:

Create A Map Using FileMaker I was originally published on ZeroBlue

#filemaker #google maps

FileMaker and PHP: Link to A Record Using Redirect

Let’s Learn How We Can Link to A Record Using Redirect

In this article we’ll look at how you can link to a record using redirect with FileMaker and PHP.

In later versions of FileMaker we have Snapshot Links. When you work in a database you can always give someone a Project number when you need to refer to a Project. Or we can script generating a Snapshot Link. The problem with the Snapshot link is that you’ll have to remove it once you spit it out to the Desktop. AppleScript can help with that.

Another method is the fmpURL protocol. You can generate nice links such as this:

fmp://localhost.com/SomeDB.fmp12?script=open_project_link&param=PROJECTID

Then if you copy and paste this into apps it’ll open the database. If you’re on a Mac and use Messages or email this is great!

But then I ran into a problem where my client’s law firm relies on Gmail for their primary communication AND they are using it from a web browser. Now, Gmail DOT NOT know what to do with the FMP protocol so it just breaks the URL and funky things happen. I’m sure Gmail is not the only party here that does not know what to do with it.

So one—fairly simple method—of tackling the problem is referring to good old, creating a link to a record in FileMaker with PHP. I know, this is not a novel method but when I was looking for it I couldn’t find a comprehensive article on how to achieve what I want.

What you need:

A web server

A PHP file

A script in FM that will know what to do with the parameter it receives

A button on the layout to run another script to generate the appropriate link to call your redirect PHP

Any web server anywhere will do. You just need to place a very simple redirect PHP file on it. Name it “redirect.php and place it in the appropriate document folder of the web server. Make sure the file has the proper (read, execute) permissions.

The PHP file:

<?php

//gets the query string for the value of ‘project’

$project=$_GET[‘project’];

//concats url with ID from query string.

$url = “fmp://YOUR_FM_SERVER/Database.fmp12?script=SOME_SCRIPT&param=” . $project;

//redirects to new page.

header(“Location: ” . $url);

?>

The FileMaker Scripts

You need a simple script that the PHP file will call. It will need to define the received parameter and perhaps search for the aforementioned project by ID. Obviously the script can do more, depending on your business process.

And the script that calls your redirect.php on the webserver. It should generate and copy your link to the clipboard:

http://YOUR_WEB_SERVER/redirect.php?project=PROJECTID

Then that link can be pasted anywhere and will be clickable.

FileMaker and PHP: Link to A Record Using Redirect was originally published on

#filemaker #link #PHP #redirect #URL

FileMaker and PHP: Link to A Record In Using Redirect

FileMaker and PHP

In this article we’ll look at how you can use redirect to link to a record with FileMaker and PHP.

In later versions of FileMaker we have Snapshot Links. When you work in a database you can always give someone a Project number when you need to refer to a Project. Or we can script generating a Snapshot Link. The problem with the Snapshot link is that you’ll have to remove it once you spit it out to the Desktop. AppleScript can help with that.

Another method is the fmpURL protocol. You can generate nice links such as this:

fmp://localhost.com/SomeDB.fmp12?script=open_project_link&param=PROJECTID

Then if you copy and paste this into apps it’ll open the database. If you’re on a Mac and use Messages or email this is great!

But then I ran into a problem where my client’s law firm relies on Gmail for their primary communication AND they are using it from a web browser. Now, Gmail DOT NOT know what to do with the FMP protocol so it just breaks the URL and funky things happen. I’m sure Gmail is not the only party here that does not know what to do with it.

So one—fairly simple method—of tackling the problem is referring to good old, creating a link to a record in FileMaker with PHP. I know, this is not a novel method but when I was looking for it I couldn’t find a comprehensive article on how to achieve what I want.

What you need:

A web server

A PHP file

A script in FM that will know what to do with the parameter it receives

A button on the layout to run another script to generate the appropriate link to call your redirect PHP

Any web server anywhere will do. You just need to place a very simple redirect PHP file on it. Name it “redirect.php and place it in the appropriate document folder of the web server. Make sure the file has the proper (read, execute) permissions.

The PHP file:

<?php

//gets the query string for the value of ‘project’

$project=$_GET[‘project’];

//concats url with ID from query string.

$url = “fmp://YOUR_FM_SERVER/Database.fmp12?script=SOME_SCRIPT&param=” . $project;

//redirects to new page.

header(“Location: ” . $url);

?>

The FileMaker Scripts

You need a simple script that the PHP file will call. It will need to define the received parameter and perhaps search for the aforementioned project by ID. Obviously the script can do more, depending on your business process.

And the script that calls your redirect.php on the webserver. It should generate and copy your link to the clipboard:

http://YOUR_WEB_SERVER/redirect.php?project=PROJECTID

Then that link can be pasted anywhere and will be clickable.

FileMaker and PHP: Link to A Record In Using Redirect was originally published on

#filemaker #link #PHP #redirect #URL

Getting Data From A WordPress Form To A FileMaker Database II

In my previous post, Getting Data from a WordPress form to FileMaker Database I detailed how you can get mySQL data (that perhaps comes from a WordPress form submission) into FileMaker. This time I’ll write about how you get that data into your own FileMaker table. I recommend “importing” the data from the mySQL database into a temporary table. We called ours APPLICANTI_TEMP. It contains the same exact fields as the mySQL database and it is there so you can identify records in between the two tables. So in my case the fields are text fields, just like in the mySQL DB (even the timestamp). But then we added a timestamp field that is a regular FileMaker TimeStamp and when we run the scripts we set this with the proper FM TimeStamp. Additionally I added two calculations:

One that checks whether the record we are viewing exists in the mySQL table and

Another one that checks whether we have added the record to our APPLICANT table.

These calculations are quite limited but in my case achieve the wanted result. I am not too worried about someone modifying the records in the mySQL table, because I’m the only one with access to it. But you’ll have to make decisions based on what’s best for you. I, however wanted to make sure I’m not creating the same record several times in the APPLICANT table. Well, we still are, because people apparently fill out and submit the form (sometimes with mismatched information?!?) several times. So at the end of the day you’ll still need a human to identify if a second record has a misspelled last name or it is, indeed a different applicant. But in case you have a scenario that disallows record deletion based on some criteria, you’ll need to develop a more refined logic. I have already converted my SQL TimeStamp to a FileMaker readable timestamp, but if you don’t do that in your Query you’ll have to do that in FileMaker. Bring Dunning’s Custom Functions collections is always a good place to start looking for handy custom functions. We have a set of scripts that perform the data move (because it’s not really an import). They loop through a set of fields on a layout record by record and create the record in the TEMP table, then the APPLICANT table. These scripts are run from the server every 15 minutes. You set your time interval based on your own process. The first step is to refresh the data from the mySQL database, because unless you do that any new submissions from the web (in my case) will now show in FileMaker: Before running scripts from the server, always make sure you test the hell out of them locally first. Here’s an article on server-side scripts in general. I always recommend adding a LOG table, and put in error checking in your scripts, especially when you’re running them from the server. You DO NOT have a debugger on server. And who wants to code blindly?! And if you’re lucky—like us—you’ll have to parse the data into different tables, because you’re dealing with parents, addresses and phone numbers. You’d obviously want to do that from the TEMP table record. Hope this covers it all. Any questions, feel free to ask.

Getting Data From A WordPress Form To A FileMaker Database II was originally published on

Getting Data From WordPress To FileMaker II

In my previous post, Getting Data from a WordPress to FileMaker I detailed how you can get mySQL data (that perhaps comes from a WordPress form submission) into FileMaker.

This time I’ll write about how you get that data into your own FileMaker table.

I recommend “importing” the data from the mySQL database into a temporary table. We called ours APPLICANTI_TEMP. It contains the same exact fields as the mySQL database and it is there so you can identify records in between the two tables. So in my case the fields are text fields, just like in the mySQL DB (even the timestamp). But then we added a timestamp field that is a regular FileMaker TimeStamp and when we run the scripts we set this with the proper FM TimeStamp.

I added two calculations:

One that checks whether the record we are viewing exists in the mySQL table and

Another one that checks whether we have added the record to our APPLICANT table.

These calculations are quite limited but in my case achieve the wanted result. I am not too worried about someone modifying the records in the mySQL table, because I’m the only one with access to it. But you’ll have to make decisions based on what’s best for you. I, however wanted to make sure I’m not creating the same record several times in the APPLICANT table. Well, we still are, because people apparently fill out and submit the form (sometimes with mismatched information?!?) several times. So at the end of the day you’ll still need a human to identify if a second record has a misspelled last name or it is, indeed a different applicant. But in case you have a scenario that disallows record deletion based on some criteria, you’ll need to develop a more refined logic.

I have already converted my SQL TimeStamp to a FileMaker readable timestamp, but if you don’t do that in your Query you’ll have to do that in FileMaker. Bring Dunning’s Custom Functions collections is always a good place to start looking for handy custom functions.

We have a set of scripts that perform the data move (because it’s not really an import). They loop through a set of fields on a layout record by record and create the record in the TEMP table, then the APPLICANT table. These scripts are run from the server every 15 minutes. You set your time interval based on your own process.

The first step is to refresh the data from the mySQL database, because unless you do that any new submissions from the web (in my case) will now show in FileMaker:

Before running scripts from the server, always make sure you test the hell out of them locally first. Here’s an article on server-side scripts in general. I always recommend adding a LOG table, and put in error checking in your scripts, especially when you’re running them from the server. You DO NOT have a debugger on server. And who wants to code blindly?!

And if you’re lucky—like us—you’ll have to parse the data into different tables, because you’re dealing with parents, addresses and phone numbers. You’d obviously want to do that from the TEMP table record.

Hope this covers it all. Any questions, feel free to ask.

Getting Data From WordPress To FileMaker II was originally published on ZeroBlue

#mySQL #wordpress

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 Universal Script

More useful coding principles: Wikipedia

Coding Principles And How They Apply In FileMaker was originally published on

#coding principles #DRY #KISS #variables #YAGNI

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.

That reminds me of the KISS (Keep It Simple Stupid) principle

This acronym is a design principle noted by the U.S. Navy. 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. Below is a script we use to strip fields (after a user adds data that) from unnecessary garbage.

Clean Field Universal Script

More useful coding principles: Wikipedia

Coding Principles And How They Apply In FileMaker was originally published on

#coding principles #DRY #KISS #variables #YAGNI

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

Create a custom menu (or more) for certain situations (Exhibit A)

One menu for everyday use (and different menus for different levels of users);

Another menu for when printing;

Another menu for a layout that handles sensitive data.

On every custom menu

Remove “Replace Field Contents” so users cannot accidentally replace records in fields

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

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.

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.

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.

Exhibit A

Exhibit B

Exhibit C

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.

Clean Data in FileMaker was originally published on

#calculations #clean data #clean text #custom menus #fields #fonts #phone number #Ray Cologon #replace field contents #reports #scripting #trim 4

Creating Good Looking Charts with Google Chart in FileMaker with JavaScript

Whenever possible I like to use the native tools in FileMaker. If it ain’t broke, don’t fix it, right? There are circumstances when you need something and it’s just not there or you are just not satisfied with the tools at hand. That’s how I feel about the charting in FileMaker. It’s good that we have it, but I’m just not convinced it’s the right tool for the job. Luckily we have a web viewer and you can use HTML/PHP and Javascript—just to mention a few—

Creating Good Looking Charts with Google Chart in FileMaker with JavaScript was originally published on

#chart #JavaScript

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

Create a custom menu (or more) for certain situations (Exhibit A)

One menu for everyday use (and different menus for different levels of users);

Another menu for when printing;

Another menu for a layout that handles sensitive data.

On every custom menu

Remove “Replace Field Contents” so users cannot accidentally replace records in fields

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

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.

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.

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.

Exhibit A

Exhibit B

Exhibit C

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.

Clean Data in FileMaker was originally published on ZeroBlue

#calculation #clean data #clean text #custom menu #fields #fonts #phone number #Ray Cologon #replace field contents #reports #scripting #trim4

Getting Data From A WordPress Form To A FileMaker Database

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. Finally, the school is at the point that it can accept applications for students. He needs the applications to be in a FileMaker database so we can keep their information (children names, parents, etc.) 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. 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. 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. 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.

Getting Data From A WordPress Form To A FileMaker Database was originally published on

Trending Blogs

Recently Viewed Blogs

FileMaker Posts