Discussions

Expand all | Collapse all

API_addrecord copying date in wrong format to new record

Jack Fretwell02-14-2019 16:13

  • 1.  API_addrecord copying date in wrong format to new record

    Posted 02-23-2018 00:53
    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)


  • 2.  RE: API_addrecord copying date in wrong format to new record

    Posted 02-23-2018 01:14
    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).


  • 3.  RE: API_addrecord copying date in wrong format to new record

    Posted 02-23-2018 01:24
    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


  • 4.  RE: API_addrecord copying date in wrong format to new record

    Posted 02-23-2018 01:31
    Ok sorry, I have little direct exoerience with DDMMYYYY date formats


  • 5.  RE: API_addrecord copying date in wrong format to new record

    Posted 02-23-2018 01:57
    Thanks anyway


  • 6.  RE: API_addrecord copying date in wrong format to new record

    Posted 02-23-2018 02:02
    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.


  • 7.  RE: API_addrecord copying date in wrong format to new record

    Posted 02-23-2018 02:13
    Thanks - Have reached out - I have escalated to all avenues i presently have so appreciate anything you can do to help.


  • 8.  RE: API_addrecord copying date in wrong format to new record

    Top
    Contributor
    Posted 02-23-2018 14:30
    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. 


  • 9.  RE: API_addrecord copying date in wrong format to new record

    Posted 02-26-2018 22:19
    Thank you - i will give this a try but ultimately i cant be creating another field for every date.


  • 10.  RE: API_addrecord copying date in wrong format to new record

    Posted 03-06-2018 02:47
    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.


  • 11.  RE: API_addrecord copying date in wrong format to new record

    Top
    Contributor
    Posted 03-06-2018 15:15
    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.


  • 12.  RE: API_addrecord copying date in wrong format to new record

    Top
    Contributor
    Posted 02-23-2018 17:02
    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)


  • 13.  RE: API_addrecord copying date in wrong format to new record

    Posted 02-26-2018 22:13
    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. 


  • 14.  RE: API_addrecord copying date in wrong format to new record

    Posted 07-19-2018 12:30
    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])))


  • 15.  RE: API_addrecord copying date in wrong format to new record

    Posted 02-07-2019 11:28
    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). 


  • 16.  RE: API_addrecord copying date in wrong format to new record

    Posted 02-13-2019 14:15
    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?


  • 17.  RE: API_addrecord copying date in wrong format to new record

    Posted 02-14-2019 15:11
    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[x]#dd/MM/yyyy~

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


  • 18.  RE: API_addrecord copying date in wrong format to new record

    Posted 02-14-2019 16:13
    Thank you