Jump to content
  • 0

Need to populate Data Fields in Add page with Complex Calculated Virtual fields


Lynda
 Share

Question

I am new to Caspio.

I have a Add page. I have several Virtual fields that are used to calculate nine different pretty complex calculations based on different table values and data field selections (SEX, AGE, WEIGHT, HEIGHT, LIFESTYLE, LIFEGOAL, etc). Whenever any one of these values change, the calculations recalculates. This works.

Example Virtual3 for ProteinGoal:

CASE

/* Value Based */
WHEN [@cbParamVirtual15] ="1" THEN (SELECT DietProtein FROM Diet WHERE DietID = [@field:Diet])

/* Percent Based - table field entered as integer */
WHEN [@cbParamVirtual15] ="2" THEN [@cbParamVirtual11] * ((SELECT DietProtein FROM Diet WHERE DietID = [@field:Diet])/100)

/* Weight Based */
WHEN  [@cbParamVirtual15] ="3" THEN (Cast(([@field:Weight])AS int)/2.2)*(SELECT DietProtein FROM Diet WHERE DietID = [@field:Diet])

END

The final nine virtual fields have been validated for correctness. Now I just need to assign the value to the data field (ProteinGoal).

The end user will either accept each value or over write the value with their own value. The data values will be stored in the table.

I am NOT a java programmer. I am hoping that there is a Caspio solution to my situation.

I have tried... setting the Default Value to the virtual value on load, but all that gives me is a one time load of Null (Not what I want or need)

I have tried... making the data field a calc field and placing the final calc in the calculation, but the field is not modifiable. Not to specification.

Can someone please help me?

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

I'm using several Calculated values to calculate the final value. The record field (in this instance MyProfile:Protein) has two states. It can either be a calculated value (from [@Virtual3]) or a User entered value directly into MyProfile:Protein. It is based on the type of Diet selected(If Diet selected is 100 or 101, then Section20(containing  [@Virtual3] is hidden (the calculated section) and the table fields are displayed, else Section21 (containing  MyProfile:Protein) is hidden and the calculated values are displayed and need to be updated to the record fields. I need to know how to insert the calculated value into the table field so I can update the record.

I appreciate your quick response. Maybe I wasn't clear in my original asking.

Link to comment
Share on other sites

  • 0

Hello @Lynda,

If I got your workflow, you need to prepopulate the data source field with the value calculated in the Virtual field. This data source field should be editable at the same time.

If this is correct, it is achievable with JavaScript code.

I am not sure which DataPage type you use.

1) If you use a Submission Form, add Header and Footer elements, and paste the code to the Footer section.

Please disable the HTML editor on the Advanced tab before pasting the code.

<script>
document.addEventListener('DataPageReady', function (event) {
  
document.getElementsByName('cbParamVirtual1')[0].addEventListener("change", calcValueHandler); // change the Virtual field number 
   
      function calcValueHandler(event) {
   
          let calcFieldValue = event.target.value;
          const editableField = document.getElementById('InsertRecordNum_field'); //replace Num_field with your data source field name
          editableField.value = calcFieldValue;
     
          document.removeEventListener('DataPageReady', calcValueHandler);
     }        
});
</script>

Screenshot for vizualisaton:

JWPNI3W.png

The output:

KYRJ9MS.png

So, the data source field is prepopulated (the code copies the result of the calculation to the input field), however the field is editable and the user can change the value.

2) If you use a Single Record Update Form, or Details DataPage, the approach is the same. 

The only change is related to the syntax used to refer to the data source field.

In Submission form it is InsertRecordField_name, in Single Record Update Form, or Details DataPage it is EditRecordField_name.

<script>
document.addEventListener('DataPageReady', function (event) {
  
document.getElementsByName('cbParamVirtual1')[0].addEventListener("change", calcValueHandler); // change the Virtual field number 
   
      function calcValueHandler(event) {
   
          let calcFieldValue = event.target.value;
          const editableField = document.getElementById('EditRecordNum_field'); //replace Num_field with your data source field name
          editableField.value = calcFieldValue;
     
          document.removeEventListener('DataPageReady', calcValueHandler);
     }        
});
</script>

Hope this helps.

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.

Guest
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.

Loading...
 Share

×
×
  • Create New...