Jump to content
  • 0

using virtual calculated fields in submission form rules


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.


Screen Shot 2021-02-18 at 17.38.57.png

Screen Shot 2021-02-18 at 17.39.31.png

Link to post
Share on other sites

8 answers to this question

Recommended Posts

  • 0

@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:

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

i also tried

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

Link to post
Share on other sites
  • 0

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.

Link to post
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...