SMTP & Gmail

Sometimes someone else posts the definitive piece. Read this blog post by Lee Lukeheart, founder of Savvy Data, for the skinny on Filemaker and Gmail.

Pre-fill new fields with related data, quickly

Sometimes you need to add a field to an existing database that should have values for all the already existing records in the table.

Say you’ve added a new field on an existing database.

It’s not a calc field, but will have an auto-entered calculation field that will always be filled out.

For example, in the table People you add a field called “Full Name”. It’s a combination of First Name and Last Name fields which are in the same table.
= First Name & “ “ & Last Name.
You want users to be able to modify the combination, so it’s an auto-enter field and not an uneditable calculation. Easy enough, except that for existing records, there’s no value in the field for any of the existing records.

You could script adding data, using Replace[], or a Loop and Set Field[], but that can take a long time, especially if the table has a lot of dependencies, there’s a large record set, and / or you’re working on a served file. Instead you can leverage the power of the Filemaker’s storage and one little trick.

The trick is if you change a stored calculation field to a non-calculated (Text, Number, Date, etc) field, the values persist in the field.

Make that field a regular calculation field. Save out of Manage Databases. Filemaker then store the data and al your records now have the value you want, but in an un-editable calculation field. Re-open Manage Databases and change the field to Text, check the auto-enter calc box (the calculation should auto-fill) and Save out again.

Now you have a Full Name field where every existing record has the calculated value pre-filled in!

Additionally, this can work with related data.

If you want to pull the Full Name field from your Contacts table into an Invoice table, but have it stored, you would create a text field with an auto-enter calc that looks through a relationship based on INVOICE::fk ContactID <–> CONTACTS::pk ContactID.

To pre-fill all the existing records you again make it a calculation field, but since Filemaker won’t let you store a calculation that references related data, we get help from our friend Evaluate(). Wrap the related table field name inside an Evaluate()
= Evaluate ( “CONTACTS::Full Name”)

You can then store that calculation and save to exit Manage Database. Open Manage Database again, change the field to a text field, remove the Evaluate() wrapper and save to exit again.

Your  new auto-enter text field now has related data in all the existing records.

Make a new plan, Stan.

or even use an old one… just HAVE a plan, and STICK to it.
A few thoughts for the next time we make a brand new database:
We should set some strict naming and formatting conventions so that all the names used for tables, field, layouts and scripts use the same rules, allowing us to construct references based on variables and knowledge of our naming conventions. I suppose there still remains the problem of what happens if we change some names… but maybe we could use one login to set up names of stuff and other admin logins to do all other work – and somehow disable the permission to rename stuff… ? I don’t know. It was a thought inspired by what I’ve been working on recently. It seems that a lot of what I’ve been doing the past few weeks has involved going through several instances of parts of the database that are built SIMILARLY but not consistently, and therefore I have had to do a lot of double-checks and fixes. We might save ourselves some time if we structure things a little more strictly from the start and try not the make changes to our naming conventions part way through the game. Part of the challenge here is that there are several of us working on the database and we don’t have a central reference for the way we are going to do things. I suppose that might be a good first layout to make – a reference with naming conventions and font choices, etc. David has talked about that. It’s a good idea… but it’s less practical to implement half-way through creating the database than it is at the very start.


It’s funny how things I’ve studied thoroughly do not necessarily register themselves in my brain as useful knowledge until I’ve made use of them.
In working with Filemaker, and learning it in a somewhat haphazard way, it’s cool to observe how I learn. First off, I started by reading many books and stuff on Filemaker –  some of them I started at the beginning and worked my way through most of – others I read a chapter here or there, trying to figure out how to do stuff as I came across the problem. Anyway, then I started working here and really began applying what I’ve learned. It’s really cool when (as just happened a few minutes ago), David comes across something I’ve done and immediately notes a very elementary mistake I’ve made. I mean, I suppose mistake may not be the right word – just where I’ve done something in a silly way that one would expect me to know how to have done the correct way. And as soon as he mentioned the correct way (in this case doing a Cartesian Join of two tables), I remembered having read about that and thinking ‘mm-hmm. mm-hmm. k. got it.’ a long time ago, but since I didn’t use that information or explore it’s uses way back when, I never REALLY learned it. Anyway, it’s cool to actually learn something that you assumed you already knew!
This is also applicable with my knowledge of PHP & MySQL. Wendy and I are studying it from a great, hands-on book (called Head First PHP & MySQL by O’Reilly Publishing – the Head First series is SUPERB for learning technology stuff!), however, I feel I’ve learned just as much from poking around php files in WordPress and looking at other developers’ code and blog articles, trying to customize a website that I’m working on.
Moral of the story: While I love reading manuals and books and learning every little detail about stuff in an orderly fashion, the most useful way to learn practical things is to just try to do them and figure out the details as they arise.

Migrating Word Press

We’ve got a few websites based on WordPress. They’re little sites and have been hosted with GoDaddy since launch. GoDaddy is cheap, cheap, cheap, and good enough for the sites we’ve had up. But we want to drive more traffic to those sites, take hosting control of a couple other more complicated sites, and get out from GoDaddy’s limitations.

