Portage Bay Solutions - Bloghttp://www.portagebay.com/blog/Tue, 17 Jun 2014 20:35:34 +0000copyright Portage Bay Solutionsen-USSquarespace V5 Site Server v5.13.281 (http://www.squarespace.com)FileMaker Finds, Part 1FileMakerFindsSearchesTips & TricksPortage Bay SolutionsTue, 17 Jun 2014 00:43:48 +0000http://www.portagebay.com/blog/2014/6/16/filemaker-finds-part-1.html697931:16387101:34875428A database application provides many features, but a primary purpose is always the ability to locate whatever data you need at any particular moment. FileMaker excels in providing tools to specify very powerful search criteria, but many users never try them out, restricting their searches to the simplest kind and manually whittling away irrelevant records. However, if you take a little time to become familiar with FileMaker's built-in search tools you may find that in many cases you can get exactly the records you are interested in and no others.

Software developers know an open secret about computers: They're very stupid. I don't mean they aren't powerful nor that we aren't able to accomplish amazing tasks with them, but all of the intelligence given to computers is imparted to them by programmers and users. While you can tell another person, "Go through all of the invoices for the past month and pull out those from customers in Washington where the invoice amount is at least $1,000 or less than $50 but have not been paid," and the person will be able to do so with no further instruction, computers just aren't intelligent enough (yet) to understand what you mean. So for the time being, much of being efficient with these powerful machines is the skill of translating what we want the computer to do into what it understands. In fact, this is almost the definition of a software developer. But even if you don't earn your living writing software, knowing the details of your daily tools, such as FileMaker, will enable you to be much more productive.

Throughout this article we'll be using a very simple FileMaker database that tracks authors and books. If you plan to try the search techniques covered here I recommend downloading this sample and beginning there. This article covers the search tools that are built into FileMaker, but developers can, and often do, override those built in tools. Unless you are working with a system you built yourself, there's no guarantee that FileMaker's built-in search will be available to you as described here.

Found Sets and Current Record

Before describing FileMaker's search features, we should cover the concepts of found set and current record, which are integral to understanding how search works. The found set of records are the records that you have immediate access to. This could be all records, a subset of all records or, in some circumstances, even no records. The current record is the record that you are currently working with. This could mean that you're just perusing the data in a record or actually editing it.

A classic analog to digital databases is a recipe card system. If you open the card box and pull out one recipe, your found set and your current record are both that one card. If you pull them all out, then your found set is all of the cards. And if you notice that you have some ground beef in the fridge and therefore sift through all of the recipes, pulling out only those that include ground beef as an ingredient, and put the rest back in the box, your found set includes those that you've retained; the current record changed as you looked at each card. You select one recipe from that batch and put the rest back, returning to a found set of one, then cook that recipe and put the card away, ending with a found set of zero records.

FileMaker works very similarly. While 'found sets' are applied in many different areas of FileMaker, whenever you perform a search using FileMaker's search tools, you'll end up with a found set of records that conform to the criteria you've specified. FileMaker provides a number of indications about the state of the found set and the current record, but the easiest to take note of is in the toolbar on the far left.

Let's take a look at some examples. Throughout this article we'll work with a very simple database that tracks authors and books. In Figure 1 below, you can see our list of authors. There are nine in the database and at this point they are all in the found set. In other words, all of the records are currently being shown.

The slider and the records report at the left of the toolbar indicate the current record and the found set status. First of all, note the gray circle with the number '9' and the words 'Total (Unsorted)' next to it. The fact that the circle is gray and we see the word 'Total' is an indication that our found set includes all of the records in our authors database. And the '2' that appears above the slider indicates that we are on record 2 of 9 within that found set. You can also see this in the list with the darker blue highlight on the second record.

We can change the found set in many ways, but the easiest is to perform a quick search. If we search for 'william' in the 'first_name' field, we get a found set of two records, as shown in Figure 2 below. Three changes in the toolbar indicate that we're no longer working with all of the records. First, the gray circle has changed to two shades of green representing a pie chart of found versus all records. To the right of this little pie chart, we see '2/9' indicating that our found set contains two records out of 9 within the database. Finally, below this fraction are the words 'Found (Unsorted)' instead of 'Total (Unsorted).'

Of those two records the first one is the current record, as indicated by the number 1 above the slider and by the fact that the first record in the list is highlighted with the darker blue.

