How to force a + or - value in a field based on another using browse entry?

[smrogers]

I've got a browse in which one field is filled in as either a positive numeric value or a negative numeric value based on the value of another field in the same table.

Here's the scenario: I have paycodes being entered into a field, followed by a category code (numeric). If the paycode is assigned to a category code 100 or greater, it will be considered as a DEDUCTION, and thus the default amount can only be entered as a negative. If the code is from 1 - 99, it must only be a positive.

I can't figure out how to do this with the field validation rule... any suggestions? Thanks.

[mbmonk]

The possible solution that comes to mind is some nested if statements. So it would be something like this (this is pseudo code):

IF catagory_code > 99 Then
    'If we get here then we have a code that is 100 or greater
    IF pay_code < 0 Then
          'We have a negative value like we want, we return true
         .T.
    Else
          'We have a positive value, so we have a problem, return false
          .F.
    End If
Else
    'If we get here then we have a code that is less than 100
    IF pay_code < 0 Then
          'We have a negative value so its a problem
         .F.
    Else
          'We have a positive value, and that is what we want
          .T.
    End If
End If

The above code will not work in the field rules because you cant use the IF..Then statements. So you have to use the if() function.

if( condition_to_check, What_to_do_if_True, What_to_do_if_False)
Just check the help file on the function if().

I hope this in some way helps with your problem but I am not sure if it does. You could nest these if() functions and it *might* work.

Good Luck,
Mike

[smrogers]

Hi Mike, and thanks for the efforts!

As a so-called "newbie", I'm not completely following it, although I understand if - then's and don't have a problem with that in general. I guess my question would be: how do I use this code for the value field, when I'm using a browse to enter the data? I want the user to see the table of all the codes as they enter, rather than seeing just the one new record that a form would provide. If you have any other insights, it would be much appreciated. Thanks again!

[mbmonk]

Are you trying to just validate what the user enters? And where are you trying to enter the code to validate it? In the field rules for the table?

If possible you might want to attach the browse/form your talking about along with the table. Or maybe a screenshot so I can get an idea of what your talking about.

I am just a little slow and confused :),
Mike

[smrogers]

More detailed explanation

Ok,

I'm working on a system that will collect payroll information each period. The generated reports will be used to report payroll to Paychex.

There is a table setup for each employee, that has a number of paycodes -- these codes define different types of "jobs" each employee does, and assigns a rate per hour for each of these tasks. A Table of codes is defined before the employee is setup, which contains the paycodes and their default rates. When the paycodes are setup for each employee (paycodes assigned will vary from employee to employee), they can accept the default or be overriden with another amount. The pay codes are grouped into a range of categories. One category is "deductions"... paycodes that will DEDUCT from their pay.

I want to force the entry to only negative amounts for those paycodes that fall within that range....

I hope this explains better what I'm tryint to do... thanks! (BTW -- it's not imperative... I'm just trying to build in as much error trapping as possible!)

I've attached a zip of the screen shot (couldn't get it under 100K)

[Cheryl Lemire]

why does a negative number need to be entered?

This may be totally off base, but is it really necessary for the data entry person to enter a negative number for the deductions? Wouldn't it be easier to have the data entered all as positive numbers and use the pay codes to determine if the field value should be added or deducted from the gross pay to result in the net pay value?

If you are looking for a visible for the users entering the data, you can always color the field say 'red' if the pay code is for a deduction. But technically, the data entry person should not really care if it is positive or negative. Just my two cents :)

Thanks
Cheryl

[Scott Emerick]

Shawn,

Just a few questions for you.

I assume your employee info form is built on a set, correct?

Is the browse that your trying to do this in the one you show in your pic that is on the "Paycodes" Tab?

If the above answer is yes.
a) Do you also store the category number in the child table or just the paycode?
b) I assume Paycode is a lookup that fills Description and Rate/Hr., correct?

Once I have these answers I can show you code that will work or get you real close.

