Dates do not sort correctly when viewed using Reports

  • Profile Image
    AgilityAssoc.Canada
    Asked on February 28, 2018 at 09:04 AM

    Hi,

    I have a report, https://www.jotform.com/grid/72766002808053 , for 52745887899988. 

    When I try and sort by a date column, Trial Date, I get strange listing result weather I sort Ascending or Descending. The sort is not by year but by day and sorts 1, 10, 11, ~ 19, 2, 20 etc. Very strange.

    Why is this occurring? 

    Regards, Robert

  • Profile Image
    Richie_P
    Answered on February 28, 2018 at 11:29 AM

    I cloned your form and tested some submissions.  The Trail Date ascending or descending, seems to be working at my end.

    My sample date us 3 Mar,16 is 2016.


    Can you please give us a sample screenshot of the issue?

    -How-to-add-screenshots-images-to-questions-to-the-support-forum

    We will wait for your response.


  • Profile Image
    AgilityAssoc.Canada
    Answered on February 28, 2018 at 01:12 PM

    Hi,

    Please view my report and try sorting by any of the 3 date columns and you will see what I'm referring to. The image shows sorting by the Trial-Date column.

    https://www.jotform.com/grid/72856207008052

    1519841422wrongdate.png

    Regards, Robert

  • Profile Image
    AgilityAssoc.Canada
    Answered on February 28, 2018 at 01:14 PM

    I think I know why, it see the values as text and not as dates.

  • Profile Image
    Richie_P
    Answered on February 28, 2018 at 01:54 PM

    Further investigation, and it seems you are right, the order of the sort see the values of your Trial-date as a Text.

    You are using the mini date picker widget, May I suggest changing the Date Format to Full for the sorting to work properly.

    Here is my screenshot, sorted to descending order.

    Hope you find this information useful.

    Please let us know if we can be of further assistance.




  • Profile Image
    AgilityAssoc.Canada
    Answered on March 01, 2018 at 09:44 AM

    I have looked at your image over and I don't see that the results are correct. What about the values below 14 April 2017, they are not correctly sorted. 

    The form date fields are using mini date picker, as it is the only way that Google sheets see the values as dates and not text. Before I found this option my sheet integrations never showed the correct date. I am very reluctant to change the format. The old adage, "If it isn't broken don't fix it" applies here. 

    I will try this with a clone.

    I would like to suggest you pass this on to the next level. The Report tool should not be doing this.

    Thank you for your help. 

    Robert

  • Profile Image
    AgilityAssoc.Canada
    Answered on March 01, 2018 at 10:30 AM

    I created a clone and set date fields to all four options in mini date picker. The only option that appears to work is the ISO 8601 - mm/dd/yy. Other options post data as text and do not sort correctly.

    Form 80594248410961, Report https://www.jotform.com/grid/80594486310055

    1519917957DateTEST.png 

    Note that Trial Date column is sorted Ascending and sorted by day name, S,T,W.

    The Post Approved Date column is ISO 8601 and will sort correctly. Please feel free to use my form for any further testing.

    My sheet integration shows all column dates as dates, so that works perfectly.

    I hope this information is helpful. 

    Regards Robert

  • Profile Image
    Richie_P
    Answered on March 01, 2018 at 10:56 AM

    I'm sorry for the confusions, yes, its seems that ISO 8601 sorts correctly, while other Date Formats will not.

    I have forwarded this issue to our second level ,so that they can check it out further.

    We will let you know via this thread if updates are available.

  • Profile Image
    AgilityAssoc.Canada
    Answered on March 01, 2018 at 11:17 AM

    One more problem I just found with sorting. there is a column "CAT No" . It to is not sorting as a number but as text.

    It appears, to me at least, that the report creator code needs some tweaking. these reports are could be helpful if the sorting was working correctly.

    Regards, Robert 

  • Profile Image
    AgilityAssoc.Canada
    Answered on March 01, 2018 at 11:23 AM

    Hi, Sorry, I posted my last before I saw your answer. Thank you, Regards, Robert

  • Profile Image
    Richie_P
    Answered on March 01, 2018 at 11:43 AM

    I have moved your question to a new thread, so that we can better assist you. We would like to have one question per thread to avoid confusion.

    Please follow this link for your question regarding the "Cat No" not sorting. https://www.jotform.com/answers/1401675

    Thank you.

  • Profile Image
    AgilityAssoc.Canada
    Answered on March 08, 2018 at 09:57 PM

    Hi,

    I see the second issue was resolved quickly. Be nice if this to was resolved. I had pointed this problem out some time ago not just this time

    I noted that you changed the title of this thread to say Mini Date Picker was causing the problem. I must correct, most date tool do not work properly not only Mini Data Picker.

    I can demonstrate how it fails with any date option.

    Regards, Robert


  • Profile Image
    ashwin_d
    Answered on March 09, 2018 at 04:18 AM

    My colleague have already reported this issue to our backend team. As per the ticket status, it is already assigned and one of our developer is looking into this issue. Though we cannot provide an ETA on this, we will get back to you as soon as we have any update.

    I also tested this issue by adding a "Date Picker" field in my form and the Grid report shows the data correctly. Please check the screenshot below:

    1520587062dateinGridRep.png

    I would suggest you to please help us replicate the issue in other fields, we will report it to our backend team.

    We will wait for your response.

  • Profile Image
    AgilityAssoc.Canada
    Answered on March 09, 2018 at 09:07 AM

    Hi,

    Note on your demonstration, you never use a month value greater then 6. try doing the same thing  adding some month values like 1, 2, 3, 10, 12. You will likely see the sorting look very different. It sorts by the first character so, 1, 10,12, 2,3  and so on. I have demonstrated this many times and when a JF rep like your self makes a test they never get the results I show because it is not understood how the date is represented. It is not a date it is text so it will not sort as a date.

    I think the backup team is also not seeing the replication as the test are limited in the values entered and the number of submissions. I really think 25 or more with years between 2012 and 2019 with all 12 months represented a number of times will demonstrate the problem clearly. because I have hundreds of submissions I see it clearly.

    Please feel free to clone my form with the 4 date fields, and if possible even 10% of the submissions and change the date fields input options to any options you have. The only option that is guaranteed to work is  ISO 8601. 

    I need a champion on the JF team team to understand this issue completely and carry it forward.

    It took over a year for the issue of sorting number columns to be resolved when I finally convinced JF what was happening and it was related to this issue in that the numbers were be interpreted as text.

    This issue with dates also occurs when an integration with sheets is made. Again I have been trying to convince your team that a problem exists with no success. The only tool I have found, to-date, that works with sheets is Mini Date Picker

    One of the problems I've had with resolving this is there are too many cooks, JF team members, trying to convince me that it is working properly. If I could work with one JF rep., like yourself, we could resolve this once and for all. 

    The way it stands right now I cannot use the reports tools to give my users the data they need with sorting ability.

    I would like for you to forward this to your manager, sorry I forget her name, and I will explain in great detail, again, how I can help resolve the problem. 

    Please Help,

    Best Regards, Robert

    See image below for table report and note the error in sorting the (TD) column.

    https://www.jotform.com/table/72826043108048

    1520607336sorting-dates.png

  • Profile Image
    ashwin_d
    Answered on March 09, 2018 at 10:09 AM

    Sure, This ticket is already escalated and as per your request, I will forward this thread to our support manager as well but before that let me try and replicate the issue in the Grid report. Accordingly, I will escalate this issue as well. 

    I have added records for each month and my grid report shows the date shorted correctly. Please check the screenshot below:

    1520608055dateinGridRep.png

    I would suggest you to please go head and add the dates yourself in the following form:   https://form.jotform.com/80671890488975 

    Grid report:  https://www.jotform.com/grid/80672027288057 

    I would request you to please help us replicate the issue with date picker and we will we update assigned developers on this.

    We will wait for your response.

  • Profile Image
    AgilityAssoc.Canada
    Answered on March 09, 2018 at 11:31 AM

    Thank you for your demonstration and you are correct it is functioning as it should. I cloned it and added more date fields and it sorted correctly also.

    I'm going to create a new report with my data and the try sorting it again. I will let you know the results.

    Robert

  • Profile Image
    AgilityAssoc.Canada
    Answered on March 09, 2018 at 11:43 AM

    OK, new report, https://www.jotform.com/table/80674609797070 , The 4 date fields do not sort properly. I use the Mini Date Picker for these fields only because it is the only way that I can get the integrated sheets to record the dates correctly. I cannot try changing these fields in my form, 52745887899988, to use another date picker as it will really mess up, brake, my integrations for this form.

    Is there a way if I clone the form to also clone the submissions? Then we can do a great deal of testing. Is this something you could do from your end?

    Thanks Robert

  • Profile Image
    Richie_P
    Answered on March 09, 2018 at 12:23 PM

    Unfortunately, I cannot open the link you have given.

    I tested your form again and the dates seems to be sorting correctly.

    The only method we have for cloning submissions is through the import feature.

    Import-App-Easily-import-your-Excel-or-CSV-data-into-JotForm

    You need to clone the form, then download a copy of your submissions to a CSV file.

    Hope this helps.


  • Profile Image
    AgilityAssoc.Canada
    Answered on March 09, 2018 at 12:46 PM

    Strange the link is not to a protected report. try this one.

    https://www.jotform.com/table/80675215725055

    I will do as you suggest and clone the form and create a CSV, then import.

    this will have to be later today... I am going out on the beach for a while... so sad.

    Robert

  • Profile Image
    Richie_P
    Answered on March 09, 2018 at 01:01 PM

    It seems I need to login to your account to see the grid report, it seems the dates are sorting correctly until I keep on sorting them and then it sorted incorrectly.

    The issue has been escalated already, we will update your via this thread if there are news on the fix.

    Thank you.



  • Profile Image
    widgets
    Answered on September 13, 2018 at 04:18 AM

    Hi we apologize for the very late response. Unfortunately the grid only sort ISO dates as of the moment. The date format of your widget appears to be just a normal string because it cannot understand the format, so it just sort out based on its characters in order. We will investigate this issue further and we will try to implement the said behavior.

    Regards,
    Widget Team