Previously on this
MacNN project, we realized we had a problem, and that a
database would be the solution. To our mind, that means a FileMaker Pro solution. It means FileMaker in part because we've used that application in one-man companies and international media ones; it also means that because we've learned how flexible it is.
However, we also learned that
preparation is everything, and for the first time in our use of FileMaker Pro, we've done it properly; we've planned. Finally, though, we have that plan,
we know what our database needs to do, and we even know what it will look like. What we don't know yet is how to tell FileMaker Pro what we want.
So, at last, it is time to launch FileMaker Pro 14 and choose File/New Solution from the menu. If you've dabbled in FileMaker Pro before, we're sure it used to be File/New Database. We're also sure that we used to go straight through to a screen called Manage Database that is so familiar to us that it's featured in our dreams. Our dullest dreams, to be fair.
Now the first thing you see when you start a new database is a simpler way to start adding fields, but indulge us: click on the icon of a barrel with a cog on it. The barrel part is a standard icon for a database, and the cog is for settings. This takes you through to what we'd call home.
Tell it what you want
We made all these plans that said a Job Book that records all the commissions we get must have a place for us to write the name of the job, a deadline, who's hired us, what they're paying, and a lot more. These are the fields that will be in every record: they're the bits of information that every job must record. Turn that into a list, and give each a clear and distinct name.
We figured the name of the job should really be a description: rather than "Article for Acme News Co," we would always be writing "Write 10-step guide on how to get rich quickly." So we named this field Job_Description.
Actually, we did slightly more: we called it DR_Job_Description. The underscores just make the names easy to read; they're not essential, and you can have ordinary spaces, but that's comparatively new. We realized doing this just how much we'd forgotten about databases, but some habits are engrained -- so we automatically used underscores. The DR part is just that the jobs this database will record are for a firm of ours called Dark Ride. If we were doing this for lots of companies, we wouldn't limit it like this, but since it's one of our many databases, we do mark each one so we always know what it's for.
As we'd planned this, and effectively practiced using a pretend database for a while, we came up with quite a long list of fields that each record could possibly need. In that image above, of all the fields for this Job Book, notice that two toward the bottom have spaces in the name. They were temporary solutions we were trying to get to work, but so far haven't. They're totals of invoice amounts, and expenses across multiple currencies: our head hurt. We should delete those, but we're not using them in the database now, and later when we're happy to expand it, we'll come back and rename these entries.
You'll also have noticed that alongside each of the unexpectedly many field names, there are two columns: a central one saying things like Text or Number, then a much more involved Options/Comments one. Most of the work you'll do in a database like this is simple. You'll have a bit where you want to enter how much you're being paid, and that is going to be a number. It won't ever be text. If someone offers to pay you "oodles," ask them to put a dollar figure on that before you do anything. So, telling FileMaker Pro that this DR_Job_Fee field is a Number one means that later, you will never be able to accidentally enter anything but numbers.
Slightly more involved are the few fields here that are labelled Date. Look at DR_Job_Commission_Date: that is the date that we were hired to do a job. There's also a deadline date, and these two are both marked as being Creation Date. If we do nothing else, if we don't change anything, then when we say we've got a new job to do, FileMaker will list the commission date and the deadline date as today. It's rare that a deadline is the same day, and it's often that you don't get around to entering the new job details until after a few days, so both of these can be changed -- but today is the default.
More complicated example
We've said all along that we want a proper grown-up job number to be automatically generated for us. It's so we can sound great when we chase a payment and say "yes, it's for job number 123, invoice issued on the 9th, and if you don't pay us today we're sending the big boys around." Databases are great for numbers, and even better for automatically doing them, so we went to town. Rather than just an ordinary number like 123, we wanted DR160123. That's DR, you'll never guess what it stands for, plus 16 for the year 2016, and then a straight count of jobs.
Only, we don't want to have to lift a finger on this, and we don't want to redo the entire database next year. So look at that DR_Job_Number and how it says next to it: Calculation, Indexed, from Dark Ride Job Book, = DR_Job_Text & DR_Job_Year & DR_Job_Number_Digits. The underscores and the ampersands make that look complicated, but it's really saying that the number is made up of several elements in a row. "DR" is text so that's DR_Job_Text. The year is next, then there's just the number.
We don't want "2016", we want just "16." Similarly, we want the year, we don't want "1 January 2016." So DR_Job_Year is also a Calculation: it takes the creation date, today's date, then lops off everything but the last two digits. Next, the job number itself, the digits are a serial number that automatically goes up by 1 each time we enter a new job. So DR_Job_Number_Digits is a Number, and it's a Auto-Enter Serial one.
At what point did we just lose you? Reading this stuff is never the same as doing it, so if you're brand new to FileMaker Pro then you've glazed over seeing only that it seems very detailed. If you're a FileMaker Pro expert, then you're shaking your head at how much faster we could've done all of this. It's okay: you'll shake your head even more when you see how poor our layout design skills are tomorrow.
One thing to take away is that this what a database can do: it can take information, it can create information -- like today's date --and slice it up, switch it around, combine it and process to become something useful. The other thing to take away is it may have been difficult to figure this out, but we will never have to even blink again. From now on, forever, whenever we click a button to say we've got a new job to do, FileMaker Pro will tell us that it's job number DR160079. Until 2017, when the year digits will change automatically for jobs in that year.
It will tell us all that on what's called a layout. It's a form you create to enter the details of a new job, or to show you the details of an old one. We'll do a little more with fields and records first, but then the rest of tomorrow is
about creating this layout, it's about the visual look of your database. Since you ask, yes, writing that article is job number DR160080.
-- William Gallagher (
@WGallagher)
Top