Auto-Assign Formula for Membership Level Management

If your Membership Levels are assigned based on specific profile data, such as number of employees, number of customers, annual revenue, or status, you can use a SQL formula to automatically assign levels to your members. The formula will tell the system in which level to place a profile based on that data - for example, all members with with less than 5 employees should be set as "Small Business Level" members. 

1. Go to Admin > Set Preferences > Membership Level Management. Your levels must all be created and configured to run Auto-Assign - click here for details on creating your Levels.
2. Under the list of Levels, check "Automatically assign members to level" to enable the formula field. Enter a SQL statement to tell the system which profile data to use to automatically place a member into the appropriate level.
   
   Click "Sample Formula" to see a SQL formula based on the Employees FT and Employees PT fields on a profile, or formulas specific for your association type. Note that the formula must use the Profile_View (or link to it), and each criteria must reference a Membership Level ID number (you can see these displayed in your list of levels at the top of the page).
3. Copy any of the sample formulas, or write your own, updating the Membership Level IDs to match your levels. Click Save Settings, which will check your formula syntax - if there is an error, it will be displayed in red text explaining the issue.
4. To view a preview of how your formula will apply to member profiles, click Assignment Preview above the formula field. Enter the Profile ID of any member to preview in which Membership Level the formula will place them. You can also type in a profile name and click the Search icon if you do not have the Profile ID handy.
   
   
   
5. Once your formula is complete and saved, you can run the formula against all existing member profiles. To begin this process, click Start Auto-Assign. You will be asked to enter one or more email addresses to notify you when the process is complete. If you have a high number of profiles that will be updated (several thousand or more), this may take a few hours. Click Start Auto-Assign to begin the process.
6. While the Auto-Assign is running, you will see the status and progress below the formula:
   
   

When a member is placed in a new level through the Auto-Assign process, all configured profile data will be updated for that member (Profile Status, Billings, Affiliations, and Listings). Click here for more details on how the update process works.

Updating Profile Data

If your formula refers to a specific data field on a profile, and you update that field, when you save the profile you will be prompted to ask if you want to change that profile's level. For example, if all members with 100 employees or fewer are "Basic Level" and all members with more than 100 employees are "Bronze Level" and you change a member's Employees FT field from 50 to 150, when you save that change the following prompt will appear:

If you want to update the level and all corresponding profile data for the member, click Yes and Continue. If you do NOT want the data change to trigger a change in the member's level, click "No, I will manually update all information for this profile" and Continue.