A blank virtual field is required when hidden

[JKSGT]

Hi,

I have got an issue with [@cbParamVirtual3].  [@cbParamVirtual3] value is calculated with the below CASE statement. For clarity [@cbParamVirtual2] is a calendar pop up. The CASE statement below works fine. When a user picks a date in [@cbParamVirtual2], [@cbParamVirtual3] is calculated correctly.

CASE

WHEN [@cbParamVirtual2] IS NOT NULL

THEN

DateAdd(year, 1,DateAdd(day, -1,CONVERT(DATETIME, '[@cbParamVirtual2]', 103)))

END

However, the problem is when  [@cbParamVirtual3] is hidden and blank.  [@cbParamVirtual2] and [@cbParamVirtual3] are hidden under certain circumstances with a rule which hides the section that contains those two fields.  When a user tried to move on to the next datapage, the 'Values in one or more fields are invalid' message appears. It is appearing because  [@cbParamVirtual3] is required even though it is hidden and not made required by the field setting or any rule.

I have tried to add an ELSE statement to the above CASE statement to get around the issue:

CASE

WHEN [@cbParamVirtual2] IS NOT NULL AND [@field:Price_Type]='New'

THEN

DateAdd(year, 1,DateAdd(day, -1,CONVERT(DATETIME, '[@cbParamVirtual2]', 103)))

ELSE

DateAdd(year, 1,DateAdd(day, -1,CONVERT(DATETIME, '[@cbParamVirtual2]', 103)))

END

I was expecting the result in [@cbParamVirtual3] to be 31 Dec 1900 but I'm not getting anything if  [@field:Price_Type] does not equal 'New'.

 

If you can see where I am going wrong with the above, I will be very grateful for any tips.

 

Many thanks

Jay

[Tubby]

Hi @JKSGT,

Sorry about my previous message. I think I may have found the ACTUAL solution. 

What I found is, wrapping your Virtual Fields with single quotes (' ') actually fixes your problem. It gives 12/31/1900 as the result when [@cbParamVirtual2] is null, you don't even need to use ELSE to set it as NULL. Check this formula:
 

CASE

WHEN '[@cbParamVirtual2]' IS NOT NULL

THEN

DateAdd(year, 1,DateAdd(day, -1,CONVERT(DATETIME, '[@cbParamVirtual2]', 103)))

END

I hope this helps. 

-Tubbs

Edited May 28, 2021 by Tubby

[JKSGT]

Hi,

Thank you. In fact the solution that I found to be working is:

CASE
WHEN LEN('[@cbParamVirtual2]') > 0
THEN
CONVERT(nvarchar, (DateAdd(year, 1,DateAdd(day, -1,CONVERT(DATETIME, '[@cbParamVirtual2]', 103)))), 106)
ELSE ' '
END

Thanks again for your thoughts.

Jay