Zip Code Role-Basing and the 'Contains' Operator

Contains operator

In both the Data Viewer tool and Creating Roles tool there exists a 'contains' operator. This operator is used to find matches to the provided substring in a field's data.

• The data in the field can be longer than the substring.
  • Example: the substring '234' would match to field values like '12345'.
• The wildcard underscore (_) can be used. This is useful when requiring values to be a certain length or skipping over variable characters.
  • Example 1: Finding constituent IDs that have leading zeroes and are (at least) ten digits long: 0_________ (that is a zero followed by nine underscores).
  • Example 2: Filtering 3 character activity codes that start with an 'A' and end with a 'G': A_G
• Filters for individual characters can be assigned with brackets '[]' including the "not equal to" caret character (^)
  • Example 1: Character that is not a number [^0-9] or that is not a letter in the alphabet [^a-z]. This can be used to check for characters/numbers in a field.
  • Example 2: 'Grand[mp]a' would match to both 'Grandma' and 'Grandpa' because both match the criteria of 'm' or 'p' for the substring.
  • Example 3: '[a-z]ary' would match to 'Gary', 'Cary', 'Mary', etc.
  • Example 4: '[^m]ary' would match to everything in the previous example, except 'Mary'.
  • Example 5: Matching constituent/activity codes that start with a specific letter like 'A', 'B', or 'G': [abg]__

Other operators

Other operators "Starts with" and "Ends with" are available in the Data Viewer and can use wildcards underscore (_) and brackets []. "Starts with" and "Ends with" is not available when role-basing on a field.

 

Strategy: Assigning Roles Based on Zip Code Prefixes

Using the contains operator, the following is a strategy to automatically assign a role to records based on the Zip Code field.

The goal for this strategy is to set up a role based on the first three digits of a Zip Code and automatically assign this role to records with 5 digit Zip Codes and ZIP+4 codes that match. For this example, Kansas City Zip Codes, identified from a list of ZIP code prefixes, were used: 640, 641, 649, 660, 661, and 662 (Overland Park).

The summary of this strategy:

• An inclusion role on Zip Code with the 'contains' operator of the first three digits followed by two wildcards characters that are numbers.
  • Example: Contains 660[0-9][0-9] ^{1 Why?🔽}
• An exclusion role on Zip Code with the 'contains' operator identical to the inclusion role but prepended with an underscore wildcard (_).
  • Example: Contains _660[0-9][0-9] ^{2 Why?🔽}
• An exclusion role on Country with the 'contains' operator that matches to any country other than 'United States' (example shown further down). ^{3 Why?🔽}

Inclusion and Exclusion Roles Assigned with Zip Code Field for Kansas City:

¹ Why is this format, e.g. 660[0-9][0-9], used?

• '660' is insufficient because it could match the substring anywhere in the Zip Code and not be the first three digits.
• '660__' is better but ends up matching to Zip +4 codes that do not start with '660' like '52660-1921'.
• '660[0-9][0-9]' is more robust as it requires the prefix followed by two numbers, which allows for matching both 5 digit and Zip +4 codes. '660[^-][^-]' could also work by blocking matches with hyphens in the substring sequence.

² Why is the exclusion role, e.g. _660[0-9][0-9], used?

• Role-basing does not have access to the "Starts with" operator so the goal is to identify and remove results that should not be included by the inclusion role.
• In the above example, the foreign country code of the Haigang District in China, 066000, would end up matching and should not be included.
• When building a query, exclusions overrule inclusions. Excluding matches to '_660[0-9][0-9]' will remove results like 066000.

Exclusion Role Assigned with Country Field (video):

³ Why is this used and why is it set up as an exclusion role?

To refine the strategy, it may be useful to add an additional/extra exclusion role based on Country not equaling, in this example, 'United States' to ensure foreign addresses do not get included.

An exclusion role is used instead of an inclusion role because the country field will often have a NULL (unassigned) value. An inclusion role for a dropdown field is not able to search for NULL values, so an exclusion role is built to detect if any country has been selected or assigned that is not, in this example, 'United States'. The value of NULL and 'United States' would not be picked up by this exclusion role.

Using these Roles in Data Viewer:

When using the roles to send emails, assign content blocks, or any other purpose, include the intended role and exclude the exclusion roles:

Roles for additional cities or areas can be created and assigned using this strategy as needed.