Forum Discussion

JoshuaTate's avatar
JoshuaTate
Qrew Cadet
8 years ago

API_addrecord copying date in wrong format to new record

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)
APIs and custom code
Ask about Quickbase functionality
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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).
  • JoshuaTate's avatar
    JoshuaTate
    Qrew Cadet
    8 years ago
    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
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      8 years ago
      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.
    • JoshuaTate's avatar
      JoshuaTate
      Qrew Cadet
      8 years ago
      Thanks - Have reached out - I have escalated to all avenues i presently have so appreciate anything you can do to help.
  • EvanMartinez's avatar
    EvanMartinez
    Quickbase Staff
    8 years ago
    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. 
    • JoshuaTate's avatar
      JoshuaTate
      Qrew Cadet
      8 years ago
      Thank you - i will give this a try but ultimately i cant be creating another field for every date.
    • JoshuaTate's avatar
      JoshuaTate
      Qrew Cadet
      8 years ago
      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.
    • EvanMartinez's avatar
      EvanMartinez
      Quickbase Staff
      8 years ago
      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.
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    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)
    • JoshuaTate's avatar
      JoshuaTate
      Qrew Cadet
      8 years ago
      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. 
    • PaulVorster1's avatar
      PaulVorster1
      Qrew Cadet
      7 years ago
      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])))
  • JackFretwell's avatar
    JackFretwell
    Qrew Assistant Captain
    7 years ago
    Just raised another call about this issue.  We're using the Juiced Exact Forms but the API call used just uses US format dates and we're in the UK.  QuickBase should make it clear it doesn't support non US date formats with it's API's and the date format in the application properties only works some of the time.  (#582346). 
  • JackFretwell's avatar
    JackFretwell
    Qrew Assistant Captain
    7 years ago
    Just noticed that if a date field in format 'dd-mm-yyyy' is used in a letter (exact forms) then it is automatically converted into the US format of 'mm-dd-yyyy'. 
    Any solutions to this?
  • AnnaRayner's avatar
    AnnaRayner
    Qrew Member
    7 years ago
    In the template you create to use with Exact Forms Plus, replace the use of ~x~ where x is the relevant field id with:

    ~d#f#dd/MM/yyyy~

    Put the field ID in place of the x and the date should come out in the correct format.