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.
Primary key: MemberID_
Unique index: Domain_, UserNameLC_, List_
New index as of ListManager 9.1: IX_members_MemberType, index on (Lists_,MemberType_,DateJoined_)
|
Field Name |
Description |
Version Added |
|
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, "F" is default) |
|
|
CanAppPend_ |
As a list admin, can this member approve pending (moderated) messages? (bool, "F" is default) |
|
|
CleanAuto_ |
Do not mark as 'held' this member if they bounce too much email (ie: let them bounce all they want) (bool, "F" is default) |
|
|
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_ |
Set all to lowercase.(microsoft.com, for example). (varchar(250), NOT NULL) |
|
|
EnableWYSIWYG_ |
Whether this admin should have the HTML editor enabled by default. (bool, 'T' is default) |
Added in 8.8 |
|
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, "F" is default) |
|
|
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, (M)ultipart (the default), (T)ext, 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, "F" is default) |
|
|
NotifyErr_ |
For list admins: receive error mail? (bool, "F" is default) |
|
|
NotifySubm_ |
List admin: receive notification of pending moderated messages (bool, "F" is default) |
|
|
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. The password is encrypted. (varchar(50), NULL) |
|
|
PermissionGroupID_ |
Permission group for this administrator. (int, NULL). |
Added in 7.8. |
|
RcvAdmMail_ |
For list admins: receives email messages destined for list admins (bool, "F" is default) |
|
|
ReadsHtml_ |
Does the mail recipient read HTML mail? (bool, default is "F") |
|
|
ReceiveAck_ |
Receive an acknowledgement when contributing a posting? (bool, "F" is default") |
|
|
SubType_ |
Kind of subscription. Defaults to "mail". (mail, digest, index, nomail, mimedigest) (varchar(20), NOT NULL) |
|
|
SyncCanResubscribe_ |
Used by ODBC synchronization when a member is not found in the remote DB. (bool 'T' or 'F', default is 'F') |
Added in 8.8 |
|
SyncResubscribeType_ |
Used by ODBC synchronization when removing a member. Filled with member_.MemberType_. |
Added in 8.8 |
|
UnsubMessageID_ |
The outmail_.MailingID_ of the message this member requested to unsubscribe via %%email.unsub%% or %%url.unsub%% |
Added in 8.8 |
|
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) |
|