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)
Question
API_addrecord copying date in wrong format to new record
This topic has been closed for replies.
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).
Ie, convert the data to an American Date format. Using Date(yyyy,mm,dd).
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
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
+17Hi 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.
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.
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)
Otherwise you get the day difference depending on if it runs early morning or late evening.
(Yes I know, its ridiculous)
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).
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?
Any solutions to this?
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.
~d#f
Put the field ID in place of the x and the date should come out in the correct format.
Sign up
Already have an account? Login
Login to the community
Login with Quickbase US – quickbase.com Login with Quickbase EU – quickbase.eu
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.