close ad
WARNING PC USERS: Do Not Install the DREAMWEAVER CC 2017 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Arithmetic Formula

Thread began 6/02/2015 4:12 am by salaroche | Last modified 6/19/2015 1:56 pm by Ray Borduin | 1278 views | 17 replies |

salaroche

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.

Sign in to reply to this post

Ray BorduinWebAssist

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

Sign in to reply to this post

salaroche

Ray:

I'll try your formula. Many thanks.

Sign in to reply to this post

salaroche

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

Sign in to reply to this post

Ray BorduinWebAssist

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)); ?>

Sign in to reply to this post

salaroche

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

Sign in to reply to this post

Ray BorduinWebAssist

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()"

Sign in to reply to this post

salaroche

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.

Sign in to reply to this post

salaroche

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.

Sign in to reply to this post

Ray BorduinWebAssist

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.

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...