Jason’s swapping out the results portal in our free SQL Explorer with a webviewer so he can take advantage of column resizing and other UI goodies you just don’t see in FileMaker. The second part of the video shows how he lets users easily swap out the styles used in the “portal” as their layouts change.
HTML5 in FileMaker apps. Yummy.
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.” Continue reading
I’ve had some small pre-FileMaker experience with SQL and had it in my head that subqueries work in the FROM clause. They do in MySQL etc, so I was frustrated when I couldn’t get that to work in FileMaker’s SQL as it’s really needed in some cases.
In a current project we have a case where there is a relationship from Contacts to Loans and we wanted to see the total Loan amount from the Contact context. However, Contacts can be associated with a Loan using multiple roles, and we only want to have the Loan amount counted once for the Contact, otherwise the total will seem inflated. To further complicate this, the Portal is filtered, and we want to see the filtering reflected in the total. This, I thought was a job for SQL…
If you’re using SQL in your FM12 work, you’re in luck. Jason has released a new version of SQLexplorer that writes queries which survive field renaming.
It will also apply this abstraction to queries you simply type in there (vs ones you build with the wizard). Big step forward.
Still free and unlocked, the new version is here.
Just got this from John Weinshel and had to share…
“I love SQL, love that it’s as old as relational databases, sort of Codd’s afterthought. It’s like Freud, after describing his entirely new model of the universe, added a coda, psychoanalysis, for putting it to use. Except SQL is free and it works.”
Priceless.
The problem.
SQL, including the new ExecuteSQL calc function in FileMaker 12, returns dates in this format: YYYY-MM-DD. This can be a bit of a pain if you have a string of text like this and just want to transform the date part:
1170 - 2012-03-09 - - Keep: Beta Presentation @ BRN Co
1171 - 2012-03-15 - 08:30:00 - Call Arthur Murray to finalize data
1172 - 2012-03-16 - 12:30:00 - Conference call with Melinda
1173 - 2012-03-20 - 11:15:00 - @ Breakfast
The calc for the results above looks like this: ExecuteSQL ( “SELECT id, DateStart, TimeStart, Summary from SampleEvents” ; ” – ” ; “¶” )
The solution.
Jason Young discovered that you can cast the dates as text in your results simply by concatenating them with a bit of text, so instead of using just the field DateStart in your query, you can use ” || DateStart to get this:
1170 - 3/9/2012 - - Keep: Beta Presentation @ BRN Co
1171 - 3/15/2012 - 08:30:00 - Call Arthur Murray to finalize data
1172 - 3/16/2012 - 12:30:00 - Conference call with Melinda
1173 - 3/20/2012 - 11:15:00 - @ Breakfast
The calc for those results looks like this: ExecuteSQL ( “SELECT id, ” || DateStart, TimeStart, Summary from SampleEvents” ; ” – ” ; “¶” )
Yes, FileMaker supports concatenate in it’s select statements.
Hope that helps.
FileMaker 12 lets us run SQL queries against our own FileMaker tables. This has been possible with plugins for a while and a number of serious devs like Mike Lee, Greg Lane, and Kevin Frank have been talking about what a great technique this is. I completely agree and think the new ExecuteSQL calc function is the killer feature of FileMaker 12.
There are a few reasons for this and I hope to get to write about more of them. But for now, consider the following simple script that checks to see if any records match a given ID:
Before SQL (click for a larger version)
There is nothing really wrong with this script and in fact it comes from our FileMaker 10 calendar. But look at all the work we have to do to make sure we’re in the right context to perform the find. We have to:
Kind of complicated. And frustrating to debug as the action is happening off-screen.
Now take a look at the same script in FileMaker 12:
SQL Version (click for a larger image)
Because the new ExecuteSQL calc function lets us search for records independent of context, we don’t need to manage layouts and windows. We can just ask FileMaker if the records exist. Fewer moving parts means simpler scripts, which means more stable solutions: no question about it.
(For what it’s worth, we could have written that as just one line, putting the ExecuteSQL calc in the ExitScript result, but I find these things are easier to debug if you can look in the script debugger for $sc_Result before the script exits. That way, I can alter it right here if I need to.)
FileMaker’s new ExecuteSQL function only accepts versions of the SELECT statement so there isn’t that much SQL to learn But it does support joins, union, and groupby, so if you already know your way around this stuff you’ll be rewarded. If you don’t, SeedCode will soon be releasing a free tool to help. (You’ll love it.) If you’re not getting our newsletter, sign up here to be notified as soon as we ship.
And if you’re wondering what you can possibly do with just the SELECT statement (which returns lists of matching data), think Virtual List and stay tuned.
We’re thrilled to announce our new Free Calendar Template for FileMaker 12. We’ve rewritten the free calendar to take advantage of some of the new things in FileMaker 12, including the new look and feel, but perhaps most importantly, the new ExecuteSQL function.
This makes the new calendar simpler, less dependent on context, and easier to integrate.
Checkout this short introduction demonstrating how this new SQL stuff makes things simpler:
Now download a copy of the new calendar and dive in or learn more about our calendars.
(And if you’re looking for some more detail on how we made that cool little “x” toggle in the interface, we describe that here. It’s a great little switch that is super portable. That video will also give you a sneak peak into our new Pro calendar for FileMaker 12. Coming soon!)
SeedCode: Next 