We settled on a hosting reseller account through HostGator. That gives us room to expand and freedom to configure the server as we want.

This was fairly easy. We logged into cPanel and then followed the directions in this video here. The video is great and there’s no need to re-say here what they say there. Basically it shows how to set up ‘packages’, which determine how much space and bandwidth a particular site will get.

Then you can ‘Add a New Account’ in cPanel where you tell it the domain name and choose a package for its settings.

After we had the account for the new set up, we followed HostGator’s directions for moving a WordPress blog. (See here ).

Their directions are pretty good. We backed up the database, downloaded and uploaded the WP files, changed wp-config.php and imported the blog as directed.

Ta-dah! We now could go to our new IP address and see the site. However, the css wasn’t taking effect and the links all went back to our old site.

At the end of their directions HostGator has a little section on this very problem. They say you should log-in to wp-admin for your site and in the General Settings change the site url and home to the new address. We did this and then realized because we hadn’t changed the DNS name servers yet, we now had a loop happening which didn’t allow us to log back into wp-admin! In effect, we changed the old database instead of the new database. Now the new database was telling it to go to the old url and the old database was telling it to go to the new url! To fix this, we followed HostGator’s directions here. This meant logging into phpMyadmin for both databases and changing the url and home variables in both places.

Then we logged into the the new wp-admin and changed the links from ‘pretty’ links to the default setting.

Now we had wp-admin’s accessible for both versions of the site and the links were working, but our css still wasn’t taking effect. We looked at the page source and it was telling it the right link to the file. We tried uploading a new image and placing it in a new post. The wp-admin could find the photo just fine, but the page wouldn’t display it.

Finally, we thought to look at the Error log in cPanel. It told us over and over ” (13)Permission denied: /home/public_html/wp-content/uploads/.htaccess pcfg_openfile: unable to check htaccess file, ensure it is readable” for this files and various other ones in the wp-content file.

After doing some web searching, we read of other accounts of there being problems with FrontPage, a WYSIWYG editor. We uninstalled it. CSS still wasn’t taking effect. Then we took a look at the permissions on the wp-content file. On the actual file they were fine, but the lower files with the themes, uploads, etc, were set way too low. So we changed the entire contents of the wp-content folder to 755 and all of its files to 644.

Now the CSS is working!

Magento Shipping with Matrix Rate

The client is running Magento 1.1.8.
Magento was installed and configured a couple years ago by another company. The client wants to change the way shipping is calculated.
There are three types of shipping each with a single fixed price:, Ground ($12), 2nd Day Air ($35) and 2nd Day Air Oversized ($65). Samples of the corresponding products are  jewelry, small pottery, and large pottery.
Products are stored in an external FileMaker Pro database. Every two weeks or so, the user exports a list of products from FMP to a csv. The csv is uploaded to the site by a custom PHP page. The PHP converts the csv into XML. The user then goes to the Magento backend and selects the XML through System–>Import/Export–>Profiles–>Import All Products–>Run Profile.
It’s unclear how the system is determining the shipping rates, but here’s what I believe. There is an extension, Matrix Rates, installed which can be accessed by System–>Configuration–>Shipping Methods–>Matrix Rates. The extension is the free version of a well-supported pay version that costs about $200. The extension works by selecting the Main Website as the scope and uploading a csv formatted to tell the extension how to handle shipping rates.
I think the previous web admin upload a csv telling Matrix Rates to look at product weights to determine the available shipping rates. During the product upload, the shipping type column in the csv is converted to a weight. In pounds, Ground becomes .5, 2nd Day Air becomes 1, and 2nd Day Oversized becomes 5.
The Problem we have to solve is that when two items at “.5” are purchased, the only option is 2nd Day Air. The client would like the system to allow multiple small items to be shipped at the ground rate.
Problem Zero: Is my belief about the origin of the problem correct? Is the weight of products adding up and triggering the shipping options?
I tested by changing the weights of products and seeing what the shipping options are. Doing so confirms that’s what the problem is.

Possible solutions:
A) Reconfigure Matrix Rates.
B) Change product weights to allow multiple products.
C) A & B

Update to follow…


We are trying to move our website to a new hosting company (godaddy is SLOW and not very easy to use – and, as we just found out, VERY RESTRICTIVE). We researched a bunch online and found that a good way to move a wordpress site safely (and with little or no downtime) is to direct the wordpress site on the old server to a copy of the database hosted on the new server, then to move all the files over and finally to change the DNS addresses. SO. Here goes:

We exported a copy of our database that was on the old server (via phpMyAdmin), created a new database on our new server and imported the copy of our database. Then we opened the wp-config.php file on our old server and updated the database server, username and password info. Then we saved and refreshed and wa-BAM! nothing. Now when we went to our old site and clicked any blog posts we were informed that there was an error connecting to the database. Great.

