4 Replies Latest reply on Jun 6, 2015 7:49 AM by montshoman

    Calculating Prices With a Sliding Fee Scale

    montshoman

      Title

      Calculating Prices With a Sliding Fee Scale

      Post

       Once again thanks for your previous assistance, now that FM is calculating my total size, is there a way to do fee calculations with a sliding scale?  Basically the bigger unit you buy, the cheaper the per inch the fee is

      For Example if it measure 1-15 total inches the fee will be $15.20/inch
      if the total inches are 15.25-30 the fee will be $14.50/inch and so on, but to make it more complex there are 3 exact measurements that get discounted to a total dollar amount instead of a per inch charge (those are 35, 70, and 150)?

      I don't know if maybe I could use the > < and = signs to simplify this?  And if it's not too much to ask could this be set up so the salesman can override the price calculation (in case of mass quantity ordering, make goods, etc)?

      Thank you in advance for any advice or experiences you can lend

        • 1. Re: Calculating Prices With a Sliding Fee Scale
          philmodjunk

          You can set up a related table with one field as the Max inches and the other field as the rate to charge.

          Your original table can then use an auto-enter field option and the relationship to copy over the rate. Since this rate would be copied into a simple number field, the user can be allowed to modify the rate when necessary.

          Take a look at the Looked up Value auto-enter option and note the options that you can select if there is "no exact match".

          Note that with this related table approach, updating your pricing tiers is a data entry task not a database design change task.

          • 2. Re: Calculating Prices With a Sliding Fee Scale
            montshoman

            Sorry PhilModJunk, I'm somewhat lost, I can't help but think I'm trying to make this too difficult

            I set up 2 fields in a new table I created, one I named Max Inches, the other I named Rate,  so are you saying the next step is to enter the max number of inches in each price break section into the comments section, would that be separated by a comma or a less than sign, then enter the appropriate /inch rate into the rate field comments making sure to stay in the same order also separating them by a comma or something else?

            Or am I completely off base?

            Once again thank you so much for your assistance

            • 3. Re: Calculating Prices With a Sliding Fee Scale
              philmodjunk

              Create a series of records on a layout based on this new table. Enter the max inches and rate into each field. Using your example data, it might look something like this:

              MaxInches          Rate
              15                        15.2
              30                         14.5

              Then set up this relationship in Manage | Database | Relationships:

              YourTable::Inches = RateTable::MaxInches

              Then, in YourTable, set up field options for YourTable::Rate to use a looked up value field option:

              Starting From: YourTable
              Look up From Related Table: RateTable

              And then, select over in the "if no exact match, use: next higher value"

              That way if you enter 25.2 into YourTable::Inches, the YourTable::Rate field auto-enters 14.5. If you enter 7, you get 15.2. This is an auto-entered value so you can allow the user to edit this looked up value if necessary.

              • 4. Re: Calculating Prices With a Sliding Fee Scale
                montshoman

                 

                I have made some progress PhilModJunk, but I've made some kind of mistake since it's not figuring correctly, I think I may have missed a step or something.

                I set up a Rate Table with the 2 fields MaxInches and PerInchFee, and created the records as you advised, but when the field TotalInches populates automatically (from my previous posted question, that you answered, which works so AWESOME, thank you again) it figures too low of a rate.

                I'm trying to take TotalInches and multiply it by the correct PerInchFee from the Rate Table and auto enter the field "Amount".  Right now I have the Amount field set up as a look up value per your above response.  Can I change the Amount field to be a look up field and a calculation field, or what step have I missed?

                Once again thank you for all of your help