Confusing formula syntax comparisons such as !=' ' vs. IS NOT NULL etc

[KG360]

Hello,

I wonder if there is some ambiguity regarding syntax pertaining to: IS NULL vs. = ' ' (see example below). I have highlighted the differences in red bold.

Doesn't work: 
CASE 
WHEN '[@cbParamVirtual30]' != 0 AND [@field:tbl30_CaseObj_usrSelectedTWA] = ' ' THEN '[@cbParamVirtual30]'
WHEN [@field:tbl30_CaseObj_usrSelectedTWA] !=' ' THEN [@field:tbl30_CaseObj_usrSelectedTWA]
ELSE '[@cbParamVirtual37]'
END

Works:
CASE 
WHEN '[@cbParamVirtual30]' > 0.001 AND [@field:tbl30_CaseObj_usrSelectedTWA] IS NULL THEN '[@cbParamVirtual30]'
WHEN [@field:tbl30_CaseObj_usrSelectedTWA] != ' ' THEN [@field:tbl30_CaseObj_usrSelectedTWA]
ELSE '[@cbParamVirtual37]'
END

(The > 0.001 is instead of != 0. It puzzles me why != 0 don't work.. The Case is for users to alter an app generated parameter, Virtual30, and replace it with usrSelectedTWA, or default to an app parameter virtual37 in the absence of app generated value and user input)

It would be great with a list of situations in which the respective syntax works. I tried but can't find logic, nor consistency. It becomes a time-consuming trial and error. With large form containing serial calculations, this steals a lot of valuable time from app development.

Any advice is highly appreciated, as always,

KG aka Confusius

PS: Another is ;  = ' ' vs. = " " — single quotes vs. double quotes. I have been advised a few times to 'try' double quotes but don't understand when and why. 

[CoopperBackpack]

Hello @KG360,

Thank you for clarifying.

The syntax to use depends on the:

• Field type (Data Source fields/Virtual fields)
• DataPage type + field settings on the DataPage

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1) Data source fields + displayed value

Let`s say you use Calculated Value/Calculated Field on some type of the Report DataPage, Details DataPage (non-editable fields), Single Record Update DataPage (non-editable fields) or Calendar DataPage. And use the field from the data source (Table/View) in a formula.

It is important that data source fields have defined data type that were defined in the Table Design.

Also, it is important that records already exist in the Table/View and are displayed on the mentioned DataPages.

In the existing record some fields can store no value. 

In Caspio Tables this 'no value' can be NULL or Blank. And these are different values. You can learn brief info in this external article https://sqlskull.com/2019/12/10/null-vs-blank/

If the data type of the field is Text(255) or Text(64000) it is Blank when has no value.

If the data type of the field is Integer, Number, Currency, Date/Time is stores NULL when has no value.

So, on the data base level it should look like:

In this example, let`s imagine that PhoneNo1 has Number data type and PhoneNo2 is Text(255). Both fields has no value, so the first field stores NULL, the second field stores Blank.

The correct way to check if the Text(255) or Text(64000) field is empty: 

• to check the length of the field (if the length = 0),
• or to compare it with an empty string. 

 For example:

/*Option 1*/
CASE                                                
WHEN LEN([@field:user_email]) = 0
THEN 'email field has NO value'
ELSE 'email field has a value'
END

/*Option 2*/
CASE                                                
WHEN [@field:user_email] = ' '
THEN 'email field has NO value'
ELSE 'email field has a value'
END

DataPage example:

The correct way to check if the Integer, Number, Currency, Date/Time  field is empty is to compare it as IS NULL

 For example:

CASE 
WHEN [@field:Currency] IS NULL
THEN 'currency field has NO value'
ELSE 'currency field has a value'
END

DataPage example:

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2) Data source fields + editable value

Let`s say you use Calculated Value/Calculated Field on a Submission Form DataPage, Details DataPage (editable fields), Single Record Update DataPage (editable fields). And use the field from the data source (Table/View) in the formula.

In such cases check them as IS NULL/IS NOT NULL if you need to check if the field is empty/not empty.

For example:

CASE 
WHEN [@field:user_email] IS NULL
THEN 'email field is empty'
ELSE 'email field is populated'
END

DataPage example:

                         

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

3) Virtual fields

The peculiarity of the Virtual fields:  the value in it has no specific data type. 

As a result, to receive correct results of comparison, it is needed to convert the value to the needed data type explicitly.

Examples:

CONVERT(FLOAT, [@cbParamVirtual1]) - convert to the Float value (numbers with a decimal part)

CONVERT(INT, [@cbParamVirtual1]) - convert to the Integer value

CONVERT(DATETIME, '[@cbParamVirtual1]', 103) - convert to the Date/Time data type. Pay attention that the Virtual field should be wrapped in single quotes.

If the value in the Virtual field is a text, just wrap it  in single quotes '[@cbParamVirtual1]' (conversion is not needed)

 

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

4) Single quotes vs. Double quotes

In SQL there is a rule to remember:

[S]ingle quotes are for [S]trings Literals;

[D]ouble quotes are for [D]atabase Identifiers;

 

Since calculations on the DataPages allows to manipulate data, so we don`t create tables using SQL, etc., usage of  the single quotes is recommended.

