Details about this thread
  • State Not Answered
  • Replies 8 replies
  • Subscribers 321 subscribers
  • Views 2539 views
  • Users 0 members are here

Capturing age of an opportunity when its closed won

Jaqueline Bargmann

I'm trying to calculate the age of an opportunity when the opportunity stage becomes “Closed Won”.  I used the steps as described in SugarCRM knowledge base - "Capturing the Age of Closed Won Opportunities”  yet it's not working properly. After saving the record the attached error message occurs.

I've only slightly adapted the formula to the fields we're using. 

Knowledge base:

ifElse(and(equal($sales_stage,"Closed Won"),equal($closed_won_cbk_c,false)),today(),date(""))

 

Ours: 

ifElse(and(equal($icc_probability,"closed"),equal($closed_won_cbk_c,false)),today(),date(""))

I can't figure out why the error occurs as I followed all the steps in the instruction. Is there a bug related to this?

Thanks in advance. 

  • 0

    I assume the closed won date can't save the timestamp you are trying to save on it. If it is a datetime field and you are trying to just save a date in it (or reverse), it may fail.

    If this not helps, let me know!

    Bests

    Björn Canales Pfisterer

    Technical Support Manager

    provalida GmbH

  • 0

    Hi Jaqueline Bargmann,

    Is it resolved now? If not, please try the following and let us know if it works.

    ifElse(and(equal($icc_probability,"closed"),equal($closed_won_cbk_c,false)),today(),date("NULL"))

    i.e. pass "NULL" in date() formula.

    Regards.

  • 0 in reply to Hatim Alam

    Björn Canales Pfisterer yes it's a datetime field but that's what they described in the knowledge base. Is there a way to fix it? 

    hats thanks for your idea but your formula Sugar doesn't show a closed won date at all. Do you have another idea?

  • 0 in reply to Jaqueline Bargmann

    Hey Jaqueline Bargmann,

    the knowledgebase says it should be a date field.

    Can you try it with a date field? Saving just a timestamp without hours, minutes, seconds and miliseconds can explain the sql error.

    Bests

    Björn Canales Pfisterer

    Technical Support Manager

    provalida GmbH

  • 0 in reply to Hatim Alam

    I had the same desire and resolved it by creating a field (in multiple modules) that I called something like: "Days in Process".  Then a simple addition to a Process (or a new process) was written to Add "1" to the field, Wait one day, then check the status - if it was still in the same state, I'd exit, otherwise, add "1" to the field, and loop back to the "wait one day".

    I have a number of these where I'm counting days.

    The second thing I've done for some of the modules is to have a process that captures the date when something is changed to a particular value, then when the value changes the process calculates the difference between the two dates and populates a field.

    We don't have any SQL coding in our implementation and do everything through Studio and Workflows.  I hope this helps.

    Bud Hartley | Cape Foulwind, NZ (and Oregon, USA)

  • 0 in reply to Björn Canales Pfisterer

    Björn Canales Pfisterer  my mistake, it is a datefield. I didn't know that there is another datefield which includes the time. 

  • 0 in reply to Jaqueline Bargmann

    Hi Jaqueline Bargmann 

    We were using this but when we upgraded to Sugar 9, it stopped working. What version are you on?

    If version 9, is this working fine on it?

    Thanks, KGM