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

# Lookup Record Value

### 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

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

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

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

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