Hi all,

So here's what we are trying to implement.  We want to have a custom field on the Account object which will either say "lost" "cold" "new" or "current", based on opportunity activity for that account.  The criteria would be as follows:

• if the account was active two years ago, but has had no activity since, the account would be "lost"
• if the account was active one year ago, but not active this year, the account would be "cold"
• if the account was created this year, the account would be "new"
• if the account had activity last year and this year, the account would be "current"

So we thought we could do this based on the opportunity activity.  We would make a new field on the opportunity called current_year_amount and populate it using this formula:

IF(
    AND(
        YEAR(CloseDate) =  YEAR(Today()),
         ISPICKVAL(StageName, "Booked" )
             )
    Amount, 0
)

Then the idea would be to roll up these amounts using a roll up field on the Account level. (We would have separate fields for each scenario, this one would be for current year amount - last year would be YEAR(Today()) -1 and etc)  However, it came to our attention that you cannot reference today() in a roll up field.  To try to skirt this issue, we created another custom field on the opportunity level, which is a currency field, whose default value would be the result of current_year_amount.  This too failed, as I believe it still references the today() function.  Does anyone have any good ideas of how to accomplish what we want to do above sans this forumula field?  Is there a way around this?  Do we have to look outside of roll ups?

Any help is greatly appreciated.  I think others would be able to benefit from something like this as well...an "account status" field which shows the health of an account right on the page.  We are not looking to do this from reporting, but from the SF interface.

Thanks in advance for any help,
Andrew




Message Edited by AndySF on 04-14-2008 12:16 PM