Demo files: set-var-by-name-v4 and set-var-by-name-md5
This is a quick follow up to last December’s Set Variable By Name Revisited, and to avoid repetition will assume the reader is familiar with the material that was presented in that article. But to briefly recap:
1. FileMaker does not provide an obvious way to programatically name a variable.
Update: See Jason Wood’s suggestion in the comments section for making this technique more secure.
Today I want to take a look at a certain Perform Script On Server (PSOS) behavior, and for those who find this behavior to be inconvenient, propose a work around.
Here’s the behavior, or misconception, actually: a server side script initiated via PSOS (apparently) cannot access files on the server unless the user already has those files open locally. But of course there may be circumstances where you’d like to be able to access files server side that the user does not have open client side, either because the user’s credentials do not allow access to those files, or because you’d rather not have to open the files client side merely to facilitate a PSOS call.
If we consult the online help entry for running scripts on server, the official word is that server-side scripts can access other FileMaker files only when…
…in other words, if you want PSOS to be able to access files, you need to either a) already have the files open client side, or b) throw caution to the wind, and configure the hosted files to auto-open with pre-entered credentials.
Hmmm… I think we can agree that from a security standpoint “b” is a non-starter, and the whole point of this article is to avoid “a”. Fortunately there is a “c” option not mentioned above which I’ll get to shortly, but first let’s demonstrate the issue. Continue reading “PSOS – Run Script in File not open Locally”
Recently I had the pleasure of speaking with Jeremy Brown on The Context Podcast about an open-source project called JSONata that enables you to query and transform JSON.
The JSONata site is packed with useful information, including this short video…
…as well as…
Okay, a JSON query and transformation language sounds intriguing, at least in theory, but wouldn’t it be great if we could harness that power from within FileMaker? Well, now we can, utilizing techniques in this demo, built by Jeremy and shared here with his permission.
Demo file: JSONata.zip (requires FM 19+ on Mac, 19.3+ on Windows)
As you’ve probably surmised, the demo makes use of the Perform JavaScript in Web Viewer script step. If you’d like to learn more re: what’s going on under the hood, Jeremy goes into specifics here: Towards a Better JS Development Environment.
Note: the demo requires an online connection to access the JSONata library. Continue reading “FileMaker 19 + JSONata”
Earlier today I needed 1 billion to render in the calc engine as 1000000000, but FM helpfully converts numbers to scientific notation if they A) are >= 1 billion, and B) contain nothing but zeros following the initial digits.
Example where “A” and “B” are both true:
Editor’s note: today we have a guest article by long-time FileMaker developer, Peter Doern, who has come up with an elegant, outside-the-box methodology for managing imports. This is one of the coolest techniques I’ve seen in a long time. Enjoy.
FileMaker includes a powerful and fast method for importing data from a source table to a target table. FileMaker 18 introduced an overhauled file import dialog which allows us to easily specify source data character set and delimiter, target fields, and auto-enter options on import.
But what if you want to pull a source field into multiple target fields, manipulate data during import, or split a flat source table into multiple related records? What if you want to import data from multiple different sources with different structures into a single, consistent, file? What if the structure of the source file changes frequently?
Using the traditional FileMaker import method, any of these scenarios will require multiple imports using hard-coded import script steps, or a solid understanding of XML and XSLT transformations. This Flexible, Robust, Accessible and Portal Mapping technique, aka FRAP-Map, provides a powerful alternative.
Continue reading “FRAP-Map: A Flexible, Robust, Accessible and Portable Data Mapping Technique”
Today we’re going to take a quick look at a potential “gotcha” that can occur when performing numeric comparisons. Jason DeLooze and I discussed the general behavior back in 2011 (Space Is The Place), but this time around I want to focus on one particular set of circumstances where the problem can arise.
At first glance this may appear to apply to beginner-level developers only, but the problem is nuanced, and I have occasionally seen it crop up in solutions written by experienced developers as well as those created by newbies.
Specifically, when comparing two variables, for example…
ExitLoopIf [ $counter >= $rows ]
…FileMaker must determine whether to do a text comparison or a number comparison, and under certain circumstances, contrary to developer expectations, may perform the comparison in “text space” rather than in “number space”.
Of course we can explicitly ensure that all comparisons take place in “number space” like so…
ExitLoopIf [ GetAsNumber ( $counter ) >= GetAsNumber ( $rows ) ]
…but let’s dig a little deeper to see the circumstances under which GetAsNumber is actually necessary. Continue reading “When 2 is greater than 10”
Demo file: sql-join-fun-etc-v2.zip requires FM 18 or later.
[26 Feb 2022: demo file has been updated to v2 to address the concern raised by Phil McGeehan in the comments section. Screen shots have not been updated.]
Recently I was asked to create a mini-report combining data from a pair of related tables via ExecuteSQL.
This provided an opportunity to think about SQL joins, and also to come up with a way to apply currency formatting to dollar amounts in the SQL query result, given that FileMaker’s SQL implementation does not support the standard SQL way of accomplishing this (e.g., CAST AS NUMERIC or CAST AS DECIMAL) . Continue reading “Fun with SQL Joins and Currency Formatting”
21 Jan 2021: demo has been updated to fix a couple bugs; article has been updated to reflect these changes.
Demo file: file ids, table ids and field schema, v4.zip (requires FM 18 or later)
Recently I was asked to help document a FileMaker solution by creating and populating a “schema” table as per the above screen shot.
One of the requirements was to programmatically determine internal ID numbers for the file and for the tables (as opposed to table occurrences) within that file, and this was accomplished by leveraging some script steps that were introduced in FileMaker 18.
(Thank you Rob Russell for pointing me in the right direction, i.e., to Save A Copy As XML.)
Clicking this button…
Continue reading “File IDs, Table IDs and Field-Level Schema”
Update 19 Aug 2021: demo file has been superseded by the one available in Set Variable By Name Re-Revisited
You’ve probably heard the old joke that goes…
Patient: Doc, it hurts when I do this.
Doctor: Don’t do that.
Perhaps you’re also familiar with the FileMaker equivalent?
Developer: It hurts when I try to assign certain variable names.
FileMaker: Don’t do that.
Demo file: a-value-list-experiment.zip
Note 1: today’s demo is recycled from the one that accompanied Virtual Portal, part 2. All we care about this time around is how we assign the parent company; the remainder can be ignored.
Note 2: if you’re unclear on value list basics, check out Thinking About Value Lists, part 1.
“I want it to work like it does in Quickbooks.” Based on a client request I recently implemented a Quickbooks-like picker… shown here enabling the user to easily assign a parent company via auto-complete as the user types the first few letters of the company name.
To be clear, we aren’t actually storing the parent company name, merely using it as a selection mechanism. When a name is chosen via this interface, the corresponding foreign key is immediately auto-entered into “id_parent”… Continue reading “A Value List Experiment”
