I spent some time this weekend doing some old fashioned FileMaker hacking that I’ve always enjoyed (viva La WorkAround Pro).  Definitely inspired by Todd Geist’s very cool hyper-list stuff, but he had also reminded me of the problem of ExecuteSQL on the found set, and I’ve heard this as a common feature request.  My big take away from Todd’s comments was that the found set is a uniquely powerful thing, so if it could somehow be incorporated into FMSQL, then that would certainly be a potentially powerful tool.  Let me say right now that I did get this to work, but the results are such that it’s really not a practical solution except in maybe some edge cases.  I can proudly say that it is neither as fast (edge exception) or as well-abstracted as hyper-list, however…

When I was in Woodworking school we were given a demo on creating an initial flat edge using a table saw.  It was an elaborate jig set up, safety was a bit questionable and took a little practice to get right.  Some of us were scratching our heads as the joiner is a dedicated machine for doing just this task.  We would all have access to joiners and they work about 10 times better that this elaborate table saw “hack.”  The demonstrator acknowledged this and made two important points:  First, obviously, you indeed may not have access to a joiner at all times.  And second, and a more important point, is that it gives us a deeper understanding of how the table saw works, what it can do and what it’s limitations are, i.e. fruitless experiments (like mine) can lead to a deeper understanding of your tools.

I had remembered, from some elaborate work that Lisette Wilson had done with Snapshots and dynamic sorts, that Snapshots are very light and fast.  They also, if you look at them as text, give a logical reading of Record IDs representing the found set in this syntax:

[CDATA[415956
 417878
 418016-418017
 421900
 422026-422027
 422035
 ]]

So, could I capture this info and somehow incorporate it into a SQL Query?  This would depend on the answers to two questions.

The first question to answer is:  ”Does FMSQL give access to the Record ID?”   I ask this question because that’s the only info I’ll be able to get from the snapshot (If I can even do that).  Looking at the reserved words in the ODBC guide, I didn’t see RECORDID, but I did see ROWID, and sure enough that will get you the Record ID!  You can use both in SELECT and WHERE.  Record IDs, of course, have their limitations as actual key fields, but they can be relied upon to be unique within a table at a given time.  This does seem like a somewhat significant discovery (for me), and worth the effort spent to pursue this already!

The next question is: “Can I capture information written to a snapshot in a relatively “hack free” way?”  The answer, of course, is no.  After that, we have to ask: “Can I just do this?” and the answer there is invariably yes.  I first tried to “read” a snapshot using InsertFromURL into a global field (so much for pure abstraction), but got a 507 validation error.  I did remember hearing that you could insert the contents of a text file.  After playing around for a bit I realized that I could export a snapshot using a variable path that specified it as a text file, and to my pleasant surprise I realized that I could Insert the contents of this file into the global immediately after exporting it.  It is useless as a snapshot, but that’s not really the point here.  So, the second cool thing I learned is that InsertFromURL from a file, particularly a hacked Snapshot works and could have some potentially powerful uses.  I had used a web viewer for this in the past, and scraped the contents for similar results, but it required some hard refreshing after exporting the file.  The InsertFromURL doesn’t require this and works right after the Snapshot creation step is completed, which seems significant as well.  Although I initially “poo-poo’d” the idea of reconstructing the sort of a Found Set, the snapshot does provide this info to you in a logical way as well, so dynamically reconstructing the sort from here into the query is actually relatively doable, with the sort by value list being the snag.

From here it’s relatively easy and fast to parse out the record id info and convert it to a combination of a series of BETWEEN clauses and a single IN clause.  This leads to the third thing I learned:  The performance is so variable from a practical perspective that it’s not really viable.

For example, when run on the full found set of 25,000 records it takes less than as second…pretty darn good.  However, when you run it on a found set of 1437, defined by first_name = S*, it takes 6 seconds.  When you have a found set of 16277, defined by last_name = A…O, then it takes a ridiculous 2:26!!  These results are actually not that surprising.  I’m more surprised that it works at all and actually resolves.  If you look at the queries themselves, the results make perfect sense.  For the full Found Set we get something nice and lean:

SELECT "Name_First"
FROM "Contact Management"
WHERE RowID BETWEEN 415933 AND 440932

However, for the Found Set defined by First_Name = S* we get something significantly more complex:

