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

Advance Reporting on Next Step text field not updated in the last 30 days

Anthony Seghetti

Good morning,

Hoping someone can help here:

I am looking to report on a field in our opportunities module called  "next_step" this is a simple text field but I want to see a list of open opportunities where this field has not changed in the last 30 days - Is this possible I'm guessing this needs to be done through the advance reporting? Please help!

Parents
  • 0

    Hi Anthony Seghetti 

    It is possible through Advanced Report.

    In order to fetch such data you must set field next_step on Opportunities as Audited.

    Regarding the Report query you have to join tables opportunities and opportunities_audit:

    SELECT
       o.id,
       o.name,
       (
          SELECT MAX(DATEDIFF(NOW(), oa.date_created)) days
          FROM opportunities_audit oa
          WHERE oa.parent_id = o.id AND oa.field_name = 'next_step'
       ) next_step_days_last_change
    FROM opportunities o
    WHERE o.deleted = 0
    HAVING next_step_days_last_change IS NULL OR next_step_days_last_change > 30
    ORDER BY next_step_days_last_change DESC;

    Regards

    André Lopes
    Lampada Global
    Skype: andre.lampada
Reply
  • 0

    Hi Anthony Seghetti 

    It is possible through Advanced Report.

    In order to fetch such data you must set field next_step on Opportunities as Audited.

    Regarding the Report query you have to join tables opportunities and opportunities_audit:

    SELECT
       o.id,
       o.name,
       (
          SELECT MAX(DATEDIFF(NOW(), oa.date_created)) days
          FROM opportunities_audit oa
          WHERE oa.parent_id = o.id AND oa.field_name = 'next_step'
       ) next_step_days_last_change
    FROM opportunities o
    WHERE o.deleted = 0
    HAVING next_step_days_last_change IS NULL OR next_step_days_last_change > 30
    ORDER BY next_step_days_last_change DESC;

    Regards

    André Lopes
    Lampada Global
    Skype: andre.lampada
Children