One last fact of note before moving on from found sets: When your found set includes anything other than all of the records, you can press 'Cmd/Ctrl-J' (i.e., 'Cmd-J' on Macs and 'Ctrl-J' on Windows) or choose 'Records>Show All Records' to return to a found set that includes everything.

The Simplest Find

Most FileMaker users are familiar with the simplest find. You enter Find Mode either by pressing 'Cmd/Ctrl-F', selecting 'Find Mode' from the View menu or by selecting 'Find' from the menu at the bottom of the window (which will usually say 'Browse'). Once in Find Mode, you tab to or click into the field you wish to search on, enter your search criterion and either press 'Return', click 'Perform Find' in the the toolbar, or choose 'Requests>Perform Find' from the menu bar. FileMaker then provides a found set of records that have whatever you entered in the field you entered it into.

For simple finds, where you're searching a single field for a single piece of information, this works fine. Even if your search is a bit general, by navigating through the found set (by using the flipbook in the toolbar or by pressing 'Ctrl-Up' or 'Ctrl-Down', you can soon get to the record you want. This is exactly what I did above when I wanted to find the author records with 'william' in the first name.

Boolean (And, Or, Not) Searches

George Boole was a 19th century mathematician who invented a system called Boolean logic that programmers use to test complex conditions. If I use the invoice example given above, this could be translated into a boolean test:

Include all invoices that: > have a date greater than or equal to 30 days ago > 'AND' have a date less than or equal to today > 'AND' have a customer state of Washington > 'AND' have an amount less than or equal to $50 'OR' have an amount greater than or equal to $1,000 > 'AND' have 'NOT' been paid

All of the basic boolean operators are being used here, 'AND', 'OR' and 'NOT', and we'll cover how to use each of their equivalents in FileMaker.

The first method for specifying more advanced search criteria will be to search for records that satisfy multiple criteria. For example, if we want to find all of the science fiction books by Isaac Asimov, we simply enter Find Mode and place 'science fiction' in the genre field and 'Isaac Asimov' in the author field, as in Figure 3 below.

This search says, "Show me all of the book records where 'science fiction' is in the genre field and 'isaac asimov' is in the author field."

In a similar vein, you can say something like, "Show me all of the book records where the genre field is either 'fantasy' or 'science fiction'," and as you might guess, whereas the first is called an 'AND' search, this one is called an 'OR' search. To perform an 'OR' search, you need to create multiple find requests. Just as you can create multiple records by pressing 'Cmd/Ctrl-N' while in Browse Mode, pressing 'Cmd/Ctrl-N' in Find Mode creates a new find request. One such request is shown in Figure 4 below. This was created by performing these steps:

Multiple Routes

Many operations in FileMaker can be accomplished with multiple methods, and boolean searches are no exception. When in Find Mode, the Requests menu offers two menu items to assist with boolean searches: 'Constrain Found Set' and 'Extend Found Set'. 'Constrain Found Set' operates exactly like an AND search, but the operations are performed in multiple steps instead of one. If we want to duplicate the search we performed that found Isaac Asimov's science fiction works, we could have done the following:

Note that steps 2 and 5 could have been swapped with no change in the end result.

Similarly, using 'Extend Found Set' could be used to duplicate our search for fantasy or science fiction novels:

1. Enter Find Mode 2. Type 'fantasy' in the genre field 3. Press 'Return' to execute the find first find 4. Enter Find Mode again 5. Type 'science fiction' in the genre field 6. Choose 'Requests>Extend Found Set' from the menu bar.

Again, steps 2 and 5 can be swapped without altering the end result.

The difference here is only one of how you think of the search or perhaps in what order the search criteria come to mind. The first time we performed an AND search; you can think of this as saying, "Perform a search that matches both of these criteria," while in the second case, it's more like saying, "Find the records that satisfy this first criterion and then search those records for those that satisfy the second crition."

Translating the OR searches methods, the first is saying, "Show me the records that match either this criterion or this other criterion," while the second one says "Find the records that satisfy this first criterion and then search all of the records for this second criterion and add any you find to the first found set."

Reversing the Logic

Sometimes it's easier to define the records you want by what they're *not* rather than what they are. Perhaps you want to read something. You don't know what, but you know you're not in the mood for a tragedy, so you'd like to see all of the records that have a genre of anything *other* than Tragedy.

Given what you've seen so far, defining such a search would require that you know what every possible genre is, but FileMaker provides two other methods. The first is direct and immediately shows you the records you're interested in, and the second first finds what you're not interested in and then allows you to reverse the found set. To directly show all records that don't satisfy some criterion, you use the 'Omit' button that's available in Find Mode, so we could show all non-tragic books with the following steps:

