DISH: Many organizations use external forms to acquire new constituents, program prospects, donors and the like. NPSP uses affiliations to store contact to account relationships but auto-creating new accounts and affiliations could create more duplicate accounts than anyone is ready to manage.
WHY THIS MATTERS: Knowing external org affiliations is powerful and generally your users know which connections are important and which are not. A system that users cannot trust is not going to be well adopted.
SOLUTION: Store text-based account data coming from contact forms in a hidden field, then calculate Primary Affiliation via a formula field.
INGREDIENTS:
- Know exactly how you plan to use affiliations in your org
- Custom Hidden Text Org Affiliation field on the contact object
- Custom Primary Affiliation (Text) field on the contact object
TOOLS REQUIRED:
- FormAssembly, SurveyGizmo or other external form tool integrated with Salesforce
STEPS:
- Create Hidden Text Org Affiliation field
- Navigate to the contact setup field section (Setup|Customize|Contacts|Fields)
- Select New
- Select Text
- Select Next
- Enter “Hidden Text Org Affiliation” on Field Label
- Enter “Hide_OrgValue” in Field Name
- Enter 255 in Length
- Enter “Hidden field for all users to store text value of Org from external website etc, will be calculated to the PAff_txt__c field if the primary affiliation is null” in description
- Select Next
- Uncheck Visible for all user profiles
- Select Next
- Uncheck Add Field for all Contact Layouts
- Select Save & New
- Create Primary Affiliation (Text) field to calculate between Primary Affiliation, Hidden Text Org Affiliation or all blank
- Select Formula
-
- Select Next
- Enter “Primary Affiliation (Text)” on Field Label
- Enter “PAff_txt” on Field Name
- Enter “Calculated text value for primary affiliation. External integrations will update the PAff_txt field , when an actual primary affiliation is added it will replace the name in the calculation” on Description
- Enter “This is a text representation of either an external web form entered by the actual contact or the account name of the primary affiliation.” on Help Text
- Enter Text on Formula Return Type
- Enter ‘IF( IsBlank( npsp__Primary_Affiliation__c ) = False,npsp__Primary_Affiliation__r.Name , IF( IsBlank( Hide_OrgValue__c ) = True && IsBlank( npsp__Primary_Affiliation__c ) = True,”Unknown-Unverified”, Hide_OrgValue__c & ” -Unverified” ))’ As Formula
- Select “Treat blank fields as blanks”
- Select Next
- Make visible for all profiles
- Select Next
- Add field for all page layouts
- Select Save
- Select Page Layouts
-
- Move Primary Affiliation (Text) below Primary Affiliation on all contact layouts
- Select Save
- Move Primary Affiliation (Text) below Primary Affiliation on all contact layouts
- Now map your integrated form to the Hidden Text Org Affiliation field where you’re asking for an employer/university etc.
SERVE: Test your solution with your integrated form. You may also want to adjust the visibility on the Hidden Text Org Affiliation field so users can see it in reports to manage overall data quality.
Scenario 1: If the Hidden Text Org Affiliation field is filled out but no Primary Affiliation exists the value of Hidden Text Org Affiliation appears with “-Unverified” in the Primary Affiliation (Text) field.
Scenario 2: If the Hidden Text Org Affiliation field is blank and no Primary Affiliation exists the value of Hidden Text Org Affiliation appears as “Unknown-Unverified” in the Primary Affiliation (Text) field.
Scenario 3: If the Primary Affiliation exists Hidden Text Org Affiliation field is ignored and no the value of Primary Affiliation appears in the Primary Affiliation (Text) field.
RISK LEVEL: Medium, if form data is constantly conflicting with affiliation data you may need to take additional steps (Post Coming Soon) to determine which is more recent and more relevant. For example if a new form was filled out with a newer Hidden Text Org Affiliation value you may want to put things in place to prompt a user to view and update the affiliation history. This can be combined with general Affiliation Verification methods (Post Coming Soon)
TIME: 10 minutes (not including creating and integrating your external form)
RELATED POSTS:
Cookbook – Strengthen NPSP affiliations management
Part 1 Decide what affiliations are to be used for