3 Replies Latest reply on May 8, 2010 7:20 AM by LaRetta_1

    calculation problem

    cutykamu

      Title

      calculation problem

      Post

      i’m new to filemaker pro, here is what i’m looking for:

       

      i have fields “price”, "charges 1”, "charges 2”, “terms 1”, “terms 2”, "final price"

       

      “price" is the price field for example 1.00, "charges 1" i will adding records for example 0.01 or -0.01, “terms 1” i will be using records as A or B terms, 

      "charges 2” i will be adding records for example 0.01 or -0.01, “terms 2” i will be using records as A or B terms, 

       

      “final price” will be the calculation with “price” + “charges 1” + “charges 2” = “final price"

       

      what i want is if my “terms 1” having a record A than “charges 1” should have record 0.01 or if i choose "terms 1” record as b than “charges 1” should have records -0.01 automatically

       

      pls help !!!

        • 1. Re: calculation problem
          raybaudi

          Hi

           

          Auto-Enter calcularion for "charge 1" :

           

          Case(
          terms 1 = "A" ; 0.01 ;
          terms 1 = "B" ; -0.01
          )

           

          Note that it is possible that I haven't understand correctly your question.


          • 2. Re: calculation problem
            LaRetta_1

            It would probably be better if you have your charges (with their terms) as a related lineitems table.  What if you want to add an additonal charge?  If you ever do, you will have to change many things if you keep down this path.  Also, can charges be added on different dates?  You cannot track that fact (as it stands) because you don't have a Date1 and Date2 field.  Whenever you see this type of situation (particularly on something so ever-changing), it is best to use a related table.  Design will be much simpler as it grows.

             

            Anyway, I wonder if you are indicating that if Terms = "B", the charge should be negative (whatever the charges).  But we would need business rule ... what if the charge was entered as a negative?  Would you want B terms to REVERSE the charge whatever it is?  Or, more probably, keep the charge negative if B?  I will guess that, even if User properly entered a negative number, you would wish to keep that number negative (implying that Terms B means Credit). 

             

            Please clarify ...

             

            • 3. Re: calculation problem
              LaRetta_1

              The problem I see is if the User changes the terms.  If you apply auto-enter to the charge field then you must uncheck 'replace existing value'.  If you always want the charge entered as simple negative if Terms = B (assuming some Users might forget to include a minus) then try this auto-enter (replace) on the charges 1 field:

               

              Let (

              n = Abs ( Self ) ;

              Case ( terms 1 = "B" ; n  * - 1 ;   n )

              )

               

              corrected red