Does anyone know if there is a way to change the minimum allowable date for template fields? It's currently set to 01/01/1753 (270 years in the past) but I have a customer who needs to set this much lower.
Dates can only go back to 1753. If you need to store earlier dates, then the field will need to be formatted as text.
That's disappointing. Text fields don't have the same functionality as date fields so that's not much of a workaround.
Does that minimum date increase each year? If so, what will happen to documents that have date fields in 1753 when the new minimum year is 1754?
It doesn't change, it's a fixed minimum in Microsoft SQL Server's datetime implementation. The ultimate reason has to do with the non-uniformity of calendars in that era, as Europe was switching from Julian to Gregorian.
We have some work in progress that may improve this situation. I can't say much at this point, but stay tuned.
Okay thanks Brian. In the meantime, is there an "acceptable" workaround you guys have seen in use? I'm thinking about the implications of having a year drop down field that can go as low as we want but I still think that will severely limit users who need to run a search for documents when they don't know the exact year and would want to search a range or use greater/less than operators
This customer is trying to open up part of their repository to the public to perform searches for public records and obviously we can't just use text fields and expect the public to be able to understand how to craft a query for date fields that aren't behaving like date fields if they are looking for something from a certain date period.
I haven't heard of any workarounds that customers have built. It would seem you would need to start with the proper date formatting - you need a string representation that sorts correctly, so that ranges and comparisons produce expected results. You'll get that with something like "2023-10-03" [year-month-day, with month and day padded to 2 digits], though your UI has to guide users to enter their dates in the matching format.
Yea that really only solves the problem visually though, it still doesn't have any of the necessary functionality we would need from a searching standpoint.
Public users will want the option to select a range like "Before, After, Between" then select a date and run the search. As far as I know Date fields and Number fields are the only fields where you can select an operator like that. Text fields and drop down fields are pretty much "search for this exact value" unless you know how to craft custom queries which the public users will not know how to do.
We'll just have to hope the customer is okay with separate templates for older documents that use drop downs instead of date pickers
Would separating the year out in a number field help this use case? You could give them a "Year between 103 and 1441" option and then narrow down some more with text searches.
Out of curiosity, are these only going to be years in A.D., not B.C.?
I think 1600 would be the lowest they would go and so yea maybe just having a number field for Year where they can select greater than/less than/between would still be an option.
I'm thinking from a practical standpoint, documents that are more "current" should to be able to be searched for using specific dates OR date ranges.
But older more historical documents will be much fewer in number and so broader searches should be acceptable.
I'll just have to get more information from the customer and see what they have in mind for these public records.
Thanks guys