1. Slate Knowledge Base
  2. Advancement
  3. Donor Management

Calculating the Head of Household

Updated
Expand-All  Collapse-All

Leveraging the Rules Editor, institutions can automatically identify and standardize records that should be marked as the head of a household. This designation can inform other business operations and aspects with a school's database, such as salutations, tracking interactions, and prioritizing communication preferences. 

Like other rules, an override can be created to handle exceptional cases where the standard identification of a head of household record falls outside of conventional handling. The calculation of the head of household involves five primary elements:

  • Creation of a Head of Household Field - A custom field will need to be created to store whether or not an individual record is the head of household.
  • Creation of an Override Field - This custom field can be set manually, which will exclude a record from being included in the head of household calculation.
  • 'Do Nothing' Override Rule - This first rule within the head of household exclusivity group evaluates each record for the override field. If that field is set to 'Yes,' the record will not be included in the head of household calculation.
  • Head of Household Calculation - A rule will be created that evaluates every individual record against its relationships to determine who should be identified as the head of household. This rule sets the custom field value to 'Yes.'
  • Not Head of Household Calculation - As a later part of an exclusivity group, this second rule sets the custom field value to 'No' for all other records who are not the head of household. 

  Best Practice

Before getting started with calculating the head of household, ensure all relationship_type prompts have an appropriate Category. This category will be referenced in determining which linked relationship records are evaluated alongside each record in the head of household calculation.

Creation of a Head of Household Field

The first step in creating a head of household calculation is the creation of a field to store the outcome of the calculation -- a head of household field. Because the outcome of the calculation is either a 'Yes' or a 'No,' a field can be created that stores this bit value. To create the field:

  1. Click Database in the top navigation bar and select Fields.
  2. Click Insert.
  3. Enter the following configurations in the popup window:
    • Scope - Person
    • ID - Provide a computer friendly and intuitive name for this field, such as 'head_of_household'
    • Name - Provide a human friendly and intuitive name for this field, such as 'Head of Household
    • Prompt - Select 'bit' from the list
    • Value - Select 'Store Value'
  4. Click Save.

Click the Slate Scholar Lightbulb    in the top left corner for a complete listing of all setting descriptions.

Creation of an Override Field

There may be records that do not fall in line with the standard convention and require manual designation. To exclude these records from the calculation, an override field should be created and subsequently set on each record that will be an exception to the calculation. To create this custom field:

  1. Click Database in the top navigation bar and select Fields.
  2. Click Insert.
  3. Enter the following configurations in the popup window:
    • Scope - Person
    • ID - Provide a computer friendly and intuitive name for this field, such as 'hoh_override'
    • Name - Provide a human friendly and intuitive name for this field, such as 'Head of Household Override'
    • Prompt - Select 'bit' from the list
    • Value - Select 'Store Value'
  4. Click Save.

Click the Slate Scholar Lightbulb    in the top left corner for a complete listing of all setting descriptions.

'Do Nothing' Override Rule

The head of household calculation will consist of three rules, all within an exclusivity group. The order of rules within the exclusivity group is important -- as it determines which of the three rules will take affect on a record.

The first rule in the exclusivity group is a 'Do Nothing' rule. The purpose of this rule is to find all of the records where the 'Head of Household Override' field has been manually set - and exclude them from the subsequent rules. To create this rule:

  1. Click Database in the top navigation bar and select Rules Editor.
  2. Click New Rule.
  3. Enter the following configurations in the popup window:
    • Name - Provide an easy to understand name for the rule, such as 'Head of Household - Override'
    • Base - Select 'Configurable Joins Library (Preview) - Person'
    • Type - Select 'Field'
    • Trigger - Select 'Upon Update (Deferred)'
    • Folder - Create or place the rule in a folder, such as a 'Head of Household' folder
    • Exclusivity Group - Create an exclusivity group for this first rule in the group of three rules. Provide a name such as 'Head of Household'
    • Non-deterministic - Select 'Rule is deterministic and has an exclusive priority'
    • Priority - Set the priority to '1'
    • Status - Set the status to 'Preview'
  4. Click Save.

    Head of Household Override Rule Settings

  Tip

Create rules in the Preview status. After testing select records and being satisfied with the results, modify the status to Active.

With the rule initially created, the next step is to filter to find records that have the Head of Household Override field value set to 'Yes.' 

With these records identified, this rule can be configured with the selected action of No Action Rule. By selecting this action, Slate will skip the processing of other lower-priority rules within the same exclusivity group when the filter criteria has been met.

Override_Rule.PNG

Head of Household Calculation

The next rule in the exclusivity group is designed to set the custom head of household field to 'Yes' for only those records that should be marked as the head of household. Within this rule, each individual record within the database will be evaluated against their linked relationships - with only one person being selected as the head of household. 

