7 Replies Latest reply on Feb 13, 2014 9:49 AM by SamJaques

    Unique IDs for only certain records



      Unique IDs for only certain records


           I'm trying to build a database of people involved with a medical clinic where most but not all of the records in the table are for members of the clinic. Each member needs a unique membership number, and these numbers should only increment with the addition of members, not with the addition of other people in the database.

           Is there any way I can set a field to automatically generate a unique membership number by incrementing past the last membership number, but only when the "Member" field is set to "Yes"?

        • 1. Re: Unique IDs for only certain records

               U can do it by script, find the relevant members -> sort by member-ID -> get the last number and add one. Alternativly U can split the table into two separate tables.

          • 2. Re: Unique IDs for only certain records

                 I suggest the following method as it avoids the possibility of getting two records with the same member ID if two or more users generate records for new members at the same time.

                 Let's call your current table "people". We'll define a new table called "Members". I'll assume that the primary key field in People is called __pkPeopleID. Define two fields only in Members: __pkMemberID and _fkPeopleID. Define __pkMemberID to auto-enter a serial number.

                 Define this relationship:


                 People::_pkPeopleID = Members::_fkPeopleID

                 enable "allow creation of records via this relationship" for Members in the above relationship.

                 Put Members::__pkMemberID on your People layout to show the member ID if they are a member of the clinic.

                 Use this script step to assign a member ID to a given record in People:

                 Set Field [ Members::_fkPeopleID ; People::__pkPeopleID ]

                 Note that if you run this script twice from the same People record, no change to your data takes place. Only if there is no related Members record will the script step create a new record in Members that then auto-enters a serial number in the new record which then appears in the Members::__pkMemberID field on your People layout.

            • 3. Re: Unique IDs for only certain records

                   Thanks for the ideas, but I slept on it and came up with a solution that doesn't involve a script and shouldn't create multiple identical member IDs.

                   I created a self-join relationship with the "People" table based on the following criteria: The "Person ID" in People 2 (a numerical, auto-incrementing field that every person, including non-members, has) is less than or equal to the person ID in People, and the Member status (Yes or No) is equal. So it links a person to everyone who's person ID is lower than them and is also a member (or also a non-member, but I don't care about that).

                   Then I sent the member number to a calculation: If(Member="Yes";Count([some field in person 2, doesn't matter what];""). So if they're not a member it leaves it blank, and if they are a member, it counts up how many records it's related to and uses that number. That should increment in the same way as the person ID, but not increment if a newly added person is not a member.

              • 4. Re: Unique IDs for only certain records

                     There's a specific reason that I did not recommend such an approach. If you have two or more people adding new records at the same time, you can get duplicate values. You could trip for this error, but my method avoids the issue altogether by using an auto-entered serial number.

                • 5. Re: Unique IDs for only certain records

                       Since the relationship is based on a person ID, which is an auto-entered serial number, doesn't that avoid the problem?

                       Suppose I have a person A and person B. If person A's person ID is greater than person B's, then person A's member number must be greater than person B's, because the number of records related to person A will be at least all of the records related to person B, plus person B's record, plus any records for which person ID B < person ID < person ID A. The same reasoning works if person B's person ID is greater than person A's. The only case where they could end up with the same member number is if they have the same person ID, which can't happen(I think?) because the person ID is automatically entered.

                       How does filemaker sync? If two people create new records at the same time, how does it make sure that an auto-entered field doesn't enter the same value for both records?

                       Actually my method has a big flaw in that if a record is ever deleted, or someone changes their mind and stops being a member, that will change the member numbers of everyone after that.

                       What I'm concerned with in your method is that if someone accidentally runs the member-ID-number-creator script for a record that isn't actually a member, then it will use up a member number. Though a simple check if the record is a member will solve that problem, unless they set the person to a member by accident before they run the script by accident. That's unlikely, and fixable, and I don't know that there's any solution that won't have that problem.

                       Alright, you've convinced me. Your way is probably the best way to do it.

                  • 6. Re: Unique IDs for only certain records

                         But your count is not based on just the PersonID, this is a count of all previous members and if user 1 and user 2 create a record for New member A and New Member B respectively but at the same time, the count returned might be the same for both users.

                         The assignment of serial numbers is managed on the host application. This is a rock solid feature of FileMaker that has been used from day one to assure that each new record gets a unique value. It only processes a request for a new serial number one at a time from each client session so no matter how the timing works out on the client side, only one of these two imaginary new records at a time will get a value in the serial number field and this avoids the duplicate values issue.

                    • 7. Re: Unique IDs for only certain records

                           Ah, I understand you now. If two people are added at the same time, even though they have unique person ID's, the system won't realize that the first one is a member, so the second user will get the same member number until the database syncs and the field is recalculated. But by then that user might have printed off something to give to the member or something else that involved the erroneous member number.

                           Thanks for clarifying.