1 2 Previous Next 17 Replies Latest reply on Apr 15, 2014 6:25 AM by CG_1

    Increment Gap - Membership number

    CG_1

      Title

      Increment Gap - Membership number

      Post

           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.

            

            

        • 1. Re: Increment Gap - Membership number
          schamblee

               It is not a wise design to use a key field as anything other than a key field.  This design will cause you to have miss linked records.  You should have a key field then you should have a member id field.   There is no real benefit to change the membership number just because a record becomes inactive, FileMaker can filter records based on the status field.  A key field should never change.

          • 2. Re: Increment Gap - Membership number
            philmodjunk

                 I don't think that C. G. is using the membership number as a match field in relationships.

                 But I am scratching my head as to what possible use you would put such a number. With scripting, it can be done, but I can't help thinking that if we knew how you were going to use this sequential, gap free number series, we might be able to suggest alternatives that are far simpler to manage. Depending on what you intend to do with this field, you might not need any field at all.

            • 3. Re: Increment Gap - Membership number
              CG_1

                   I have a key - and it's ok that it has gaps if necessary. It's auto-incremented, it's for database use only, and it has relationships.

                   Membership number is for the member to know, and according to internal club decisions, it has to change when someone quits membership. So it's for the club to know and the member. Every year it's revised, they remove people who quit, people who die, people who don't pay, and your number, as a member may change because of that, since there can't be gaps. I'm not crossing membership number with any other table, since I thought it could be difficult to manage the possibility of changing every year. It's just a field from the member page.

                   It's just for that... 

              • 4. Re: Increment Gap - Membership number
                philmodjunk

                     Since this happens once a year, have a script perform a find for all active members. Then have the script use Replace Field Contents with the serial number option to assign a number to them.

                     PS. Doesn't sound like this is your call to make, but if I were a member, I wouldn't want my member number to change each year. I'd prefer it to stay the same and wouldn't care if that resulted in "gaps" in the sequence.

                • 5. Re: Increment Gap - Membership number
                  CG_1

                       I agree with you. I would prefer to have the same number. But as you said, it's not my call.

                       And when a member becomes inactive and then active again? It has to get a new number. They never delete the record, the membership number becomes "0". If they come back, they get a new number at that exact time...

                       This is so confusing... It was a lot easier , i believe, to fill the gaps with new members... I think, but not my call.

                  • 6. Re: Increment Gap - Membership number
                    philmodjunk

                         That should not be a problem if you are updating once a year.

                         And if you need to add more members through out the year whether new or due to a status change, your script can auto enter the next value in the sequence.

                         Do a self join that only links to active members. (Define a calculation field constActive that you match to your status field.)

                         Max ( Members 2::MemberNumber ) + 1

                         will auto-enter the next value in the series.

                         But put a Unique Values validation option on MemberNumber just in case you might have two users adding new members at the same time--there's a slight chance that two new members could get the same exact member number with this method.

                    • 7. Re: Increment Gap - Membership number
                      CG_1

                           Thank you. I will try and report :)

                      • 8. Re: Increment Gap - Membership number
                        CG_1

                             Hi there.

                             Been solving other things and today tried your suggestion.

                             The "renumbering" works great.

                             Have some difficulties with the rest: "Do a self join that only links to active members. (Define a calculation field constActive that you match to your status field.)" Little help?

                              

                        • 9. Re: Increment Gap - Membership number
                          philmodjunk

                               If you define a calculation field named constActive, you can select text as the result type and enter the text you want in quotes such as:

                               "Active"

                               Then that field will have the same value in every record of your table and you can use it as a match field to match to only records that have "Active" in teh matching field on the other side of the relationship.

                               Or was it "self join" the part that is causing you trouble?

                          • 10. Re: Increment Gap - Membership number
                            CG_1

                                 So i created the field as you said, self joined the Contacts table (constActive > Status).

                                 MemberNumber -->  If ( Status = "Active"; Max ( Members 2::MemberNumber ) + 1 )

                                 What happens is when Inactive becomes Active, the number becomes 1 since when inactive is 0 or null. I'm missing something...

                                  

                                 UPDATE::

                                 There are some conflicts but I'm going to test it some more

                                 I believe I've solved everything except for the initial script to "renumber". Doesn't work anymore...

                                  

                                 (...)

                                 Enter Find Mode [ ]

                                 Set Field [Members::Status;"Active"]

                                 Perform Find [ ]

                                 Replace Field Contents [No dialog; Members::MemberNumber;Serial Numbers]

                                 (...)

                            • 11. Re: Increment Gap - Membership number
                              philmodjunk

                                   Exactly how are you using this calculation?

                                    If ( Status = "Active"; Max ( Members 2::MemberNumber ) + 1 )

                                   Is this in a script step,\ the definition for a calculation field, or an auto-enter calculation?

                              • 12. Re: Increment Gap - Membership number
                                CG_1

                                     MemberNumber has an auto-enter serial number on commit and an auto-enter calculated value with that "formula".

                                     And unique number validation.

                                • 13. Re: Increment Gap - Membership number
                                  philmodjunk

                                       MemberNumber has an auto-enter serial number on commit and an auto-enter calculated value with that "formula".

                                       And that won't work and is why you can't renumber the fields.

                                       Keep it auto-enter serial and remove the calculation option.

                                  • 14. Re: Increment Gap - Membership number
                                    CG_1

                                         But if I remove the calculation, where should I use it, so that when I change State to Active, it sums 1 to the new number for the member?

                                    1 2 Previous Next