Case Statement in Formula of Table

[RobDunbar]

Hello All,

 

I am stumped. I have the following case statement in a formula field and it verifys.

CASE
WHEN [@field:SMNType] = 1 THEN 'l1q1.html?XBSPropertyID=' + [@field:SMNPropertyID] + '&;VRRYear' + [@field:SMNYear]
WHEN [@field:SMNType] = 2 THEN 'l1q2.html?XBSPropertyID=' + [@field:SMNPropertyID] + '&;VRRYear' + [@field:SMNYear]
WHEN [@field:SMNType] = 3 THEN 'l1q3.html?XBSPropertyID=' + [@field:SMNPropertyID] + '&;VRRYear' + [@field:SMNYear]
WHEN [@field:SMNType] = 4 THEN 'l1q4.html?XBSPropertyID=' + [@field:SMNPropertyID] + '&;VRRYear' + [@field:SMNYear]
WHEN [@field:SMNType] = 8 THEN 'b1.html'

ELSE ''

END

 

However, when I try to save the table I get Unable to save table due tto incompatible values in one or more formula fields.

 

[Kurumi]

Hi @RobDunbar - what are the Data types of [@field:SMNType]  [@field:SMNPropertyID] and [@field:SMNYear]?

[NailDyanC]

14 hours ago, Meekeee said:

Hi @RobDunbar - what are the Data types of [@field:SMNType]  [@field:SMNPropertyID] and [@field:SMNYear]?

Agree with @Meekeee. You need to check what are the Data types of your fields to make sure that it is compatible when you will try to concatenate or add it. If they have different Data types, you can try to convert them all as one Data type.

[RobDunbar]

15 hours ago, Meekeee said:

Hi @RobDunbar - what are the Data types of [@field:SMNType]  [@field:SMNPropertyID] and [@field:SMNYear]?

All are Integers

[RobDunbar]

What I am tring to do is to create a  destination URL but I have two different URLs that are possible and each page has a different set of variables

[autonumber]

Hello, @RobDunbar, could you please try this formula if this will work? 

CASE
WHEN [@field:SMNType]= 1 
THEN 'l1q1.html?XBSPropertyID=' + CONVERT(VARCHAR,[@field:SMNPropertyID]) + '&;VRRYear' + CONVERT(VARCHAR,[@field:SMNYear])
WHEN [@field:SMNType]= 2
THEN 'l1q2.html?XBSPropertyID=' + CONVERT(VARCHAR,[@field:SMNPropertyID]) + '&;VRRYear' + CONVERT(VARCHAR,[@field:SMNYear])
WHEN [@field:SMNType]= 3
THEN 'l1q3.html?XBSPropertyID=' + CONVERT(VARCHAR,[@field:SMNPropertyID]) + '&;VRRYear' + CONVERT(VARCHAR,[[@field:SMNYear])
END

Since you your fields are all Integer DataTypes, and you're concatenating it to a String, you need to convert the Integer fields into a String as well. 

I hope this helps! 

[autonumber]

Also, you may check this link to view the list of functions that you can use for calculations. https://howto.caspio.com/function-reference/