7 Replies Latest reply: May 15, 2014 10:13 AM by OddJob _

# calculated fields

Is there a way to create a lookup caluclated field inside of a formula based calculated field?

• ###### calculated fields

Hi Allison

I'm not aware of a way to create a lookup field within a calculated field.

Obviously, you can create a lookup field and then include that within a calculated field or, depending how many entries might be in the lookup, you can use nested If statements e.g.

If(number=1,"A", If(number=2,"B","Z"))  etc.

What is it that you're trying to achieve ?

• ###### calculated fields

A long while back it seems to me that someone posted a nifty way to simplify nested if statements (which I think OddJob is correct about being the solution).  I may not do it justice, but it seems like it was essentially just breaking each if statement out into its own line and then concatenating (for character fields, summing for numeric) the results.

Say you have a hypothetical that can produce values in the set {1, 2, 3}, and you want to then convert those to {red, blue, yellow}.  You could write something like this: if(=1,"Red","")+

if(=2,"Blue","")+ if(=3,"Yellow,"")[/code]

Since one of these criteria will evaluate to TRUE and the other two to FALSE you're technically concatenating two null strings and one useful string.  (E.g., "" + "Blue" + "")  But effectively[/I] you have just the "Blue" string left hanging around.

If you want to do this with a numeric output I think you just replace the "" with 0 and wrap a sum statement around the entire thing.  So reversing our example would be:

sum(

if(="Red",1,0)+ if(="Blue",2,0)+

if(="Yellow",3,0) )[/code] EDIT:  The sum() statement isn't necessary.  The "+" will cover it. Again, if you're looking at "Blue" you're really evaluating to 0 + 2 + 0.  But this should be fine for the purpose. Obviously one drawback of this is that you have to enumerate all possible values of , but this is often workable.

One great advantage of this approach (which someone else completely discovered and I take no credit for - there are many really bright people on this forum and I couldn't begin to guess who it was) is that it bypasses the limitation on how many if statements can be nested within each other.  (Off the top of my head I'm guessing 17 nested if statements?)

It also might even have some slick advantages over the traditional internal lookup field in that you can handle the same input in different ways based on outside criteria.  Like if you wanted to treat “Blue” differently based on what of the week it was:

sum(

if(="Red",1,0)+ if(="Blue" .and. =”Tuesday”,2,0)+

if(="Blue" .and. <>”Tuesday”,2.5,0)+ if(="Yellow",3,0)

)[/code]

• ###### calculated fields

A long while back it seems to me that someone posted a nifty way to simplify nested if statements (which I think OddJob is correct about being the solution).  I may not do it justice, but it seems like it was essentially just breaking each if statement out into its own line and then concatenating (for character fields, summing for numeric) the results.

Say you have a hypothetical that can produce values in the set {1, 2, 3}, and you want to then convert those to {red, blue, yellow}.  You could write something like this: if(=1,"Red","")+

if(=2,"Blue","")+ if(=3,"Yellow,"")[/code]

Since one of these criteria will evaluate to TRUE and the other two to FALSE you're technically concatenating two null strings and one useful string.  (E.g., "" + "Blue" + "")  But effectively[/I] you have just the "Blue" string left hanging around.

If you want to do this with a numeric output I think you just replace the "" with 0 and wrap a sum statement around the entire thing.  So reversing our example would be:

sum(

if(="Red",1,0)+ if(="Blue",2,0)+

if(="Yellow",3,0) )[/code] EDIT:  The sum() statement isn't necessary.  The "+" will cover it. Again, if you're looking at "Blue" you're really evaluating to 0 + 2 + 0.  But this should be fine for the purpose. Obviously one drawback of this is that you have to enumerate all possible values of , but this is often workable.

One great advantage of this approach (which someone else completely discovered and I take no credit for - there are many really bright people on this forum and I couldn't begin to guess who it was) is that it bypasses the limitation on how many if statements can be nested within each other.  (Off the top of my head I'm guessing 17 nested if statements?)

It also might even have some slick advantages over the traditional internal lookup field in that you can handle the same input in different ways based on outside criteria.  Like if you wanted to treat “Blue” differently based on what of the week it was:

sum(

if(="Red",1,0)+ if(="Blue" .and. =”Tuesday”,2,0)+

if(="Blue" .and. <>”Tuesday”,2.5,0)+ if(="Yellow",3,0)

)[/code][/QUOTE]

Great post kbelli and credit to whoever discovered this originally.

I'd never thought of trying this.

I don't know if it solves Allison's problem but, I can definitely find a use for it.

• ###### calculated fields

Great post kbelli and credit to whoever discovered this originally.

/QUOTE

Agreed! This approach (appending If's) has [URL="http://www.monarchforums.com/showthread.php?3359-Can-we-do-nested-if&p=14618#post14618"]Nick's stamp all over it[/URL], but I'm not certain that he was the originator either. Regardless, it's a great solution.

• ###### calculated fields

Just for amusement, I decided to have another think about this, to see if there was an alternative to either a lookup or If's.

Here's what I came up with.

Imagine you have a field named 'content' that contains one of the following - Ap, Ba, Ch or Da  and you want to convert these to Apple, Banana, Cherry and Date.

It's possible to create two arrays within a formula calculated field; one containing the initial values and one containing the desired lookup values.

Here's an example.

Extract("#1Apple#2Banana#3Cherry#4Date#",Substr("1Ap2Ba3Ch4Da",Instr(Content,"1Ap2Ba3Ch4Da")-1,1),"#") /code

This finds the position of the original value in an array, picks up the number to the left, uses this to find a match in the other array and extracts the proceeding text, upto the # delimiter.

Anything that doesn't find a match should return a blank field.

This seems like a lot of hard work just to avoid using a lookup field and, I'm not sure what practical use it would be but, it does appear to be possible.

There's probably a slicker way to code this but, I'll leave that one to the experts.

• ###### calculated fields

FWIW, just in case, I'd add an error checker to your nice little expression, as follows:

If(Instr(Content,"1Ap2Ba3Ch4Da")>0,

Extract("1Apple#2Banana#3Cherry#4Date#",Substr("1Ap2Ba3Ch4Da",Instr(Content,"1Ap2Ba3Ch4Da")-1,1),"#"),

• ###### calculated fields

FWIW, just in case, I'd add an error checker to your nice little expression, as follows:

If(Instr(Content,"1Ap2Ba3Ch4Da")>0,

Extract("1Apple#2Banana#3Cherry#4Date#",Substr("1Ap2Ba3Ch4Da",Instr(Content,"1Ap2Ba3Ch4Da")-1,1),"#"),

Hi Data Kruncher

You're quite right, there should be some visible error for unmatched values. I took the lazy option and just returned a blank field.

By adding the 'content' field and relevant text to the beginning of the string that contains the results; it should still be possible to do this without using an 'If'.