Issue with virtual fields in formulas

[KG360]

I spend about 30% of my time testing variants to get formulas to work. I wish Caspio would issue a documentation of rules for virtual fields in formulas. Does anyone else struggle with formula syntax related to virtual fields? For example, what is wrong with this formula? It does not deliver anything.

CASE
WHEN '[@cbParamVirtual73]' != ' ' THEN SELECT Org from tbl_360Obj WHERE Objective_PK='[@cbParamVirtual73]'
WHEN '[@cbParamVirtual82]' = 'Define my own' THEN '[@cbParamVirtual80]'
ELSE NULL
END

Virtual 73, 82, and 80 are handling text. V73 is a drop down selection outputting a random ID and so is V80.  82 is a radio button selection. Instead of != ' ' I have tried IS NOT NULL many times.

Any hints or written set of rules for virtual fields?

[IamNatoyThatLovesYou]

Hello @KG360, Is the formula just not show anything or are you receiving error messages? Can you provide me with an exported copy of the DataPage and the expected result?

[KG360]

1 minute ago, IamNatoyThatLovesYou said:

Hello @KG360, Is the formula just not show anything or are you receiving error messages? Can you provide me with an exported copy of the DataPage and the expected result?

Hi and thank you. Actually I think I just solved it by putting the SELECT statements in parentheses. I believe Caspio should do a better job at providing a rule book, for example: 'Always put SELECT statement sin parenthesis when they are part of a formula'. I will validate that this works now...

[Flowers4Algernon]

Hello! This documentation should also be helpful as you go along: https://howto.caspio.com/function-reference/

[RJArcher]

I agree! I'm a newbie at Caspio, converting from MS Access, and I find formulas and referencing variables very confusing. Do I use [@field:ItemName] or [@ItemName] or [ItemName] or something else and do I enclose it in single quotes or not? And my current issue is how to include a virtual field on the form in the formula for another field on the same form. The OP is using '[@cbParamVirtual#]' which I've tried, but my understanding is that that notation is for APP parameters. In my case, I'm trying to refer to a simple virtua field defined on the form.

[CoopperBackpack]

Hello @RJArcher,

First, please use the 'Insert' button to add fields to the formula. For example:

When the field is added its syntax is [@field:FieldName]

The following syntax is used to receive the value as a parameter from the previous step [@FieldName]

 

As for Virtual fields, let me copy from this post https://forums.caspio.com/topic/30870-confusing-formula-syntax-comparisons-such-as-vs-is-not-null-etc/?do=findComment&comment=79008

 

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

As a result, to receive correct results of the 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)

 

If you need assistance with a specific formula, please provide the formula example. 

 

[RJArcher]

Problem solved - thank you for your reply and assistance! I'm still struggling with the various syntax schemes as I bounce back and forth between Caspio, JavaScript and T-SQL, but I'm getting there. 

Thanks again.