DateTransformer date format issue

[montaniasystemab]

I'm using a repository created from XML metadata and when trying to fetch a record from FileMaker I'm getting this error from DateTransformer:

  Notice: Undefined offset: 0                           

Trace looks like this (testing with a Symfony console command): https://gist.github.com/montaniasystemab/3808750fc6cbe04e88832cb25bcafbc9

Dumping the value reveals another date format than what DateTransformer is expecting: 08-23-2010

Adding another block in DateTransformer with this date format seems to work, but I'm not sure if this is the best solution:

if (false === $dateTime) {
    $dateTime = DateTimeImmutable::createFromFormat(
        '!d-m-Y',
        $value,
        self::$utcTimeZone ?: (self::$utcTimeZone = new DateTimeZone('UTC'))
    );
}

I would like to discuss approaches to solving this issue before submitting a PR.

Maybe the date format should be configurable some way?

Also, this field is actually not required by the web application, so some way to exclude it from hydration would also effectively resolve the issue for us. I've not added this field to the XML metadata file.

[jeremiahsmall]

Can you also provide a gist of the metadata part of your xml response from the FileMaker Server? I'm wondering if your date field in FileMaker is actually a text field. Pretty sure we found out that the date format is not variable when the field is properly typed.

Example:

<?xml version ="1.0" encoding="UTF-8" standalone="no" ?><!DOCTYPE fmresultset PUBLIC "-//FMI//DTD fmresultset//EN"
        "http://localhost/fmi/xml/fmresultset.dtd">
<fmresultset xmlns="http://www.filemaker.com/xml/fmresultset" version="1.0">
    <error code="0"></error>
    <product build="11/02/2015" name="FileMaker Web Publishing Engine" version="14.0.4.412"></product>
    <datasource database="SC_Web" date-format="MM/dd/yyyy" layout="report_Schedule" table="SCH__Schedule"
                time-format="HH:mm:ss" timestamp-format="MM/dd/yyyy HH:mm:ss" total-count="186386"></datasource>
    <metadata>
        <field-definition auto-enter="yes" four-digit-year="no" global="no" max-repeat="1" name="ID"
                          not-empty="no" numeric-only="no" result="number" time-of-day="no"
                          type="normal"></field-definition>
        <field-definition auto-enter="no" four-digit-year="no" global="no" max-repeat="1" name="WeekBeginningSunday"
                          not-empty="no" numeric-only="no" result="date" time-of-day="no"
                          type="normal"></field-definition>
<!-- snip -->

If you have an example where the datasource element has an attribute different than date-format="MM/dd/yyyy", we missed that as a possibility. It should definitely be pulled from the datasource date-format if it can vary from FMS.

[jeremiahsmall]

So it seems that there is a way to alter the formats. For all intents and purposes it boils down to the host system settings, and it can vary by individual datasource. Here is where the per-file (per-datasource) setting can be altered:

@DASPRiD, so this means that we need to assume the Date, Time, and Timestamp formats can vary, even if that isn't very common. See hardcoded formats in these three transformers:

• src/Client/ResultSet/Transformer/DateTimeTransformer.php
• src/Client/ResultSet/Transformer/DateTransformer.php
• src/Client/ResultSet/Transformer/TimeTransformer.php

[montaniasystemab]

Thanks for looking into it so quickly!

@jeremiahsmall Do you still need the XML response from us?

[jeremiahsmall]

@montaniasystemab, that would be very helpful since you have a system with a different date format. All we need is the top part down to metadata and showing an example date field (you can manually delete any other fields). Bonus if you have a time and a timestamp field example you can include too.

[montaniasystemab]

@jeremiahsmall Here's a redacted XML response from our server:

https://gist.github.com/montaniasystemab/5fa0d64c4f9a2040a902da10ff269d6b

Unfortunately there's no timestamp field and I'm not a FileMaker developer so I'm not sure how to add another field.

[jeremiahsmall]

Ok, well the xml you provided doesn't show an alternate format in the data source. You have one type "calculation" and two type "normal" fields. In other words, they are not date type fields. They appear to be custom formatted string results. So my proposed dynamic formatting wouldn't help in your case. Two things you could do here: let them be hydrated as strings, or change them to dates in FileMaker.

[jeremiahsmall]

NB, a type "calculation" should be fine, as long as it's returning a date result.

[jeremiahsmall]

Gah! I'm on a mobile device and scanned it too fast. It's result attribute, not type that matters, and your data shows that as date. Is that xml directly copied from your host? I am confused now.

[montaniasystemab]

That XML is the response of a findBy() call to a generated repository.

I've gathered the XML document by doing this:

1. Adding a logger to the Connection object
2. Using the Console Command that results in the error mentioned in the description
3. Getting the request URL from logs and manually calling it with curl

The request URL looks like this:

http://server/fmi/xml/fmresultset.xml?-db=File.fmp12&-lay=layout&x=...&y=...-find

Hydrating them as strings would work just fine for our use case, I tried adding type="string" in the entity metadata file but it still gets hydrated as a date. Any ideas how to force them to be hydrated as strings?

Thanks!

[DASPRiD]

Chiming in here. There are actually several parts working together here. For once there is the XML-based repository, where you define specific types. That repository is getting its data from the result set client though, which sees a field definition with a date result and will accordingly transform it into a DateTimeImmutable object.

Looking through everything posted so far, it is indeed quite weird that those are defined as date results, but don't match the date format defined in the data source. I'm no Filemaker developer myself, so I have no idea how that would be possible.

The only thing which strikes my eye is that the date data are actually standard American dates, but with a dash instead of a slash (so that's not an ISO date or any locale date I've ever seen). Without knowing what exactly is going on there, I can't see how we'd properly fix the problem.

My current best guess is that the date and time separators are actually not important and can be changed to whatever one dislikes. If that'd be the case, we'd have to write a more sophisticated parser. But as I said, before we can write a proper fix for that, we'd have to figure out what's going on there.

[DASPRiD]

Also, just doing a quick Google search, I found this: https://community.filemaker.com/message/301320#301320

[jeremiahsmall]

@montaniasystemab did you find a solution? To hydrate them as strings, you could try one of two things in FileMaker:

1. Change the field type to text
2. Create a read-only (calc) field which re-casts the existing field as text, and reference that instead.

[montaniasystemab]

Sorry for the late reply, it's been holiday season here in Sweden =)

@jeremiahsmall I decided to add the format used by our FileMaker Server in DateTransformer because the project was about to go live.

I've not been able to figure out a solution which does not require changes in DateTransformer

Unfortunately changing the types in FileMaker is not an alternative since there's other integrations in place as well that we're reluctant to break/change.

[jeremiahsmall]

With the upcoming v6 of SimpleFM, this will no longer be an issue, because we no longer use the metadata directly from FileMaker (they don't return it in the Data API, so it's not an option anyway). Now you define your own field mapping. So now one can now just use their own date transformer within the repository builder.