Go Back
Ellucian Ethos to Salesforce EDA ( Courses )
Publisher
: Lingk
Run In Lingk
Description
Load Courses from Ellucian Ethos to Salesforce EDA. 1. Pulls all courses from ethos. 2. Insert/Update courses info into SF matching with external guid. 3. Set "College / University" account AS hed__Account__c in course object.
Browse the knowledge base
Twitter
E-Mail
# _____ _ _____ __ # | __ \ (_) |_ _| / _| # | |__) |___ ___ _ _ __ ___ | | _ __ | |_ ___ # | _ // _ \/ __| | '_ \ / _ \ | | | '_ \| _/ _ \ # | | \ \ __/ (__| | |_) | __/ _| |_| | | | || (_) | # |_| \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ # | | # |_| # Project Name - ELLUCIAN ETHOS TO SALESFORCE EDA ( COURSES ) # Recipe URL - https://app.lingk.io/a/10932/tf/17445 # Description - Load Courses from Ellucian Ethos to Salesforce EDA. #1. Pulls all courses from ethos. #2. Insert/Update courses info into SF matching with external guid. #3. Set "College / University" account AS hed__Account__c in course object. # Industry - Higher Ed # Business Process - Graduate Reporting # Systems - Ethos, Salesforce # Connectors - Ethos, Salesforce # Data Flows - Single Direction # Connection Type - API to API # Data Models ## Ethos Models Used: #1. Courses ## Salesforce Objects Used: #1. Account #2. Ethos__c #3. hed__Course__c # Add Recipe notes / Change log information here! # _____ _ # / ____| | | # | | ___ _ __ _ __ ___ ___| |_ ___ _ __ ___ # | | / _ \| '_ \| '_ \ / _ \/ __| __/ _ \| '__/ __| # | |___| (_) | | | | | | | __/ (__| || (_) | | \__ \ # \_____\___/|_| |_|_| |_|\___|\___|\__\___/|_| |___/ # # 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: ethosCoursesReader type: ethosReader # parameterizedBy: FilterdTermStartDateInfo delayedRead: true properties: path: /api/courses dataModelVersion: 16.0 schema: fields: - name: 'categories' type: 'array' items: name: 'arrayElement' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'id' type: 'string' - name: 'description' type: 'string' - name: 'hours' type: 'array' items: name: 'arrayElement' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'administrativeInstructionalMethod' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'id' type: 'string' - name: 'minimum' type: 'double' - name: 'id' type: 'string' - name: 'owningInstitutionUnits' type: 'array' items: name: 'arrayElement' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'institutionUnit' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'id' type: 'string' - name: 'ownershipPercentage' type: 'double' - name: 'schedulingEndOn' type: 'string' - name: 'schedulingStartOn' type: 'string' - name: 'status' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'id' type: 'string' - name: 'subject' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'id' type: 'string' - name: 'number' type: 'string' ###### End - Ethos Reader connectors ###### ###### Start - Salesforce Reader connectors ###### - name: sfUniversityInfo type: salesforceReader delayedRead: true properties: useBearerToken: true query: SELECT Id, Name FROM Account WHERE Name = '{{env.vars.universityname}}' LIMIT 1 schema: fields: - name: 'Id' type: 'string' - name: 'Name' type: 'string' - name: sfEthosReader type: salesforceReader delayedRead: true properties: useBearerToken: true query: SELECT ID__c, Name, Code__c FROM Ethos__c WHERE RecordType.Name IN ('Subjects') schema: fields: - name: 'ID__c' type: 'String' - name: 'Name' type: 'String' - name: 'Code__c' type: 'String' - name: sfhedCourseReader type: salesforceReader delayedRead: true properties: useBearerToken: true query: SELECT Id, hed__Course_ID__c, External_GUID__c FROM hed__Course__c WHERE External_GUID__c != NULL schema: fields: - name: 'Id' type: 'String' - name: 'hed__Course_ID__c' type: 'String' - name: 'External_GUID__c' type: 'String' ###### End - Salesforce Reader connectors ###### ###### Start - Salesforce Writer connectors ###### - name: sfhedCourseWriter type: salesforceBulkWriter properties: onError: continue sfObject: hed__Course__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 *********************************************************************************************** ## Get date before 3 months # - statement: (FilterdTermStartDateInfo) => SELECT date_format(add_months(current_date(), -3), "yyyy-MM-dd") AS date # - statement: print FilterdTermStartDateInfo - statement: REFRESH ethosCoursesReader - statement: (ethosCoursesReaderCount) => SELECT COUNT(*) FROM ethosCoursesReader #- statement: print ethosCoursesReaderCount - statement: REFRESH sfEthosReader - statement: (sfEthosReaderCount) => SELECT COUNT(*) FROM sfEthosReader #- statement: print sfEthosReaderCount # TRIM - Removes the leading and trailing space characters from str # Data in ethosCoursesReader contains different objects retrieved as an arrays, that's the reason on why some of the items selected in the query below specifies the element [0] - statement: | (coursesInformation) => SELECT c.id `External_GUID__c`, IF(TRIM(e.Code__c) != '', CONCAT(TRIM(e.Code__c), '-', TRIM(c.number)), '') `hed__Course_ID__c`, c.titles[0].value `Name`, c.description `hed__Extended_Description__c`, c.hours[0].minimum `hed__Credit_Hours__c` FROM ethosCoursesReader c LEFT JOIN sfEthosReader e ON c.subject.id = e.ID__c #- statement: print coursesInformation - statement: (coursesInformationCount) => SELECT COUNT(*) FROM coursesInformation #- statement: print coursesInformationCount - statement: REFRESH sfhedCourseReader - statement: (sfhedCourseReaderCount) => SELECT COUNT(*) FROM sfhedCourseReader #- statement: print sfhedCourseReaderCount #### Start - Update hed__Course__c #### - statement: | (courseInfoToUpdate) => SELECT sf.Id, course.hed__Course_ID__c, course.Name, course.hed__Extended_Description__c, course.hed__Credit_Hours__c FROM coursesInformation course INNER JOIN sfhedCourseReader sf ON course.External_GUID__c = sf.External_GUID__c - statement: (courseInfoToUpdateCount) => SELECT COUNT(*) FROM courseInfoToUpdate #- statement: print courseInfoToUpdateCount - statement: UPDATE courseInfoToUpdate INTO sfhedCourseWriter #### End - Update hed__Course__c #### - statement: REFRESH sfUniversityInfo #- statement: print sfUniversityInfo #### Start - Insert hed__Course__c #### - statement: | (courseInfoToInsert) => SELECT course.*, (SELECT Id FROM sfUniversityInfo) `hed__Account__c` FROM coursesInformation course LEFT ANTI JOIN sfhedCourseReader sf ON course.External_GUID__c = sf.External_GUID__c - statement: (courseInfoToInsertCount) => SELECT COUNT(*) FROM courseInfoToInsert #- statement: print courseInfoToInsertCount - statement: INSERT courseInfoToInsert INTO sfhedCourseWriter #### End - Insert hed__Course__c #### ## To check all subject info exist in salesforce or not - statement: | (blankCourseIdRecords) => SELECT External_GUID__c, Name FROM coursesInformation WHERE IFNULL(hed__Course_ID__c, '') = '' #- statement: print blankCourseIdRecords - statement: (blankCourseIdRecordCount) => SELECT COUNT(*) FROM blankCourseIdRecords #- stateemnt: print blankCourseIdRecordCount # Add more statements to convert, join, aggregrate, transform, and integrate your data
Ellucian Ethos to Salesforce EDA ( Academic Programs)
Ellucian Ethos to Salesforce EDA ( Instructional Events / Course Offerings)