So I wrote a little php file to test our connection to the database. I first uploaded it to the new server and tested its local connection – worked. Then I modified the script and put it on our old server (godaddy) and for three hours, no matter WHAT we tried for the database name and connection to the database server and several other settings – we got errors connecting to the mysql database. (Slightly different than the entry-through-wordpress errors, but errors all the same). After those three hours of research and testing and working and wondering and feeling frustrated, David thought to check and see if the problem could be godaddy itself not allowing connections from within its servers to databases outside of its own servers – and he found the answer: GO DADDY DOES NOT ALLOW CONNECTIONS TO DATABASES ON REMOTE SERVERS – EVEN IF YOU HAVE ALL THE SETTINGS ON THE TARGET SERVER SET CORRECTLY TO ALLOW INCOMING CONNECTIONS!!!

Then again, no.

Awww, scratch that last post, as yet another thing has gone wrong with this horrific ‘easy’billing database. Now the invoice numbers aren’t updating – we had this problem before but the solution was ?? – it just fixed itself magically, as far was we could tell. I strongly dislike old databases. My advice to all: keep things up-to-date. Newer things are better when they need to work nicely with other things. Granted, books, pants and shirts are better second-hand, broken-in a bit, but other than that you’re just askin’ for trouble. BAH!!!
To be continued…

CONTINUED: Okay, turns out, same problem as before, same solution. I simply had to tell the database where to find it’s other table. Since ‘billing’ and ‘patients’ are different files in the EASYBILLING database solution, it turns out I had to tell each separately how to find the tables it needed. Bah.

FINAL (??? let’s hope!) UPDATE:
Went back to the client’s office yet again yesterday to fix more printer issues – after unsuccessfully trying to install an ancient Epson printer to work with his antique Mac, we resorted to a (GASP!) new printer!!! I had to first find one that loaded paper vertically (the only way these forms seem to go through the paper feed properly) AND worked with his antique iMac. We again went with an Epson – not typically my top choice in printer brands but it appeared to serve our needs. Got the printer at Staples and installed it. Opened database, tested printing, worked. DONE. Drove home through snowstorm (practically).

A Solution So Simple It Could Never Work…

Again, working on the EasyBilling database on a Mac running 10.4, Filemaker Pro v. 5.5…

This time, when our client tried to create a new patient, he got an error message, a document navigation window and then a blank record where he was unable to enter a patient account number.
I watched him go through the fruitless motions of trying to create a new patient and then repeated the process myself, except when it lead me to the document navigation window to locate the document that the database claimed it could not find, I took a stab at finding it. Problem solved!

That is, until you close the database and reopen it – then it no longer knows where it’s files are. After trying to “teach” this database where its own corresponding file was located (by locating the file for it several times in several different ways), I thought to myself, “Fine, database. Obviously, I cannot teach you to fish. I suppose I’ll just have to bring you a pile of permafish so you’ll never go hungry again.” The file that it could not find was in a folder that enclosed the folder that the main database file was in. Does that make sense? Probably not.

Explanation: First off, Filemaker 5.5 is before you could have more than one table in a single file (or maybe Filemaker 5.5 could handle that, but this vertical market billing solution was designed in a version of Filemaker that separates each table into its own file) – so each table in this billing solution is its own file. That’s cool, until one of it’s files is moved – and then it freaks out (understandably – I would freak out if you moved my arm to a separate location!). You see, when we moved these files from the ancient computer to the antique computer a few weeks ago, they somehow came to be in a bit of disarray (I guess…). The EASYBILL.FP5 file was in a folder called EASY BILLING 3.5.7, in which there was another folder called EASY BILLING 3.5.7 which contained the rest of the database files. Which means the main database file was looking for its arm inside its house (folder), when really the arm was sitting just outside the house. You could point it to the right place, but as soon as you closed the database it would forget where you had told it to find its arm.

So… after the database proved itself dumb, I (figuring it would never work, that the solution could not POSSIBLY be this simple, but having no other ideas left to try) moved a copy of the EASYBILL.FP5 file into the same folder as the rest of the database files. And…. (drum-roll, please) VOILA! Magic! I restarted the computer, reopened the main file, clicked on the create new patient button and the database was able to find its arm / pile of permafish – all by itself – no learning necessary! Phew.

Rational descision making

An excellent list of how to rationally achieve a goal rationally…

1) Ask ourselves what we’re trying to achieve.
2) Ask ourselves how we could tell if we achieved it (“what does it look like to be a good comedian?”) and how we can track progress.
3) Find ourselves strongly, intrinsically curious about information that would help us achieve our goal.
4) Gather that information (e.g., by asking as how folks commonly achieve our goal, or similar goals, or by tallying which strategies have and haven’t worked for us in the past).
5) Systematically test many different conjectures for how to achieve the goals, including methods that aren’t habitual for us, while tracking which ones do and don’t work.
6) Focus most of the energy that *isn’t* going into systematic exploration, on the methods that work best.
7) Make sure that our “goal” is really our goal, that we coherently want it and are not constrained by fears or by uncertainty as to whether it is worth the effort, and that we have thought through any questions and decisions in advance so they won’t continually sap our energies.
8) Use environmental cues and social contexts to bolster our motivation, so we can keep working effectively in the face of intermittent frustrations, or temptations based in hyperbolic discounting.