To use the second method, perform a find you normally would (i.e., by finding all of the tragic books) and then choose 'Records>Show Omitted Only' from the menu bar. This basically reverses the found set of records. For example, there are 18 books in my sample database, and 2 of them are tragedies, so finding the tragedies gives me a found set of 2 records. If I then select 'Records>Show Omitted Only' from the menu bar the found set will include the 16 records that are *not* currently part of my found set.

Take note here of a slight inconsistency in FileMaker. I said earlier that creating new find requests is the equivalent of creating an OR search, but using the 'Omit' button is an exception. As you'll see in the examples below, if you create a new find request and use the omit option, it's equivalent to saying "Find records that match the first request and exclude those that match the second."

Complex Examples

While there's more to come with the next installment of FileMaker Finds, in order to highlight how much power just the addition of boolean searches can add to your FileMaker use, let's try a few complex examples that combine AND and OR and NOT (omit) searches. I personally prefer direct searches to using the constrain and extend capabilities, so that's what will be shown in these examples. If you'd like to test your own understanding of boolean searches, try duplicating these direct searches using FileMaker's constrain and extend options. All of these examples assume that we're starting out in Find Mode and end by executing the find, so I'll omit those steps.

First, how about all books that have the word 'the' in the title that were written by an author with a name (any name) that begins with either 'c' or 'g'.

1. Type 'the' in the title field2. Type 'c' in the author field3. Press 'Cmd/Ctrl-N' to create a new request4. Type 'the' in the title field5. Type 'g' in the author field

Something more complex. Let's have FileMaker show us all of the books by an author named "william" that aren't comedies.

1. Type 'william' in the author field 2. Create a new request 3. Type 'comedy' in the genre field 4. Click the 'Omit' button

This is the example of a new request with the omit option providing AND logic instead of OR logic.

Finally, let's just go crazy with an obviously contrived example. Let's tell FileMaker to find all the books that:

- were published in 1600 - are neither a novel nor science fiction - by an author with "william" or the letter "g" in the name

We'll use one last new feature here, the ability to duplicate find requests, just like duplicating records.

1. Enter '1600' in the published field 2. Enter 'william' in the author field 3. Duplicate the request by pressing 'Cmd/Ctrl-D' or choosing 'Requests>Duplicate Request' from the menu bar 4. Change the author field from 'william' to 'g' 5. Create a new find request 6. Enter 'novel' in the genre field 7. Click the 'Omit' button 8. Create a new find request 9. Enter 'science fiction' in the genre field 10. Click the 'Omit' button

Just The Beginning

If you've never used them before, boolean searches, either directly or with Constrain and Extend Found Set, will significantly extend your control over your data. And it's possible that all your searching needs can be solved by boolean searches, but perhaps not, and next time we'll cover the additional power in FileMaker searches by exploring the find operators.~Chuck Ross

I spent the last four days in Washington DC. This was my 5th or 6th visit to DC, a city that I really enjoy visiting. You could spend months there and never see the same thing twice, and you would learn a ton.

On this visit I took in two sites I’d never been to before, the National Archives and the Library of Congress. Of course, seeing the declaration and constitution was great, but I was really impressed with the library of congress. The roman renaissance interior is pretty amazing.

Unfortunately, much of the library is not accessible to the general public, unless you go through the trouble to get a User card. We were going to go to the main reading room to work for a few hours, but when we got to the library it turned out that you had to go to the Madison building several blocks away to get the card. We ended up deciding not to do that on this trip, but maybe next time.

So if you want to visit the library of congress, including all of the stacks and the main reading room, consider stopping by the Madison building first and getting a Library of Congress User card.

--John Newhoff

]]>http://www.portagebay.com/blog/rss-comments-entry-33686114.xmlSuper Hi Res MonitorFileMakerFileMakerNew TechnologyTech reviewTechyPortage Bay SolutionsWed, 24 Apr 2013 00:50:00 +0000http://www.portagebay.com/blog/2013/4/23/super-hi-res-monitor.html697931:16387101:33427198I spent a rather dumb amount of time recently trying to get my MacBook Pro to display more than 1920x1440 on my external 27" monitor. At that resolution, I'm just not taking very good advantage of the size of the monitor. In FileMaker (and other apps) having the higher resolution allows you to deal with multiple windows and palette windows more effectively.

It turned out that my monitor simply wasn't capable of displaying anything higher than that resolution.