I don`t remember cases when I needed to use double quotes in calculations in Caspio. 

[CoopperBackpack]

As far as I understood in the formula you provided, the tbl30_CaseObj_usrSelectedTWA is a dropdown and Virtual30 returns a float value.

If so, you may also check if this formula works correctly: 

CASE 
WHEN CONVERT(float, [@cbParamVirtual30]) > 0 AND [@field:tbl30_CaseObj_usrSelectedTWA] IS NULL 
THEN [@cbParamVirtual30]
WHEN [@field:tbl30_CaseObj_usrSelectedTWA] IS NOT NULL 
THEN [@field:tbl30_CaseObj_usrSelectedTWA]
ELSE '[@cbParamVirtual37]'
END
 

I wrapped Virtual 37 in single quotes, since I don`t know which value is returns (for numeric values you can skip using the quotes).

There could be some nuances, but the summary is:

• if you need to check if Text(255)/Text(64000) field is empty and this field is non-editable, the reliable way is to check its length or to compare with an empty string
• in other cases comparison with IS NULL/IS NOT NULL should work
• convert virtual fields explicitly if needed
• use single quotes, e.g. wrap in single quotes virtual fields, auth parameters, external parameters if they are text values ( for example,  '[@authfield:email]', '[@Param]')

 

Feel free to update this thread if you have further questions.
And I hope that the long post didn`t confuse you even more  

[CoopperBackpack]

Hello @KG360,

I will try to a add a description of using ' ' vs. IS NULL + single quotes vs. double quotes a little bit later today.

In the meanwhile, could you please provide more details about the fields used in this formula?

1) What is the data type of the tbl30_CaseObj_usrSelectedTWA field?

2) As as understood from your description Virtual30 receives a parameter. Is it correct? Could you provide an example of values that can be received as a parameter? 

  What is the goal of the comparison when you compare as ! =0?  Do you need to check whether the value is any positive number? 

[KG360]

15 minutes ago, CoopperBackpack said:

Hello @KG360,

I will try to a add a description of using ' ' vs. IS NULL + single quotes vs. double quotes a little bit later today.

In the meanwhile, could you please provide more details about the fields used in this formula?

1) What is the data type of the tbl30_CaseObj_usrSelectedTWA field?

2) As as understood from your description Virtual30 receives a parameter. Is it correct? Could you provide an example of values that can be received as a parameter? 

  What is the goal of the comparison when you compare as ! =0?  Do you need to check whether the value is any positive number? 

Thank you so much. If I could get clarity and some degree of logic / consistency I would save a ton of hours!

Great questions! 1) Number field (storing decimals only presented as percentage; 2) I misstated that a bit (not really a parameter from an app perspective): Virtual30 is a calculated value rendering a positive decimal value or zero (if not all related value are present). The calculation consists of a series of multiplicated decimal values resulting in a value from zero to 1.0 (It's a target weighted average calculation) Example: 30%*75% + 40%*68% + 30%*83%, or in the actual field 0.3*0.75 + 0.4*0.68 + 0.3*0.83. Cannot be a negative number. The user may enter a decimal value (seen as percentage) from a dropdown in field SelectedTWA, and this value over-rides Virtual30. If Virtual30 is zero, AND the user does not select something in SelectedTWA, the formula defaults to 'ELSE' i.e. Virtual37, an app parameter, also a decimal value seen as a percentage.

Sorry about lengthy reply, but I hope it covers your great questions.

Best regards / KG

[Tubby]

I believe the main difference is how those two checks the values. While ='' and IS NULL basically means the same in other programming languages, in Caspio ='' might just be comparing your field to a NULL string/text data type. IS NULL, if I am not mistaken ignores data type. But I might be wrong. I just thought maybe it is similar to the concept of == and === operators of JavaScript where the data type is important depending on what you use.

[KG360]

Many tanks @Tubby, I think your reply concur with @CoopperBackpack regarding the comparison syntax.

Regarding the difference between == and === in JavaScript? May I ask if you could explain the difference?

Cheers/KG

[KG360]

15 hours ago, CoopperBackpack said:

Hello @KG360,

Thank you for clarifying.

The syntax to use depends on the:

• Field type (Data Source fields/Virtual fields)
• DataPage type + field settings on the DataPage

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1) Data source fields + displayed value

Let`s say you use Calculated Value/Calculated Field on some type of the Report DataPage, Details DataPage (non-editable fields), Single Record Update DataPage (non-editable fields) or Calendar DataPage. And use the field from the data source (Table/View) in a formula.

