help: create a spreadsheet-like feel

[Mark]

I've been asked to convert a pay increase spreadsheet to an intranet
page. So far I've developed a page that uses a combination of ASP and
Javascript to retrieve all of the required information from the HR
database. The problem is that I need to be able to calculate the new
salary based on an increase_amount field, as well as maintain a
running total of the increase amounts. Once the amounts have been
determined, I then need to submit them, matched up with an employee ID
that is already on the page, to a stored procedure.

I've never attempted something like this before and am a little lost
on how to complete the desired functionality. Is it even possible?
Can this be done without embedding an Excel spreadsheet?

- Mark

 

[Greg Griffiths]

probably your best bet is to use Javascript Event Handlers to capture
fields being entered/exited, then call a recalculation funciton to sort
out the data. For DB Communication I'd suggest a hidden IFrame that can
then be submitted to a server side script - ASP, PHP, Perl etc - to be
processed and then have that reply come back with Javascript in it to
update the form on the main page.

Mail me if you need more

 

[Zac Hester]

I've been asked to convert a pay increase spreadsheet to an intranet
page. So far I've developed a page that uses a combination of ASP and
Javascript to retrieve all of the required information from the HR
database. The problem is that I need to be able to calculate the new
salary based on an increase_amount field, as well as maintain a
running total of the increase amounts. Once the amounts have been
determined, I then need to submit them, matched up with an employee ID
that is already on the page, to a stored procedure.

I've never attempted something like this before and am a little lost
on how to complete the desired functionality. Is it even possible?
Can this be done without embedding an Excel spreadsheet?

- Mark

This is actually very simple for JavaScript. As long as you know how
all of your formulas convert into the JavaScript math operators and
methods, setting up a spreadsheet-like page is trivial. Instead of
cells, just use text boxes for columns and rows of user input (one text
box for each cell).

In fact, with a little attention to layout, you can create a much more
"user friendly" spreadsheet than Excel.

Look up how to create forms and how to access the values of the input
fields. The HTML spec can be found at w3.org. If you want to learn how
to use JavaScript, you should read a good programming book about
JavaScript. The book I recommend is "JavaScript: The Definitive Guide"
by David Flanagan (3rd Ed).

(Go ahead and email me if you need any implementation-specific help. I
have example code of a project I did that calculated signal loss and FCC
rules adherence for microwave radios given many input values. It
started out as an Excel spreadsheet [provided by my boss] and ended up a
web page that fed off of a database on the backend.)

HTH,
Zac

 

[Greg Griffiths]

probably your best bet is to use Javascript Event Handlers to capture
fields being entered/exited, then call a recalculation funciton to sort
out the data. For DB Communication I'd suggest a hidden IFrame that can
then be submitted to a server side script - ASP, PHP, Perl etc - to be
processed and then have that reply come back with Javascript in it to
update the form on the main page.

Mail me if you need more

 

[Mark]

Thanks Zac & Greg. I appreciate the replys. If you have an example
that you could show me I think it would help me understand. If not, I
at least have some direction.

Thanks again!

- Mark

 

[Zac Hester]

Thanks Zac & Greg. I appreciate the replys. If you have an example
that you could show me I think it would help me understand. If not, I
at least have some direction.

Thanks again!

- Mark

Hey Mark,

Here's an implementation of my system here:

http://planetzac.net/test/apdb/wiapdb.php

I just set it up for educational purposes, so there's no sensitive
information there. Normally, I would password protect something like
this, but there's no need in this case. Go ahead and play around with
it and see how it works. Feel free to create new access points and
alter their values.

To see how the JavaScript works, just view the page source (it's all
there). If you're interested in the server-side script, I wrote
everything in PHP and I'd be happy to email you a copy of the code if
you're interested in that as well.

HTH,
Zac

 

[Mark]

Black Hills, huh? That would be in my "neighborhood"...

Thanks for sharing your page. I'm not sure that its going to help me,
however. Your page has a set number of fields all uniquely named and
referenced. I'm trying to figure out how to take something more like
a 2 column table that can have a varying number of rows. (The number
of rows needs to be flexible as each manager can oversee a different
number of people.) The first column would be the record key, and the
second column would be the salary adjustment from which other fields
would be calculated (ie a total adjustment amount field).

If I can create this, I'm thinking that I can then combine the key
with the adjustment and send it as one field, parse it out at the
server and then populate my table.

Does this make sense?