Go Back
Ellucian Ethos to Salesforce EDA ( Instructional Events / Course Offerings)
Publisher
: Lingk
Run In Lingk
Description
This recipe retrieves Ellucian Ethos Instructional Events and writes back to Salesforce custom objects (hed__Course_Offering_Schedule__c & hed__Time_Block__c)
Browse the knowledge base
Twitter
E-Mail
# _____ _ _____ __ # | __ \ (_) |_ _| / _| # | |__) |___ ___ _ _ __ ___ | | _ __ | |_ ___ # | _ // _ \/ __| | '_ \ / _ \ | | | '_ \| _/ _ \ # | | \ \ __/ (__| | |_) | __/ _| |_| | | | || (_) | # |_| \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ # | | # |_| # Project Name - ELLUCIAN ETHOS TO SALESFORCE EDA ( INSTRUCTIONAL EVENTS / COURSE OFFERINGS) # Recipe URL - https://app.lingk.io/a/10932/tf/17447 # Description - This recipe retrieves Ellucian Ethos Instructional Events and writes back to Salesforce custom objects (hed__Course_Offering_Schedule__c & hed__Time_Block__c) ## RECIPE STEPS: #1. Pulls instructional events from ethos for existing sections in salesforce. #2. Insert/update time info in time block object in salesforce. #3. Transform instructional events data and insert/update in course offering schedule object matching with section(course offering). #4. Set "College / University" account AS hed__Educational_Institution__c in time block 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.instructional-events ## Salesforce Objects Used: #1. hed__Course_Offering__c #2. accounts #3. hed__Time_Block__c #4. hed__Course_Offering_Schedule__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: ethosInstructionalEventReader type: ethosReader parameterizedBy: sfhedCourseOfferingReader delayedRead: true properties: path: /api/instructional-events?criteria=%7B%22section%22:%7B%22id%22:%22{{var.External_GUID__c}}%22%7D%7D dataModelVersion: 11 schema: fields: - name: 'id' type: 'string' - name: 'recurrence' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'repeatRule' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'daysOfWeek' type: 'array' items: name: 'arrayElement' type: 'string' - name: 'ends' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'date' type: 'string' - name: 'interval' type: 'long' - name: 'type' type: 'string' - name: 'timePeriod' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'endOn' type: 'string' - name: 'startOn' type: 'string' - name: 'section' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'id' type: 'string' ###### End - Ethos Reader connectors ###### ###### Start - Salesforce Reader connectors ###### - name: sfhedCourseOfferingReader type: salesforceReader delayedRead: true properties: useBearerToken: true query: SELECT Id, External_GUID__c, hed__Facility__c FROM hed__Course_Offering__c WHERE External_GUID__c != NULL schema: fields: - name: 'Id' type: 'String' - name: 'External_GUID__c' type: 'String' - name: 'hed__Facility__c' type: 'String' - 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: sfhedTimeBlockReader type: salesforceReader delayedRead: true properties: useBearerToken: true query: SELECT Id, External_GUID__c FROM hed__Time_Block__c schema: fields: - name: 'Id' type: 'String' - name: 'External_GUID__c' type: 'String' - name: sfhedCourseOfferingScheduleReader type: salesforceReader delayedRead: true properties: useBearerToken: true query: SELECT Id, hed__Course_Offering__c FROM hed__Course_Offering_Schedule__c schema: fields: - name: 'Id' type: 'String' - name: 'hed__Course_Offering__c' type: 'String' ###### End - Salesforce Reader connectors ###### ###### Start - Salesforce Writer connectors ###### - name: sfTimeBlockWriter type: salesforceBulkWriter properties: onError: continue sfObject: hed__Time_Block__c batchSize: 200 useBearerToken: true - name: sfhedCourseOfferingScheduleWriter type: salesforceBulkWriter properties: onError: continue sfObject: hed__Course_Offering_Schedule__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 sfhedCourseOfferingReader - statement: (sfhedCourseOfferingReaderCount) => SELECT COUNT(*) FROM sfhedCourseOfferingReader #- statement: print sfhedCourseOfferingReaderCount - statement: REFRESH ethosInstructionalEventReader - statement: (ethosInstructionalReaderCount) => SELECT COUNT(*) FROM ethosInstructionalEventReader #- statement: print ethosInstructionalReaderCount # date functions: # First - Parses the timestamp expression with the fmt expression to a timestamp. Returns null with invalid input. By default, it follows casting rules to a timestamp if the fmt is omitted. # Through "date_format" function it provides the format needed to the date retrieved - statement: | (eventTimeInformation) => SELECT id `External_GUID__c`, date_format(to_timestamp(recurrence.timePeriod.startOn), 'HH:mm:ss') `hed__Start_Time__c`, date_format(to_timestamp(recurrence.timePeriod.endOn), 'HH:mm:ss') `hed__End_Time__c`, (SELECT Id FROM sfUniversityInfo) `hed__Educational_Institution__c` #- statement: print eventTimeInformation - statement: (eventTimeInformationCount) => SELECT COUNT(*) FROM eventTimeInformation #- statement: print eventTimeInformationCount # 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: | (timeBlockInfoToInsert) => SELECT t.External_GUID__c, t.hed__Start_Time__c, t.hed__End_Time__c, t.hed__Educational_Institution__c FROM eventTimeInformation t LEFT ANTI JOIN sfhedTimeBlockReader sf ON t.External_GUID__c = sf.External_GUID__c #- statement: print timeBlockInfoToInsert - statement: (timeBlockInfoToInsertCount) => SELECT COUNT(*) FROM timeBlockInfoToInsert #- statement: print timeBlockInfoToInsertCount - statement: INSERT timeBlockInfoToInsert INTO sfTimeBlockWriter # date functions: # First - Parses the timestamp expression with the fmt expression to a timestamp. Returns null with invalid input. By default, it follows casting rules to a timestamp if the fmt is omitted. # Through "date_format" function it provides the format needed to the date retrieved # CASE function works as a dictionary and retrieves a specific value depending on the type field value pulled from the table as long as it is not null. - statement: | (InstructionalEventInfo) => SELECT id `ethos_id`, section.id `section_id`, date_format(to_timestamp(recurrence.timePeriod.startOn), 'HH:mm:ss') `start_time`, date_format(to_timestamp(recurrence.timePeriod.endOn), 'HH:mm:ss') `end_time`, recurrence.repeatRule.type `repeat_rule_type`, CASE WHEN IFNULL(recurrence.repeatRule.type, '') = 'weekly' THEN recurrence.repeatRule.daysOfWeek ELSE array('') END `days_of_week_array` FROM ethosInstructionalEventReader #- statement: print InstructionalEventInfo # CASE function works as a dictionary and retrieves a specific value depending on the type field value pulled from the table as long as it is not null. - statement: | (courseOfferingScheduleInformation) => SELECT event.start_time `hed__Start_Time__c`, event.end_time `hed__End_Time__c`, CASE WHEN array_contains(event.days_of_week_array, 'monday') = "true" THEN "true" ELSE "false" END `hed__Monday__c`, CASE WHEN array_contains(event.days_of_week_array, 'tuesday') = "true" THEN "true" ELSE "false" END `hed__Tuesday__c`, CASE WHEN array_contains(event.days_of_week_array, 'wednesday') = "true" THEN "true" ELSE "false" END `hed__Wednesday__c`, CASE WHEN array_contains(event.days_of_week_array, 'thursday') = "true" THEN "true" ELSE "false" END `hed__Thursday__c`, CASE WHEN array_contains(event.days_of_week_array, 'friday') = "true" THEN "true" ELSE "false" END `hed__Friday__c`, CASE WHEN array_contains(event.days_of_week_array, 'saturday') = "true" THEN "true" ELSE "false" END `hed__Saturday__c`, CASE WHEN array_contains(event.days_of_week_array, 'sunday') = "true" THEN "true" ELSE "false" END `hed__Sunday__c`, sfOff.Id `hed__Course_Offering__c`, sfOff.hed__Facility__c `hed__Facility__c`, "" `hed__Time_Block__c` FROM InstructionalEventInfo event INNER JOIN sfhedCourseOfferingReader sfOff ON event.section_id = sfOff.External_GUID__c LEFT JOIN sfhedTimeBlockReader t ON event.ethos_id = t.External_GUID__c #- statement: print courseOfferingScheduleInformation - statement: (courseOfferingScheduleInformationCount) => SELECT COUNT(*) FROM courseOfferingScheduleInformation #- statement: print courseOfferingScheduleInformationCount - statement: REFRESH sfhedCourseOfferingScheduleReader - statement: (sfhedCourseOfferingScheduleReaderCount) => SELECT COUNT(*) FROM sfhedCourseOfferingScheduleReader #- statement: print sfhedCourseOfferingScheduleReaderCount #### Start - Update hed__Course_Offering_Schedule__c #### - statement: | (courseOfferingScheduleInfoToUpdate) => SELECT sf.Id, s.hed__Start_Time__c, s.hed__End_Time__c, s.hed__Monday__c, s.hed__Tuesday__c, s.hed__Wednesday__c, s.hed__Thursday__c, s.hed__Friday__c, s.hed__Saturday__c, s.hed__Sunday__c, s.hed__Facility__c, s.hed__Time_Block__c FROM courseOfferingScheduleInformation s INNER JOIN sfhedCourseOfferingScheduleReader sf ON s.hed__Course_Offering__c = sf.hed__Course_Offering__c #- statement: print courseOfferingScheduleInfoToUpdate - statement: (courseOfferingScheduleInfoToUpdateCount) => SELECT COUNT(*) FROM courseOfferingScheduleInfoToUpdate #- statement: print courseOfferingScheduleInfoToUpdateCount # - statement: UPDATE courseOfferingScheduleInfoToUpdate INTO sfhedCourseOfferingScheduleWriter #### End - Update hed__Course_Offering_Schedule__c #### #### Start - Insert hed__Course_Offering_Schedule__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: | (courseOfferingScheduleInfoToInsert) => SELECT s.* FROM courseOfferingScheduleInformation s LEFT ANTI JOIN sfhedCourseOfferingScheduleReader sf ON s.hed__Course_Offering__c = sf.hed__Course_Offering__c #- statement: print courseOfferingScheduleInfoToInsert - statement: (courseOfferingScheduleInfoToInsertCount) => SELECT COUNT(*) FROM courseOfferingScheduleInfoToInsert #- statement: print courseOfferingScheduleInfoToInsertCount - statement: INSERT courseOfferingScheduleInfoToInsert INTO sfhedCourseOfferingScheduleWriter #### End - Insert hed__Course_Offering_Schedule__c #### # Add more statements to convert, join, aggregrate, transform, and integrate your data
Ellucian Ethos to Salesforce EDA ( Courses )
Ellucian Ethos to Salesforce EDA ( Person / Contact )