Lookup Results

Raftsman · Post by **Raftsman** » Fri Nov 18, 2011 7:58 am

When a reference record is not found during the lookup for a VarChar record it returns ''. It there a way to change the '' to ~. I am using the INDEX function. INDEX('ALPS', expression, 1). the problem is '' returns 1 from the INDEX call. Not sure why but in order for my INDEX to work, I need to convert the ''.

Thanks in advance

chulett · Post by **chulett** » Fri Nov 18, 2011 8:39 am

Why not just if-then-else around the index function?

Raftsman · Post by **Raftsman** » Fri Nov 18, 2011 8:46 am

I was trying to shorten the statement. I thought I could use the following

INDEX('ALPS',function(expression,'','~'),1)

My IF statement already is complex.

chulett · Post by **chulett** » Fri Nov 18, 2011 8:49 am

Perhaps something like?

INDEX('ALPS', (if expression = '' then '~' else expression),1)