3 Replies Latest reply on Jul 10, 2013 6:44 AM by philmodjunk

    Automated Levy Calculations

    Matty_1

      Title

      Automated Levy Calculations

      Post

           I'm wondering if I could get a suggestion on how to best move forward with an automated levy calculation.  Certain purchases within our organization have applicable levies.  These levies can be percentage based or $/metric tonne purchased.

           I don't want to use triggers because typically the levy information gets automatically populated in the purchase transaction slips via a lookup function calling upon the purchase contracts fields therefor in any typical situation the fields don't get modified.

           I'm thinking the best solution would be an auto-entered calculation that verifies the levy association chosen, calls upon it's record in a levy table (yet to be created), finds what type of calculation is needed then applies the calculation once all the required fields are filled.  By this I mean creating a table that simply has two fields, the levy association and it's type (percentage or $/MT)  Is that something that can be done in an auto-calculation field?  I can't recall which script step could be used to find the levy record based on it's name and then pull it's type, if that's even possible or did I dream this up?

           I'm open to any better solution if someone's got one out there.  Fell free to hit me back and ask for more information, I'm horrible at leaving out important information.

        • 1. Re: Automated Levy Calculations
          philmodjunk

               Levies = taxes?

               I see no need for triggers either.

               There are two basic approaches you can use with your auto-enter calculation:

               Do the calculation in the auto-enter expression:

               IF ( LevyType = "PCT" ; Levies::Rate * MT  ;  Levies::PerUnitTon * MT )

               Set up the calculation in your related Levies table:

               Evaluate ( Levies::LevyCalc )

               In the second case, LevyCalc is a text field where you'd enter a FileMaker calc such as "Levies::Rate * MT" for percent calculations and "Levies::PerUnitTon * MT" for amount per metric ton calculations.

               The second option is trickier to set up and to debug issues when they fail to calc correctly but makes adding additional formulae a matter of data entry--you don't have to redefine a field option's calculation to handle additional formulas.

          • 2. Re: Automated Levy Calculations
            Matty_1
                 

            Levies = taxes?

            Yes I suppose you could say it's something like that but it's not constant and clearly defined like a tax would be.  It's only applied in certain situations and varies on a case by case basis.

                 

            Do the calculation in the auto-enter expression:

                 

                      IF ( LevyType = "PCT" ; Levies::Rate * MT  ;  Levies::PerUnitTon * MT )

                 I'm trying to avoid this for the very reason you mention later in your reply.  Ideally I'd like to give the user the ability to add in more levies and their "types" and teaching them how to crack open an calculation field just isn't an option.  So what  would like it something along these lines;  if it's a blue levy calculate on a % basis, red $/MT, orange % basis etc.

                 The auto calc field would look at which association is selected, go to the levy records find the same association, take the type and apply the calculation.

            • 3. Re: Automated Levy Calculations
              philmodjunk

                   Yet you still have the same calculation formula for blue and orange in your example--leaving you with only two formulas. The inputs to those two methods may differ--the rate or unit amount per ton may differ, but the math performed with those values appears to always be one of the two. The rate and unit cost values can be entered into your related Levies table for each "color" along with an indicator field that tells your auto-enter calc which method to use to calculate the levied amount. The relationship to that levies table will then match by that "color" to access the correct calculation method and inputs for computing it.