# _____ _ _____ __ # | __ \ (_) |_ _| / _| # | |__) |___ ___ _ _ __ ___ | | _ __ | |_ ___ # | _ // _ \/ __| | '_ \ / _ \ | | | '_ \| _/ _ \ # | | \ \ __/ (__| | |_) | __/ _| |_| | | | || (_) | # |_| \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ # | | # |_| # Project Name - ELLUCIAN ETHOS TO SALESFORCE EDA ( SECTIONS / COURSE OFFERINGS ) # Recipe URL - https://app.lingk.io/a/10932/tf/17454 # Description - This recipe retrieves Ellucian Ethos Courses and writes back to Salesforce custom object (hed__Course_Offering__c) #1. Select all terms from SF started in past 3 months. #2. Pulls sections from ethos filterd by term. #3. Get site id from sections and pulls sites from ethos. #4. Insert/Update site into SF facility object matching with external guid. #5. Insert/Update sections into SF course offering object matching with external guid. # 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. sections #2. sites ## Salesforce Objects Used: #1. hed__Term__c #2. hed__Course__c #3. hed__Facility__c #4. hed__Course_Offering__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: ethosSectionsReader type: ethosReader delayedRead: true parameterizedBy: sfhedTermReader properties: path: /api/sections?criteria=%7B%22academicPeriod%22%3A%7B%22id%22%3A%22{{var.External_GUID__c}}%22%7D%7D dataModelVersion: 16 maxRecords: 200 schema: fields: - name: 'academicPeriod' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'id' type: 'string' - name: 'code' type: 'string' - name: 'course' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'id' type: 'string' - name: 'site' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'id' type: 'string' - name: 'endOn' 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: 'maxEnrollment' type: 'long' - name: 'number' type: 'string' - name: 'startOn' type: 'string' - name: ethosSiteReader type: ethosReader parameterizedBy: ethosSiteIdList delayedRead: true properties: path: /api/sites/{{var.site_id}} dataModelVersion: 6 schema: fields: - name: 'id' type: 'string' - name: 'code' type: 'string' - name: 'title' type: 'string' - name: 'description' type: 'string' - name: 'institution' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'id' type: 'string' ###### End - Ethos Reader connectors ###### ###### Start - Salesforce Reader connectors ###### - name: sfhedTermReader type: salesforceReader parameterizedBy: FilterdTermStartDateInfo delayedRead: true properties: useBearerToken: true query: SELECT Id, External_GUID__c, hed__Start_Date__c, Name FROM hed__Term__c WHERE hed__Start_Date__c >= {{var.date}} schema: fields: - name: 'Id' type: 'String' - name: 'External_GUID__c' type: 'String' - name: 'hed__Start_Date__c' type: 'String' - name: 'Name' type: 'String' - name: sfhedCourseReader type: salesforceReader delayedRead: true properties: useBearerToken: true query: SELECT Id, External_GUID__c FROM hed__Course__c WHERE External_GUID__c != NULL schema: fields: - name: 'Id' type: 'String' - name: 'External_GUID__c' type: 'String' - name: sfhedFacilityReader type: salesforceReader delayedRead: true properties: useBearerToken: true query: SELECT Id, External_GUID__c FROM hed__Facility__c WHERE External_GUID__c != NULL schema: fields: - name: 'Id' type: 'String' - name: 'External_GUID__c' type: 'String' - name: sfhedCourseOfferingReader type: salesforceReader delayedRead: true properties: useBearerToken: true query: SELECT Id, hed__Section_ID__c, External_GUID__c FROM hed__Course_Offering__c WHERE External_GUID__c != NULL schema: fields: - name: 'Id' type: 'String' - name: 'hed__Section_ID__c' type: 'String' - name: 'External_GUID__c' type: 'String' ###### End - Salesforce Reader connectors ###### ###### Start - Salesforce Writer connectors ###### - name: sfhedFacilityWriter type: salesforceBulkWriter properties: onError: continue sfObject: hed__Facility__c batchSize: 200 useBearerToken: true - name: sfhedCourseOfferingWriter type: salesforceBulkWriter properties: onError: continue sfObject: hed__Course_Offering__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 from today date - statement: (FilterdTermStartDateInfo) => SELECT date_format(add_months(current_date(), -3), "yyyy-MM-dd") AS date #- statement: print FilterdTermStartDateInfo - statement: REFRESH sfhedTermReader - statement: (sfhedTermReaderCount) => SELECT COUNT(*) FROM sfhedTermReader #- statement: print sfhedTermReaderCount - statement: REFRESH ethosSectionsReader #- statement: print ethosSectionsReader - statement: (ethosSectionsReaderCount) => SELECT COUNT(*) FROM ethosSectionsReader #- statement: print ethosSectionsReaderCount ###### Start - Facility ###### - statement: (ethosSiteIdList) => SELECT DISTINCT site.id AS `site_id` FROM ethosSectionsReader - statement: (ethosSiteIdListCount) => SELECT COUNT(*) FROM ethosSiteIdList #- statement: print ethosSiteIdListCount - statement: REFRESH ethosSiteReader - statement: (ethosSiteReaderCount) => SELECT COUNT(*) FROM ethosSiteReader #- statement: print ethosSiteReaderCount - statement: | (siteInformation) => SELECT id `External_GUID__c`, code `Facility_Code__c`, title `Name`, description `hed__Description__c` FROM ethosSiteReader #- statement: print siteInformation - statement: (siteInformationCount) => SELECT COUNT(*) FROM siteInformation #- statement: print siteInformationCount - statement: REFRESH sfhedFacilityReader - statement: (sfhedFacilityReaderCount) => SELECT COUNT(*) FROM sfhedFacilityReader #- statement: print sfhedFacilityReaderCount # 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: | (siteInfoToInsert) => SELECT f.* FROM siteInformation f LEFT ANTI JOIN sfhedFacilityReader sf ON f.External_GUID__c = sf.`External_GUID__c` - statement: (siteInfoToInsertCount) => SELECT COUNT(*) FROM siteInfoToInsert #- statement: print siteInfoToInsertCount - statement: INSERT siteInfoToInsert INTO sfhedFacilityWriter ###### End - Facility ###### ###### Start - Section/Course Offerings ###### - statement: REFRESH sfhedCourseReader - statement: (sfhedCourseReaderCount) => SELECT COUNT(*) FROM sfhedCourseReader #- statement: print sfhedCourseReaderCount - statement: REFRESH sfhedFacilityReader - statement: (sfhedFacilityReaderCount) => SELECT COUNT(*) FROM sfhedFacilityReader #- statement: print sfhedFacilityReaderCount #TRIM - Removes the leading and trailing space characters from str. # date functions: # First - converts the person dates (startOn and endOn) 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: | (sectionInformation) => SELECT s.id `External_GUID__c`, IF(TRIM(t.Name) != '', CONCAT(TRIM(t.Name), '-', TRIM(s.code)), '') `hed__Section_ID__c`, date_format(to_date(s.startOn), 'yyyy-MM-dd') `hed__Start_Date__c`, date_format(to_date(s.endOn), 'yyyy-MM-dd') `hed__End_Date__c`, s.maxEnrollment `hed__Capacity__c`, IF(TRIM(t.Name) != '', CONCAT(TRIM(s.titles[0].value), '-', TRIM(t.Name)), '') `Name`, c.Id `hed__Course__c`, t.Id `hed__Term__c`, f.Id `hed__Facility__c` FROM ethosSectionsReader s INNER JOIN sfhedCourseReader c ON s.course.id = c.External_GUID__c INNER JOIN sfhedTermReader t ON s.academicPeriod.id = t.External_GUID__c LEFT JOIN sfhedFacilityReader f ON s.site.id = f.External_GUID__c #- statement: print sectionInformation - statement: (sectionInformationCount) => SELECT COUNT(*) FROM sectionInformation #- statement: print sectionInformationCount - statement: REFRESH sfhedCourseOfferingReader - statement: (sfhedCourseOfferingReaderCount) => SELECT COUNT(*) FROM sfhedCourseOfferingReader #- statement: print sfhedCourseOfferingReaderCount ###### Start - Update hed__Course_Offering__c ###### - statement: | (sectionInfoToUpdate) => SELECT sf.Id, s.hed__Section_ID__c, s.hed__Start_Date__c, s.hed__End_Date__c, s.hed__Capacity__c, s.Name, s.hed__Course__c, s.hed__Term__c, s.hed__Facility__c FROM sectionInformation s INNER JOIN sfhedCourseOfferingReader sf ON s.External_GUID__c = sf.External_GUID__c - statement: (sectionInfoToUpdateCount) => SELECT COUNT(*) FROM sectionInfoToUpdate #- statement: print sectionInfoToUpdateCount - statement: UPDATE sectionInfoToUpdate INTO sfhedCourseOfferingWriter ###### End - Update hed__Course_Offering__c ###### ###### Start - Insert hed__Course_Offering__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: | (sectionInfoToInsert) => SELECT s.* FROM sectionInformation s LEFT ANTI JOIN sfhedCourseOfferingReader sf ON s.External_GUID__c = sf.External_GUID__c - statement: (sectionInfoToInsertCount) => SELECT COUNT(*) FROM sectionInfoToInsert #- statement: print sectionInfoToInsertCount # - statement: INSERT sectionInfoToInsert INTO sfhedCourseOfferingWriter ###### End - Insert hed__Course_Offering__c ###### ###### End - Section/Course Offerings ###### ## Section's course is not found in SF # 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: | (noCourseFound) => SELECT e.id, e.titles[0].value FROM ethosSectionsReader e LEFT ANTI JOIN ON sectionInformation s ON e.id = s.External_GUID__c #- statement: print noCourseFound - statement: (noCourseFoundCount) => SELECT COUNT(*) FROM noCourseFound #- statement: print noCourseFoundCount # Add more statements to convert, join, aggregrate, transform, and integrate your data