circular calculation

[Robbydobbs]

I'm having a problem with a calculation.

I have a Grand Total which consists of Total Price + Sales Tax + Buyer Fees + Credit Card Surcharges and a Total Paid which consists of a Summary of the Total Payment. Total Payment consists of Payment to Surcharges.

So it would look like this:

[color:"green"] Grand Total = Total Price + Sales Tax + Buyer Fees + [color:"blue"] Credit Card Surcharges

[color:"purple"] Total Payment = [color:"brown"] Payment + [color:"blue"] Credit Card Surcharges (line items)

[color:"orange"] Total Paid = Summary of [color:"purple"] Total Payment

[color:"red"] Balance = [color:"green"] Grand Total - [color:"orange"] Total Paid Paid

Now up to this point everything is o.k.

But for the Total Payment I need to decide how to apply the payment. With some of the payments, there will be a credit card surcharge, depending on whether a credit card is used or not.

Now if the credit card is used I have to decide whether to just add the payment with out the surcharge added to the payment or add the payment and surcharge together.

If the [color:"brown"] payment is < the [color:"red"] balance , then I just add the [color:"brown"] payment to get the [color:"purple"] Total Payment .

If the [color:"brown"] payment is = to the [color:"red"] balance , then I add the [color:"brown"] payment and [color:"blue"] Credit Card Surcharges to get the [color:"purple"] Total Payment .

Whenever this is done I get a circular definiton and it won't let me continue.

Is There any way of getting around this? Or do I have this all screwed up?

[danjacoby]

Since Balance is based (in part) on TotalPaid, and TotalPaid is based on TotalPayment, and TotalPayment is based on Payment, you can't have Payment be based on Balance.

That's the circle.

Now I've defined the problem; I'm just too lazy today to determine the solution, but I suspect that you need to rethink your various totals first.

HTH,

Dan

[Chuck]

I think you need to give us some more information about what you're trying to do. It seems that you have a related payments file (where Total Payment is stored?). If that's the case, then why is Total Paid a summary field instead of a calc? If Total Paid is in fact a summary field, then why aren't you using GetSummary to access its value? I'm not sure if this gets any closer to solving your problem, but every time I try to track what the origin of it is, the summary field leaves me stumped.

Chuck

[Robbydobbs]

In the Payments database on the data_entry layout I have a portal showing items that were posted with amounts due on them from the Clerking database.

The clerking|price, clerking|buyers_fee and clerking|sales_tax_fee is shown in the portal.

I then have the c_total_price which is a sum of the (clerking|price), c_total_buyers_fee which is a sum of the (clerking|buyers_fee)

and the c_total_sales_tax which is a sum of the (clerking|sales_tax_fee).

I have a clerking|buyer_number that I do a find on. The find brings up all the information from the portals.

I then create a new record to post the payments in the payment database.

The fields:

payment, c_credit_card_fee, payment_type and c_total_payment are the fields that are populated in the line items.

Each new payment will start a new line item (record).

s_total_credit_card_surcharges is a summary of the [calculation field] c_credit_card_surcharges.

s_total_paid is a summary of the [calculation_field] c_total_payment

c_total_payment is a calculation of the [number_field] payment + [calculation field] c_credit_card_surcharges.

c_grand_total = c_total_price + c_total_sales_tax + c_total_buyers_fee + s_total_credit_card_surcharges

c_balance = c_grand_total - s_total_paid

But for the Total Payment I need to decide how to apply the payment. With some of the payments, there will be a credit card surcharge, depending on whether a credit card is used or not.

Now if the credit card is used I have to decide whether to just add the payment with out the surcharge added to the payment or add the payment and surcharge together.

If the payment is < the balance , then I insert the payment amount into the c_total_payment field.

If the payment is = to the balance , then I add the payment and c_credit_card_surcharges into the c_total_payment field.

I hope this makes more sense.

[danjacoby]

The problem is, you're determining what the payment is before you determine what the payment is.

You say "If the payment is < the balance..." -- but you haven't yet determined what the payment is. You need another field, call it "initialPaymentAmount", and enter the number there for the initial payment determination. Then the "payment" field will depend on whether the "initialPaymentAmount" is less than the Balance.

[Ugo DI LUCA]

Hi Dobbers,

Maybe Danjacoby is right, it"s quite hard for me to see the exact process without having all the relationships used and a sample viewable.

Have you tried using a calc Case(CreditcardPayment = "yes", ....) ?

I know I solved one of my circular calcs once with a Case calc.

[Robbydobbs]

I think I'm trying to do something that is just not possible. I'm going to have to result to the case function. Thanks for all your help and advise, it is always a pleasure to get advise and suggestions from all of you, as you guys are always quite informative.

Thanks again.

[danjacoby]

Dobbers, think about it. You're defining two separate things as "payment", which is why you're getting a circular calc. That's the problem. The solution, therefore, is either to separate your two "payment" definitions into two separate fields (my comment), or two get rid of one of them (the "Case" comment).

The trick to programming computers is to be absolutely logical. Any flaw in your logic will result in glitches. We're showing you where your logic flaw is.