I have some workflows set up to produce PDF letters, and I’ve given the workflows a Job that pulls out the account Reference and the Date of the letter from the data to use as metadata. I’ve got a file mask to name PDFs on the fly (they are separated by document and documents are separated by Reference) and my intention is that each PDF should be named something along the lines of ‘[Reference] letter sent on [Date]’.
If I use the mask ${document.metadata.Reference} letter sent on ${document.metadata.ArrearsLetterDate}.pdf it almost works - it will come out as ‘[Reference] letter sent on [incomprehensible string of numbers]’.
The string of numbers, I noted, does correspond to the date - specifically it counts each thousandth of a second since midnight (GMT+1) starting 2nd December 1969. I’m not sure if there’s any particular relevance to that date or that way of counting, but at least it shows that the process is picking up the correct data, even if it is currently in an unusable state. So how do I format it to make the date readable?
I’ve tried ${document.metadata.Reference} letter sent on ${document.metadata.ArrearsLetterDate,dd MMM yyyy}.pdf and ${document.metadata.Reference} letter sent on ${document.metadata.ArrearsLetterDate,‘dd MMM yyyy’}.pdf but the process fails completely with those ones. And if I use ${document.metadata.Reference} letter sent on ${document.metadata.ArrearsLetterDate,“dd MMM yyyy”}.pdf the output setting won’t even save because it thinks the mask is so incorrect. I don’t necessarily need the date in a dd MMM yyyy format, so long as I have the date, month and year in there somewhere (and preferably no hour/minutes). Is there a way to format a date taken from metadata?
This is somewhat odd, since [according to our own documentation fomatting is something you can do](PlanetPress Connect 1.5 User Guide output). And, I know it’s a date type because the way you describe the “number of milliseconds since a date” is definitely a Javascript date. However, perhaps it’s as simple as the fact that you’re using MMM but I only see MM or MMMM in the docs… try changing it to one of the formats at the complete bottom of the page, and see if that works?
Unfortunately it still doesn’t work with the example formats on the User Guide, although it did give me a different error on Workflow.
Using dd MMM yyyy the error was: There was an error running the output creation process caused by ApplicationException: parse error at line 1, column 91 (near ‘MMM’) in expression ‘${document.metadata.Reference} COPY Court Action ${document.metadata.ArrearsLetterDate,dd MMM yyyy}_${file.nr,0000}.pdf’
Using dd MMMM yyyy gave me the same kind of error.
However, using yyyyMMdd, ‘yyyyMMdd’, ‘dd MMMM yyyy’ or ‘dd MMMM yyyy’, nl gives me an error along the lines of There was an error running the output creation process caused by ApplicationException: cannot format value of class ‘java.lang.String’ at line 1, column 52 in pattern ‘${document.metadata.Reference} COPY Court Action ${document.metadata.ArrearsLetterDate,yyyyMMdd}.pdf’
Using double quotations " or a colon : in the mask prevents Designer from saving the output preset, although these characters are included in examples of the formatting on the User Guide.
I also tried using ${document.metadata.ArrearsLetterDate,nl} to get a default date but while it would actually process the PDFs with this mask on, they again came out with the millisecond count in the file name instead of a date.
Regretfully I’m not the best person with Javascript so these error messages don’t make much sense to me, any help deciphering the problem would be appreciated.
When you try the second one, it will give you “19700101-01-00-00”. So, the date is actually the number of milliseconds since January 1, 1970 (so you were pretty close with 2nd December 1969).
To take the date from a metadata field as you are trying to do, you either need to have your value as milliseconds since January 1, 1970 or a string formatted in a way that JavaScript can parse it. The millisecond value, should not be that hard when using a bit of JavaScript in the data mapper. The formatted string should be in a format recognized by the Date.parse() method (IETF-compliant RFC 2822 timestamps and also a version of ISO8601).
There is 1 snag: the preset config checks the file name box for illegal characters, so it will cause you trouble when you try to use characters such as : " or ,
To avoid that, you either have to avoid those characters, or you can put that formatting string also in metadata.
So you can try something like this: ${new Date(document.metadata.ArrearsLetterDate), dd MMM yyyy}.pdf
Thanks for this, recalculating the numbers I was getting before I realise it does indeed count from 1 Jan 1970 (I think perhaps I didn’t account for leap years before and of course we’re in British Summer Time which is GMT +1 so that might explain that as well), so ${document.metadata.ArrearsLetterDate} is putting out the parsed date already.
I’ve now got it to output a date format - ${new Date(0), ‘dd MMM yyyy’} gives me 01 Jan 1970. I can see if I type the numbers into the mask e.g. ${new Date(1475276400000), ‘dd MMM yyyy’} it will come out with the appropriate date e.g. 01 Oct 2016.
So, I would think that your suggestion above with my slight format modification using ${new Date(document.metadata.ArrearsLetterDate), ‘dd MMM yyyy’} should do exactly what I want - but when I put it in as above it comes out 01 Jan 1970, suggesting that it is not picking document.metadata.ArrearsLetterDate up as a value (or as a value of 0). This is even though when I use a mask of ${document.metadata.ArrearsLetterDate}, in the data I’ve been testing today for example with a letter date of 01 Oct 2016, it appears as 1475276400000 (which as noted above should return the correct date).
I think that I’m almost there and this just requires a bit more fiddling with the formatting - I’ve tried ${new Date{document.metadata.ArrearsLetterDate}, ‘dd MMM yyyy’} and ${new Date${document.metadata.ArrearsLetterDate}, ‘dd MMM yyyy’} but they just throw out an ‘invalid expression’ error.
I’ll continue to hammer at it but if anyone can see the solution to what appears to be the last thing I’m missing that would be excellent.
The last two things you mention you have tried (using ${} or {} inside the ${}) will not work; these things can’t be nested. But you already found that out.
I should have tried using a metadata field myself: the metadata fields are strings and don’t get interpreted as numeric by themselves. So, we have two ways forward:
Use parseInt() on the field: ${new Date(parseInt(document.metadata.ArrearsLetterDate)), ‘dd MMM yyyy’}
This is the solution if the field you are using is of type Date in the data model.
Or we can make sure the field is already formatted as a date string that is a valid parameter for Date().
This is done in data mapper.
If your input already has the “milliseconds since 1-1-1970”, you can switch the field to a JavaScript field and use something like this: var myDate = data.extract(‘myInputColumn’,0);
new Date(1475276400000).toDateString();
The part in blue will look different if your input is not CSV, but data mapper takes care of that when you switch to a scripted field.
If your input has some text representation of a date (this is the most common case), you can keep the Date field, but create an additional String field in your data mapper and set its value from the existing Date field: record.fields.myDateField.toDateString()
Now you can use ${new Date(document.metadata.ArrearsLetterDate), ‘dd MMM yyyy’}
in your output preset, without a need for parseInt().
Ah! ${new Date(parseInt(document.metadata.ArrearsLetterDate)), ‘dd MMM yyyy’} works - the field is set to a date in the datamapper, although the tips you’ve provided about scripts in data mapper might be useful to me elsewhere!