6 Replies Latest reply on Apr 11, 2011 11:14 AM by philmodjunk

    Inconsistent result buildling calculated field with {Case( *** = (Get(CurrentDate))}

    DanielKarp

      Title

      Inconsistent result buildling calculated field with {Case( *** = (Get(CurrentDate))}

      Post

      Hey Forum World -

      Thanks in advance for any help you can lend.

      I recently noticed incorrect results on a calculated field that assigns a two-charaacter code (ex, "1A or 2B") after it compares cases based on the recency of most recent gift from the (Get(CurrentDate)) function "and" another field that contains the most recent transaction.

      Here's an example of nested case fields from the calc criteria:

      Case ( zz_RcntTransDate > (Get (CurrentDate) - 60)  and zz_RcntTransAmt > 499.99 ; "1A"
      ; Case ( zz_RcntTransDate > (Get (CurrentDate) - 60)  and zz_RcntTransAmt ≥ 250 and zz_RcntTransAmt ≤ 499.99 ; "1B"
      ; Case ( zz_RcntTransDate > (Get (CurrentDate) - 60)  and zz_RcntTransAmt ≥ 100 and zz_RcntTransAmt ≤ 249.99 ; "1C"
      ; Case ( zz_RcntTransDate > (Get (CurrentDate) - 60)  and zz_RcntTransAmt ≥ 50 and zz_RcntTransAmt ≤ 99.99 ; "1D"
      ; Case ( zz_RcntTransDate > (Get (CurrentDate) - 60)  and zz_RcntTransAmt ≥ 25 and zz_RcntTransAmt ≤ 49.99 ; "1E"
      ; Case ( zz_RcntTransDate > (Get (CurrentDate) - 60)  and zz_RcntTransAmt ≥ 10 and zz_RcntTransAmt ≤ 24.99 ; "1F"
      ; Case ( zz_RcntTransDate > (Get (CurrentDate) - 60)  and zz_RcntTransAmt  <  10 ; "1G")))))

      If today (4/8/2011) is the CurrentDate, and a donor's zz_RcntTransAmt = 500 and zz_RcntTransDate is anytime after 2/6/11, the result should return "1A".

      In my actual calculation field crtieria I have another 8 nested iterations like the above set returning values of 1A - 1G through 9A - 9G. It allows us to code donors on reply slips and then track the replies to see which segements are most responsive.

      The Problem

      FM is assigning incorrect values for many of the records. I can't find a pattern making it difficult to solve the problem. Some records are coding correctly. Other's aren't.

      A twist

      I created a test DB with exported field values to test different configuations without bogging down our live DB. In the test DB with the same Calc Field criteria, the code applies corrcetly 100% of the time. I cannot figure out why in the original DB the calc field is assigning incorrectly.

      Any ideas?

      Thank you very much.

      Daniel