Calculating the days in between two dates

D365FO
1

Hi! I need your suggestion for the following request: Calculate the days between two dates at the template level in an Xpath expression.

To simplify the expression, I first tried creating a formula based on two dates from the same CollectionLetterLines record:

@CustCollectionTrans_TransDate and
@CustCollectionTrans_DueDate

But in the end, I need to calculate the day difference between the two dates below:

CollectionLetterHeader/@CollectionLetterDate and
CollectionLetterLines/@CustCollectionTrans_DueDate.

image
image911×244 7.25 KB

In the attached template, I used a formula that creates a number out of both dates so that I can subtract them:

number(concat(year(@CustCollectionTrans_DueDate),
format(number(month(@CustCollectionTrans_DueDate)),‘00’),
format(number(day(@CustCollectionTrans_DueDate)),‘00’))) -

number(concat(year(@CustCollectionTrans_TransDate),
format(number(month(@CustCollectionTrans_TransDate)),‘00’),
format(number(day(@CustCollectionTrans_TransDate)),‘00’)))

As you can see, it doesn’t do the job:

image
image880×445 24.8 KB

The correct difference between the dates is 45 days. Where am I going wrong? Is there something I’m missing?

I noticed some time and date functions that I’ve also tried to use, but none of them work for my case.

image
image898×752 52.5 KB

I did my tests on the attached Replica template:
CustCollectionJour.Report_DateDifference_1.docx (83.3 KB)

Thank you very much for your help.

2

Hi Michael,

Calculating the date difference on the template level is not possible as the Xpath language we use does not support this.

How to calculate day difference in X++

However, it is possible to do this by introducing a custom data field in X++, so let me provide more details about this approach.

First, we need to create a new data field in the DocCustCollectionJourReportDSP class:

_addingRecord.addCalculatedField('DaysBetween', _currentLine.CollectionLetterDate - _currentLine.CustCollectionTrans_DueDate);

image-20221214145757-1
image-20221214145757-11241×604 58.5 KB

Once you have this in place and generate a new data source file (DDSP), it should contain the newly created @DaysBetween data field:

image-2
image-21242×323 32 KB

The last step is to add this field to the template you use for this report.

image-4
image-4974×596 21.2 KB

And here are the results:

image-3
image-31265×634 40.9 KB

I am attaching the test template below:
CustCollectionJour.Report_DateDifference_3.docx (77.5 KB)

How to compare dates in Xpath

The approach you tried to use by converting dates to numbers is only applicable for date comparisons (e.g., which date is greater), and you can see some examples in the attached template.

I used your formulas in the first three columns to compare the two dates, and the comparisons are working, as you can see.

image
image1749×471 58.3 KB

The last two columns use a slightly different approach to using the format() function, formatting the date as yyyyMMdd:

number(format(data-source(‘MainData’)/CollectionLetterHeader/@CollectionLetterDate, ‘yyyyMMdd’))

To read the value of the @CollectionLetterDate from the current context of the List tagging element, we need to introduce the data-source() function.

Finally, the number() function converts the date formatted as yyyyMMdd into a number.

As you will see in the last column, the Xpath expression to get the value of the Due date converted to a number is:

number(format(@CustCollectionTrans_DueDate, ‘yyyyMMdd’))

The entire Xpath expression (i.e., comparison) now reads as follows:

number(format(data-source(‘MainData’)/CollectionLetterHeader/@CollectionLetterDate, ‘yyyyMMdd’)) >
number(format(@CustCollectionTrans_DueDate, ‘yyyyMMdd’))

And here are the results:

image
image1459×253 19.9 KB

Please let me know if this has helped.