AnsweredAssumed Answered

Increment Gap - Membership number

Question asked by CG_1 on Mar 7, 2014
Latest reply on Apr 15, 2014 by CG_1


Increment Gap - Membership number


     Hi there.

     I'm building a membership database.

     Each member record has an unique ID and that's ok, and it's used as Key.

Each member has to have a Membership Number, which is auto-incremented as you create a new record, BUT:

     - A member can become "Inactive" (there's a Status field) and therefore loses it's membership number, although it remains in the database

     - A member that was "Inactive" can come "Active" and therefore needs a new membership number (Like on a new record)

     - Everytime a member becomes "Inactive", the sequence of membership numbers must reorder itself so there are no gaps


What I have:

     - New Record button which uses FM auto-increment on creation

     - "Status" field with options "Active" and "Inactive"

     - "Membership Number" field, with the options:

         - Number, Indexed, Auto-enter Serial, Auto-enter Calculation replaces existing value

         - If ( Status="Inactive" ; Membership Number ="0";If ( Status="Active"; ...

     - If I change member status to inactive, the number becomes 0, but i don't know how to build the rest of the condition or if I should build a script (the new record button maybe could benefit from that too)


     Any help?

     Thanks in advance.