To start the rule:

  1. Click Database in the top navigation bar and select Rules Editor.
  2. Click New Rule.
  3. Enter the following configurations in the popup window:
    • Name - Provide an easy to understand name for the rule, such as 'Head of Household - Yes'
    • Base - Select 'Configurable Joins Library (Preview) - Person'
    • Type - Select 'Field'
    • Trigger - Select 'Upon Update (Deferred)'
    • Folder - Create or place the rule in a folder, such as a 'Head of Household' folder
    • Exclusivity Group - Place the rule in the same 'Head of Household' exclusivity group that was created for the Do Nothing rule.
    • Non-deterministic - Select 'Rule is deterministic and has an exclusive priority'
    • Priority - Set the priority to '5'
    • Status - Set the status to 'Preview'
  4. Click Save.


Within the Action section of the rule, configure the following settings:

  • Field - Select the custom 'Head of Household' field
  • Action - Select 'Replace Values'
  • Prompt - Select 'Yes'

The next step is the adding of a subquery filter to select the appropriate record as the head of household. To get started:

  1. Select the subquery filter icon and enter the following configurations in the popup window:
    • Name - Provide a descriptive name (e.g.,'Person GUID = Head of Household GUID")
    • Type - Dependent subquery
    • Aggregate - Comparison
    • Operator - '='

      What's going on with the comparison?

    Slate is evaluating each record and its relationships. If the ID is the same as the person being evaluated, then that person is the head of household.

  2. Add two exports for comparison: the person's GUID and the GUID of the person who will be selected as the head of household. The Person GUID can be added directly and selected as the Field 1 option for comparison. 

    Initial Comparison

  3. The second comparison export will need to reference not only the person being evaluated, but also their linked relationships. To accomplish this, add a subquery export and configure the following settings:
    • Name - Provide an easy to understand name, such as 'Person and Relations GUID'
    • Type - Dependent subquery
    • Output - Rank

      Why Rank?

    This export will be looking at each person and all of their relationships. By selecting the Rank output, Slate will only choose 1 record out of all the potential records that exist (each person + their relationships). By using a custom sort, that one record will be the head of household.

  4. Click Join to add a new join to the Relations Linked Persons and Self base. Select the category of relationships that are intended to be evaluated in the head of household calculation (such as Spouse and Child)
  5. Add an export for the Relations Linked Persons and Self GUID.
  6. Add any additional filters as necessary, such as 'Relations Linked Person and Self = Living.'

    Persons and Relations GUID


    The actual calculation for determining which record should be the head of household occurs in the custom sort order within the Persons and Relations GUID subquery. The first items in the sort are the highest priority considerations when making a determination as to which GUID will be selected as the export. 

    Asking questions, such as "Is the person a Board Member" or "Is the person an Alum", where there is a yes/no response allow institutions to leverage existence values (such as '1' or '0') within a subquery sort to make quick work of determining if a record meets certain criteria. For example, the sort in our example references the constituency type of persons and their linked relations and returns a '1' if they are a Board Member and a '0' if they are not:

    Board Member Existence Sort

    At the end of the list of sorts, a unique value should be added. For these scenarios, records and their relationships match on all of the characteristics of being the head of household. By adding a unique value, a consistent record will be selected every time the rules run on the records. 

    Sort Order

    Once the desired sort order has been created:

  7. Click Save.
  8. Select Person and Relations GUID as the Field 2 option within the comparison.
  9. Click Save on the overall rule.

  Tip

Start outside of Slate. Decide what institutional criteria should be evaluated when making the determination of who should be listed as the head of household. Use post-it notes or a white board to build the hierarchy of who should be chosen as the head of household over another person.

Remember: the override rule can handle any one-off exceptions. Design the rule for the majority of records. 

Not Head of Household Calculation

The final rule in the series is designed to set the value of the custom head of household field to 'No' for all records who do not meet the criteria in the previous two rules. 

To create the rule:

  1. Click Database in the top navigation bar and select Rules Editor.
  2. Click New Rule.
  3. Enter the following configurations in the popup window:
    • Name - Provide an easy to understand name for the rule, such as 'Head of Household - No'
    • Base - Select 'Configurable Joins Library (Preview) - Person'
    • Type - Select 'Field'
    • Trigger - Select 'Upon Update (Deferred)'
    • Folder - Create or place the rule in a folder, such as a 'Head of Household' folder
    • Exclusivity Group - Place the rule in the same 'Head of Household' exclusivity group that was created for the previous two rules.
    • Non-deterministic - Select 'Rule is deterministic and has an exclusive priority'
    • Priority - Set the priority to '10'
    • Status - Set the status to 'Preview'
  4. Click Save.
  5. In the Action section, configure the following:
    • Field - Select the 'Head of Household' field
    • Action - Select 'Replace Values'
    • Prompt - Select 'No'
  6. Click Save.

Not Head of Household

  No Filters?

Because this rule is the last rule in the exclusivity group and all of the head of household records have already been established, all remaining records will be marked with 'No' as the value in the Head of Household field.

Was this article helpful?
1 out of 1 found this helpful