Working with Federal Poverty Guidelines in Salesforce (part 2 of 2)

In Part 1, we focused on providing an overview of the Federal Poverty Guidelines package for Salesforce, and how to install and use it.  In this part, we’re going much more technical, including instructions on how to “DIY” a solution if you’re not using the NPSP3 with Households.

This section is much more of a video tutorial, and below the videos are all of the components you’ll need to get started. Don’t worry – we’ll explain it all to you and we’ve saved you the trouble of typing it out.

A technical overview of all of the pieces in the Federal Poverty Guideline package

  • Length: 0:11:48
  • Audience: The curious admin or beginning admin who wants to know more about what they’ve just installed from Part 1, or what they might encounter if they venture into Setting Up Your Own (video below).

How to set up your own from scratch

  • Length: 0:53:47 (just under one hour)
  • Audience: Anyone interested in learning how to set up custom settings, how custom settings can be used in formula fields, or how to approach a large, complex formula field; anyone who can add new fields in Salesforce who needs to create a Federal Poverty Guideline system in Salesforce, but not using the Nonprofit Starter Pack 3.

What You’ll Need:

  1. The package that contains the custom setting and its fields only.  This is appropriate for any Nonprofit Salesforce installation, no matter what version of the NPSP you’re running (or if you’re not running the NPSP at all).
    1. PRODUCTION Salesforce instance. As usual we recommend installing into a sandbox first.
    2. SANDBOX Salesforce instance.
  2. The spreadsheet that contains the data for upload.  This part is not different from Part 1, and while you’re welcome to DIY we figure why not save some typing.
  3. The formula for percentage above/below the poverty line:
    • Household_Income__c/Federal_Bracket__c
  4. The monster formula for federal bracket:

IF(npsp__Number_of_Household_Members__c > 0 && npsp__Number_of_Household_Members__c < 9 && BillingState != ‘AK’ && BillingState != ‘HI’ && BillingState != ‘Alaska’ && BillingState != ‘Hawaii’,
CASE(npsp__Number_of_Household_Members__c,
1,$Setup.FederalPovertyGuidelines__c.X48_1__c,
2,$Setup.FederalPovertyGuidelines__c.X48_2__c,
3,$Setup.FederalPovertyGuidelines__c.X48_3__c,
4,$Setup.FederalPovertyGuidelines__c.X48_4__c,
5,$Setup.FederalPovertyGuidelines__c.X48_5__c,
6,$Setup.FederalPovertyGuidelines__c.X48_6__c,
7,$Setup.FederalPovertyGuidelines__c.X48_7__c,
8,$Setup.FederalPovertyGuidelines__c.X48_8__c,
0),
IF(npsp__Number_of_Household_Members__c > 0 && npsp__Number_of_Household_Members__c < 9 && (BillingState == ‘AK’ || BillingState == ‘Alaska’),
CASE(npsp__Number_of_Household_Members__c,
1,$Setup.FederalPovertyGuidelines__c.AK_1__c,
2,$Setup.FederalPovertyGuidelines__c.AK_2__c,
3,$Setup.FederalPovertyGuidelines__c.AK_3__c,
4,$Setup.FederalPovertyGuidelines__c.AK_4__c,
5,$Setup.FederalPovertyGuidelines__c.AK_5__c,
6,$Setup.FederalPovertyGuidelines__c.AK_6__c,
7,$Setup.FederalPovertyGuidelines__c.AK_7__c,
8,$Setup.FederalPovertyGuidelines__c.AK_8__c,
0),
IF(npsp__Number_of_Household_Members__c > 0 && npsp__Number_of_Household_Members__c < 9 && (BillingState == ‘HI’ || BillingState == ‘Hawaii’),
CASE(npsp__Number_of_Household_Members__c,
1,$Setup.FederalPovertyGuidelines__c.HI_1__c,
2,$Setup.FederalPovertyGuidelines__c.HI_2__c,
3,$Setup.FederalPovertyGuidelines__c.HI_3__c,
4,$Setup.FederalPovertyGuidelines__c.HI_4__c,
5,$Setup.FederalPovertyGuidelines__c.HI_5__c,
6,$Setup.FederalPovertyGuidelines__c.HI_6__c,
7,$Setup.FederalPovertyGuidelines__c.HI_7__c,
8,$Setup.FederalPovertyGuidelines__c.HI_8__c,
0),
IF(npsp__Number_of_Household_Members__c > 8 && BillingState != ‘AK’ && BillingState != ‘HI’ && BillingState != ‘Alaska’ && BillingState != ‘Hawaii’,
$Setup.FederalPovertyGuidelines__c.X48_8__c+($Setup.FederalPovertyGuidelines__c.X48_Over8__c*        (npsp__Number_of_Household_Members__c-8)),
IF(npsp__Number_of_Household_Members__c > 8 && (BillingState == ‘AK’ || BillingState == ‘Alaska’),
$Setup.FederalPovertyGuidelines__c.AK_8__c+($Setup.FederalPovertyGuidelines__c.AK_Over8__c*(npsp__Number_of_Household_Members__c-8)),
IF(npsp__Number_of_Household_Members__c > 8 && (BillingState == ‘HI’ || BillingState == ‘Hawaii’),
$Setup.FederalPovertyGuidelines__c.HI_8__c+($Setup.FederalPovertyGuidelines__c.HI_Over8__c*(npsp__Number_of_Household_Members__c-8)),
0
)
)
)
)
)
)

 

Allan Huntley

Author Allan Huntley

More posts by Allan Huntley