calc field changes between browse and preview

[stefangs]

i have a master file with companies which has an order form with line items to a child file.. the master file has a check box whether tax should be added for orders from this company. the child file has the following tax calc field:

If(IsEmpty(company::taxOption), 0, subtotal * (company::tax rate / 100))

so if the check box is empty, don't add tax, otherwise if the box is ticked, add a global tax rate which is in the master file. the order form has the line items in the body part and the subtotal (summary), tax (calc), and total (calc) in a trailing grand summary part.

now for the question: when i have the child file order form visible in browse mode, i can toggle the check box in the master file on/off and i can see how tax gets added or not. but when i view the order form in preview mode, the tax is always 0. what am i doing wrong??

[Geeksharka]

Two parts to this.

First, I wouldn't have the TaxRate for any given invoice be hooked (live) up to a global field. What happens if the tax rate changes? -- all the old invoices' taxes will change with it (not a good thing). I recommend you create, per invoice, a TaxRate field, that does a lookup from the Global Tax Rate field when the invoice is created.

Second part, as to why the calc field changes from Browse to Preview mode -- well, I don't know. First thing to check, are you summarizing anything (sub-summary parts in the layout?) Where is the fauly field located on the layout?

[stefangs]

1. you are certainly right about changes to the old invoices when the tax rate is defined globally. in this case, i didn't care, since old invoices will never get looked at again, but i may follow your advice regardless.

2. i'm summarizing the net amounts and then add the tax on the summary field. the summary field, tax amount, and totals are all in a trailing grand summary part. there aren't any sub-summaries in the layout. hm, perhaps you need to have one if you want a grand summary? haven't tried this, because i didn't have to sort on anything, just put the items (in the body part) in any old order one after another.

[Geeksharka]

i'm summarizing the net amounts and then add the tax on the summary field

Hmm. This sounds a little dangerous. I do not recommend using a summary field to represent an Invoice subtotal. Your invoice should relate to Line Items via a relationship, and then in the Invoice record you should use a calc field for an Invoice subtotal that is the Sum of the related Line Item price extensions (unit x qty). THEN apply the taxes to that calc field, not a summarized one.

The problem with summarized fields is that they change depending on which current set you're browsing.

It's hard to tell what structure you're using, but basically One Client can have Many Invoices, and Each Invoice can have Many Line Items. If you stick to that, using a separate Table for Clients, Invoice, and Line Items, you should be able to avoid the issues you're having. (Printing will be much easier too).

[stefangs]

actually, i'm always calculating on the summary field, because it's quicker than a calc in the master file, but i do have the child records in a portal. when i want to summarize, i use 'go to related record' with the show only option turned on, so that i'm including the proper set. unless i'm previewing or printing, the child file (and the summary field) are actually not visible, so it works out.

because i still couldn't figure out why the calc field changes in browse vs. preview modes, i've made a new template which only includes the bare essentials to this problem and... it worked! so no wonder i couldn't figure out what i was doing wrong - this doesn't seem to be the problem. so far, i have no idea where to look, but at least my original plan worked. i'll have double check my code and find the problem elsewhere, i'm afraid. thanks much for your help.