Hoping for some help with a Calculation...

[FruitFul]

Greetings!

I am working on my Filemaker Solution (backbone to a Photography Studio). On my Order Page, I currently have the following Calculation for the Total:

((((Invoice::Subtotal - DiscountDollar) - Invoice::PercTotal) + Invoice::TaxTotal ) - DiscountCredit)

Subtotal/Tax should be self explainitory... The Dollar Discount is just that - an option to take $X dollars off of the total. The PercTotal is an option to take a percentage off of selected items.

Discount Credit is the new part I am trying to Add (everything minus that has worked great up until now). Based on my business (or maybe just my nature - I tend to get into a lot of 'Barter' Situations.... Trading photos for hair cuts,etc. This new 'Discount Credit' is trying to factor in Studio (in-house) credit that the client might have.

The Questions:

-As it is working right now, there may be situations where the client has more credit then they are actually using... I am hoping to somehow only drop them to Zero and leave any remaining credit they might have?

-Also, as it stands right now, I enter the credit under peoples accounts. As I generate an order for them, it pulls in that information. BUT it is not modified once the deduction is taken into account...

An Example: Say John Doe comes in, he has $1200 Credit for giving my kid braces. He runs up $850 in prints. As it works right now it would show the total as -$350, and NOT modify that $1200...

Basically: Is there a way for me to have it drop down to zero, AND to modify the remaining 'credit' balance accordingly?

My apologies if the answer is right under my nose... Maybe I am just overcomplicating things in my head

[FruitFul]

Well I came up with a script that seems to work - however this is Less than ideal. It forces me to add a button that has to be pushed to complete the order - would prefer NOT to need this if someone else sees a better solution?

Here is the script:

If [ (Invoice::DiscountCredit > 0) and (Invoice::Total > 0) ]

Set Field [ Invoice::GrandTotal; Invoice::Total - Invoice::DiscountCredit ]

Else If [ Invoice::DiscountCredit ≤ 0 ]

Set Field [ Invoice::GrandTotal; Invoice::Total ]

End If

If [ Invoice::GrandTotal < 0 ]

Set Field [ Invoice::gCredit; 0 - Invoice::GrandTotal ]

Set Field [ Invoice::GrandTotal; 0 ]

Set Field [ Invoice::DiscountCredit; Invoice::gCredit ]

Set Field [ Company::Credit; Invoice::gCredit ]

Else If [ Invoice::GrandTotal > 0 ]

Set Field [ Invoice::DiscountCredit; 0 ]

Set Field [ Company::Credit; 0 ]

End If

GrandTotal (as well as gCredit) is a new field that I had to add, and will have to adjust to other layouts that currently call upon the total for printing, or show the total of the order.

[SlimJim]

Mathematically it is simple. For brevity call the total you have calculated without the Discount credit as TSF and call the discount credit DC.

Then the final part of the calculation is

Grand Total= max(TSF - DC;0}

and the new value of DC

DC = max(0,DC-TSF)

[FruitFul]

Thanks! That is a great start to a much better solution I was able to modify the GrandTotal into a calculation that works - but I came into a problem with the Credit...

I first tried turning the Credit field into a calculation, but that just turned it and the GrandTotal field into ? marks... I then tried making a secondary field as the calculation to see if that would force the Credit field to change as items were added (and the credit was used up) - but this did not work either...

Is there someway to use that Max(0;DiscountCredit-Total) calc (DC = max(0,DC-TSF) as you have it) to modify the Credit field?

[comment]

IMHO, from an accounting point of view, an invoice for $850 in prints should have the total of $850. How the customer decides to pay for it is a separate issue.

It seems that you already run a system of accounts. So, an invoice record could simply be a crediting element in the customer's account.