help: create a spreadsheet-like feel

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

re: help: create a spreadsheet-like feel

Mark wrote:
[color=blue]
> 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[/color]


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

re: help: create a spreadsheet-like feel

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 wrote:
[color=blue]
> 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[/color]

re: help: create a spreadsheet-like feel

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 wrote:
[color=blue]
> 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[/color]

re: help: create a spreadsheet-like feel

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

Greg Griffiths <greg2@surfaid.org> wrote in message news:<3F74C74B.9ADE0AD1@surfaid.org>...[color=blue]
> 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
>[/color]

re: help: create a spreadsheet-like feel

Mark wrote:[color=blue]
> 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[/color]

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

re: help: create a spreadsheet-like feel

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?

Zac Hester <news@planetzac.net> wrote in message news:<3f78c5db$1@news.enetis.net>...[color=blue]
>
> 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.[/color]