4 Replies Latest reply on Feb 13, 2012 1:53 PM by AlexXander

    Lookup Record Value

    AlexXander

      Title

      Lookup Record Value

      Post

      Is there a way to make a formula return the value of a field where a criteria matches.

      So a formula to return the value in Field B where Field A=(????) , granted that Field A is all unique values. 

      Field A       Field B      (Field C)

        1                36            (43)

        2                43

        3                71

       

      So in Field C there would be a formula that states (Where Field A=2 Then return the value in Field B which equals 43)

        • 1. Re: Lookup Record Value
          philmodjunk

          In what field would you specify the matching value for Field A to use for looking up a a value in Field B?

          This looks like you need two tables. One table storing the values for Field A and B, a second table with a value for Field A on which to specify the match. Then a calculaiton field can refer to the related value in B or you can use a looked up value fields setting to copy the value or you can just put Field B on your layout based on the other table. In all cases, your relationship would look like this:

          TableShownAbove::FieldA = SecondTable::MatchValueForFieldA

          • 2. Re: Lookup Record Value
            AlexXander

            Well I  guess it is more like this then:

            Field A       Field B      (Field C)

              1                36            (43)

              2                43            (71)

              3                71             .....

             

            In Field C it would be (=Field A+1 then return the coresponding value in Field B)

            Because the full formula I am trying to achive is something like 

            Field C=If( Field A (Retun Field B=36)=Field A+1(Return Field B=43);"True";"False")

             

             

            • 3. Re: Lookup Record Value
              philmodjunk

              As I read that expression, you are comparing 36 to 43 which would be false.

              I suggest describing the real problem you are trying to work with instead of a generalized abstract expression.

              GetNthRecord can be used to return a value from the next record in the found set if your records are sorted to make it the next record. It's also possible to set up a self join relationship to link to a record that has value one greater than a value in the current record. I'm just not sure if that will actually fit the real problem behind these examples.

              • 4. Re: Lookup Record Value
                AlexXander

                Yes your right the final formula would result in a "False" and not 43 or 71 that were in the example. However, GetNthRecord doesn't help as I have found out or at least to my knowledge, that when you use use GetNthRecord in a IF statement then you can't reference(use) those results in a different table. So I was hoping to find an alternative solution to the GetNthRecord