Field Level Calculations

[Ted Giles]

Client has 2 numeric fields;
Meters, Yards.
They can enter the fields in either order, Yards OR Meters.
Depending on which has been entered, I need to calculate the other.

The calculation is simple, however the order of entry is more problematic.
I cannot simply Skip a field with data present as a mistake may have been made.

Logically;
Enter Meters, Insert calculation into Yards - easy
Edit Yards, recalculate Meters - data already present
Enter Yards, Insert calculation into Meters

All ideas welcome and off to review Script conditional firing in the meantime.

[Stan Mathews]

Re: Field Level Calculations

First thought would be to code the onchange event of each to calculate the other.

[Ted Giles]

Re: Field Level Calculations

Thanks Stan. I was about to post that I had a solution similar to the above.
Appreciate your time.

[Ted Giles]

Re: Field Level Calculations

So I won't get in a loop with OnChange?

[Stan Mathews]

Re: Field Level Calculations

For type-in fields, the onchange event fires when the control looses focus.

Since the value is being changed behind the scenes with code and the object is never given focus, no loop.

My initial thought was to use the ondepart event for the fields but that would require coding to skip over the second of them, in tab order, in the ondepart of the first and force the user to click or back-tab into the second to change it.

[Ted Giles]

Re: Field Level Calculations

Spot on Stan. I have just tested your suggestion and no loop.
The way I approached it initially was to set a procedure to clear the fields and whichever was entered recalculated the other.
The OnChange works if there is an erroneous data entry so doesn't require fields to be cleared. IE more simple.

[Stan Mathews]

Re: Field Level Calculations

Simple is my middle name.

[MoGrace]

Re: Field Level Calculations

Quickbooks does this on an invoice to calculate the amount when you change the quantity and / or price ; and if you change the amount, it recalculates the price - always wondered how to do that.

Do you think it could be done at the field rule level so it would work the same in a browse?

[Stan Mathews]

Re: Field Level Calculations

Seems to work ok in the field rules

onwrotefield event for price field -

tbl = table.current()
tbl.amount = tbl.qty*tbl.price

onwrotefield event for amount field -

tbl = table.current()
tbl.price = tbl.amount/tbl.qty

Not sure what sort of logic you might want to handle uneven divisors. Suppose quantity is 3 and price is 11.50, yields amount 34.50. Change amount to 34.00, yields price 11.3333....., but extension of 3*11.33 (as stored in a field with two decimal places) is 33.99.

Probably something like

tbl = table.current()
tbl.price = tbl.amount/tbl.qty
if tbl.amount/tbl.qty <> round(tbl.amount/tbl.qty,2)
tbl.amount = round(tbl.amount/tbl.qty,2)*tbl.qty
end if

and you would, of course, need to code the onwrotefield event of the quantity field to recalculate the amount based on the price.

[MoGrace]

Re: Field Level Calculations

The table I am thinking of is one I use to import summary invoices from Alpha into QB. QB allows 5 decimal places for price, but the import can use either the price or the amount field. And since Alpha is used to print the detailed invoices, I can import fixed values for quantity and amount and let QB calc the price. So in the temp table I create in Alpha, if the summary is off a penny or 10, this field rule will allow me to correct the total amount I need to match before importing. Adding an OnKey event to use the + and - keys to change the amount by pennies, makes this a really quick fix in Alpha using a browse view.

Well quick for me since I usually summarize less than 20 invoices at a time.

hmmm, now that I think about that a bit - the OnKey event may be all I really need if I can skip the price field in the import...

[Mike Wilson]

Re: Field Level Calculations

This is just another example of why I do data entry using dialogs. So much much more control. All calculations and checks done then enter data to the tables.

[MoGrace]

Re: Field Level Calculations

I am using Transaction Pro Importer to bring the Alpha data into QB and I discovered a command line switch I can use to select Amount instead of Price - which solves the entire problem and allows QB to calculate the price instead of the amount.