It is important that data source fields have defined data type that were defined in the Table Design.

Also, it is important that records already exist in the Table/View and are displayed on the mentioned DataPages.

In the existing record some fields can store no value. 

In Caspio Tables this 'no value' can be NULL or Blank. And these are different values. You can learn brief info in this external article https://sqlskull.com/2019/12/10/null-vs-blank/

If the data type of the field is Text(255) or Text(64000) it is Blank when has no value.

If the data type of the field is Integer, Number, Currency, Date/Time is stores NULL when has no value.

So, on the data base level it should look like:

In this example, let`s imagine that PhoneNo1 has Number data type and PhoneNo2 is Text(255). Both fields has no value, so the first field stores NULL, the second field stores Blank.

The correct way to check if the Text(255) or Text(64000) field is empty: 

• to check the length of the field (if the length = 0),
• or to compare it with an empty string. 

 For example:

/*Option 1*/
CASE                                                
WHEN LEN([@field:user_email]) = 0
THEN 'email field has NO value'
ELSE 'email field has a value'
END

/*Option 2*/
CASE                                                
WHEN [@field:user_email] = ' '
THEN 'email field has NO value'
ELSE 'email field has a value'
END

DataPage example:

The correct way to check if the Integer, Number, Currency, Date/Time  field is empty is to compare it as IS NULL

 For example:

CASE 
WHEN [@field:Currency] IS NULL
THEN 'currency field has NO value'
ELSE 'currency field has a value'
END

DataPage example:

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2) Data source fields + editable value

Let`s say you use Calculated Value/Calculated Field on a Submission Form DataPage, Details DataPage (editable fields), Single Record Update DataPage (editable fields). And use the field from the data source (Table/View) in the formula.

In such cases check them as IS NULL/IS NOT NULL if you need to check if the field is empty/not empty.

For example:

CASE 
WHEN [@field:user_email] IS NULL
THEN 'email field is empty'
ELSE 'email field is populated'
END

DataPage example:

                         

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

3) Virtual fields

The peculiarity of the Virtual fields:  the value in it has no specific data type. 

As a result, to receive correct results of comparison, it is needed to convert the value to the needed data type explicitly.

Examples:

CONVERT(FLOAT, [@cbParamVirtual1]) - convert to the Float value (numbers with a decimal part)

CONVERT(INT, [@cbParamVirtual1]) - convert to the Integer value

CONVERT(DATETIME, '[@cbParamVirtual1]', 103) - convert to the Date/Time data type. Pay attention that the Virtual field should be wrapped in single quotes.

If the value in the Virtual field is a text, just wrap it  in single quotes '[@cbParamVirtual1]' (conversion is not needed)

 

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

4) Single quotes vs. Double quotes

In SQL there is a rule to remember:

[S]ingle quotes are for [S]trings Literals;

[D]ouble quotes are for [D]atabase Identifiers;

 

Since calculations on the DataPages allows to manipulate data, so we don`t create tables using SQL, etc., usage of  the single quotes is recommended.

I don`t remember cases when I needed to use double quotes in calculations in Caspio. 

Hi @CoopperBackpack,

Thank you very much for this exhaustive explanation and taking the time to write this guide! It will make an enormous difference as I move on and deepen my efforts using Caspio!

I understand now that I have been making mistakes that have consumed a lot of time. I will read it a few times and make sure I understand it, then use it as my reference for any comparisons, and let you know if I have any other questions!

Thank you for making this forum such a valuable asset!

Cheers/KG

[Tubby]

On 1/29/2023 at 12:59 AM, KG360 said:

Many tanks @Tubby, I think your reply concur with @CoopperBackpack regarding the comparison syntax.

Regarding the difference between == and === in JavaScript? May I ask if you could explain the difference?

Cheers/KG

The difference between the two in JavaScript is == ignores data type while === compares them.

For example,
a = 10 (number)
b = '10' (string)

a == b  is true as b will be converted to a number first before comparison
a===b will be false because a number is not equal to a string.

Hope it helps!

[KG360]

On 2/1/2023 at 12:18 AM, Tubby said:

The difference between the two in JavaScript is == ignores data type while === compares them.

For example,
a = 10 (number)
b = '10' (string)

a == b  is true as b will be converted to a number first before comparison
a===b will be false because a number is not equal to a string.

Hope it helps!

Many thanks for clarifying this !!!