Author Topic: EmployeeAdd - date conversion  (Read 3741 times)

david1000

  • Member
  • *
  • Posts: 21
EmployeeAdd - date conversion
« on: June 14, 2012, 01:21:05 AM »
When adding an employee the birthdate supplied is changed in the resulting xml query.
I supply the following field in an EmployeeAdd process - the field is formatted yyyy-mm-dd as outlined in the OSR for QB UK version (which applies to the AU version).
The field value in Filemaker is: 1963-04-07

PCQB_RqAddFieldWithValue( "BirthDate" ; staff::birthdate)

The xml query created has the following result:

CODE: -1000
SEVERITY: failed validation
MESSAGE: Error at BirthDate
Reason:   Union doesn't support this value.
The element 'BirthDate' with value '2007-04-1963' failed to parse.

Field Value:   2007-04-1963

The xml sent looks like this (n.b. with most field data removed):

<?xml version="1.0" encoding="ISO-8859-1" ?><?qbxml version="6.1" ?><QBXML><QBXMLMsgsRq onError="stopOnError"><EmployeeAddRq><EmployeeAdd><Name></Name><IsActive></IsActive><Salutation></Salutation><FirstName></FirstName><MiddleName></MiddleName><LastName></LastName><EmployeeAddress><Addr1></Addr1><City> </City><PostalCode></PostalCode></EmployeeAddress><Phone></Phone><Mobile></Mobile><AltPhone></AltPhone><Email></Email><BirthDate>2007-04-1963</BirthDate></EmployeeAdd></EmployeeAddRq></QBXMLMsgsRq></QBXML>

Anybody want to take a guess as to the likely cause? :-)

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: EmployeeAdd - date conversion
« Reply #1 on: June 14, 2012, 05:14:22 AM »
Are you wrapping the field in PCQB_SFormatString?

PCQB_RqAddFieldWithValue (“BirthDate” ; PCQB_SFormatString ( staff::birthdate ; "DATETYPE," ) )

A date field's format affects its layout representation and data entry characteristics, but not the way FileMaker stores and works with the date value.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852

david1000

  • Member
  • *
  • Posts: 21
Re: EmployeeAdd - date conversion
« Reply #2 on: June 14, 2012, 04:51:45 PM »
Thanks for your reply Geoffrey and I thought that must be the issue.
However, I included the code as you outlined:
PCQB_RqAddFieldWithValue (“BirthDate” ; PCQB_SFormatString ( staff::birthdate ; "DATETYPE," ) )

I tried both with and without the comma which I thought may have been a typo - but the result was the same. It seems that no matter what I do the response adds '20' in front of the day.

I have tried the following:
Filemaker date field which looks like this "11/09/1983" - converts to 2011-09-1983. 
Filemaker text field (calculation) which looks like "1983-09-11" converts to "1983-09-2011"

and for something different a Filemaker text field (calculation) which looks like "11-09-1983" converts to "2011-09-1983".

So the issues seems somehow related to the day but I cannot see any reason why.
Hopefully, it is not due to my blindness ;-)

david1000

  • Member
  • *
  • Posts: 21
Re: EmployeeAdd - date conversion
« Reply #3 on: June 14, 2012, 05:15:33 PM »
... in addition to my previous post ...

I have now discovered this issue occurs even if you hard code the date.
For example:

PCQB_RqAddFieldWithValue ("BirthDate" ; "1963-04-07" )

returns:  Field Value:2007-04-1963

PCQB_RqAddFieldWithValue ("BirthDate" ; PCQB_SFormatString( "1963-04-07" ; "DATETYPE" ))

returns: Field Value:1963-04-2007

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: EmployeeAdd - date conversion
« Reply #4 on: June 15, 2012, 06:40:14 AM »
The comma was a typo. This...

PCQB_SFormatString( "1963-04-07" ; "DATETYPE" )

...will fail because it's trying to operate on a Text string. I'm wondering if the problem you're seeing is related to an internal mixup in how FMP is storing the date field value in your file. FMP stores all date values the same way, but presents them using the format options appropriate to the localized environment. Is your solution file "localized" to the UK/Australia?

Try creating a new sample file called "test" and add a Date field called "testDate" to it. Create one record and enter a date, then commit the record. Now see what result is returned by...

