tax rate won't change in one record

[ken_s2007]

FM7 database. Has a global storage number field to hold the tax rate. I created a record with two line items (both quantity of 1) $23,595 and $4,990. (Note that this table uses repeating fields; I'm going to be changing that.) Then I realized the tax rate was wrong (it needs to be .09 (9%)). I took the database offline, changed the rate it in FMPro, reserved it, however the calculated tax will not change: it should be $2,572.65. If I create a new record with those same figures, the updated tax amount is correct. If I modify the original record so that both line items are $5.00, the tax is $.90 -- which is correct. The latter seems very odd; unless I'm overlooking something obvious, which is quite possible.

Here are the players:

tax_rate_current_g

Type: Number

Global storage

tax_rate_this_record

Type: Number

Auto Enter:

Calculated value: tax_rate_current_g

Do not evaluate if all fields are empty (enabled)

tax

Type: Calculation

tax_rate_this_record*Sum(subtotal)

Calculation result is: Number.

Do not evaluate if all fields are empty (enabled)

subtotal

Type: Calculation

qty*unit price

Calculation result is: Number.

Number of repetitions: 15

Do not evaluate if all fields are empty (enabled)

tax_total

Summary

Total of: Tax

Summarize repetition: All together

[Vaughan]

Existing records have the old tax rate stored in the tax_rate_this_record field. It;s an auto-enter so it does not automatically update.

[ken_s2007]

Thanks for the post, and I was thinking along those lines as well, but when I change the amounts in the two lines items to $5.00 each (in the original record), the (total) tax changes to $.90, not $.88 -- which equals the original rate (8.8%). This has me puzzled.

Ken

[Vaughan]

Ideally the tax rate should not be in a global field, it should be stored in a singe-record preferences table, in a normal number field. This prefs table is related via a cartesian X join so that all records can see it.

Once this is done we don't have to worry about weird things happening with global fields in a hosted multi-user environment.

[comment]

when I change the amounts in the two lines items to $5.00 each (in the original record), the (total) tax changes to $.90, not $.88

Which shows that tax_rate_this_record is .09 for this record. Perhaps you have changed the global before creating this record and before realizing that you need to make the change in single-user mode to make it stick?. Or you may have changed the tax_rate_this_record value directly. Can you reproduce this problem?

I don't know of any weird things happening with global fields.

[ken_s2007]

Thanks, I'll look into that technique.

On the specific situation I'm trying to understand, the behavior is the same even on a copy of the file I pulled off the server and have been working with on my computer in single user mode.

Ken

[ken_s2007]

"Which shows that tax_rate_this_record is .09 for this record."

One would think this is the case, but when I then replace the two line items with the original amounts, the tax then shows up at the .088 rate.

"Perhaps you have changed the global before creating this record and before realizing that you need to make the change in single-user mode to make it stick?."

Indeed I did, but I still have no understanding of why, after changing it in single-user mode, that the original rate sticks with the two original line items, yet with new values, the new rate kicks in. And if I put the original line item amounts back in, the rate seems to revert to the original rate.

"Or you may have changed the tax_rate_this_record value directly."

I don't believe so.

"Can you reproduce this problem?"

I will try and post again. Great suggestion, thanks.

Ken

[ken_s2007]

PROBLEM NOT REPRODUCIBLE.

Sorry for the trouble; thanks very much for the tips which will come in handy.

Ken

[comment]

Something doesn't make sense here. Either something impossible has happened, or something is missing from your description (my money is on option #2 ).

The way you have it, the 'tax_rate_this_record' field will be populated ONCE when the record is created (you can change it manually later, but you say you didn't). It will be populated by the contents of the global field 'tax_rate_current_g' at the moment the new record is created.

The field 'tax_rate_current_g' will initially have the value it had the last time the file was in single-user mode. But it CAN be changed by each user to any value, and records created by a user will be populated by that user's global value at that moment.

Modifying the line items (or any other field) should have no impact on the contents of the 'tax_rate_this_record' field.

[ken_s2007]

"Something doesn't make sense here."

I agree!

"Either something impossible has happened, or something is missing from your description (my money is on option #2)."

I disagree with the "impossible" statement -- things in software have been known to behave outside their planned behavior. In both the hosted file (after changing the global to the new tax rate), and then after using a local (non-served) of the same, I repeatedly changed the two line item amounts (which were yielding an .088 (the old) tax tate, to $5 each, and the tax rate would then shift to .09.

OTOH, despite my careful testing, I realize that operator error or observation is quite possible, so unless I can recreate the situation, I can only, in the field of objectivity, yield to your #2 assessment

The rest of what you wrote is spot in in terms of how things are supposed to work.

I can only state that I spent a couple of hours on this with the same results, but the next day, I could not reproduce. I'm sorry if I have in truth or in effect usurped you all's worthy time, but I appreciate very much you bearing with me.

Ken

[Vaughan]

Check to see if there is a startup or shutdown script that is setting the global tax rate field to something else.

[Vaughan]

Check which table occurrance the value list is looking at: I'll bet it's not the one you think it is.