using virtual calculated fields in submission form rules

[aaronfreed]

BLUF: does anyone have any suggestions on how to make a submission form's rules engine read the values from a virtual calculated field?

i am using virtual calculated fields in a submission form to query five boolean variables in another table which specifies which of the five fields in the form should be required (this is customizable by the user through a separate form). the queries successfully execute and i am getting  expected results (i.e. 'Yes' and 'No' for each of the five  variables), which are based on the user's selection of a profile in a dropdown in the same form (e.g. profile a, profile b, profile c, which, again, they configure through another form). the queries are dynamic...they correctly recalculate as the user changes their 'profile' selection. i've attached a screenshot of the interface.

i need to use these virtual calculated fields ('Yes' and 'No') values in the form's rules so that i can specify which of the fields are mandatory based on the selected profile. though the rules engine seems to be able to distinguish between 'is blank' and 'is not blank' on the virtual fields, it seems incapable of reading the values. that is, i have tried "virutal field 1 is equal to Yes" (or 'yes' or 'true' or 1)....and nothing works. 

does anyone have any suggestions on how to make the rules engine read the values from a virtual calculated field?

p.s. for the form submission, i am using a view which joins the profile table with the test entries table. however, since only the test entries are editable in the view, i don't have access to the profile values (even though they are duly joined through the view) and therefore can't use them directly in the rule. this is why i have had to resort to using virtual calculated fields with sql queries.

 

[Nuke354]

Hi @aaronfreed

May I know what comparison are you using for the Rules?

This post may give you some ideas in converting the datatypes:

 

[aaronfreed]

hi @Nuke354 i have tried "virutal field 1 is equal to Yes" (or 'yes' or 'true' or 1)....and nothing works.

[aaronfreed]

(and @Nuke354 i apologize for the slow response, i didn't realize that i wasn't automatically following this post...the answer to this question is very important for me).

[aaronfreed]

@Nuke354 so the original virtual fields formulas, which work as expected (as indicated in my second visual), use the following formula format:

SELECT profile_FC FROM _v_profile_plus WHERE profile_id=target.[@field:test_profile_id]

as you can see, these queries return Yes and No values (but whether those are values or aliases, i don't know. whatever they are, the problem is that those calculated values (established by checkbox in another form), aren't being recognized by the rules engine.  i have tried checking for 'Yes' 'yes' 1 and 0 and nothing works. 

i tried creating an additional virtual field for each as per your suggestion, so that i could convert the query results into values that the rules might be able to read, but i am getting an 'invalid formula' error with no additional details. that formula is:

CASE 
WHEN [@cbParamVirtual1] = 'Yes' THEN 1 ELSE 0
END

i also tried

CASE 
WHEN [@cbParamVirtual1] = 'Yes' THEN '1' ELSE '0'
END

[Nuke354]

Have you also tried to enclose the virtual field parameter with single quotes like this?

CASE 
WHEN '[@cbParamVirtual1]' = 'Yes' THEN 1 ELSE 0
END

[aaronfreed]

hi, @Nuke354 i have/did try with single quotes and no joy. just to ensure that i had general structure correct, i set a rule to require a field if the CASE statement resolved to zero, which proved that this approach should work. unfortunately,  it is the case that the CASE always resolves to zero so that is non-starter. but it does tell me that 'Yes' is wrong. i've tried 'yes' and 'True' and 'true' and True and true and 1 and 0...can't figure it out.

instead of querying the view, i am now querying the table directly:
SELECT FC FROM test_profile WHERE id=target.[@field:test_profile_id]

the 'Yes' that i see when i query the table (as shown in the second screenshot above) , doesn't seem to be a string (as demonstrated by the failure of the CASE statement). perhaps it is just an alias for a boolean. but that begs the outstanding question...what is the value of the boolean that the CASE statement can read? 

thanks for taking the time help.

[aaronfreed]

@Nuke354 i opened a ticket on this one. support figured it out: the values are Y and N. i told them that the handling of Yes/No variables was inconsistent in caspio (e.g. triggers use true/false) and that perhaps an audit was in order.

[aaronfreed]

the answer is this: in rules for datapages based on virtual calculated field queries of Yes/No variables, the values are Y and N.