Using Previous with Variable/Formula

[Former Member]

I have one formula (formula1) that does some calculations and works well. I need to implement a second formula, which would be based on the value in formula1. I want formula2 to look something like this:

     IIf {fieldA.Value} = Previous{fieldA.Value}, IIf(formula1.Value <> Previous(formula1.value), formula1.Value, ""),"")

Each time I try that I get an error highlighting Previous(formula1.Value), saying "This field has no previous or next value".

I've tried to default the formula1 value if its the first record, I've moved the formulas around to different sections, but I can't seem to get rid of that error.

Is there a way to get around this?

Any help is appreciated!

[Former Member · Accepted solution]

the error is likely being thrown on the first record which, by design, has no previous record

try adding another line of code at the begining of your formula . . .

if onfirstrecord then "" else . . . .  .

[Former Member · Accepted solution]

That's what I thought too - I tried if onfirstrecord, which still gave me the error. I even tried if not onfirstrecord - just for fun, and still got the error.

[Former Member · Accepted solution]

hmmm . . . . curious

try replacing formula1.Value with fieldB.Value to see if the formula is the culprit.

IIf(onfirstrecord,"",

     IIf({fieldA.Value} = Previous{fieldA.Value},

         IIf({fieldB.Value}<> Previous({fieldB.Value}, ({fieldB.Value}, "")

        ,"")

    )

[Former Member · Accepted solution]

Nope, I used a different field value in place of formula1, and that worked without error. It was a great idea though - I hadn't thought of the error being the formula itself

[abhilash_kumar · Accepted solution]

Could you paste the code for formula1?

-Abhilash

[Former Member]

I think I found the problem. I had created formula1 as a formula, and then tried it as a variable. The testing I did from the suggestions here were on the variable. When I went back to the straight formula, and had onfirstrecord, it worked.

My mistake.

Thanks for all the help!

[JWiseman]

hi Bernadette,

this error with previous or next usually occurs when you're referencing a Summary (Sum, Maximum, etc.) or a formula that contains a Summary.

i would suggest that you look into bringing the summary in with a SQL Expression if possible and if you can't, use a Command object for your data instead of tables and have the summary brought into the Command via a sub-query.

-jamie

[Former Member]

Hi Jamie,

Thanks for the reply. Formula1 is really just a flag to determine the status of a deal based on the most current date, so I don't think its a summary issue.

The data is coming from a universe, which leaves me at a loss of how to bring it in that way. I was hoping for a way to do it in a formula.

Thanks!