Summary of the "members_" fields in Aurea List Manager
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 are multiple rows in the members_
table for a single email address, one for each list that the member has subscribed to.
As per Internet standards, 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:
- full case-sensitive form in the
EmailAddr_column - broken into case-insensitive
pieces into columns
UserNameLC_andDomain_.
Aurea List Manager automatically populates 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_ holds the lower-case version of
the portions of the email address, while the full case-sensitive version is stored in EmailAddr_.
UserNameLC_
holds the "user name" portion of the email address, i.e., everything to the left of the
@ sign. Everything to the right of the @ sign is 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 Aurea List Manager 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
The 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 Aurea List Manager, 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 is not recommended as it stops Aurea List Manager from being able to add new members. 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 becomes slower and slower to get information from it, which impacts 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) |