Details about this thread
  • State Not Answered
  • Replies 5 replies
  • Subscribers 291 subscribers
  • Views 752 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

  • 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

  • 0 in reply to Francesca Shiekh

    Thank you Francesca Shiekh! In the end, I needed our developers to look at this and they managed to give me a very complicated calculation to use in a normal Sugar field. The formula is really complex, but thankfully it is working.

    It's just frustrating that there are so many limitations in the Pro edition of the reporting engine, as the vast majority of our clients are using this version and hence do not have Advanced Reports.

  • 0 in reply to Philippa Grover

    I agree with you about the Pro version, it's what I use and between the limitations with reporting and the workflow going away, I find myself with a LOT of logic hooks and schedulers, as well as external reporting such as gathering data via API from Mathematica to allow users to build their own reporting with fewer limitations.


    Can you share the formula for your field, I'd be interested in seeing it. Are you updating that formula field daily using a scheduler? I can't think how else you would keep that updated based on the current date as formula fields would recalculate only when the record is saved.

    thanks,
    FrancescaS

  • 0 in reply to Francesca Shiekh

    That sounds like a lot of effort, just for a report! Although we still don't know if/when Workflows will be removed...

    This is the formula:

    concat(ifElse(equal($fds_contract_term_c,36),ifElse(greaterThan(daysUntil($fds_contract_end_date_c),daysUntil(addDays(today(),548))),"Red",ifElse(greaterThan(daysUntil($fds_contract_end_date_c),daysUntil(addDays(today(),365))),"Amber","Green")),""),ifElse(equal($fds_contract_term_c,48),ifElse(greaterThan(daysUntil($fds_contract_end_date_c),daysUntil(addDays(today(),914))),"Red",ifElse(greaterThan(daysUntil($fds_contract_end_date_c),daysUntil(addDays(today(),730))),"Amber","Green")),""),ifElse(equal($fds_contract_term_c,60),ifElse(greaterThan(daysUntil($fds_contract_end_date_c),daysUntil(addDays(today(),1096))),"Red",ifElse(greaterThan(daysUntil($fds_contract_end_date_c),daysUntil(addDays(today(),730))),"Amber","Green")),""))

    I'll be honest, I get the logic but the formula is beyond me! One of our developers wrote it for me :-) At the moment, the client will need to manually perform a recalc as we do not know how to create a custom scheduler. Is it easy to do?

  • 0 in reply to Philippa Grover

    I see what they are doing, clever. Thanks for sharing.

    Francesca