Arithmetic Formula

Hi:

I’m designing a table where the user will enter integer values in 5 different columns and I would like DW to calculate and save the average of those 5 columns in a 6th column before saving the record.

Any idea of how to do that? Would a SELECT function produce such result? And, if so, how would that function look like?

Thank you for your attention.

Usually it is advisable to not save a separate record for anything that can be calculated with arithmetic. This is a general rule of database design because it introduces the potential for data inconsistency. It is advisable to instead only store the 5 columns and calculate the average value in the 6th column when displayed. That could be done in the database SELECT statement, or with PHP when displaying the page.

In the SELECT it might look like: SELECT *, ROUND((col1+col2+col3+col4+col5)/5, 2) AS colAverage FROM tableName

Ray:

I'll try your formula. Many thanks.

Ray:

Is there any way to make the arithmetic calculation BEFORE saving the document? For example to trigger the calculation right after the user enters the last value on Col5 ? I'm assuming the SELECT statement wouldn't work because the values wouldn't yet be stored in the db.

Any ideas?

Thank you for your attention

Again, I would suggest not doing that because it opens up the opportunity to introduce data inconsistencies. As a general rule of database design you should never store a value that can be calculated from other values on the same row.

However if you want to it could be done with php on the insert or update page Server Behavior setting the value from the submitted form by binding it to something like:

<?php echo(round(($_POST['field1'] + _POST['field2'] + _POST['field3'] + _POST['field4'] + _POST['field5'])/5,2)); ?>

Ray:

Thank you for your reply. I guess I didn’t make myself clear. The values I would like to save are those of the 5 variables, not the average. But my question now is whether the formula you sent can be used to DISPLAY the average so that the user can see it before saving the document.

I tried using your formula (using the right form field names) by inserting it as the initial value for the Average field, but it gives me a syntax error.

Any suggestions?

Thank you for your attention

If you want to show it to the user before the form is submitted, then you have to use javascript.

You would add an onblur event to each of the fields and call a javascript function. The function would be something like:

<script>
function showAverage() {
  var average = 0;
  var field1 = document.getElementById("field1");
  var field2 = document.getElementById("field2");
  var field3 = document.getElementById("field3");
  var field4 = document.getElementById("field4");
  var field5 = document.getElementById("field5");
  if (!isNaN(parseFloat(field1.value))) {
    average += parseFloat(field1.value);
  }
  if (!isNaN(parseFloat(field2.value))) {
    average += parseFloat(field2.value);
  }
  if (!isNaN(parseFloat(field3.value))) {
    average += parseFloat(field3.value);
  }
  if (!isNaN(parseFloat(field4.value))) {
    average += parseFloat(field4.value);
  }
  if (!isNaN(parseFloat(field5.value))) {
    average += parseFloat(field5.value);
  }
  average = avarage/5;
  average = Math.round(average*100)/100;
  document.getElementByID("displayAverage").innerHTML =  String(average);
}
</script>

This assumes you have fields with id="field1" thru "field5" and a span with id="displayAverage". Then in each field you add onblur="showAverage()"

Ray:

Thank you for your dedicated attention. Your suggestions are perfectly clear. I’ll give it a shot ASAP. By the way, I’m writing to you from Myanmar (Burma).

Many thanks again.

Ray:
I finally got around testing the function and I’m obviously not doing everything right, as it doesn’t work. Please see the details in the private message below. Thanks.

I think, since this is custom coding help, I'd have to ask you to sign up for a premier support ticket to do screen sharing and help you complete your application.

The examples I've given were of course out of context, but the general principals should be solid. If you want me to take over and edit your files to make them work, that would require purchasing a premier ticket.