3 Replies Latest reply on Oct 3, 2012 10:37 AM by philmodjunk

    Lookup value in "previous" /conditional format



      Lookup value in "previous" /conditional format


           I am trying to conditionally format a number of fields based on whether their value changes from the "previous" record or not.  If the field value is equal to the value in the record before it it must display differently than when it changes.

           Record order would be based on a "Record#" field.  I'm not sure if it matters but Record# unfortunately cannot be serialized as the record#'s will sometimes change or move around.  Record#'s are also not always sequential (skip numbers) and sometimesa are whole integers, sometimes decimals.


           One part of what I'm trying to accomlish can be illustrated with the screen shot below.  When a value stays consistant it is purple, when it changes it is a different color.

        • 1. Re: Lookup value in "previous" /conditional format

               Use Get ( RecordNumber ) to return the current record's position in the found set.

               Use GetNthRecord to refer to a field in the previous record.

               Let ( R = Get ( RecordNumber ) ; If ( R = 1 ; True ; FieldName ≠ GetNthRecord ( FieldName ; R - 1 ) ) )

          • 2. Re: Lookup value in "previous" /conditional format

                 Hi Phil,

                 Unfortunately that is where the inconsistant field names come in.  They are not always sequential, skip numbers, and sometimes use decimal values. Would there be anyway to use LookupNext Higher/lower?  Or perhaps a formula that would subtract R - .001 and then round down to the next lowest value?

            • 3. Re: Lookup value in "previous" /conditional format

                   Get (RecordNumber) does not return serial number or recordID values. It returns the record's position in the found set's sort order. These are always whole numbers and skipped values are not an issue.

                   Say you have 5 records in your current found set. If you are on the first record, Get ( RecordNumber ) returns 1. If you are on the second record, Get ( RecordNumber ) returns 2 and so forth on to 5 for the last record. If you sort your records in a new order, Get ( RecordNumber ) from the first record still returns 1.