Salesforce, Python, SQL, & other ways to put your data where you need it

Need event music? 🎸

Live and recorded jazz, pop, and meditative music for your virtual conference / Zoom wedding / yoga class / private party with quality sound and a smooth technical experience

Transforming CommonApp into Salesforce EASY question responses

11 Aug 2022 🔖 salesforce integration databases
💬 EN

Table of Contents

I just posted how to use Oracle’s UNPIVOT to turn a “wide” table into a “skinny” one with just “field name” & “field value” columns … but why? Let’s take a look at an example from higher education with the Common App and Salesforce EASY / EDA.

I was helping someone in Higher Ed build a daily data-load between Common App CSV files as received by SFTP and their Salesforce-based admissions application database, which leverages the Education Data Architecture (“EDA” / “HEDA”) and the open-source package Enterprise Application Solution for Yield (“EASY”).

Basline transformation

Common App input data

The Common App’s dataset looks like my input from the UNPIVOT tutorial:

record_id country gender name_lf other_junk
123 Azerbaijan F Amjit, Anush 123abc
456 Zimbabwe N Borges, Benita def456

Each Common App source record represents 1 admissions application to the school.

Each source record has dozens of columns/fields describing things like home-country, gender, and more.

Salesforce EASY output requirements

For any given Common App record, each of these dozens of field values needs to be inserted as a standalone record into a Salesforce object called Question_Response__c that comes with “EASY” (as a child-object of an Application__c object).

  • The actual answer (field_value, e.g. “Azerbaijan“) goes into a field such as ValueText__c.
  • The original common app ID (record_id, e.g. “123“) helps pick an appropriate parent Application__c lookup record ID.
  • The column name (field_key, e.g. “COUNTRY“) helps pick an appropriate parent Question__c lookup record ID.
  • The column name (field_key, e.g. “COUNTRY“) also helps create/pick an appropriate parent Requirement_Response__c lookup record ID.
  • Other data (e.g. name_lf) might not directly get inserted into any part of a Question_Response__c record, but some of it might be helpful for humans if they need to troubleshoot problems with the data-load.

Transform with Oracle UNPIVOT

This school often facilitates transforming CSV files by dumping them into Oracle tables of the same structure – hence being interested in UNPIVOT:

  • The source data originally contained one row per application.
  • The data-load-ready UNPIVOT results now contain one row per detail noted on the application.
record_id field_key field_value name_lf
123 COUNTRY Azerbaijan Amjit, Anush
123 GENDER F Amjit, Anush
456 COUNTRY Zimbabwe Borges, Benita
456 GENDER N Borges, Benita

Lookup helpers

There’s still work to do. For example, helpers still need to be built out that transform a record_id value like “123” into a Question_Response__c.Application__c suggestion, and that transform a field_key like “country” into a specific Question_Response__c.Question__c suggestion. But at least now the data is in the correct “shape” for tacking such details onto.

Build a helper Oracle table

For example, the school might decide to build out an Oracle table called common_app_to_easy_questions that looks like this, which could be joined into the query’s field_key column on common_app_column_name:

common_app_column_name question_name question_requirement_item_name
COUNTRY Home Country Contact Info
GENDER Stated Gender Demographics
CREATE TABLE common_app_to_easy_questions (
    common_app_column_name VARCHAR2(60) PRIMARY KEY NOT NULL -- No idea if there's a limit on the CSV file column name
    , question_name VARCHAR2(80) -- Salesforce built-in "Name" field is, as of 8/11/22, max 80.
    , question_requirement_item_name VARCHAR2(80) -- Salesforce built-in "Name" field is, as of 8/11/22, max 80.
);

INSERT ALL
    INTO common_app_to_easy_questions (common_app_column_name, question_name, question_requirement_item_name)
        VALUES ('COUNTRY', 'Home Country', 'Contact Info')
    INTO common_app_to_easy_questions (common_app_column_name, question_name, question_requirement_item_name)
        VALUES ('GENDER', 'Stated Gender', 'Demographics')
SELECT * FROM dual;
COMMIT;

Join the data

select
    record_id
    , common_app_to_easy_questions.question_name
    , common_app_to_easy_questions.question_requirement_item_name
    , qUnpivot.field_value
    , name_lf
from
    source_table
unpivot (
    the_value
    for the_key
    in (
        citz_status
        , race_ethnicity
    )
) qUnpivot
inner join common_app_to_easy_questions
    on qUnpivot.the_key = common_app_to_easy_questions.common_app_column_name
;

Once you did the join, your data for loading into Question Response could look more like this:

record_id question_name question_requirement_item_name field_value name_lf
123 Home Country Contact Info Azerbaijan Amjit, Anush
123 Stated Gender Demographics F Amjit, Anush
456 Home Country Contact Info Zimbabwe Borges, Benita
456 Stated Gender Demographics N Borges, Benita

Insert with an enterprise ETL tool

As long as there exists, for example, some Salesforce record for which this SOQL query would return data…

select id
from Question__c
where Name = "Home Country"
and Requirement_Item__r.Name = "Contact Info"

…then a decent “Extract, Transform, Load” (ETL) tool ought to offer a mechanism by which you can tell it that you’d like it to look up what value to put into Salesforce’s Question_Response__c.Question__c field when inserting a new Salesforce Question_Response__c record, based on the data you have in your Oracle SQL query’s question_name and question_requirement_item_name columns.

--- ---