Brain Teaser - Solution
Alright, so nobody solved this brain teaser. I’m sure this had nothing to do with people having clients and lives, and everything to do with the original post getting hosed and the challenge being too darn hard anyway 
So here, at long last, is the solution:
mebeliLet (
[
NewSubSummary =( GetNthRecord ( MyTable::MyKeyField ; Get(RecordNumber)-1) <> MyTable::MyKeyField ); //Test if record is the start of a new subsummary. MyKeyField is the sort field for my subsummary part.$on = $val; // $on will always be whatever $val was last
$val = If (NewSubSummary; If ( $val = 1;”" ; 1) ) // switch the value of val
];
$on // if $on=1, then apply the conditional format)
[edit:] Or, in much simpler terms:
Let (
[
$b = If ($b ;”" ;1 )
];
$b
)
To me, the interesting thing here is the way that these variable expressions are handled by FileMaker. First of all, the conditional formatting seems to get evaluated, as one might expect, from top to bottom of the page, allowing you to use variables to keep track of formatting decisions that were made earlier on the page, or even on previous pages. It seems there should be some much cooler implications of this, like multiple title headers and pseudo-headers (don’t know what I mean by that? Neither do I…). The other interesting thing is that the variables here are local variables, but they’re kept active while the whole page is rendered by preview mode, which is what allows me to make reference to the last evaluation of $val and produce the alternating effect (yeah, I know I could just as easily use global variables, but it’s handy to know I don’t have to)
The reason that this gave me a checkerboard on the first try:
was because I had placed the formula on a repeating field, meaning that each conditional format formula was evaluated for each repetition, left-to-right. Once I placed the calc on a regular field, the checkerboard was gone and I had nice alternating rows.
Brain Teaser
I was working on a crosstab report to show grade distributions and I wanted alternating highlights. I was using Mikhai Edoshin’s snazzy technique (http://edoshin.skeletonkey.com/2006/12/crosstab_report.html),
which meant that each row was a subsummary part, which, at least in my copy of FileMaker, doesn’t support alternating fill. Like an idiot, I threw in a conditional format
————-
EDIT: This post got hosed somehow, and everything beyond the part where I refer to myself as an idiot was erased. I can’t remember what was idiotic about my original conditional formatting in this case (if I weren’t such an idiot, I’d probably remember). In any case, below is a reprise of the brain teaser:
———–
Each line in Edoshin’s crosstab technique is basically a repeating summary field based on a repeating summary calc. My goal was to get each one of these repeating fields to alternate its background fill. Using any kind of Mod-based calculation based on record number was going to fail, since these fields are on a subsummary, and there’s no way to predict how many records correspond to each subsummary part.
Taking a purely throw-rocks-at-it-until-it-stops-moving approach, I opened up the conditional formatting dialog on that repeating summary field, and hammered out a calculation. I was astounded, when I went back to preview mode, to see this:
My conditional formatting calc had inadvertently produced a checkerboard pattern. I realized, shortly thereafter, that by placing the same calc on a regular field, rather than a repeating field, I could get the alternating pattern as I desired:
The brain teaser challenge, before it got erased, was to figure out what sort of calculation could yield this effect on a subsummary part. The reward, if I recall correctly, was a signed copy of my memoir.
Google Mapping Part Deux (Multiple Addresses)
In my first post on this topic, I showed you how to create a simple, API-driven Google Map directly within your FileMaker Pro database. Today, I’ll be taking that one step further. In the previous example, you were limited to mapping one address at a time. In this example, I’ll show you how to produce maps with multiple addresses at once.
Step One
Step one, of course, is to read the original post. Familiarize yourself with the concepts. Is essence, we’re leveraging PHP to create a javascript page which displays the map in a FileMaker Web Viewer. In the original post, we used the Easy Google Map PHP class, and we’ll do the same here. You’ll also need a Google Maps API key if you wish to run this on your own server.
This example will be staged at http://etc.proofgroup.com/fmcollective/. The Easy Google Map PHP class will be hosted here http://etc.proofgroup.com/fmcollective/EasyGoogleMap.class.php. Neither of these links will prove very useful to you on their own, but they will be instrumental later when I build the actual application.
You will need a complementary PHP file to translate the FileMaker request (a GET request via HTTP) to the Easy Google Map class. This is where it gets just a little bit tricky. First off, how should we send multiple addresses via a GET HTTP request?
Perhaps something like this:
http://DOMAIN/MYPHPFILE.PHP?addr1=1st_Address&addr2=2nd_Address
But I can see all sorts of trouble with that, not the least of which is that it involves an ever-growing number of parameters (one for every address we want to map). It’s also not very PHP friendly. With some research and testing, I settled on using a PHP Array. PHP Arrays are very nice indeed. The only problem, how to create a PHP Array (a complex data structure) in FileMaker and then transmit it to the complementary PHP file? It turns out, PHP has a solution for that, too. It’s called a Serialized Array. According to the PHP manual, “This is useful for storing or passing PHP values around without losing their type and structure.” I like it!
Serialized arrays are strings which look something like this:
a:2:{i:0;s:3:"foo";i:1;s:3:"bar";}
That’s a 2-item array, the first item is “foo” (index 0) and the second item is “bar” (index 1).
Here’s my concept: serialize the data (manually or via custom function) in FileMaker, pass the string to the PHP, unserialize it there (turning it into a full-fledged array), and using the native PHP array to build the map data points via looping through the array items.
The Details
I mentioned a custom function above. I’ll need a custom function to translate a carriage-return separated list of data, such as this:
One
Two
Three
Into this:
a:3:{i:0;s:3:"One";i:1;s:3:"Two";i:2;s:5:"Three";}
I wrote a recursive custom function, called cfSerializeData, which is the following:
/*By: Mike Lee
Date: 4 May 2007
Version: 1
Fxn Name: cfSerializeData
Parameter: ‘values’ - a carriage-return delimited list of values that you want to turn into a PHP serialized array of data
Description: This is a recursive function that takes a list of data in ‘Values’ and turns it into a serialized array for use
within PHP (see serialize() and unserialize() in the PHP documentation). This can be useful for passing lists of data to
a PHP function–either through HTTP calls or the PHP plugin.Example Input: One¶Two¶Three
Example Result: a:3:{i:0;s:3:”One”;i:1;s:3:”Two”;i:2;s:5:”Three”;}*/
Let(
[
//Set variables
$NumItems = Case( IsEmpty( $NumItems ) or $NumItems=”0″ ; ValueCount( Values ) ; $NumItems );
i = ValueCount( Values ) - 1;
lastItem = RightValues(values;1);
lastItem = Left(lastItem;Length(lastItem)-1);
remainder = LeftValues(values;ValueCount(values)-1);
headertxt = “a:” & $NumItems & “:{”;
footertxt = Case(i+1=$NumItems;”}”)
]
;//If remainder is not empty recurse, else clear the variable and write the header txt
Case( not IsEmpty( remainder );cfSerializeData( remainder );Let( $NumItems = $null ; headertxt ))&
//Actual serialized data
“i:” & i & “;s:” & Length(lastItem) & “:\”” & lastItem & “\”;” &//Write footer txt (built-in conditional)
footertxt)
You can then use that custom function, or, if you don’t have FileMaker Pro Advanced, some form of script, to turn a list of addresses into a serialized array of addresses suitable for PHP.
List of addresses:
1 Any Street, Anytown, Anystate, 12345
2 Any Street, Anytown, Anystate, 12345
3 Any Street, Anytown, Anystate, 12345
4 Any Street, Anytown, Anystate, 12345
Serialized array of addresses:
a:4:{i:0;s:38:"1 Any Street, Anytown, Anystate, 12345";i:1;s:38:"2 Any Street, Anytown, Anystate, 12345";i:2;s:38:"3 Any Street, Anytown, Anystate, 12345";i:3;s:38:"4 Any Street, Anytown, Anystate, 12345";}
The Complementary PHP file
All this talk about serialized arrays and custom functions and I almost forgot to say that you’ll also need a suitable, complementary PHP file to accept the request and turn it into the Google Maps request. This is the file I’ve written, hosted at http://etc.proofgroup.com/fmcollective/googlemaparray.php:
<?php
/* File Name: googlemaparray.php
File URI: http://etc.proofgroup.com/fmcollective/googlemap.php
Description: Google Map API wrapper script
Version: 1.1
Author: Mike Lee, The Proof Group LLC
Author URI: http://www.proofgroup.com
*
* Copyright (c) 2007-2008, The Proof Group LLC
* All rights reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions are met:
* * Redistributions of source code must retain the above copyright
* notice, this list of conditions and the following disclaimer.
* * Redistributions in any form must reproduce the above copyright
* notice, this list of conditions and the following disclaimer in the
* documentation and/or other materials provided with the distribution.
* * Neither the name of The Proof Group LLC nor the
* names of its contributors may be used to endorse or promote products
* derived from this software without specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY THE PROOF GROUP LLC “AS IS” AND ANY
* EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
* WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
* DISCLAIMED. IN NO EVENT SHALL <copyright holder> BE LIABLE FOR ANY
* DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
* (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
require 'EasyGoogleMap.class.php';
//Google Maps API Key
$googlemaps_api_key = “XXX-XXX-XXX-INSERT-YOUR-KEY-HERE”;//Get the ‘addr’ parameter from the GET request
$addrarray = $_GET[’addr’];
$addrarray = stripslashes($addrarray);$addrarray = unserialize($addrarray);
$gm = & new EasyGoogleMap($googlemaps_api_key);
# Set address point(s)
foreach( $addrarray as $key => $value){
$gm->SetAddress($value);
}# To Enable/Disable Map Type (Map/Satellite/Hybrid)
$gm->mMapType = TRUE;# Set map size
$gm->SetMapWidth(’700′);
$gm->SetMapHeight(’500′);# Set map zoom
$gm->SetMapZoom(8);
?>
<html>
<head>
<title>The Proof Group: Google Map Array API Example</title>
<?php echo $gm->GmapsKey(); ?>
</head>
<body>
<center>
<?php
echo $gm->MapHolder();
echo $gm->InitJs();
echo $gm->UnloadMap();
?>
<font face=”arial, helvetica, verdana, sans-serif” size=”-1″>
Google Maps Array API Example hosted by <a href=”http://www.proofgroup.com”>The Proof Group</a>.<br>
See the original post on this topic at <a href=”http://fmcollective.com/?p=88″>The FileMaker Collective</a> for more information.<p>
</center>
</font>
<font face=”arial, helvetica, verdana, sans-serif” size=”-2″>
Terms of Use: Thank you for using <a href=”http://www.proofgroup.com”>The Proof Group</a> Google Maps API. (1) You may not use this API for commercial purposes (2) Use of this API is limited to 100 requests per IP per day (3) You are subject to all terms and conditions of the Google Maps API Terms of Use, <a href=”http://www.google.com/apis/maps/terms.html”>
http://www.google.com/apis/maps/terms.html</a>.
</font>
</body>
</html>
The important part is the ‘foreach’, foreach( $addrarray as $key => $value) {$gm->SetAddress($value);}, which loops through the array and adds all the addresses as individual map points. This is really the only major difference betweent he single-point mapping PHP file and this one.
What of FileMaker
How does this work from within FileMaker? Essentially, using a list of addresses, you loop through the addresses and capture them to a carriage-return delimited list - the old SetField(LIST;LIST & myAddress & ¶) trick.
Once you’ve got the list, you’ll serialize it using the custom function - SetField(ARRAY;cfSerializeData(LIST)).
Then, it’s just a matter of calling the correct URL from within the Web Viewer:
http://etc.proofgroup.com/fmcollective/googlemaparray.php?addr=ARRAY
First, in FileMaker, you need a table of address data. Here I’m using some really awful, made-up addresses:
I’ve also created a Globals table, with the fields: ARRAY, LIST, and URL (all of which are text, globals).
In layout mode, add a Web Viewer (here, in an enlarged header part), point it to the Globals::URL field and name it ‘wv’ in the object information floating window:
Within FileMaker, I wrapped the entire thing into a Script called “Update Map With Found Set”, a screenshot of which follows:
For convenience, I also attached this script to a button on the layout to update the Web Viewer. Back in browse, get a found set (more about that later), and press the button:
For a found set of 25 records, the request URL will look something like this (pardon the mess):
What could go wrong?
There’s one problem with this technique. Here’s how to reproduce the issue. Show all records and update the map. With all 177 records showing, the Web Viewer map should fail and the following should be shown:
Request-URI Too Large
The requested URL’s length exceeds the capacity limit for this server
I’m no Apache expert, but I know this is based on some form of compile-time configuration and is specific to a GET request. Since the Web Viewer only allows me to use GET requests, we have this upper limit issue (somewhere around 100 records in this example). Alternatively, you can re-compile Apache, but I’ll let you figure that one out.
The solution, of course, is to use POST, but I’ll leave that as an exercise for the reader. Hint: you may have to do things completely differently and FM9 helps.
By the way, once you add “Labels” (one for each address, for example to show demographic info) and other display niceties (e.g. varying marker icons or colors) to your request URL, the maximum number of addresses you can map will plummet. When I’ve fleshed this out, the max easily drops to below 50 records.
Do have fun and let me know what you think in the comments.
You can download a zipped copy of the example file here.
Pseudoportals with Alternating Fill
Among my longest-standing feature requests for FileMaker are portal rows that slide individually to accommodate their content. Although I can usually get by with constructing my layouts in the related table, that’s an approach that usually leaves something to be desired. My favorite workaround, to date, has been what I call a “pseudoportal” (though I’m sure there’re other names for the technique). Read more
Tip of the hat
Having skipped DevCon in order to afford an iPhone, I’ve been dealing with my DevCon envy by exploring some of the wonderful sample files that people have been putting together for FM9.
The fuss over Andy’s conditional formatting tricks, for example, is certainly well-deserved (though let’s face it, we all would have worked that stuff out on our own, right? no?). After playing with those for a bit, a post about Ray Cologon’s “Progress Bars” ( http://www.nightwing.com.au/FileMaker/demos.html ) caught my attention, and I ended up spending a good afternoon hacking that file apart. Read more