Tag Archives: FileMaker

A Database for Grant Research

1 Reply

I put together a grants database screen (click to view full size) to consolidate information for funding sources, and to track dates and interactions.

It is definitely an evolving project, but contains the basic information need to contact the funder, the deadline dates involved, the funder’s areas of interest, and the typical range of a grant award.

So far, I’ve been concentrating on foundation funding. Many foundations typically ask for a letter of interest before you put together a full proposal. So, I’ve included multiple date fields, a deadline for a letter of interest, a deadline for a full proposal, and a date when they announce their award.

Originally I thought that this database would be mostly for research, but after working with the online grants database, Grantstation, I think I will reserve this database for funders that I really expect to submit to. Some ideas for future enhancements include:

  • Links to standard “boilerplate” paragraphs that are used in an application. 
  • Links to edit the proposal or letter directly in Word. 
  • Links to the PDFs of the proposal. 
  • Reports that create a grants calendar. 
Before anyone comments that “you should really use X software” for this purpose, I just want to say that I’ve used several in the past, including DonorPerfect and Blackbaud, and evaluated many others. Right now, I’m in the process of rethinking my entire workflow automation from the ground up, and this very lightweight approach is just what I’m looking for. Plus its in FileMaker, so I can run it on my Windows machines at work, or my Macs at home.     

Tech Friday: FileMaker Resources

Leave a reply

Random FileMaker-related resources: 

1. You can create an alternative icon for FileMaker 13. When you have multiple FileMaker versions installed, it sometimes is difficult to distinguish between them. FM12 and FM13 have virtually identical icons with the same color schemes. Here are alternative icons that can be installed, for both Win and Mac from HomeBase Software. HomeBase has a ton of of technical information on their web site.

2. In support of a project to integrate SmartyStreets with FileMaker, I’ve been doing some additional research on JSON, (Javascript Object Notation), which is a simplified version of XML.

3. Coding Standards for FileMaker

4. Modular FileMaker: is shared library of FileMaker functions. There are huge community-developed libraries for other languages such as PHP and Python. These folks are attempting a similar idea for FM. I’ve downloaded their JSON module, and am experimenting with it. Other examples include a nifty SQL query generator, and and another interface to Mailchimp.

Custom Functions are a way of adding small chunks of user-defined code that can be called within a FileMaker script. Brian Dunning is the guru here and curates the largest library of custom FM functions on the web. He also has sample data sets available for the U.S., Austria, Canada, and the UK. Five hundred records are free, and a million records are available for ten bucks.

5. If you need a 100,000 records or so, you could also download the database of public and private schools available at the National Center for Education Statistics. This includes demographic data as well as mailing and location addresses for schools. The data is fun to play around with. You can give yourself some sample exercises in FileMaker. For example:

6. What percentage of public school students are eligible for free or reduced lunch in your state? It is 38% in mine. Poking around in some other states, it looks like that isn’t unusual; in many states it is 40-60% or more. The lunch program is often considered a proxy for the family poverty rate. But maybe that’s another discussion.

 

SmartyStreets – Validate Mailing Addresses

Leave a reply

Some months ago I submitted a mailing list file to our mailer (we use Quad), and I was somewhat taken aback to get their report that over 5% of the addresses that we sent to them were invalid or incorrect.. I was thinking this wasn’t too bad a figure, but they informed me that “people who knew what they were doing” would achieve valid rating of close to 100%. My thought was to do some “pre-validation” before sending the mail file to Quad. Turns out this can get expensive. Then I found SmartyStreets.

SmartyStreets is a web-based address validator for U.S. addresses. Using the US Postal Service official address database, SmartyStreets will validate any address that you send to it with varying amounts of correction.  Addresses are classified in a number of different ways, including:

  • Nomatch             The address is invalid.
  • Mailable              The address is valid and can receive mail. 
  • Mailable-Vacant:  The address is valid but vacant
  • Match-Inactive.   The address is valid but inactive. 

SmartyStreets has a one-off web interface which allows you to validate addresses one at a time on the fly. http://www.smartystreets.com. It will supply a nine digit zip code for valid street address, and it will suggest addresses nearby if you submit an invalid address. In short, if SmartyStreets returns an address, it will most probably be mailable. That’s what I’m hoping, anyway. I just sent a new file to Quad, and we’ll see what the accuracy is.

Click to Enlarge.
The web-based interface returns, not just a corrected address but additional information such as lattitude and longitude of the address, whether it is commercial or residential,and the address’s time zone and congressional district. 
Smartystreets will process lists of addresses. You can simply paste in an ASCII comma-delimited list or an Excel file containing an address list, and SmartyStreets will return the list with in a mail/no-mail format, or with about 30 fields of additional information. 
SmartyStreets frequently donates their services to churches, schools, libraries, and many non-profit organizations. They have extensive documentation that explains how the validation process works, and what the results of your file can contain. And they have an API (an application programmer’s interface) which will return results in XML or JSON format.  
A great addition to the mailing toolbox.  

Alabama Eye Bank runs on FileMaker Pro.

Leave a reply

Over at  Tech for Home Healtcare,  I’ve described how the Alabama Eye Bank uses FileMaker Pro to manage the process of receiving donated corneas thorough finding a recipient and scheduling the surgery. It is an amazing application that shows the cross-platform versatility of FileMaker, hosted on Windows servers, and deployed to Mac workstations, iPads, and iPhones.  

FileMaker: A Quick and Dirty SQL Query Screen

Leave a reply

How to create a quick and dirty SQL screen for a FileMaker database:
1. Create a data table. I call it SQLWB. Give this table two text fields: “Query” and “Result”. Both of these will be text fields.
 2. Modify the layout I call it SQL Workbench.
 3. Resize the two fields to show a larger amount of text. I add a scrollbar to the end of each field

4. Create a scripts to run the query.  I call it RunSQLQuery.

Set Field [SQLWB::Results; ExeuteSQL (SQLWB::Query;””;””)]


5. Create a script to clear the two text boxes.  I call this script ClearResults.

Set Field [SQLWB::Query;””]
Set Field [SQLWB::Results;””]

6. Add two buttons to the layout; one for each of the scripts.

The result will look something like this:








Note that this isn’t a full-blown SQL implementation, it will only respond to SELECT statements. (You can’t INSERT or DELETE, for example.   Also, if there any problems with your SQL syntax, the result box will only show a question mark.