Look up and return value formulas. 1

[AngChristman]

Hello, I really need some help.
The way that our data was set up in our data base is causing a bit of a problem for one of our labels.

What I need to do is Pull Data from Field X based off of Field A to look up the Value from Field X in Field A to return the final value, the one that will be displayed from Field Z.

They have the same data in Field A and X, but we need X's return value of field Z not A's.

In Excel it would be two formulas:

1.) =Vlookup(Value from Field A, A:Z, 24, false) {the return value would be Field X which it is doing, but we need to look up This return value in field A to return it's Value in field Z.}

2.) =Vlookup(Value from Field X, A:Z, 26, false) {the return value would be field Z of Field X, what we need}

Thank you for any help you can offer,
Angie

 

[lbass]

If I'm following you, you can use an if/then formula. Create the following formula:

//{@formulaZ}:
if {table.fieldA} = <somevalue> then {table.fieldX}

I think you should be posting in forum149 or forum767.

-LB

 

[AngChristman]

ItemCode ItemShortDesc CompositionCode
10000299 STK/4 4.0x6.0 Z-Perform 2T 66105RM
10000300 STK/4 4.0 X 6.5 Z-Perform 1D 65832RM
66099 Label,1ac,3.000x3.000,Z-Trans 05024RM
66104 3X4, 5127 05127RM
66105RM Z-Perform 2000T
66106 2.203X.250 1AC Z-ULT 3000 WHT 05612RM
66110RM EXP-66110RM
66112 Label,1ac,4.000x6.500,Z-Ultima 05612RM

The above three are the fields (ItemCode [column A], ItemShortDesc [column B], and CompositionCode [column C]).
What I have pulled is the Item code for 10000299 (first one). Then I need the composition codes for that item (66105RM), but inorder to get the correct ItemShortDesc, I have to take the value from column C and look that value up in column A to return the value in column C for the composition code. Will the if then statement work for this?

Thank you for the heads up on where to post... New to this sight.

Thank you,
Angie

 

[lbass]

You should add the table to your report twice, and link the first table to table_1 by linking {table.compositioncode} to {table_1.itemcode}. Then you could place {table.itemcode} and {table_1.shortitemdesc} on the report, without using any formulas.

-LB