I decided to get rid of my old monitor and purchased an Asus monitor capable of displaying 2560x1440. It was a little expensive, but now I'm really enjoying the additional screen real estate, particularly in FileMaker when I'm using the debugger and data viewer.

-- John Newhoff

]]>http://www.portagebay.com/blog/rss-comments-entry-33427198.xmlLinked Web Browser Page in FileMaker Web ViewersFileMakerFileMaker 12TechyWeb ViewerPortage Bay SolutionsMon, 15 Apr 2013 17:38:18 +0000http://www.portagebay.com/blog/2013/4/15/linked-web-browser-page-in-filemaker-web-viewers.html697931:16387101:33389648Web viewers extend the capabilities of FileMaker by letting your database interact with the web. Recently we added a simple web viewer to E-Com, our special education software for FileMaker, that displays a web page with recent news and events. We wanted the users to be able to click on a link within the web viewer and see the linked page in a window in their web browser outside of FileMaker. It sounds simple, but turned out to be a 'gotcha' because FileMaker's default behavior is that all interaction with the web page happen within the web viewer in FileMaker. There is no preference to open links in a new windows.

The simple fix to this issue is to use a bit of HTML, using the 'target="_blank”' syntax to allow the link to open up in a new window.

In the demo file, we have a web viewer object set up that will perform a search for the given product in different search engines.

The web viewer is using the 'target=“_blank”'syntax so that clicking on each link will open up a new browser window, leaving the web viewer object intact.

]]>http://www.portagebay.com/blog/rss-comments-entry-33389648.xmlThe Drive to Ke’e BeachDon HoHikingKalalauKaua'iPersonalRoosterTravelPortage Bay SolutionsTue, 26 Mar 2013 20:55:15 +0000http://www.portagebay.com/blog/2013/3/26/the-drive-to-kee-beach.html697931:16387101:33154218Your correspondent, coming to you from the back of a minivan ( a grand caravan to be precise), reports to you on a drive from Poipu beach to Ke'e beach on the island of Kaua’i. Yes, I'm on Kaua’i for 10 days, apologies to those of you suffering from the recent snow in Seattle.

I've never been to Kaua’i, so this 1.5 hr drive is my first chance to see much of the island. Friends I'm with have been coming here since they were 6, so the trip is full of exclamations of 'hey, I stayed in that condo there when I was twelve' or 'look, that's where my sister Adrienne was stung by a jellyfish'.

In Kapaa now and we’re driving by the remains of the Cocoa Palms resort, destroyed by hurricane Iniki in 1992 and not restored or torn down to this day. It’s a very odd scene as you drive by.

Leaving Kapaa, the sun is starting to reflect on the mountains to the east and the mountain range called 'sleeping giant'. Quite beautiful.

Jurassic Park was filmed partially on Kaua’i, and so traveling around the island is in some ways a tour of of the film. We did a tough 10 mile hike out to the Na Pali coast from Koke’e state park yesterday where it turns it some of the first scenes in the first movie were filmed. Yesterday we drove by a very large meadow where the scenes of the herds of dinosaurs running were filmed, and just now we passed a turnoff to the house that was used for the park visitors center. None of this was intentional.

We just passed a big rooster on the side of the road. Kaua’i is literally overrun with chickens that have escaped into the wild or descended from original wild Polynesian stock. We even saw some miles into the Na Pali coast backcountry.