SELECT "Name_First" 
FROM "Contact Management"
WHERE RowID BETWEEN 416318 AND 416319 OR RowID BETWEEN 418176 AND 418177 OR RowID BETWEEN 418525 AND 418526 OR RowID BETWEEN 418572 AND 418573 OR RowID BETWEEN 418706 AND 418708 OR RowID BETWEEN 418758 AND 418759 OR RowID BETWEEN 419383 AND 419384 OR RowID BETWEEN 420280 AND 420281 OR RowID BETWEEN 420498 AND 420499 OR RowID BETWEEN 420961 AND 420962 OR RowID BETWEEN 422125 AND 422126 OR RowID BETWEEN 422310 AND 422311 OR RowID BETWEEN 422552 AND 422554 OR RowID BETWEEN 422940 AND 422941 OR RowID BETWEEN 422996 AND 422997 OR RowID BETWEEN 423130 AND 423131 OR RowID BETWEEN 423504 AND 423505 OR RowID BETWEEN 423666 AND 423667 OR RowID BETWEEN 423754 AND 423755 OR RowID BETWEEN 424383 AND 424384 OR RowID BETWEEN 424408 AND 424409 OR RowID BETWEEN 425521 AND 425522 OR RowID BETWEEN 425541 AND 425542 OR RowID BETWEEN 425868 AND 425869 OR RowID BETWEEN 425969 AND 425971 OR RowID BETWEEN 426006 AND 426007 OR RowID BETWEEN 426227 AND 426228 OR RowID BETWEEN 426500 AND 426501 OR RowID BETWEEN 427215 AND 427216 OR RowID BETWEEN 427802 AND 427803 OR RowID BETWEEN 427878 AND 427879 OR RowID BETWEEN 428300 AND 428302 OR RowID BETWEEN 428347 AND 428348 OR RowID BETWEEN 428641 AND 428642 OR RowID BETWEEN 428873 AND 428874 OR RowID BETWEEN 428898 AND 428899 OR RowID BETWEEN 429080 AND 429082 OR RowID BETWEEN 429178 AND 429180 OR RowID BETWEEN 429878 AND 429879 OR RowID BETWEEN 430104 AND 430105 OR RowID BETWEEN 430463 AND 430464 OR RowID BETWEEN 430522 AND 430523 OR RowID BETWEEN 430957 AND 430958 OR RowID BETWEEN 431248 AND 431249 OR RowID BETWEEN 431303 AND 431305 OR RowID BETWEEN 431457 AND 431458 OR RowID BETWEEN 431824 AND 431825 OR RowID BETWEEN 431921 AND 431922 OR RowID BETWEEN 432230 AND 432231 OR RowID BETWEEN 432810 AND 432811 OR RowID BETWEEN 432996 AND 432997 OR RowID BETWEEN 434099 AND 434100 OR RowID BETWEEN 435032 AND 435033 OR RowID BETWEEN 435194 AND 435195 OR RowID BETWEEN 435240 AND 435241 OR RowID BETWEEN 435248 AND 435249 OR RowID BETWEEN 435348 AND 435349 OR RowID BETWEEN 435371 AND 435372 OR RowID BETWEEN 436152 AND 436153 OR RowID BETWEEN 436341 AND 436342 OR RowID BETWEEN 436479 AND 436480 OR RowID BETWEEN 436546 AND 436547 OR RowID BETWEEN 437503 AND 437505 OR RowID BETWEEN 437726 AND 437728 OR RowID BETWEEN 438154 AND 438155 OR RowID BETWEEN 438193 AND 438194 OR RowID BETWEEN 438372 AND 438373 OR RowID BETWEEN 438574 AND 438575 OR RowID BETWEEN 438728 AND 438729 OR RowID BETWEEN 439290 AND 439292 OR RowID BETWEEN 439330 AND 439331 OR RowID BETWEEN 440026 AND 440027 OR RowID BETWEEN 440448 AND 440449 OR RowID BETWEEN 440615 AND 440616 OR RowID BETWEEN 440660 AND 440661 OR RowID BETWEEN 440896 AND 440897 OR RowID IN ( 415940, 415974, 415986, 416084, 416086, 416089, 416106, 416141, 416148, 416155, 416173, 416184, 416189, 416200, 416218, 416240, 416280, 416305, 416359, 416379, 416382, 416402, 416442, 416498, 416507, 416512, 416530, 416544, 416571, 416659, 416691, 416694, 416742, 416746, 416749, 416786, 416793, 416795, 416827, 416833, 416841, 416878, 416907, 416915, 416992, 416995, 417005, 417011, 417015, 417074, 417081, 417084, 417137, 417153, 417174, 417200, 417227, 417230, 417236, 417270, 417273, 417279, 417295, 417349, 417387, 417417, 417425, 417439, 417470, 417545, 417557, 417559, 417570, 417588, 417596, 417643, 417665, 417671, 417708, 417730, 417732, 417736, 417748, 417754, 417762, 417764, 417778, 417793, 417801, 417849, 417871, 417912, 417922, 417938, 417963, 417977, 418021, 418040, 418045, 418062, 418070, 418089, 418103, 418130, 418137, 418174, 418204, 418218, 418221, 418244, 418285, 418331, 418341, 418354, 418401, 418433, 418437, 418491, 418496, 418516, 418530, 418563, 418612, 418636, 418671, 418692, 418761, 418763, 418839, 419019, 419025, 419027, 419029, 419051, 419062, 419081, 419083, 419107, 419152, 419184, 419193, 419206, 419213, 419230, 419243, 419277, 419288, 419292, 419340, 419395, 419430, 419446, 419471, 419499, 419508, 419528, 419534, 419538, 419549, 419561, 419575, 419579, 419596, 419610, 419624, 419637, 419647, 419685, 419690, 419696, 419705, 419722, 419746, 419768, 419779, 419788, 419802, 419832, 419855, 419871, 419888, 419907, 419922, 419942, 419966, 420010, 420020, 420022, 420025, 420054, 420056, 420099, 420106, 420111, 420172, 420185, 420201, 420213, 420229, 420232, 420245, 420249, 420284, 420322, 420324, 420332, 420337, 420361, 420363, 420372, 420395, 420415, 420423, 420436, 420461, 420548, 420557, 420575, 420591, 420607, 420614, 420625, 420658, 420686, 420720, 420760, 420776, 420794, 420800, 420810, 420835, 420944, 420971, 421039, 421069, 421086, 421100, 421103, 421110, 421121, 421125, 421152, 421164, 421180, 421193, 421215, 421234, 421247, 421254, 421270, 421272, 421309, 421349, 421351, 421366, 421425, 421477, 421485, 421489, 421495, 421497, 421505, 421530, 421553, 421556, 421576, 421584, 421603, 421606, 421680, 421686, 421727, 421745, 421750, 421762, 421772, 421803, 421809, 421867, 421927, 421933, 421938, 421943, 421946, 421951, 421957, 421964, 421968, 421981, 421987, 421997, 422007, 422037, 422046, 422050, 422060, 422150, 422170, 422177, 422209, 422218, 422306, 422313, 422352, 422358, 422388, 422427, 422429, 422453, 422468, 422500, 422524, 422533, 422584, 422594, 422614, 422618, 422625, 422639, 422642, 422655, 422664, 422668, 422670, 422683, 422688, 422716, 422731, 422752, 422754, 422761, 422768, 422794, 422806, 422821, 422841, 422863, 422908, 422920, 422951, 422954, 422956, 422976, 422978, 422985, 423007, 423037, 423047, 423090, 423113, 423116, 423127, 423147, 423158, 423160, 423175, 423202, 423207, 423214, 423225, 423238, 423250, 423281, 423356, 423372, 423398, 423413, 423460, 423477, 423558, 423586, 423591, 423596, 423603, 423612, 423614, 423638, 423642, 423653, 423701, 423718, 423722, 423732, 423738, 423744, 423751, 423770, 423803, 423815, 423819, 423828, 423837, 423877, 423882, 423905, 423923, 423929, 423942, 423998, 424000, 424012, 424017, 424035, 424055, 424059, 424061, 424081, 424090, 424099, 424113, 424167, 424190, 424223, 424244, 424255, 424269, 424285, 424298, 424301, 424313, 424347, 424378, 424390, 424411, 424429, 424436, 424467, 424502, 424572, 424623, 424625, 424666, 424669, 424676, 424692, 424710, 424724, 424741, 424778, 424793, 424813, 424818, 424828, 424837, 424851, 424864, 424928, 424940, 424944, 424953, 424963, 424981, 424985, 425013, 425060, 425087, 425096, 425107, 425137, 425139, 425146, 425172, 425199, 425218, 425224, 425253, 425266, 425270, 425274, 425278, 425282, 425285, 425307, 425363, 425392, 425400, 425421, 425434, 425437, 425504, 425511, 425559, 425571, 425578, 425597, 425605, 425691, 425695, 425718, 425730, 425736, 425755, 425794, 425801, 425807, 425843, 425846, 425849, 425878, 425899, 425910, 425930, 425948, 425973, 425980, 426012, 426020, 426024, 426037, 426047, 426049, 426057, 426080, 426100, 426104, 426108, 426126, 426136, 426163, 426173, 426186, 426253, 426273, 426334, 426365, 426383, 426426, 426449, 426462, 426488, 426535, 426563, 426576, 426584, 426598, 426620, 426651, 426653, 426692, 426719, 426733, 426740, 426749, 426763, 426793, 426809, 426833, 426845, 426864, 426872, 426875, 426895, 426897, 426975, 427023, 427055, 427094, 427103, 427119, 427121, 427152, 427156, 427161, 427181, 427194, 427197, 427236, 427266, 427282, 427292, 427318, 427325, 427332, 427360, 427371, 427449, 427454, 427458, 427488, 427502, 427507, 427510, 427518, 427573, 427583, 427618, 427640, 427648, 427654, 427668, 427682, 427698, 427706, 427721, 427789, 427808, 427811, 427826, 427838, 427868, 427873, 427904, 427906, 427927, 427960, 427968, 427998, 428011, 428025, 428028, 428061, 428104, 428119, 428127, 428130, 428172, 428185, 428190, 428197, 428213, 428233, 428272, 428315, 428318, 428438, 428451, 428473, 428502, 428505, 428523, 428527, 428564, 428572, 428580, 428587, 428665, 428670, 428691, 428694, 428734, 428771, 428800, 428802, 428825, 428827, 428839, 428857, 428863, 428903, 428929, 428931, 428935, 428950, 429033, 429122, 429147, 429194, 429238, 429242, 429252, 429269, 429287, 429297, 429304, 429336, 429339, 429344, 429346, 429367, 429400, 429433, 429436, 429447, 429465, 429473, 429480, 429587, 429612, 429653, 429698, 429707, 429731, 429734, 429746, 429758, 429776, 429795, 429818, 429836, 429851, 429854, 429870, 429910, 429920, 429925, 429940, 429947, 429950, 429990, 429993, 430015, 430026, 430036, 430060, 430089, 430124, 430130, 430151, 430167, 430171, 430173, 430221, 430252, 430257, 430260, 430274, 430290, 430299, 430305, 430314, 430321, 430341, 430358, 430440, 430458, 430468, 430476, 430494, 430517, 430527, 430530, 430541, 430555, 430567, 430642, 430654, 430686, 430703, 430729, 430737, 430764, 430775, 430802, 430819, 430822, 430828, 430830, 430860, 430878, 430885, 430889, 430921, 430933, 430967, 430977, 430989, 430993, 430996, 431014, 431034, 431036, 431049, 431057, 431076, 431078, 431105, 431108, 431162, 431201, 431209, 431257, 431286, 431301, 431319, 431326, 431331, 431342, 431358, 431365, 431385, 431395, 431449, 431469, 431482, 431548, 431570, 431590, 431610, 431615, 431695, 431714, 431719, 431795, 431822, 431852, 431859, 431862, 431888, 431905, 431911, 431915, 431925, 431928, 431938, 431953, 431991, 431997, 432005, 432023, 432092, 432103, 432111, 432142, 432147, 432155, 432158, 432167, 432171, 432185, 432188, 432220, 432222, 432251, 432320, 432331, 432337, 432346, 432382, 432403, 432410, 432415, 432442, 432458, 432463, 432497, 432505, 432509, 432511, 432528, 432531, 432533, 432556, 432573, 432582, 432615, 432624, 432628, 432668, 432686, 432702, 432720, 432733, 432741, 432743, 432749, 432756, 432782, 432803, 432820, 432834, 432871, 432965, 432973, 432989, 433019, 433028, 433035, 433060, 433065, 433071, 433101, 433130, 433138, 433182, 433191, 433195, 433208, 433213, 433252, 433283, 433326, 433332, 433349, 433353, 433402, 433404, 433483, 433494, 433540, 433578, 433613, 433618, 433639, 433642, 433655, 433662, 433670, 433674, 433676, 433686, 433689, 433698, 433713, 433732, 433763, 433791, 433801, 433808, 433936, 433949, 433990, 433995, 434033, 434058, 434067, 434091, 434121, 434150, 434154, 434156, 434158, 434210, 434220, 434223, 434225, 434236, 434296, 434318, 434328, 434365, 434370, 434402, 434433, 434438, 434469, 434530, 434573, 434577, 434586, 434597, 434612, 434627, 434636, 434675, 434684, 434704, 434740, 434753, 434757, 434809, 434811, 434846, 434857, 434867, 434881, 434890, 434921, 434942, 434948, 434955, 434960, 434976, 434979, 434989, 435009, 435011, 435017, 435043, 435071, 435080, 435084, 435092, 435119, 435125, 435150, 435156, 435184, 435188, 435197, 435209, 435211, 435217, 435246, 435251, 435255, 435257, 435261, 435264, 435266, 435274, 435278, 435281, 435364, 435402, 435413, 435418, 435427, 435510, 435513, 435515, 435558, 435584, 435597, 435607, 435641, 435659, 435666, 435683, 435696, 435721, 435729, 435747, 435754, 435774, 435781, 435786, 435795, 435800, 435826, 435853, 435867, 435874, 435884, 435901, 435911, 435927, 435941, 435963, 435977, 435998, 436001, 436041, 436044, 436052, 436056, 436086, 436101, 436133, 436142, 436164, 436274, 436301, 436315, 436325, 436357, 436371, 436373, 436377, 436398, 436433, 436473, 436508, 436519, 436526, 436560, 436591, 436593, 436599, 436622, 436624, 436637, 436649, 436659, 436755, 436786, 436803, 436815, 436819, 436833, 436844, 436858, 436892, 436896, 437005, 437022, 437033, 437062, 437070, 437093, 437095, 437105, 437140, 437145, 437181, 437212, 437249, 437277, 437284, 437324, 437352, 437360, 437370, 437402, 437433, 437437, 437444, 437454, 437490, 437498, 437515, 437518, 437532, 437552, 437585, 437598, 437611, 437614, 437628, 437645, 437656, 437694, 437698, 437721, 437761, 437807, 437839, 437845, 437871, 437945, 437952, 437961, 438006, 438021, 438086, 438123, 438138, 438147, 438187, 438217, 438222, 438229, 438262, 438265, 438328, 438380, 438398, 438417, 438425, 438446, 438485, 438488, 438504, 438543, 438583, 438593, 438606, 438608, 438615, 438618, 438626, 438630, 438651, 438697, 438713, 438731, 438750, 438774, 438785, 438788, 438818, 438830, 438835, 438846, 438855, 438869, 438913, 438921, 438930, 438934, 438943, 438953, 438975, 438990, 439007, 439014, 439023, 439028, 439031, 439059, 439103, 439111, 439141, 439151, 439195, 439215, 439260, 439263, 439344, 439363, 439392, 439397, 439414, 439442, 439445, 439450, 439489, 439492, 439510, 439521, 439563, 439577, 439611, 439620, 439629, 439631, 439656, 439669, 439689, 439756, 439786, 439789, 439793, 439796, 439803, 439805, 439817, 439853, 439857, 439863, 439904, 439921, 439958, 440030, 440055, 440057, 440075, 440088, 440093, 440111, 440117, 440170, 440204, 440233, 440257, 440260, 440277, 440283, 440332, 440366, 440368, 440394, 440410, 440412, 440418, 440420, 440491, 440495, 440508, 440521, 440538, 440551, 440581, 440597, 440600, 440612, 440656, 440658, 440666, 440705, 440712, 440718, 440724, 440773, 440779, 440790, 440802, 440815, 440819, 440847, 440855, 440865, 440906, 440913, 440923, 440927 )

(scroll that to the right to see the whole query)

The found set defined by the more complex criteria of First_Name = A…O generates a query that’s 134062 characters long and takes just under 2 seconds to create itself, so the fact that it takes 2:26 to complete could be seen as relatively good news!

I am as infatuated by ExecuteSQL as anyone. Todd points out that native FileMaker provides us uniquely powerful tools, and he could not be more right.  For me, and many of us, the temptation to take these tools for granted and forsake them for the newest thing without objective evidence is a disservice to our code.  This seems like a truism in retrospect, but always good to go through the process and see it validated.

Although I had a strong sense this was a fools errand as far as getting a practical solution to the SQL vs Found Set question, I am pleased I went through the process for another reason.  I don’t think my method for “re-engineering” a found set this way was flawed.  From a query stand point, I’m not sure there’s a more efficient way to reconstruct the available info into something more compact for FMSQL, so the idea of not being able to have a native way to do this seems more reasonable.  Having said that, FileMaker can generate a new window with the same found set from snapshot link in less than a second.  It would be nice to be able to tap into that kind of speed!