Jump to content
  • 0

A blank virtual field is required when hidden


JKSGT
 Share

Question

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

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

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 by Tubby
Link to comment
Share on other sites

  • 0

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...