API_addrecord copying date in wrong format to new record

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • Acknowledged
Hi QB Community,

We are using an API Call to create a new work package record using data from a SOW record, date on newly created work package appears to have pasted the date in American format or is being read by the system as an American date:

SOW record shows: 02 MAR 2018
New Work Package shows: 03 FEB 2018

App properties:
Identifies that all dates should be in DD-MM-YYYY format

Talking to Quickbase support that have said this is due to the API_Addrecord function using the import tool that exports in US format apparently and there only suggestion is to use formula fields to reformat the dates. for instance ToFormattedText([your date field],"MMDDYYYY")

This unfortunately isn't a workable solution (and i have advised support as such), we would have multiple problems with this approach one being we want editable fields in both forms another being the number of additional fields that would have to be created. While we have requested support treat this as a priority issue we are seeking advise from the community.

Does anyone have any suggestions on how to handle this is a very minimal way?

(FYI this is a severity one for us, we need to have a release for use due Monday Aus EST)
Photo of Joshua Tate

Joshua Tate

  • 1,016 Points 1k badge 2x thumb

Posted 10 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
I’m not quite sure what you are asking here, but why not just use a a formula to extract out the DD MM and YYYY from your source data by converting it to Text and parsing out the DD MM YYYY and then feeding the American Date format into the API AddRecord.

Ie, convert the data to an American Date format. Using Date(yyyy,mm,dd).
Photo of Joshua Tate

Joshua Tate

  • 1,016 Points 1k badge 2x thumb
Ok so Quickbase has date properties to display in the format of "DD MM YYYY" which we use, these on our forms but the data is actually stored in the javascript format of MM DD YYYY etc so when the API is called to create a new record it pastes the data into the date field in the new record as if it was MM DD YYYY but because the data is displaying as DD MM YYYY the system now has the date as the day and month as the day.

If i was to do as you suggested nothing would change as the data is already stored as the american format - its how you handle it on the new record that matters. If i did put in the effort to convert the date to text, push that text to the new record - how do i without having to open the new record get it to move the text date into a editable date field?

We are talking about 100+ fields in different tables this would have to apply to.. so any solution would i feel need to be minimal especially given future development work we plan to complete.

thanks
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
Ok sorry, I have little direct exoerience with DDMMYYYY date formats
Photo of Joshua Tate

Joshua Tate

  • 1,016 Points 1k badge 2x thumb
Thanks anyway
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
If you are saying that the API fails to work in a DDMMYYYY environm3nt, then that sounds like a clear bug and you should escalate the best you can. I have clients and contacts in Australia who use DDMMYYYY

Contact me via my website QuickBaseCoach and I will share the Contacts. They can also make the bug case with you to increase your voice.
Photo of Joshua Tate

Joshua Tate

  • 1,016 Points 1k badge 2x thumb
Thanks - Have reached out - I have escalated to all avenues i presently have so appreciate anything you can do to help.
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 9,214 Points 5k badge 2x thumb
Hi Joshua,

We are sorry to hear that this issue you have run into with the date formatting and API exporting is causing you so many problems. It is something that has been put into our intake for our PD team. I know that your previous Rep gave the suggestion for formatting the data after it is imported into your destination table via formulas which doesn't quite work for you. Alternatively, I was going to suggest creating a formula text field in your starting table that pulls your date out and formats it in the DDMMYYYY format as text. I haven't tested it in a mixed environment but I'm thinking if you then bring over that text element via your API calls it and push it into your date field in your destination table it may hold the DDMMYYYY  formatting instead. I did this in a test app using a formula text field and the below formula:

ToText(Day([Last Day of Month Check]) &"/" & Month([Last Day of Month Check])& "/" & Year([Last Day of Month Check]))

This should let you leave your destination field as editable. At least as workaround as you are working with a time table. I hope this suggestion is helpful Joshua. 
Photo of Joshua Tate

Joshua Tate

  • 1,016 Points 1k badge 2x thumb
Thank you - i will give this a try but ultimately i cant be creating another field for every date.
Photo of Joshua Tate

Joshua Tate

  • 1,016 Points 1k badge 2x thumb
Hi Evan, I forgot to mention, this suggestion worked. I have put far more time than i would have setting up a text formula field for ever date and relinking with the scripts but this at least got us to the end.
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 9,214 Points 5k badge 2x thumb
I'm glad the suggested work around at least worked to keep your current workflow going. It is a lot of work to make the necessary adjustments depending on how much data you are passing along that needs to be converted. I have passed this feedback up via our internal intake as a place to enhance the date fields and settings.
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
You have to account for the time zone of the data center when using the API, and have to convert to unix time as well. 

Otherwise you get the day difference depending on if it runs early morning or late evening.

(Yes I know, its ridiculous)
Photo of Joshua Tate

Joshua Tate

  • 1,016 Points 1k badge 2x thumb
The difference isn't a day, the difference is the dates are being input as american so its a month out. Whats strange is the dates are fine when the day is above 12 but not lower. 
Photo of Paul Vorster

Paul Vorster

  • 556 Points 500 badge 2x thumb
HI Joshua, i had the same problem, and this formula resolved my problem...

"&_fid_34=" & Right("0"&ToText(Day(ToDate([Current Date and Time]))),2) &"-" & Right("0"&ToText(Month(ToDate([Current Date and Time]))),2) &"-"& ToText(Year(ToDate([Current Date and Time]))) &" "& ToText(Hour(ToTimeOfDay([Current Date and Time]))) &":"& ToText(Minute(ToTimeOfDay([Current Date and Time])))