PCQB_SFormatString( test::testDate ; "DATETYPE" )

The newly created file should be using the local format for the Date, and I'd expect a valid result.

david1000

  • Member
  • *
  • Posts: 21
Re: EmployeeAdd - date conversion
« Reply #5 on: June 15, 2012, 07:33:49 AM »
Yes, I have misunderstood the PCQB_SFormatString function. I assumed it converted the data to a date format which is obviously wrong.

Localization could be the issue as my file is set to use system settings and this would be set to the standard Aust format (ie ddmmyyyy).

I assume you are suggesting it may be this format causing the problem.  If this is the case, I may have inadvertently created a file structure which will enable me to overcome the problem. That is I am using separate file which facilitates transactions between the main database and QB. This would mean I could set the linking file to non system dates. I will give it a go and let you know the outcome.  Again, thanks for your help.

david1000

  • Member
  • *
  • Posts: 21
Re: EmployeeAdd - date conversion
« Reply #6 on: June 15, 2012, 08:26:15 AM »
Creating a file which has US dates when everything is set to Aust settings turns out to not be as easy as you would think. It appears the system is too smart and automatically resets the document even when things like use file format is set.  Fortunately, I was able to use the FM Books example file which is from the US and has US dates.

I created an EmployeeAdd function which added an employee and included a birthdate using the Filemaker date field (US format). The result was successful and the date in QB has the Aust format. 

Possibly the biggest issue will be converting the current file to a US formatted file. I will most likely alter another machine to have US localization and use this to convert the file. I will change the settings to 'Always use file's saved settings'. This should enable it to retain the US formatting. 

Thanks once again Geoffrey - you certainly know what your talking about.

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: EmployeeAdd - date conversion
« Reply #7 on: June 15, 2012, 11:03:19 AM »
I was thinking the problem might be coming from a file that was US-localized but somewhere along the line the user opened it with a "non-US" version of FMP and had selected "Use Local Formatting."

What happens if you create an OZ-localized file? I would have expected PCQB_SFormatString( test::OZDate ; "DATETYPE" ) to work, too.

I've worked with client files that I'm fairly sure were created with a UK-localized version of FMP but have never run into the problem you've described.

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852

david1000

  • Member
  • *
  • Posts: 21
Re: EmployeeAdd - date conversion
« Reply #8 on: June 15, 2012, 05:42:44 PM »
I started a totally new file with a standard date field to see what would happen (just to be sure).
I used the following function within an EmployeeAdd process:
PCQB_RqAddFieldWithValue( "BirthDate" ; PCQB_SFormatString( Test_Dates::Date ; "DATETYPE" ))
The value in the field was: 25/12/1965  (the Aust format as provided by the system settings)
The result was: <BirthDate>2025-12-1965</BirthDate>

What I outlined previously may be a work-around but not exactly the ideal solution.

Ritesh Gupta

  • Member
  • *
  • Posts: 13
Re: EmployeeAdd - date conversion
« Reply #9 on: October 10, 2013, 05:58:59 AM »
Hi
I am getting same issue...I have changed the date format in local file...
Again I getting the same error...

<?xml version="1.0" ?><!DOCTYPE QBXML PUBLIC '-//INTUIT//DTD QBXML QBO 6.0//EN' 'http://webapps.quickbooks.com/dtds/qbxmlops60.dtd'><QBXML><QBXMLMsgsRq onError="stopOnError"><EmployeeAddRq><EmployeeAdd><Name>Tyler Perry</Name><FirstName>Tyler</FirstName><LastName>Perry</LastName><SSN></SSN><Email></Email><HiredDate>2002-2020-2011</HiredDate><Phone>334-669-4381</Phone></EmployeeAdd></EmployeeAddRq></QBXMLMsgsRq></QBXML>

Please help me..
Thanks in advance

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: EmployeeAdd - date conversion
« Reply #10 on: October 10, 2013, 08:59:54 AM »
This may be an issue where the plug-in is not parsing the elements of a date value correctly for non-US formatted dates. You should be able to create an acceptable value by calculation:

Let ( x = TO::YourDate ; Year ( x ) & "-" & Right ( "0" & Month ( x ) ; 2 ) & "-" & Right ( "0" & Day ( x ) ; 2 ) )

which should evaluate to a date formatted as YYY-MM-DD.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852