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

  • 24 October 2018
  • 6 replies
  • 11 views
G
Badge +4

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" ))))

6 replies

B
Badge +11

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"))

G
Badge +4

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

219839_pastedImage_1.png

G
Badge +4

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

B
Badge +11

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.

G
Badge +4

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. 

219840_pastedImage_1.png

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

219841_pastedImage_2.png

G
Badge +4

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


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings