Datakick Data Manager module

[haylau]

A great module that I use for many activities. Looking now to extend a bit more

I need a report that exports all orders from the previous month. I would probably do this via endpoint. The idea being that our accountant can click a link and immediately see all the orders from the previous month (actually all the orders where VAT / TAX was not paid - but I can sort that out)

What I can't work out is how to produce a report limited by date without someone with knowledge modifying the report

Can anyone think of a way of limiting the report. So looking at today's date, work out the month, then the report is filtered accordingly

For example,

any time in June the link is clicked and the report is all the orders in May

any time in October the link is clicked and the report is all the orders in September

And probabky the trickiest, any time in January the link is clicked and the report is all the orders in December

[datakick]

Hi @haylau,

thanks for the question. I'm really glad you like my module. 

Your use case can be done with a bit of 'datetime arithmetic'. Let me guide you through.

I just implemented this on my demo server, you can have a look at list named Previous month orders only:

 

When I run this list today (2020-06-02), I want to remove orders that:

• were created before the start of previous month (2020-05-01)
• or were created after the start of current month (2020-06-01)

So I will need to add two new conditions. Let's start with the second one, because it's easier

 

Filter out order created after the start of current month   

In my expressions, I will use field runtime.timestamp that contains current time. I need only a year and month parts of this date, though. To extract it, I can use function formatDate. I will use format 'Y-m' which will return year and month:

formatDate('Y-m', runtime.timestamp) 

this will return string 2020-06

This is almost what I want. All I need to do is append static string to it:

formatDate('Y-m', runtime.timestamp) + '-01 00:00:00'

this will return string 2020-06-01 00:00:00

Which is the start of current month. Now, I can use this expression to include only orders created before this date. The condition will look like this:

orders.created < toDate('Y-m-d H:i:s', formatDate('Y-m', runtime.timestamp) + '-01 00:00:00')

I had to convert string back to datetime using toDate function, and then I can finally compare it with order creation date

 

Filter out orders created before the start of previous month   

Second condition will be more complicated, because we need to figure out previous month

To do this, I will once again use formatDate function, but this time I will use format 'm' to extract month part only. As of today, this format would return string '06'. I will convert string to number 6, and then substract 1:

toNumber(formatDate('m', runtime.timestamp)) - 1

When I run this expression on 2020-06-02, it will return number 5, corresponding to previous month. However, if I run it on 2020-01-20, the result would be 0. That's not what I want, I need 12. To fix this, I can use simple if expression:

if(formatDate('m', runtime.timestamp) == '01', 12, toNumber(formatDate('m', runtime.timestamp)) - 1)

The if function takes three parameters. The first parameter must evaluate to true/false. If it evaluates to true, then the result of if function is the second parameter. If it evaluates to false, the result is third parameter.

In our case -- if month part is '01' (January), the previous month is 12, otherwise the previous month is (current month - 1)  

Now we know the month part. We also need to figure what year is previous month in. To do so, I will use formatDate function with 'Y' format:

if(formatDate('m', runtime.timestamp) = '01', toNumber(formatDate('Y', runtime.timestamp))-1, toNumber(formatDate('Y', runtime.timestamp)))

This expression reads as: if current month is January, then return current year - 1, otherwise return current year.

Now that we know year and month part, we can construct start of previous month. I can simply concatenate these expressions together:

if(formatDate('m', runtime.timestamp) = '01', toNumber(formatDate('Y', runtime.timestamp))-1, toNumber(formatDate('Y', runtime.timestamp))) + '-' + if(formatDate('m', runtime.timestamp) == '01', 12, toNumber(formatDate('m', runtime.timestamp)) - 1) + '-01 00:00:00'

The result of this expression is 2020-5-01 00:00:00

Now I can use this expression to finally create a condition to include only orders created after this date:

orders.created >= toDate('Y-m-d H:i:s', if(formatDate('m', runtime.timestamp) = '01', toNumber(formatDate('Y', runtime.timestamp))-1, toNumber(formatDate('Y', runtime.timestamp))) + '-' + if(formatDate('m', runtime.timestamp) == '01', 12, toNumber(formatDate('m', runtime.timestamp)) - 1) + '-01 00:00:00')

 

It's a little bit complicated, but fortunately it can be done.

 

 

 

 

[haylau]

Brilliant. Thanks. Well that is my evenings entertainment all lined up 😂😂