Jump to content
  • 0

Issue with virtual fields in formulas



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.

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

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?

Link to comment
Share on other sites

6 answers to this question

Recommended Posts

  • 0
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...

Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

  • 0

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.


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. 


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.

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.

  • Create New...