ISO To American

One of the things that drives me nuts about FileMaker is that the ExecuteSQL doesn't work with ISO dates. You have to first convert the date to American or similar format.

In any case, I wrote a helper method (will be glad to share code to anyone interested) that, using a date API, takes an ISO 8601 date format and converts it to the American format (any format including timezones is super easy to implement as well).

I'm assuming most would use a different technique (plug-in, or even hard-coded date string manipulation), but I just wondered if this approach sounds interesting or it sounds too complicated.

For me, the part of this approach that makes it a winner for me is that this approach is general so it would work from any application that an issue HTTP verbs.

Here's the entire script code:

// Convert date formats:
// SQL Lower Date -- call micro-service
Insert from URL [ Select ; With dialog: Off ; $lowerDate ; "http://localhost:4567/ISOToAmerican/" & Customers::SQLDateLowerEnd ]

// SQL Upper Date - call micro-service
Insert from URL [ Select ; With dialog: Off ; $upperDate ; "http://localhost:4567/ISOToAmerican/" & Customers::SQLDateUpperEnd ]

// set variable with count (could update field depending on need after this step)
Set Variable [ $result ; Value: ExecuteSQL ("select count(*) from customers where z_timestamp >= ? and z_timestamp <= ?" ; "";""; $lowerDate; $upperDate ) ]
Show Custom Dialog [ "Result" ; $result ]


The time taken to make the two micro-service calls is about 0.1 seconds.

Here's the FMP solution:

The SQL count result is on top.

The screenshot shows all the data (left) and the two global fields in list view.

The query is basically the same as the one posted yesterday, but I didn't use a BETWEEN function.

2 Likes

There's actually a very simple way to use ISO dates: do a simple substitute of the "-" with "+" and wrap it in a GetAsTimestamp().

Try this in your data viewer:

let(
[
_ISO = "2020-02-12 09:34 AM" ;
_FM = GetAsTimestamp ( substitute ( _ISO ; "-" ; "+" ))
];
_FM
)

Make this into a CF and away you go.

5 Likes

That's nice, but as I noted above, I want a general solution that isn't tied to FMP only. Plus, I want to use a Date API.

The solution you included works well, but it's convoluted (not your fault) and not intuitive. A Date API fixes that problem which is important for code maintenance (code clarity).

I'm going to preface my answer by saying that I'm a fan of a Lambda approaches or micro-services. However, I'd need a pretty compelling reason to use them in association with our FileMaker systems. For a simple data conversion I'd go with a CF as outlined by Wim.

In our environment if I were to add an external API, I'd be making a commitment to ownership of that API unless I could persuade someone else to look after it. That means having to deal with external auditors who are going to run pen testing at random times. Having to keep on top of our security standards and any number of other IT admin functions that I really don't want to deal with. An all FileMaker solution is much easier for the next FileMaker developer to look at and understand.

Micro-services certainly have their place, but at the cost of additional system complexity that outweighs the advantages in many cases. Most FileMaker clients I dealt with over the years lack the technical skills to run a FileMaker Server on its own. Adding more complexity wouldn't be appropriate for them.

8 Likes

Understood, but our need for code that would work with not just FMP but all our apps is a high priority. Really not much additional complexity considering the huge gains possible (that are often not even remotely possible with FMP and CFs)

If you think about multiple FMP applications, you'd have to copy CFs from one to another since, as I understand it, FMS STILL does not have a global CF repository (like stored functions or stored procedures) callable by all hosted apps. Thus, you would have possibly large maintenance chores for multiple FMP applications all using the same CF -- keeping up to date, debugging, adding new features, ...

Using the micro-service approach, there is a SINGLE point of update. The FMP apps never need to be touched, assuming the interface stays the same, if you change the service. MUCH SIMPLER!

Also, our micro-services never return the FMP brain-dead "?" to a user, but rather always an informative message about what happened and then in most cases it's obvious what to change.

I guess it all depends on your needs, installed user base, and priorities, however.

Thanks for your reply.