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:
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: 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”
Demo file: backup-simplified.zip (requires FM 18 or later)
Update 11 Apr 2021: as per comments below, demo has been updated to use a new service: openrates.io.
Welcome back for another exciting round of JSON exploration. Last time we discussed a JSON-related bug fix in FM 19, as well as a free package tracking service that returns results in JSON format. Today we’re going to look at a free currency exchange rate service.
Demo file: json-exchange-rates-via-openrates.zip
Today we’re going to look at a bug fix in FM 19, and a free web service that returns useful information in JSON format.
In FM 18 (but not in 16 or 17) JSONFormatElements and other JSON functions can mask mangled JSON errors, making them harder to detect and correct. I wrote about this in detail last year, but, briefly, a JSON bug that was introduced in FM 18 has been corrected in FM 19.
Steps to replicate:
Create some JSON, and then transform it, but intentionally botch the transformation, for example, attempt to update an array using object notation:
Long time readers of this blog will be familiar with the following scenario —
An ExecuteSQL expression is given, e.g.,
ExecuteSQL ( "
SELECT SUM ( net_amount )
FROM cc_transactions
WHERE batch_date = ?
AND batch_region = ?
AND card_type = ?
" ; "" ; "" ; $theDate ; $theRegion ; $theCard )
…followed by a disclaimer along the lines of
For readability, static code has been used… in the real world I would employ robust coding practices to prevent accidental breakage due to field and/or TO renaming.
…with the link pointing to custom functions utilizing a combination of GetFieldName and Quote to ensure that using reserved words or potentially-problematical characters (such as a space or #) in table occurrence or field names, or renaming either of the preceding, will not break your SQL code. Continue reading “GetFieldName Revisited”
Recently I was working in FM 17 and ran into an unexpected bug. My use case was more complex than this, but the following bare bones demo illustrates the problem.
Demo file: List+GetNthRecordBug.zip
Today we’re going to dig a little deeper into the new-in-18 While function, and to avoid repetition, will assume readers are familiar with last month’s article on SetRecursion and While. We’ll look at some new examples ranging from basic to advanced, investigate the circumstances under which While can use variables previously declared via Let, and check out some benchmark results comparing While vs. CustomList.
