Selecting a matching value from a second table in a calculation

[Jodie]

Hi - I'm struggling to get the right syntax for my select statement.

My users table holds everyone's email address - virtual7 holds the userid for a person's manager. In this formula if the 'manager' has been selected, I'm trying to use virtual 7 (the manager's USERID) to retrieve their matching email address stored in the the user table. If I remove this select from my syntax I get a response, but with this syntax the formula is null. I've tried to troubleshoot this but can't quite fix my code. Any help would be appreciated. Thanks

CASE 
WHEN [@field:tbl_opportunities_sign2]="manager" THEN (SELECT Email FROM tbl_users WHERE UserGUID=target.'[@cbParamVirtual7]')
ELSE CAST(‘[@cbParamVirtual9]’ AS CHAR)
END

[CoopperBackpack]

Hello @Jodie,

The 'target' keyword doesn`t work with Virtual fields, as far as I understand.

Please test this formula, it should work.

CASE 
WHEN [@field:tbl_opportunities_sign2]='manager'
THEN (SELECT Email FROM tbl_users WHERE UserGUID='[@cbParamVirtual7]')
ELSE '[@cbParamVirtual9]'
END

[Jodie]

Thank you! This works - except for the ELSE option. If I write all the other conditions I finally get the value passed on. Any ideas why the Else option won't pass on the email value?  

[CoopperBackpack]

Hello @Jodie,

It is a strange issue regarding the 'ELSE' part. It should work for all the cases except when the 'tbl_opportunities_sign2' field is equal to 'manager'.

Could you please provide more details about the Virtual9 field? What is its form element? Is it just an input field? Or is it a Calculated Value? 

 

[Jodie]

It's currently set as a text field that reads in a data source field that stores the correct email address. It's odd that I can get it to read virtual 9 correctly if I use a WHEN statement but not for the ELSE.