Calculation help!

[James Patrick]

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

[doughemi]

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.

[comment]

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".

[James Patrick]

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

[comment]

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.

[James Patrick]

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

[comment]

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.