Increment Gap - Membership number
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)
Thanks in advance.