4 Replies Latest reply on Feb 11, 2015 10:00 AM by gdsmall

    New user create 2 table membership database



      New user create 2 table membership database


      I want to build a membership database with two tables. The "parent" table will be "Members" and contain contact fields. The "child" will be a "Dues" table for annual payments. I assume that this is a pretty common and basic relationship, but I'm not clear on the "Primary Key field" logic. If I use a numeric auto-create primary key on "Members", do I also create the same for "Dues". Are there any examples or tutorials covering this?

      I am reading the "Missing Manual", but the relationship structure is still confusing.


        • 1. Re: New user create 2 table membership database

          If Dues is a child to Members, you would see the Dues for each Member in a portal on the Member record.

          The Dues database would use the Member Key.   In the Dues table you might enter a record for dues for each member for the month.  For example, if you had 100 members there would be 100 Dues Records each month.  

          On the Member Record your portal could show the dues a Member has had charged to them.    I usually use the Date field to order the dues in the newest first order.

          Does this address your question?

          • 2. Re: New user create 2 table membership database

            Thank you. This is very helpful, but my main uncertainty centers on the "Member" key. My Members table has LastName and FirstName fields. As expected, there are instances of more than one member with the same LastName.

            I have not (yet) generated a numeric "Primary Key" as suggested in ""The Missing Manual", because I am not clear on how (for example) a "001" primary key associated with "Adams" would be used on the Dues table. If there were no duplicate Last Names, then it would make sense to use LastName as Primary Key, then a user entering dues would simply key in Last Name along with the other fields.

            I was thinking that I needed to create a MemberID with an alphanumeric Key "Adams01", Adams02" etc, but the "book" seems to imply that this can be avoided simply by auto-generating a numeric number for each Member record. To my inexperienced thinking, a user would have to know the "numeric primary key" to enter each dues payment. 

            I continue to look for an example or template to resolve this confusion, but I very much appreciate your and any other reader's assistance.


            • 3. Re: New user create 2 table membership database

              Make your member key a number field with an auto-entered serial number.

              Define a relationship between Member and Dues that links this key field to a simple number field in dues.

              To create a new record in dues and link it to a member, you enter that member's MemberKey value into this number field in Dues.

              WHen you use a portal to Dues on the member layout with "allow creation of records via this relationship" enabled for Dues in the relationship, this action takes place automatically when you record dues in the portal.

              There are also a number of other ways to get the correct value into the match field in dues--none of which require knowing the actual number assigned to a given member record.

              • 4. Re: New user create 2 table membership database

                Thank you. I am beginning to see a glimmer of light now. I was originally thinking that the dues entry function would somehow "happen" in the dues table, but using a portal (which I am studying now) FROM the Members info would post into the Dues table, which is what I want.

                I am taking the day today (retired) experimenting with some of this new information and may have a better understanding, which will hopefully enable me to post more concise and relevant additional questions.