Summary of the "members_" fields in ListManager
The members_ table stores information about every list member on the server. Members are organized by list, and the same member may be on multiple lists. In this case there will be multiple rows in the members_ table for a single email address, one for each list that the member has subscribed to.
Internet standards are that the email address should preserve the case of the name, but not make a distinction for comparisons. To support this, and to make searching for members fast, the email address is represented twice in the table: once in full case-sensitive form (the EmailAddr_ column), and broken into case-insensitive pieces into columns UserNameLC_ and Domain_. ListManager will automatically populate these column when a new member is added, but if you want to add members to this table directly using SQL you MUST populate all the fields with the correct values. UserNameLC_ and Domain_ should hold the lower-case version of the portions of the email address, while the full case-sensitive version is stored in EmailAddr_. UserNameLC_ should hold the "user name" portion of the email address, i.e., everything to the left of the @ sign. Everything to the right of the @ sign should be stored in the Domain_ column. Neither the UserNameLC_ or the Domain_ should contain an @.
Information about the state of the user (such as on-hold or unsubscribed) is stored in the members_ table. This information allows ListManager to automatically handle administrative tasks such as putting user on hold, taking them off hold, purging users, requiring approvals before joining, etc. Many of the exact options for these are set in the lists_ table.
Note that failed send attempts are recorded in the lyrCompletedRecips table: a total of the number of bounces for an address is no longer contained in the members_ table.
The members_ table has two fields that are not populated by ListManager, and are available for you to put any data desired. These are UserID_ for up to 20 characters of text, and Additional_ for unlimited text. Additional columns may be added to the table as long as the columns allow NULL or have default values. Adding columns that are NOT NULL will stop ListManager from being able to add new members, so is not recommended. Additional fields in the members table is an easy way to add information that is available for mail merging. However, it is also easy to create an SQL join to other tables, so that is generally a more flexible and efficient way to make your data available for mail merging. You should be careful about adding too many columns to the members table because as the table grows "wider" it will become slower and slower to get information from it, which will impact ListManager's ability to send mail quickly.
Note that due to the libraries ListManager access for MSSQL, data for varchar fields will be truncated at 255 characters. If you need access to data greater than 255 characters, use a text field.
Primary key: MemberID_
Unique index: Domain_, UserNameLC_, List_
|
Additional_ |
Place holder for any information you want to store associated with this member. (text, NULL) |
|
AppNeeded_ |
Can this person bypass approval to send messages? (bool) |
|
CanAppPend_ |
As a list admin, can this member approve pending (moderated) messages? (bool) |
|
CleanAuto_ |
Do not mark as 'held' this member if they bounce too much email (ie: let them bounce all they want) (bool) |
|
Comment_ |
Holds whatever comments the programmer wishes to put in. Useful as a user-defined 'additional info' field (Text, NULL |
|
ConfirmDat_ |
Date the user was last sent a 'confirm' message (smalldatetime, NULL) |
|
DateBounce_ |
The date of the most recent bounce (smalldatetime, NULL). |
|
DateHeld_ |
Date the user was held (smalldatetime, NULL) |
|
DateJoined_ |
Date when person became a member of this list (smalldatetime, NULL) |
|
DateUnsub_ |
Date when person unsubscribed from this list (smalldatetime, NULL) |
|
Domain_ |
Lower case version of the domain portion of email address (microsoft.com, for example). (varchar(250), NOT NULL) |
|
EmailAddr_ |
Full case-sensitive Internet email address (varchar(100), NOT NULL) |
|
ExpireDate_ |
Membership expires on this date (smalldatetime, NULL) |
|
FullName_ |
Full name of this person (varchar(100), NULL) |
|
IsListAdm_ |
Whether this person a list admin of this list (bool) |
|
List_ |
What list is this person a member of? (link to lists) (varchar(60), NOT NULL) |
|
MailFormat_ |
What format does the user want to receive mail, (T)ext, (M)ultipart, or (H)TML. (char(1) NOT NULL) |
|
MemberID_ |
Unique member ID (int, identity, NOT NULL) |
|
MemberType_ |
What kind of member is this? ('normal', 'confirm', 'private', 'expired', 'held', 'unsub', 'needs-goodbye', 'needs-hello', 'needs-confirm') (varchar(20), NOT NULL) |
|
NoRepro_ |
Member should not receive a copy of their own posting (bool) |
|
NotifyErr_ |
For list admins: receive error mail? (bool) |
|
NotifySubm_ |
List admin: receive notification of pending moderated messages (bool) |
|
NumAppNeed_ |
How many more approvals does this person need before they can send messages unapproved? (smallint, NOT NULL) |
|
NumBounces_ |
The number of recent bounces this person has produced ( smallint, NOT NULL). As of version 7.8, this column is no longer used. |
|
Password_ |
Password this person uses for restricted functions (varchar(50), NULL) |
|
RcvAdmMail_ |
For list admins: receives email messages destined for list admins (bool) |
|
ReadsHtml_ |
Does the mail recipient read HTML mail? (bool) |
|
ReceiveAck_ |
Receive an acknowledgement when contributing a posting? (bool) |
|
SubType_ |
Kind of subscription (digest, index, nomail, mail) (varchar(20), NOT NULL) |
|
UserID_ |
Holds the user-definable 'user id' information, such as a key back to another table. (varchar(20), NULL) |
|
UserNameLC_ |
The user name (portion before the @) from the EmailAddr_ column, in lower case. (varchar(100), NOT NULL) |
|
|