3 Replies Latest reply on May 4, 2010 9:25 AM by fitch

    Help with calculation

    Joanner

      Title

      Help with calculation

      Post

      We have a database for timesheets/billings where the employee enters the quantity of hours, accommodations, mileage etc.,  and the rates are entered automatically via the rates table.  I'm running into a problem where the accommodation rate fluctuates and am trying to come up with a calculation such as: 

       

      (lineItem_PRODUCT::EmpRate)  
      and If  (order_lineItem_PRODUCT::ItemName="Accommodations (Hotel/Motel)" ; order_lineItem_PRODUCT::EmpRate= ( ORDER::Accommodation Rate ))

       

      I've set up  a seperate area on the order where the employee enters the accommodation rate and I want that cost to be used in the portal row for accommodation.

       

      Hopefully I've given enough information!

      Joanne

      Filemaker 10

        • 1. Re: Help with calculation
          fitch

          Is this what you mean?

           

          Case(

             order_lineItem_PRODUCT::ItemName="Accommodations (Hotel/Motel)" and

              ORDER::Accommodation Rate > 0 ;

              ORDER::Accommodation Rate ;

            lineItem_PRODUCT::EmpRate

          )

           

          It's generally a good idea to use an auto-entered value rather than a calculated field, so that your existing line items don't change when you change a value in the Product table.

           

          What you have to watch out for is that a related (line item) field will not re-evaluate when you change the override field in the parent (order) table. You want to make sure that the parent record is committed and the override value entered before creating line items, or else handle it with a script trigger.

          • 2. Re: Help with calculation
            Joanner

            Hi Tom,

             

            Thanks for your reply!  This is what I have entered, and it doesn't seem to want to chose the rate I have entered into the side field "Accommodation Rate" for the current Order: 

             

            RatePerUnitEmpl = lineItem_PRODUCT::EmpRate  and
            Case (order_lineItem_PRODUCT::ItemName="Accommodations (Hotel/Motel)" and ORDER::Accommodation Rate > 0 ;ORDER::Accommodation Rate ; lineItem_PRODUCT::EmpRate)

             

            It's set to "auto enter Calculation, replaces existing value" 

             

            I really appreciate your help!  I'm  presently going in, and allowing myself to enter the number manually but it's getting a bit silly! 

             

            Regards,

            Joanne

             

             

             

             

            • 3. Re: Help with calculation
              fitch

              The difference between your calculation and mine doesn't make sense to me. Our Case statements are the same, essentially saying, "if there's an Accommodation Rate, use that, else use the EmpRate." But then you're adding to that a logical test, which turns the whole thing into, "is EmpRate true? AND is the Case statement true?" I'd guess the answer is generally yes, and it returns a 1. Did you try my calc as written? Did it work? Did you understand the "watch out" warning I wrote?