7 Replies Latest reply on Jan 6, 2016 5:28 PM by jonsperry

    Why doesn't a reference field work the same as a number in my formula?

    jonsperry

      Help! I hope I am describing this intelligibly.

       

      I have created this formula, "Amount Specific Year" -

       

          If(Year (Clients::Payment date) = Clients::Specific Year; Clients::Payment amount;"")

       

      - to allow me to search my records for payments in a specific year that I can choose by entering the year in the "Specific Year" field, which is a simple number data entry field. (It works the same whether I use the "Clients::" before it or not.)

       

      The "Amount Specific Year" field, like the "Payment date" and "Payment amount" fields, are repeating fields with 120 repetitions each.

       

      The amounts are then totaled for the chosen year using "Total Amount Specific Year" - Sum ( Clients::Amount Specific Year)

       

      I tested the "Specific Year" field to make sure that it is recording and displaying the year entered. It works correctly.

       

      But when I enter a year in the "Specific Year" field, only the 1st repeating line seems to be "seen". I displayed the first 30 repetitions of "Amount Specific Year" to see if the correlated payments were being calculated. Subsequent repeating fields, whether they be in the chosen year or not, are not being read and calculated. Only the very first repeating field. But when I enter a year in the formula in place of the calculation -

       

          If(Year (Clients::Payment date) = 2014; Clients::Payment amount;"")

       

      - it works correctly. All the repeating fields that have that year are read correctly, regardless of where they are in the set of repeating fields, and the payments are displayed correctly and then totaled correctly in "Total Amount Specific Year".

       

      In other words, a number works, but a reference to a field where that number was entered doesn't work.

       

      What am I missing?

       

      Thanks.

       

      Jon