4 Replies Latest reply on Jun 1, 2011 6:32 PM by JohnManna

    Limiting values based on related fields



      Limiting values based on related fields


      I have a design issue with my database.  What I need is a way to limit the value of a field based on the value in another field, and then I need to have it auto-update when the limiting value increases.  

      Members in my club earn points based on what they accomplish, called status.  This status is spent to give them certain privileges in the club.  We need a system where the amount of status spendable is limited by the types of activities the members participate in.  For example, when a member earns play status, cast status, and service status, his spendable status goes up (they all feed into the one pool).  What we would like to do is automate the limits on spendable status: members can earn as much cast and service status as they want, but can only spend a matched amount based on play status.

      Here is an example of what I mean:

      Bob has earned 500 play status, 700 cast status, and 200 service status.
      His spendable status should be 1200 (500 play, 500 cast, 200 service), with 200 unavailable (left over from cast). Once he spends his cast status, I'd like the database to be able to automatically move the 200 unavailable status to spendable.  I guess the trick lies in the fact that the database would have to know to take from service and cast first, then from play.

      Does that make sense?  If so, is there any way to make a database do this?

        • 1. Re: Limiting values based on related fields

          The following calculation example should give you some ideas:

          Define your UsuableTotalStatus field as:

          Min ( UnusedPlay ; PlayMax ) + Min ( UnusedCast ; CastMax ) + Min ( UnusedService ; ServiceMax )

          This is limit each category by it's maximum. You can replace "PlayMax", "CastMax" and "ServiceMax" with constants you enter in this calculation, but for greater flexibility, you should use values stored in either global fields or in fields of a related table so that you can change the maximums without redefining the calculation.

          • 2. Re: Limiting values based on related fields

            Thank you for your response.  This does fix the problem of how to limit the field values based on the value in the PlayStatus field.  However, I don't know if this fixes the second issue I've got: how to automatically draw status from these "pools" when it's been spent from the usable pool.  Perhaps I have to work out with the club director how exactly the system works, but I think we'd pull service status first, then cast status, then finally play status.  I think it would have to be a static calculation: after each event, we would have to calculate total spendable, then allow members to spend it. Now that I've limited cast status by play status, how do I spend it?

            • 3. Re: Limiting values based on related fields

              Does it really matter from which you "pull" first? Or do you just need to keep people within limits?

              There are two ways to handle this issue depending on how you answer that question.

              "UnusedPlay, UnusedCast, and UnusedService can be calculation fields that subtract the total points used from the total earned for that category.

              If you just need to stay within the overall limit, you can use a single field to compute a running total of points used and subtract it from the above calculation.

              Either way, future effortst that "earn points" add to a total in one field and activities that spend points would add to a different total so that the difference between the two always computes the available point total.

              • 4. Re: Limiting values based on related fields

                This helps a whole lot!  We've worked out how things are going to work, and with your help, things are going to be fairly simple.  Thanks!