This is a quick follow up to Tuesday’s article, and to avoid unnecessary repetition I will assume the reader is familiar with that material.
There were some things in v3 I wasn’t completely happy about, and I realized this morning that they would be easy to fix, and that the act of fixing them would help demonstrate the power of using this approach in the first place. Continue reading “Button Bar Segment Fun, part 2”
Today we’re going to look at some ways single-segment button bars (SSBBs) can help produce dynamic column headings for list views and/or reports, with a goal of concentrating logic into the segment calculation and reducing schema dependencies elsewhere. This is a work in progress, rather than a finished, battle-hardened methodology. The aim is to explore possibilities and stimulate discussion.
Note: the demo files are built on top of an “empty” virtual list table. The point is not to (once again) dive into virtual list or clickable/sortable column headings, but to provide a list view we can pretend contains valid entries, while we focus on what’s going on in the layout header part.
Disclaimer: these techniques are in the proof-of-concept stage. As with all techniques on this (and any other) site, use with a healthy dose of common sense and at your own risk.
Recently we’ve discussed optimizing SQL queries in FileMaker, and had some fun with various SQL experiments. Today we’re going to explore some ways FileMaker can use ExecuteSQL and the While function to perform letter frequency and text pattern analysis on candidate words for the popular Wordle game.
The list of words comes from https://github.com/tabatkins/wordle-list and purports to include the actual answer words, as well as all allowable guess words. I don’t know how valid this list of words actually is, or, assuming it is currently valid, whether it is carved in stone or will change at some point in the future.
Demo file: SQL-Multi-Table-Experimentation-Wordle.zip
Today we’re going to pick up where we left off last month, and today’s article will assume the reader is familiar with the material we covered last time (in SQL Multi-Table Query Optimization).
This time we’re going to dig a little deeper into multi-table SQL queries, conduct some SQL experiments, and look at a way the While function can help speed up and/or extend the capabilities of an ExecuteSQL query.
Demo file: SQL-Multi-Table-Experimentation.zip
Continue reading “SQL Multi-Table & Miscellaneous Experimentation”
Demo file: sql-multi-table-query-optimization-v2.zip
Recently a colleague requested help w/ a SQL query that was performing slowly. I wrote back:
Well today we’re going to take a look at #3 and see what we can learn re: optimizing multi-table queries. But first, I should point out that what follows reflects my current understanding of how things work. I hope this can be the start of a conversation where others will chime in and share their knowledge. Continue reading “SQL Multi-Table Query Optimization”
On Friday, January 14, Steve Senft-Herrera and I presented JSONQuery at FM-DiSC (FileMaker Developers in Southern California).
Recording of the presentation: https://www.youtube.com/watch?v=dztdZrHdrUQ
Current version of JSONQuery: CF_JSONQuery_20211130_0120_PUBLIC.fmp12.zip
Our recent two part in-depth interview series:
Kevin Frank’s demo file from the presentation: jsonquery-sandbox.zip
More information re: JSON + FileMaker:
Have you ever wished you could connect a portal to a JSON array? Portals and JSON arrays seem like they should be a natural fit, but FileMaker doesn’t offer us an obvious way to connect one to the other.
(Why would you want to do this? One use case would be to provide dynamic scrollable selection criteria for a report.)
At any rate, today we’re going to take a look at a little proof-of-concept I threw together to enable portals to display and edit data in JSON arrays. In a real-world implementation, the JSON would likely be sitting in a $$variable, which, among other things, would help make the technique multi-user friendly. Here, in the interest of simplicity, I’ve opted to store the JSON in a regular text field. A couple benefits of doing so:
(Yes, it’s possible to accomplish the preceding with variables as well, but the aim here is to keep things simple).
Demo file: connecting-portals-to-json-arrays.zip
[Editor’s note: the demo file and custom function have been significantly updated since part 1.]
Demo file: CF_JSONQuery_20211130_0120_PUBLIC.fmp12.zip
KF: Welcome back Steve for part 2 of our JSONQuery conversation.
SSH: Thank you, Kevin.
KF: One thing we didn’t mention last time, because they were late-breaking additions, were the inequality operators.
[30 Nov 2021: Custom function and demo file have been updated. Some of the screen shots and example numbers referenced here in part 1 will deviate slightly from what you will find in the updated demo.]
Demo file: CF_JSONQuery_20211130_0120_PUBLIC.fmp12.zip
KF: Good afternoon, Steve. You’ve been developing JSONQuery over the last few years, and today I have the honor of presenting and discussing it here with you on FileMaker Hacks. I was wondering if you could start out with a brief description of what JSONQuery is?
SSH: Sure. JSONQuery is a custom function, and it operates on JSON. Typically you’re going to be feeding it a large JSON array you’ve received back from the FileMaker Data API, or somebody else’s API, where each record is a JSON object within a larger parent JSON array, and the purpose of this function is to be able to find child elements in that parent array that match certain criteria and return just those elements to you.
Above and beyond that it has a lot of bells and whistles, some of which I’m sure we’ll cover, but the main impetus for writing it was giving you an easy and fast way to essentially query a JSON array.
For example, let’s say you have an array filled with a lot of orders, but you only need to get the order items that are being shipped to a certain city, or to a certain state, then this function would allow you to easily obtain those elements in an efficient manner. Continue reading “JSONQuery, part 1”
Back in 2017 I wrote about a technique to enable users to a) produce multiple on-screen reports, and b) interact with those reports in browse mode. The article was called Virtual List Reporting, part 3, and while the approach it advocated works well enough under most circumstances, today I’d like to share some fresh ideas.
Note: as you might guess from the title of this article, implementing this technique in your solution, and/or understanding what’s going on under the hood, requires some knowledge of virtual list. If you are not familiar with virtual list, or need a refresher, you may find this article helpful: Virtual List Simplified.
