Fuzzy Dates

[Doug Bank]

Is there a way to use the date field, but not require a complete date?

I am trying to capture when I bought something. For some things I have receipts and can use an exact date. However, for many others, I might only be able to come up with the month or year when something was bought. However, as long as I use the official date field, I cannot have a partial date.

If I use the date field and select Jan 26, 2018, for example, I can click on the field and remove the specific date and just leave it as Jan 2018. As soon as I click elsewhere in the form, it restores the date back to Jan 26, 2018. If I start with an empty date and just type Jan 2018, when I click elsewhere the field is set back to empty.

I suppose I could just use a text field, but even that would be annoying because if I convert an existing Date field to a text field, the data is erased. I already have 200 records, so converting to text would require me to cut and paste the existing date 200 times.

If I had my druthers, then I would prefer the date field to have an option that allowed me to override the formatting. However, I understand that you might require the dates to be exact dates because otherwise you cannot use dates in calculations.

So, what are my options?

[Daniel Leu]

I suppose I could just use a text field, but even that would be annoying because if I convert an existing Date field to a text field, the data is erased. I already have 200 records, so converting to text would require me to cut and paste the existing date 200 times.

You could use a form script to translate the date field into a text field.

Following script would do that. You just need to update date_id and text_date_id to match your form.

function Convert_Date() {

	var date_id = 'fld-f74f442f93b7499aba86170cbab5e3a1';
	var text_date_id = 'fld-87e6c288a4e042c1bbfff27849a4b224';

	// loop over all records
	for (rec of form.getRecords()){
		let date = rec.getFieldValue(date_id); // get date
		if (date){ // if date exists
			let textDate = date.toLocaleDateString("en-US", { year: 'numeric', month: 'numeric', day: 'numeric' }); // create date string
			console.log(textDate);
			rec.setFieldValue(text_date_id, textDate); // store date string
		} else {
			console.log("Empty date");
		}
	}

	document.saveAllChanges();

}

Convert_Date();

[Doug Bank]

Thanks! So if I choose to go this route, I need to be careful with the date format. I am currently using Jan 26, 2018 as I described above. It looks like this script will now use 20180126, which isn’t easily readable. Or am I getting the syntax wrong?

It would be nice if it was readable and sortable, and these seem to sort correctly: 2018/01/28 and 2018 and 2018/01? Still kind of ugly, though. Is there any reason to use – instead of / or do special characters not have any hidden meanings?

[Daniel Leu]

The format it generates is 01/28/2018 which is the standard en-US format.

If you use let textDate = date.toLocaleDateString("en-US", { year: 'numeric', month: 'short', day: 'numeric' });, it creates Jan 28, 2018. Which is very readable but not sortable.

Or for the yyyy/mm/dd format, you can use following:

let day = date.getDate()<10 ? '0'+date.getDate():date.getDate();
let month = date.getMonth()<9 ? '0'+(date.getMonth()+1):(date.getMonth()+1);
let textDate = date.getFullYear()+'/'+month+'/'+day;

You can replace ‘/’ with ‘-‘ for dash separated dates, if you wish so.

There are international standards regarding formatting of date strings. But you can use whatever pleases you.

[Doug Bank]

I do a lot of genealogy as well, and on Ancestry.com, all my dates take the format 28 Jan 2018. However, it accepts many other formats as well (though it suggests changing them to this format). What I like is that it accepts stuff like “Jan 2018” or “2018” or “Abt 2018” or even “Bef 2018”. Ultimately, that is what I would like here. Since I don’t get to decide what features are implemented in the software, I guess converting to a text field would be simplest.

Is there any reason to keep both fields for capturing the information (one field for known exact dates and another for approximations), but maybe using a calculation to merge the information and present it readably on a display layout? (I haven’t played with layouts yet, but I can see that as being one way to deal with this issue)

[Daniel Leu]

For the “28 Jan 2018” format, you can use
let textDate = date.toLocaleDateString("en-GB", { year: 'numeric', month: 'short', day: 'numeric' });. Obviously, this format is not sortable.

I guess it all depends on how you intend to use the form. Can’t really answer that. But you might want to hide the existing date field in order to preserve the original content without having to see it all the time.

[Sam Moffatt]

If you need flexibility in representation, a text field is probably the best option. Having two fields would confuse things and I think you’d want to end up with three: one for precise dates with the picker, a text field for approximate dates and then a calc or script field for display that displayed the precise date or approximate date based on which value is set. On the Mac, a layout would help with display but for data entry you’d need both fields visible. You could potentially script it but at that point you’re losing the date picker UI functionality so you might as well just use a text field.

[Doug Bank]

Yeah, I think I will eventually end up with one field or three, but for now it seems like I should go with two: 1 containing the exact dates I know and another for the fuzzy dates. I might eventually run one of Daniel’s scripts and just use the text, but for now it is just data and I feel like maybe I should accumulate a little more before I decide what is really best in the future.

[Author]

Posts