Working with fileds and tax info

[abler]

I have created a main customer database with cust number, and I have also created a purchase order database to be able to track purchase orders for each client.

I have setup a relation with the purchase order database, and have used a portal within the customer database to display/add/modify the data from the purchase order database.

Here's the problem. on each purchase order I have a place for unit cost, then the tax on unit cost in another field. I also have a field right before unit cost that says TAXED? and options on 0 or the current tax rate. if left to 0 the tax isnt displayed in the unit tax field, but if current tax rate is selected it will show tax on the unit item.

Say I have like 5 items, and three of them have tax and 2 dont, I need to be able to show the total taxable amount, for the Purchaes order.

maybe im just not thinking this one through enough.

I dont know how to pull out of the list of unit cost only the items that are taxed. and I guess that is the question.

Thanks and I hope this isnt to confusing.

[comment]

I would define another calculation field =

Case ( Taxed, UnitCost)

and sum that.

You could also define another relationship to the same file, with a calculated key to show only taxable items.

BTW, there's something wrong with the structure you describe. Presumably, a client can have more than one purchase order, and each order can have multiple items. This requires THREE files: Clients, PurchaseOrders, POItems.

Edited October 4, 2006 by Guest

[abler]

Yeah I have it setup that way..

Actually I have the following databases:

Main Database that assigns the PO number, Sales person database, customer database and purchase order database.

I was just trying to keep it simple.

[abler]

Maybe this would be easier

Field Names: Taxed? UNit Cost Unit Tax

.0975 $96.17 $9.38

.0975 $18.81 $1.83

0 $120.00 $0.00

-----------------------------

$234.98 $11.21

The tax amount is showing correct, but I need to have a total for only the line items under unit cost that were taxed.