Details about this thread
  • State Not Answered
  • Replies 5 replies
  • Subscribers 293 subscribers
  • Views 760 views
  • Users 0 members are here
Related content from around SugarClub

Reporting - more than vs less than xxx days

Philippa Grover

Hello everyone,

I need to write a series of reports for a client and I am struggling with the filters. They have a field on a Target called "Contract End Date", and they want a list of Targets with a date that is between 365 and 548 days away.

 

Support have confirmed there is no "greater than"/"less than" option in the report filter perimeters.

 

I was wondering if I could do something clever with a workflow, but don't know where to begin!

 

Does anyone have any suggestions? This is a real show-stopper for my client, so any assistance or suggestions would be gratefully received.

 

Best wishes,

Pippa

Parents
  • 0

    If you are on Sugar Enterprise or higher you have the ability to create reports based on a SQL query.

    You could use the MySQL date functions in your where clause (I assume you are on MySQL) to add the necessary logic to your query.

    where DATE_ADD(UTC_DATE(),INTERVAL 365 DAY) < contract_end_date < DATE_ADD(UTC_DATE(),INTERVAL 548 DAY)

    If you are on Professional I'm afraid you are stuck with creating a custom scheduler which in turn runs a MySQL query to get the data you need and formats it to be returned to the user/s via email using MailerFactory.... not terribly complicated but requires programming.

    Hope this helps,

    FrancescaS

Reply
  • 0

    If you are on Sugar Enterprise or higher you have the ability to create reports based on a SQL query.

    You could use the MySQL date functions in your where clause (I assume you are on MySQL) to add the necessary logic to your query.

    where DATE_ADD(UTC_DATE(),INTERVAL 365 DAY) < contract_end_date < DATE_ADD(UTC_DATE(),INTERVAL 548 DAY)

    If you are on Professional I'm afraid you are stuck with creating a custom scheduler which in turn runs a MySQL query to get the data you need and formats it to be returned to the user/s via email using MailerFactory.... not terribly complicated but requires programming.

    Hope this helps,

    FrancescaS

Children