cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
gwpeters
Nintex Newbie

How to use an IF condition in a lookup formula in a Calculated value field

Jump to solution

I have a products list with sale and return values. I have a form that is pulling the sale price into a calculated value field with the following formula. 

lookup("Uniform Store Inventory", "ID", Product, "Sale Value")

What I want to do is use the same for for sales and returns. I added a column called transaction type and made it a choice of either Sale or Return. I still need to use the lookup to pull in the items but I want to sue the IF to determine if is should pull the sale price or return price for each item. 

 

I am trying to use the formula below but it does not work, and I am not sure what i need to correct. 

lookup("Uniform Store Inventory", "ID", Product, IF([Transaction Type]="Sale", ("Sale Value"), IF([Transaction Type]="Return", ("Return Value" ))))

0 Kudos
Reply
6 Replies
bimi82
Nintex Newbie

Re: How to use an IF condition in a lookup formula in a Calculated value field

Jump to solution

That would be something like

1) if(this,then show this, otherwise show this) 
2) if(this, then show this) or if(this, then show this)

if(TransactionType="Sale", lookup("Uniform Store Inventory", "ID", Product,"Sale Value"), lookup("Uniform Store Inventory", "ID", Product,"Return Value"))

OR

if(TransactionType="Sale",lookup("Uniform Store Inventory", "ID", Product,"Return Value")) || if(lookup("Uniform Store Inventory", "ID", Product,"Return Value"))

0 Kudos
Reply
gwpeters
Nintex Newbie

Re: How to use an IF condition in a lookup formula in a Calculated value field

Jump to solution

When Use either of those formulas when I go to preview the the form to test it I get this error. 

0 Kudos
Reply
gwpeters
Nintex Newbie

Re: How to use an IF condition in a lookup formula in a Calculated value field

Jump to solution

It does not like when I use the TransactionType Named control. 

0 Kudos
Reply
bimi82
Nintex Newbie

Re: How to use an IF condition in a lookup formula in a Calculated value field

Jump to solution

Sorry Gary, forgot to say that you need to update the named control to whatever you have.

by the way, you can have a calculated value with the lookup() function to ensure it pulls the information.

0 Kudos
Reply
gwpeters
Nintex Newbie

Re: How to use an IF condition in a lookup formula in a Calculated value field

Jump to solution

Couple of things, 

1, The lookup works fine to return the values for each product when I use:

lookup("Uniform Store Inventory", "ID", Product, "Sale Value")
lookup("Uniform Store Inventory", "ID", Product, "Return Value")

Individually in my formula, because they are on the same site. Its when I try to combine them I am having troubles

Uniform Store Inventory= The name of my list, Product is being pulled from the a different lookup that I am doing, populating the list of items in a drop down menu, I want to add the above combined formulas into my "Price" calculated control. 

2. That is what is strange TransactionType is the what my named control is called so I am not sure why it errors. 

0 Kudos
Reply
gwpeters
Nintex Newbie

Re: How to use an IF condition in a lookup formula in a Calculated value field

Jump to solution

This ended up being the final formula that I used to accomplish this. 

Formula Builder

If(TransactionType=='Sale', lookup("Uniform Store Inventory", "ID", Product, "Resale Value")) || If(TransactionType=='Return', lookup("Uniform Store Inventory", "ID", Product, "Turn In Value"))

Bim Bimi 

Thanks for getting me on the right track. 

Reply