Passing through the countryside near Kilauea (of lighthouse fame I'm told), the van has broken out into a Don Ho song, a somewhat frequent occurrence. This time, it was 'Beautiful Kaua’i '. More chickens on the roadside.

I've been in Kaua’i for five days now and I still haven't had a shaved ice.

Now driving out of Princeville and a very pretty view down into fields of Taro and other crops growing in Hanalei Valley.

Rounding the corner to our first view of Hanalei Bay, the car breaks out into song again. This time its 'Bali Hai' from the musical South Pacific, which was of course filmed right here in Hanalei Bay. So were scenes from the recent George Clooney film, The Descendants. We drove along the shore road and found the beach cottage where they filmed the scene where George Clooney's character confronts his nemesis.

Past Hanalei Bay the road narrows quickly and becomes twisty and turny and in a short time we're at Ke'e beach and our destination- the trailhead for the Kalalau trail. Ahead: 8 mile round trip hike up the Na Pali coast to the Hankapiai Falls.

We’ve recently been working on a project for a company in Seattle that you'll all be familiar with -- Space Needle. Yes, it's a company, as well as a large pointy building. Space Needle runs the eponymously named structure, as well as the Chihuly Garden of Glass nearby.

Working with the folks at Space Needle, we are developing an iPad FileMaker Go application for their managers to use to document the status of the grounds, staffing, service, etc., so that they can have objective information about their overall quality of service over time.

One of the pieces of data to be gathered is the weather conditions each day. In order to standardize how this data is gathered, we decided to try and obtain it from an internet source.

A little research at the Programmable Web turned up Weather Underground as a good-looking option. They have an extensive API for obtaining weather data, and one of their weather reporting stations is only a few blocks away from the Space Needle.

Utilizing the Insert From URL script step introduced in FileMaker 12, we created a button that allows the manager to insert the current weather. The data returned from the Weather Underground API is in JSON, which is fairly easily parsed. The API returns a lot of data, from which we parsed out the sky conditions and the high/low temps.

Upon further discussion with our client, it turned out that a button to capture the current weather was not necessarily ideal, since the weather can vary considerably through the day. We looked back at the data available from Weather Underground, and in addition to current conditions, they have an API call for getting weather history, so we may choose now to standardize on capturing the daily weather observation at a consistent time of day, rather than whenever the manager happens to click the weather button.

The Insert From URL script step is a powerful new feature in FileMaker Pro 12.

]]>http://www.portagebay.com/blog/rss-comments-entry-32833469.xmlMSF 2012NewsPersonalTechyPortage Bay SolutionsSat, 02 Feb 2013 00:01:10 +0000http://www.portagebay.com/blog/2013/2/1/msf-2012.html697931:16387101:32739996Technology as a way to get kids hooked on science

I hope that everyone is enjoying the new year! This is Rin, reporting in again after some more education-based travels. Last November I had the privilege of participating in another NASA international collaborative event. We traveled to Melaka, Malaysia for the second Melaka Planetarium Space Festival. The Melaka Planetarium is run by a small but ambitious group of passionate educators. The Planetarium sponsored nine NASA scientists and educators to bring workshops, demonstrations and activities aiming to encourage interest in space and earth sciences among elementary- and middle-schoolers.

This time, as at previous space festivals, I witnessed some exciting applications of technology in the hands-on workshops. Paulo Younse of JPL brought a set of easily programmable robots, which students “taught” to navigate an obstacle course of a Martian landscape. Boys and girls of all ages find this activity exciting and challenging, and unwittingly learn the basics of programming.

Peter Falcon, also of JPL, brought a “magic planet” - a sphere-shaped screen that allowed students to visually see patterns and changes in a way that would be impossible out of a book. On this globe, students explored changing sea levels, being able to see the changes on a global level right before their eyes. They could also see temperature changes, cloud patterns, and many other aspects of earth science that are often difficult to grasp in the abstract. And it was beautiful! I know what I want for my birthday this year!

Another fantastic hands-on use of technology was arranged by Gabriel Alvarado from the SETI Institute. Mr. Alvarado wanted students and teachers alike to have access to real data and assist in actual scientific discovery. He brought this about by introducing festival-goers to PlanetHunters.org, a website where anyone can log on and view data from the Kepler mission, which searches for planets in other star systems. Users are trained in reading this data, and are then set loose to hunt for planets by looking for patterns in light output from far-away stars. Amateur astronomers all over the world examine the Keplar data, “tagging” stars that might have planets. When a star receives enough attention from amateur users, the Keplar scientists go in and examine the data as well. Just recently, a planet in a rare double binary star system was discovered in this way. The ability to access real data and participate in real science ignited the curiosity and excitement of many a festival-goer, child and adult alike.

Even with this astounding technology, the real motivators were the NASA team themselves. Every team member was overflowing with enthusiasm and energy, encouraging students to ask questions and find solutions to problems that they might not have ever considered before. I was honored to be included and hope to continue to work with this team in the future.

~Rin Scherrer

]]>http://www.portagebay.com/blog/rss-comments-entry-32739996.xmlJan/Feb NewsletterPortage Bay SolutionsFri, 11 Jan 2013 18:12:56 +0000http://www.portagebay.com/blog/2013/1/11/janfeb-newsletter.html697931:16387101:32529798The Jan/Feb newsletter is out. Kate talks about how easy it was to convert one of our large databases to FMv12 and what we plan to do with the expanded capabilities, and there's more about John's initial experience with Windows 8. There's a little bit about the new Execute SQL function in FileMaker v12, too, all with useful links. ]]>http://www.portagebay.com/blog/rss-comments-entry-32529798.xml