ExecuteSQL using the Found Set?!?…Yes!…well maybe not.

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

<snip>

 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, 4

<big snip>

, 440779, 440790, 440802, 440815, 440819, 440847, 440855, 440865, 440906, 440913, 440923, 440927 )

Here’s the whole thing…

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!

Featured Posts

Follow Along

Stay up to date with the latest news & examples from SeedCode

18 Comments

  • Ramon Richie

    Copy All Records script step -> into a variable (will need a plugin for that) then use list in IN clause.

    • Hi Ramon,
      Thanks Ramon,
      Right, this is very fast! and you can paste into a global field and reference that without a plug-in, although it’s not very portable. Unfortunately, getting the ids for the WHERE clause is not the problem it’s the execution of the SQL itself that is so slow once that list of IDs gets long. Maybe chunking the list of ids into smaller groups and making multiple ExecuteSQL calls would be a way to make this faster? I’ll have to try that some day!
      Cheers,
      -Jason

      • Ramon Richie

        Yep that might be a good idea 🙂 In some solutions I just “replicate” the SQL to match the Filemaker found set, but that is high maintenance. Must say I’m getting the urge to click on that loop step and give a try.

  • Vincent

    Hi,
    Did you experiment with FM13 ListOF(RecordID) where RecordID is a calc filed that does Get(RecordID) ?

    • Hi Vincent,
      Thanks!
      Right, that might help a little, but getting the list of IDs for the WHERE clause is not the slow part. It’s the execution of the SQL itself that’s so slow.

  • FWIW, FileMaker Pro 12 seems to have a hard limit on the number of OR operators that can be used in the ExecuteSQL function. I get a consistent crash if I use more than 4,629. There’s also a limit for AND operators, but it seems to be somewhere above 22,000. That’s plenty for typical usage, but be careful when working with larger snapshot files.

    • Hi Greg,
      I didn’t run into any of those limits with these tests. With 25,000 records it was basically one Big IN clause with a few thousand OR BETWEENS. When I ran the straight Single IN clause with 25,000 here: http://www.seedcode.com/sql-and-the-found-set-part-2-recordid-list-and-hyperlist/, it was slow but did complete. Do you know what the limit of literals in an IN clause is? Been meaning to test someday, but seems like something you might know =)
      -Jason

      • The limit on literals with an IN operator seems to be somewhere around 500,000, at least with integer values. I’ve seen both ? results and crashes beyond that. The results are inconsistent, so there may be other factors, such as the length of the values or perhaps other things competing for FileMaker Pro’s memory.

  • I’ve been thinking we could use a similar technique to allow FMRPC to work with the user’s found set (and not just the current record or all records), based on some previous tinkering I’d done with Snapshot links. Thanks for fleshing out the non-trivial mechanical details. I’d use a similar approach, but not to drive a WHERE statement for ExecuteSQL, but to drive a subscript that performs a native FileMaker queries. That may or may not scale so well, but it’s worth some additional exploration. Great post!

  • Bill Barman

    So what does the ” Insert the contents of this file into the global immediately after exporting it.” entail? What does the URL look like? Does it refer to the path the txt document was saved, or something more simple than that?

    • Jason Young

      Hi Bill,
      The export path for the snapshot looks like:
      “file:” & Get ( TemporaryPath ) & “snapshot.txt”

      and the InsertFromURL path looks like:
      “file://” & Get ( TemporaryPath ) & “snapshot.txt”
      On Mac I’m substituting out the “Macintosh HD/” so the actual calc looks like:
      Substitute ( “file://” & Get ( TemporaryPath ) & “snapshot.txt” ; “Macintosh HD/” ; “” )

      There’s going to be a few more posts on this theme and I should have a sample file link on one of the upcoming ones as well.

      • Jason Young

        Right, Temp Path is a great tool as it cleans itself up when you close FileMaker!

      • Bill Barman

        Thanks Jason.

        I was playing around with it on my FMPro 12 desktop version and finally got it to work when I exported and imported to the Web folder within the FMP application folder.
        Now I know about “TemporaryPath” too!

        Thanks again.

  • Jason,

    This is a fantastic line of inquiry, and kudos for blazing a trail.

    I’d guess that this might evaluate much quicker if you could use a simple expression like.

    …WHERE id IN (x, y, z)…

    Assuming I have this right, then I’d wonder about creating a light native FileMaker structure that would allow us to use List() to harvest a list of either record id’s, or maybe a list of ID’s (whether we’re using UUID’s, serial ID’s, or some other method of generating our primary keys).

    There are, of course, lots of ways this can be done, and with some tinkering, we could probably find a few painless ways to do this quickly create a list of unique found set ID’s on demand, ready to be fed to our SQL query. I’d suspect this would have the advantage of creating a more readable query, avoiding the need to invoke InsertFromURL(), and potentially evaluating much more quickly.

    Thanks again for starting this discussion. Looking forward to hearing others’ ideas and seeing where this leads.

    PS – I love your woodworking example. I have a very similar memory involving a router table and a plank to be leveled. I wasn’t successful, but I still have both my hands, so I’ll call it a win 😉

    • Jason Young

      Thanks Brian, I will have a follow up post along those lines. Basically using a Hyperlist type method to build a single lN clause rather than the combination with the BETWEENS. That does seem to be generally faster, although it still seems to bog down on large sets even with the single IN clause.

      It would be nice if FileMaker had an InsertIntoVariableFromURL (base 64 encoding as needed to boot) so we didn’t need the global. For that reason, if you really wanted to have an SQL Query based on the found set, I think using HyperList to build your id list and using that rather than the snapshot method would be a better overall method than this, both for portability and predictability of results…but in that case, why not just use Hyperlist to build the ultimate results that you’d get from the SQL. My guess is that doing that will almost always be faster, maybe having a really large number of columns being the exception.

      • Hey Jason,

        Yes, I agree. I’ve been lamenting the lack of the missing InsertFromURLIntoVariable function for the past few months. And as you say, I keep wishing we didn’t need that annoying global field. And agreed also that something like Hyperlist to assemble these id’s will probably be a better route to take than using InsertFromURL.

        I think even in this case, there are likely cases when SQL can do things that are either more powerful or require less overhead than doing them natively. We may want to grab columns from unrelated TO’s, concatenate fields on the fly, do aggregations, or other things SQL excels at.

        I still think you’re onto something interesting here. One way or another, linking the current found set directly to SQL feels like a powerful concept. Maybe we’ll just need some time to get a handle on what we can do with it…

  • Russ DeMoss

    I like the idea of testing what it will do as well what it doesn’t do ( or do well)! Keep up the good work.

Leave a Reply

Your email address will not be published. Required fields are marked *

Check out some of our other posts ...

Suggesting Appointment Slots

Show Available Slots that Match Multiple Criteria Schedulers often look for gaps in their schedules to find the open resources for each opportunity. But sometimes,

Introducing Draft Settings Mode

Following up on this idea that people stretch themselves when they feel a little safer, we’ve been very focused on the customization experience for DayBack

New Longer Timescales for DayBack

Resource Scheduling Swimlanes You can now extend the pivoted scheduling view in DayBack to show items by week instead of solely by day. This lets

FileMaker Summer Camp – Recap

Unconference Sessions If you missed Pause in October, here’s a look at the sessions that attendees hosted. All the sessions are listed in this post

COMPANY

FOLLOW ALONG

Stay up to date with the latest news & examples from SeedCode

© 2024 SeedCode, Inc.