Go Back
Ellucian Ethos to Salesforce EDA ( Academic History)
Publisher
: Lingk
Run In Lingk
Description
This recipe retrieves Ethos Academic History and writes back to Salesforce custom object (hed__Education_History__c)
Browse the knowledge base
Twitter
E-Mail
# _____ _ _____ __ # | __ \ (_) |_ _| / _| # | |__) |___ ___ _ _ __ ___ | | _ __ | |_ ___ # | _ // _ \/ __| | '_ \ / _ \ | | | '_ \| _/ _ \ # | | \ \ __/ (__| | |_) | __/ _| |_| | | | || (_) | # |_| \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ # | | # |_| # Project Name - ELLUCIAN ETHOS TO SALESFORCE EDA ( ACADEMIC HISTORY) # Recipe URL - https://app.lingk.io/a/10932/tf/17443 # Description - This recipe retrieves Ethos Academic History and writes back to Salesforce custom object (hed__Education_History__c) # Industry - Higher Ed # Business Process - ACADEMIC HISTORY # Systems - Ethos, Salesforce # Connectors - Ethos, Salesforce # Data Flows - Single Direction # Connection Type - API to API # Data Models ## Ethos Models Used: #1. external-education ## Salesforce Objects Used: #1. Contact #2. Account #3. Ethos__c #4. hed__Account__c #5. hed__Education_history__c # Data Sets # Academic History & # Add Recipe notes / Change log information here! ## New development - (Sprint ) # updated with style guide model # updated with comment pattern # _____ _ # / ____| | | # | | ___ _ __ _ __ ___ ___| |_ ___ _ __ ___ # | | / _ \| '_ \| '_ \ / _ \/ __| __/ _ \| '__/ __| # | |___| (_) | | | | | | | __/ (__| || (_) | | \__ \ # \_____\___/|_| |_|_| |_|\___|\___|\__\___/|_| |___/ # # CONNECTORS specify what data will be pulled into the in-memory database during processing connectors: # Configure Ethos & Salesforce credentials in your Environment before running this recipe # Ethos Setup - https://help.lingk.io/en/articles/292-ellucian-ethos-connector-setup-guide # Salesforce Setup - https://help.lingk.io/en/articles/267-salesforce-connector-setup-guide ###### Start - Ethos Reader connectors ###### - name: ethosExternalEducationReader type: ethosReader delayedRead: true properties: path: /api/external-education dataModelVersion: 7.0 maxRecords: 10 schema: fields: - name: 'credential' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'id' type: 'string' - name: 'credentialsDate' type: 'string' - name: 'creditsEarned' type: 'string' - name: 'disciplines' type: 'array' items: name: 'arrayElement' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'id' type: 'string' - name: 'startOn' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'day' type: 'string' - name: 'month' type: 'string' - name: 'year' type: 'string' - name: 'endOn' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'day' type: 'string' - name: 'month' type: 'string' - name: 'year' type: 'string' - name: 'graduatedOn' type: 'string' - name: 'id' type: 'string' - name: 'institution' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'id' type: 'string' - name: 'performanceMeasure' type: 'string' - name: 'person' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'id' type: 'string' - name: 'transcriptReceivedOn' type: 'string' - name: 'classPercentile' type: 'string' - name: 'classRank' type: 'string' - name: 'classSize' type: 'string' ###### End - Ethos Reader connectors ###### ###### Start - Salesforce Reader connectors ###### - name: sfContactReader type: salesforceReader delayedRead: true properties: useBearerToken: true query: SELECT Id, External_GUID__c FROM Contact WHERE External_GUID__c != NULL schema: fields: - name: Id type: String - name: External_GUID__c type: String - name: sfAccountReader type: salesforceReader delayedRead: true properties: useBearerToken: true query: SELECT Id, External_GUID__c FROM Account WHERE RecordType.Name = 'Educational Institution' AND External_GUID__c != NULL schema: fields: - name: Id type: String - name: External_GUID__c type: String - name: sfEthosReader type: salesforceReader delayedRead: true properties: useBearerToken: true query: SELECT Id, ID__c, Name, Code__c FROM Ethos__c WHERE RecordType.Name IN ('Academic Credentials') schema: fields: - name: Id type: String - name: ID__c type: String - name: Name type: String - name: Code__c type: String - name: hedEducationHistoryReader type: salesforceReader delayedRead: true properties: useBearerToken: true query: SELECT Id, hed__Contact__c, hed__Account__c FROM hed__Education_History__c schema: fields: - name: Id type: String - name: hed__Contact__c type: String - name: hed__Account__c type: String ###### End - Salesforce Reader connectors ###### ###### Start - Salesforce Writer connectors ###### - name: hedEducationHistoryWriter type: salesforceBulkWriter properties: onError: continue sfObject: hed__Education_History__c batchSize: 200 useBearerToken: true ###### End - Salesforce Writer connectors ###### # _____ _ _ _ # / ____| | | | | | # | (___ | |_ __ _| |_ ___ _ __ ___ ___ _ __ | |_ ___ # \___ \| __/ _` | __/ _ \ '_ ` _ \ / _ \ '_ \| __/ __| # ____) | || (_| | || __/ | | | | | __/ | | | |_\__ \ # |_____/ \__\__,_|\__\___|_| |_| |_|\___|_| |_|\__|___/ # STATEMENTS specify how the data should be processed while in memory statements: #******************************************************************** D I S C L A I M E R *********************************************************************************************** # * # Note that in an effort to keep recipes optimized for DPH (Data Processing Hours), print statements should be commented out after development has concluded for a recipe. * # For more information on DPH optimization, please visit the following help article - https://help.lingk.io/en/articles/212-minimizing-data-processing-hours-on-the-lingk-platform * # * #******************************************************************** D I S C L A I M E R *********************************************************************************************** - statement: REFRESH ethosExternalEducationReader - statement: (externalEducationCount) => SELECT COUNT(*) FROM ethosExternalEducationReader #- statement: print externalEducationCount # CONCAT - Returns the concatenation of different values # date functions: # First - converts the person dates (graduatedOn) from String into a Date formate using the "to_date" function # Then - through "date_format" function it provides the format needed to the date retrieved - statement: | (externalEducationInfo) => SELECT id `ethos_id`, classPercentile `hed__Class_Percentile__c`, classRank `hed__Class_Rank__c`, classSize `hed__Class_Size__c`, CONCAT(startOn.year, '-', startOn.year, '-', startOn.year) `hed__Start_Date__c`, CONCAT(endOn.year, '-', endOn.year, '-', endOn.year) `hed__End_Date__c`, date_format(to_date(graduatedOn), 'yyyy-MM-dd') `hed__Graduation_Date__c`, institution.id `institution_id`, person.id `person_id`, credential.id `academic_cred_id` FROM ethosExternalEducationReader #- statement: print externalEducationInfo - statement: REFRESH sfContactReader - statement: (sfContactReaderCount) => SELECT COUNT(*) FROM sfContactReader #- statement: print sfContactReaderCount - statement: REFRESH sfAccountReader - statement: (sfAccountReaderCount) => SELECT COUNT(*) FROM sfAccountReader #- statement: print sfAccountReaderCount - statement: REFRESH sfEthosReader - statement: (sfEthosReaderCount) => SELECT COUNT(*) FROM sfEthosReader #- statement: print sfEthosReaderCount # date functions: # First - converts the person dates (graduatedOn) from String into a Date formate using the "to_date" function # Then - through "date_format" function it provides the format needed to the date retrieved - statement: | (finalExternalEducationInfo) => SELECT e.ethos_id, e.hed__Class_Percentile__c, e.hed__Class_Rank__c, e.hed__Class_Size__c, date_format(to_date(e.hed__Start_Date__c), 'yyyy-MM-dd') `hed__Start_Date__c`, date_format(to_date(e.hed__End_Date__c), 'yyyy-MM-dd') `hed__End_Date__c`, e.hed__Graduation_Date__c, sfCon.Id `hed__Contact__c`, sfAcc.Id `hed__Account__c`, ethos.Code__c `hed__Degree_Earned__c` FROM externalEducationInfo e INNER JOIN sfContactReader sfCon ON e.person_id = sfCon.External_GUID__c INNER JOIN sfAccountReader sfAcc ON e.institution_id = sfAcc.External_GUID__c LEFT JOIN sfEthosReader ethos ON e.academic_cred_id = ethos.ID__c #- statement: print finalExternalEducationInfo - statement: REFRESH hedEducationHistoryReader #- statement: print hedEducationHistoryReader #### Start - Update hed__Education_History__c #### - statement: | (educationHistoryInfoToUpdate) => SELECT edu.Id, f.hed__Degree_Earned__c, f.hed__Class_Percentile__c, f.hed__Class_Rank__c, f.hed__Class_Size__c, f.hed__Start_Date__c, f.hed__End_Date__c, f.hed__Graduation_Date__c FROM finalExternalEducationInfo f INNER JOIN hedEducationHistoryReader edu ON f.hed__Contact__c = edu.hed__Contact__c AND f.hed__Account__c = edu.hed__Account__c #- statement: print educationHistoryInfoToUpdate - statement: (educationHistoryInfoToUpdateCount) => SELECT COUNT(*) FROM educationHistoryInfoToUpdate #- statement: print educationHistoryInfoToUpdateCount # - statement: UPDATE educationHistoryInfoToUpdate INTO hedEducationHistoryWriter #### End - Update hed__Education_History__c #### #### Start - Insert hed__Education_History__c #### # The 'LEFT ANTI JOIN' function retrieves the rows from the first table (table after the FROM statement) that do not match with the rows on the second table (right table) - statement: | (educationHistoryInfoToInsert) => SELECT f.hed__Contact__c, f.hed__Account__c, f.hed__Degree_Earned__c, f.hed__Class_Percentile__c, f.hed__Class_Rank__c, f.hed__Class_Size__c, f.hed__Start_Date__c, f.hed__End_Date__c, f.hed__Graduation_Date__c FROM finalExternalEducationInfo f LEFT ANTI JOIN hedEducationHistoryReader edu ON f.hed__Contact__c = edu.hed__Contact__c AND f.hed__Account__c = edu.hed__Account__c #- statement: print educationHistoryInfoToInsert - statement: (educationHistoryInfoToInsertCount) => SELECT COUNT(*) FROM educationHistoryInfoToInsert #- statement: print educationHistoryInfoToInsertCount # - statement: INSERT educationHistoryInfoToInsert INTO hedEducationHistoryWriter #### End - Insert hed__Education_History__c #### # Add more statements to convert, join, aggregrate, transform, and integrate your data
Ellucian Ethos to Saleforce EDA ( Student Academic Programs )
Ellucian Ethos to Salesforce EDA ( Academic Programs)