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

[Lynda]

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?

[CoopperBackpack]

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:

The output:

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.

[cheonsa]

Hello @Lynda,

If you are using a Form, Submission or Details Page, you can use a Form Element called Calculated Value. The Calculated Value generates dynamic calculations that automatically update as users interact with your form. 

https://howto.caspio.com/datapages/datapage-components/calculated-values/

[Lynda]

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.

[Lynda]

You are wonderful!!!! I will try this. It is exactly what I am looking for. Thank you.