Script Troubles

[oakbridge]

I'm working on a order entry system in FM6. I'm trying to handle taxes automatically. My logic will review the following in sequence:

- tax status for the customer

- tax status at the product level based on the province (i.e. State for our U.S. readers )

My idea was to first check the product to see the customer has tax exempt status, if they do, I set the tax status for the line item to zero. If not, then see what the tax status and rate is for the product for the province, then set it accordingly.

My script works but not all the time. I have the following databases:

price list

client

req_hdr (order header)

req_dtl (order detail or line item)

In my req_dtl database, I have a field called tax status. It is an auto-enter calculation:

If(req_hdr::req_cust_tax_status = "PSTExempt", 0, (Case(req_hdr::req_billing_prov = "ON", Price List::PST Status, req_hdr::req_billing_prov = "BC", Price List::PST Status BC, req_hdr::req_billing_prov = "AB", 0)))

req_hdr::req_cust_tax_status and req_hdr::req_billing_prov are lookup fields based on the values in the client database.

I used this calculation based on the logic that if the customer is tax exempt, set the tax status for this line item to zero or false. If the billing province is equal to ON then use the standard PST status value from the price list database, same with BC and Alberta.

Entry is made using a layout in the req_hdr database with a portal to the req_dtl database. I enter the product number and it does a lookup of the various fields, description, cost, selling price and tax status. My logic fails, no tax is added.

However, if I open the req_dtl database, and scroll down to the recently added records THEN go back to the req_hdr layout and re-enter the product number (I call it dirtying it to make the lookup fields recalculate), then it works!

AND any subsequent line item entries in the portal work as well, the tax is added correctly. However if I go to a new record, the logic fails.

Edit update: Further testing has shown that I don't even need to scroll down the the actual record in the related req_dtl database, I just have to switch to it and then go back and re-enter the product number and all taxes are calculated correctly.

I'd appreciate any suggestions.

Thank you,

Edited February 24, 2006 by Guest

[Genx]

a) what about your australian readers?

id make some suggestions, but filemaker not 7 and not 8 confuse me hehe.

c) try this instead

Case(

req_hdr::req_cust_tax_status = "PSTExempt", 0,

req_hdr::req_billing_prov = "ON" , PriceList::Pst Status,

req_hdr::req_billing_prov = "BC", Price List::PST Status BC,

req_hdr::req_billing_prov = "AB" , 0 )

d) is it standard to use "," to seperate parameters in anything < fm7?

~Genx

[oakbridge]

I'll try it again, but I believe that was the way I set up my original code, just the way you suggested. I even tried moving the pst_exempt from the back of the case list to the front, just in case it was getting messed up that way. Same results.

Yes it was the notation in FM6 to use ',' to separate parameters. I'm playing around with FM8 and I noticed that change.

Thanks,

[oakbridge]

No the behaviour is still the same. No tax shows up for those situations when it should. However if I just open the req_dtl database window, and then move back to the req_hdr window, any further entries will have the tax calculated correctly. But the original line entry's tax will be missing.

I'm wondering if there is some kind of scripting that I could do which would trigger on entry.

Any other suggestions would be appreciated.

Thanks,

[oakbridge]

Well I've run the same scenario through FM8 and it works correctly. Unfortunately I need to find a workable solution for FM6, at least for the short term.

Is there any equivilant to onChange? What I'm looking for is a script to trigger when the value of a field changes. I could create a workaround to the tax problem that I'm having if I can find a way for a script to be triggered whenever the contents of the req_product_number field are changed. I've created the script and it works, just need to find a way to trigger it without making the user run it.

Thanks,

Edited February 28, 2006 by Guest
Found new info after testing in FM8