If you want to tackle it yourself then what you want is to write your code in the field rules->field events->OnWroteField event.

Scott

[smrogers]

Hi,

The paycode lookup has the description and rate/hr, however the rate/hr is only a default; there is also a field in a table for each employee, linked by emp #, that stores the actual rate/hr (some are paid differently). The Category number is NOT stored in the employee record, it is just a lookup.

Hope that answers your question, thanks for the interest!

[smrogers]

Hi,

No, your not off base -- they certainly CAN enter a positive number, I just don't know how to handle it in reports, etc.... as I said, I'm fairly new to this. I would need to reference the catagory range, and if the paycode fell within the range dedicated to deductions, it would deduct it instead -- I think that would be a more elegant way of doing it, but it just may come down to this being an "enhancement" down the line.... I need to have this done in a month or so, including menus, reports, etc.... so some of the error trapping may have to be put on hold for the moment (the client is OK with this). BUT... any light you can shed on this would be greatly appreciated! Thanks agian.

[Scott Emerick]

Shawn,

I can understand the default vs. actual based on an employee. If thats the case then shouldn't the lookup populate the Rate/Hr. field not only as a positive or negative, but also the actual rate for that employee? And then still allow a override value placed in that field?

It might be best if you can send me your application, or a stripped down version of it with enough data for me to play with. I can then make it work as you wish and show you how I did it. Thats your call.

Scott

[smrogers]

The lookup DOES populate the rate/hr field now. Here's a little more info:

I do NOT yet have the actual paycard part of the app designed yet (the part where they will enter in weekly payroll for each employee). I'm only in the employee setup phase. When the employee is being setup, one of the things they do is setup the APPLICABLE pay codes for that employee (there can be many paycodes, but not ALL are applicable to each employee -- you just pick and choose those paycodes that are necessary for each employee).

The deductions will generally be for things that may be "purchased" from the club, and deducted from their pay - therefore, there is no fixed amount set when the employee info is being setup -- the code is just entered with "0" for a default, since the actual deducted amount will be entered during the paycard entry. An example would be if they "bought" a sweatshirt -- it would be deducted from the gross pay that this app computes. I'm still working on the logistics in my head on that -- normally, the computation will be # hours worked (for each paycode type) multiplied by the rate. For some items (such as the deductions, or one time bonuses like Christmas) I want them to be able to just plug in an amount for that paycode, and be done with it... as I said, I'm new to Alpha so I may have to change things as I go along and learn more

I've attached the app for your perusal -- have at it, as they say! Thanks again for your interest and help.

BTW - password for anything that requires it is "change"

[Scott Emerick]

Here you go

Shawn,

See if this is what you were looking for.

If the category code is 100 or greater the value is negative.

This happens on both your forms now. When you set up a paycode and when you edit it on the employee info form.

Scott

[smrogers]

Scott,

That's great -- I appreciate your help. Haven't gotten to the "events" tab in the book yet... guess it's worth a look!

I'm assuming the calculation made on the "events" tab will happen any time the field is written to (changes, etc). Also, curious about the "this.value" variable -- I ordered the new XBasic book, but have little experience with it (Xbasic) so far. Is the "this.value" variable a system variable that is used to define the value of the current field? And, instead of "this.value = this.value - (this.value*2)", could you also use "this.value = this.value * -1" ?

Will you be going to that seminar in June? It's about 20 min from where I work and am considering it... think it would be worth while for a newbie?

Thanks again!

[Scott Emerick]

Also, curious about the "this.value" variable

Check out the Help File

instead of "this.value = this.value - (this.value*2)", could you also use "this.value = this.value * -1" ?

Yes, I don't know what I was thinking ... lol

Will you be going to that seminar in June? It's about 20 min from where I work and am considering it... think it would be worth while for a newbie?

I will be there and you should go. It's worth it and you get to meet all of the names on the board and even have some fun!