Hello,

 

Any help on this would be greatly appreciated as I am well out of my comfort zone.

 

Basically I have 2 related tables, Jobs and Transport orders.

On the T.Os table I have a cost field and a cost with markup field. I also have a field to select whether the job is chargeable or not with radio buttons yes/no. 

On the jobs table I have a portal showing related T.Os

 

What I am trying to do is under the portal on the jobs table have a field that totals the chargeable and a field that totals non chargeable T.Os.

 

I have tried doing this with a calculation field with and IF statement that says if the chargeable field = Yes, sum charges but it isn't working.

 

I hope this makes sense.

 

james

Add a calculation field  ChargeableKey to Jobs:   ChargeableKey = "Yes"

 

Create two new Table Occurrences  of Transport Orders (too many TOs there) ;^) .  Let's call them TOCharge and TOnoCharge.

 

Make the relationships to these the same as your portal relationship with one additional criterion:

 

For TOCharge, add the condition Jobs::ChargableKey = TOCharge::chargeable

For TOnoCharge, add  the condition Jobs::ChargableKey ≠ TOCharge::chargeable

 

On Jobs, add another calculation field TotalCharge = Sum(TOCharge::price), and another TotalnoCharge = Sum(TOnoCharge::price).

 

NOTE: Make sure that your Transport Orders::chargeable field has an auto-entered value of either Yes or No; otherwise, a blank field will mess up the totals.

On the T.Os table I have a cost field and a cost with markup field. I also have a field to select whether the job is chargeable or not

 

Don't you mean "whether the Transport Order is chargeable or not"?

 

 

 

I have tried doing this with a calculation field with and IF statement that says if the chargeable field = Yes, sum charges but it isn't working.

 

Please describe "isn't working".

Thanks for the reply Doughemi,

 

I hadn't thought of using table occurrences, I will give this a go and let you know how I get on.

 

Sorry that is correct comment I meant Transport orders and not jobs. I basically had a field on Jobs, that via the same relationship as the portal, had a calculation that said If chargeable = Yes then sum cost with markup if No then sum cost but my knowledge of calculation fields is limited at best.

 

Thanks again

 

James

I am afraid you're still not being entirely clear. In general, you need to have a calculation field in the child ( Transport Orders) table, something like =

 

Case ( Chargeable = "Yes" ; Amount )

 

Then in the parent (Jobs) table use another calculation field to sum the calculation field. That way you do not need to add another TO of Transport Orders.

Thanks Comment, this works brilliantly. I also added a field on the Transport Orders page that combines both calculations Case ( Chargeable = "Yes" ; Amount ; Chargeable = "No" ; Amount with markup ) I then put this field in the portal on the jobs page which gives you the accurate totals.

 

Thanks again

 

James

Good. A  simpler way would be to make Chargeable a number field, and format it as a checkbox using a value list of "1" (single value). Then your calculation can become =

 

Case ( Chargeable ; Amount ; Amount with markup )

 

This is assuming you want 'Amount with markup' to be the default choice.