4 Replies Latest reply on Jan 18, 2017 9:25 AM by philmodjunk

    Multiple options in calculation fields


      I have a database in which members in different categories pay different subscription rates, and I would like to link the member category to its relevant sub rate.
      For example, member category A (one field) would require to pay Subscription rate 1 (second field), Member category B would require to pay sub rate 2, and so on. In total there are 8 membership categories, and 6 subscription rates (some categories have the same sub rate).
      I have tried this for one option and it has worked well, but I cannot see how to use it for several options in each of the relevant fields.
      If what I want to do is possible, could you please be kind enough to give me a couple of examples on how this can be done.

      Any help would be much appreciated !
      Monsieur Bouchon

        • 1. Re: Multiple options in calculation fields

          Bonjour, Monsieur Bouchon!


          Probably best to use a lookup table, where each record is a combination of member category and subscription rate.


          Now not only do you not have to create a lengthy calculation, you also do not have to maintain it. Whenever you have a new rate and/or category, simply add the necessary records to the lookup table. When the rate (price) changes, change the price value in the appropriate records.


          To get the price, simply perform a lookup / auto-enter via:


          Member::id_category >-- SubscriptionCategory::id_category / SubscriptionCategory::id_rate >-- Rate::id


          An often overlooked benefit is that you have these business rules (that's what rates are) codified as data, not buried within code.

          1 of 1 people found this helpful
          • 2. Re: Multiple options in calculation fields

            Many thanks, erolst,

            I will give it a try.


            1 of 1 people found this helpful
            • 3. Re: Multiple options in calculation fields

              My apologies - I am unfamiliar with format of the website, and inadvertently clicked on the "0" for "like" in my earlier response.  In fact your message was most helpful, but I am still struggling to make "Look Up" work in the way I would like it to.  As a recap, what I am trying to do is to select a subscription rate to apply to individual members of a club in one table dependent on their membership type from a list a list of about 12 diferent rates and membership types in another table.  The latter contains a list of rates against types - mainly numbers, but a couple of texts ("Resigned 2016", for example and "Deceased").

              I would like the main database, which contains a lot of additional information irrelevant to the subscription, to be able to draw the subscription rate from the latter database, which contains just two fields "Subcription type" and "subscription rate").

              I cannot seem to get the "Lookup" function to do this;  Is "Lookup" capable of doing this ?  If not, is there a function which can do it ?  The fields common to the two databases are "Membership type" and "Subscription rate".

              At present, whenever there is a change in the subscription rate, I have to change a large number of records manually.  As you can imagine, this is rather time-consuming !!

              Any help would be much appreciated,

              J. C. Gray

              • 4. Re: Multiple options in calculation fields

                What relationship have you set up linking the two tables?


                "look up function" is actually a vague description of what you have tried to do as there are several methods you can use to Look up the current rate. What erolst is telling you to do is to set up an auto-enter option on the rate field in your original table that copies over a value from your new table of types and rates. You can do this with either the looked up value or calculation auto-enter options.

                1 of 1 people found this helpful