How do I update a table

[Mike Reed]

Hi,
I have a question about posting or updating information in a table. We pay bonuses to our employees a couple of times a year and it based on their length of service with the company. I have a field in our employee table called DAYS and another field called RATE. The DAYS field is updated at bonus time to reflect the number of days they have been employed. The RATE field is updated from a 'rate' table at the same time. The update table which is called 'rate' is a simple table containing 3 fields...
SDays
EDays
Rate

The rate table might contain these records (The rates are changed each bonus cycle)

recno sdays edays rate
1 1 90 0.00
2 91 182 .1
3 183 275 .2
4 276 364 .3
5 365 455 .4

What is the best way to update the RATE field in the employee table with value from the rate table?

I would appreciate any ideas anyone might have.
thanks,
mike reed

[Wanda Friesen]

RE: How do I update a table

Could you create a field, named "CODE", in the employee table that is a calculated field based on the number of days the employee has served? You would need to write the calculation based on employee start date. The calculated field could calculate to "1" if the employee has been there 6 months, "2" if the employee has been there 1 year, etc.

In the Rate table, set up the rate for employees who have been there 6 months, and give it the data of "1" in it's "CODE" field and thereby link all employees with a "1" in their CODE field, with the "1" in the CODE field of the RATE table.

You could then create a report, grouped on CODE, which would then give you a list of all employees who get a bonus, and what bonus they get, based on the CODE in the RATEs table. In fact, you could have a field in the RATE table stating what the bonus amount is for that particular period of time. i.e. The first record would have these fields: CODE: 1 SERVICETIME: 6 (for 6 months), or 180 (for 6 months) RATE: $300.

I hope I've explained this well enough...lif you chose to go with this idea, let me know if you have any questions.
Wanda

[Stan Mathews]

RE: How do I update a table

I haven't tried this, but if you linked your rate table to the employee table, one to one, on the expression -- between(employee->days,rate->sdays,rate->edays)---- and referred to the rate table rate field, you might not need to even have a rate field in the employee table.

Will play around with this and get back to you.

[Stan Mathews]

RE: How do I update a table

Sorry, that's not doable, should have known better.

[Stan Mathews]

RE: How do I update a table

Hope the HTML doesn't goof things up.

The easiest solution I can think of is to change your rate table
to something such as employee, sdays1, edays1, rate1, sdays2, edays2, rate2, ...
for as many rate steps as you have. The information in the days fields
and the rates fields could be the same or different for
different employees.

I would then design a post operation from the rate table
to the employee table to be run before the bonus calculations. Using the post "card" 
the master table would be employee, the transaction table would be rate, the linking 
expression would be employee (whatever your employee id or code is) and the condition
would be 
case(between(days,@rate->sdays1,@rate->edays1),rate1,
     between(days,@rate->sdays2,@rate->edays2),rate2) and so on ....

You are allowed up to sixteen pairs of conditions and results in the case statement so
you should be able to handle most rate schedule situations.

Hope I haven't missed something this time.

[csda1]

RE: How do I update a table

Mike,

Just do an update operation of the field with a lookup sort of like;

FieldName Expression
RATE LOOKUPC("C",DAYS,"RATE","RATE","indexname")

where indexname is the name of the index based upon SDAYS. EDAYS is not needed assuming that all rates are independent of each other. The function syntax is;

lookupc(c matchtype,a keyvalue,c result_expression,c dbasefile,c tagname)

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]