# _____ _ _____ __ # | __ \ (_) |_ _| / _| # | |__) |___ ___ _ _ __ ___ | | _ __ | |_ ___ # | _ // _ \/ __| | '_ \ / _ \ | | | '_ \| _/ _ \ # | | \ \ __/ (__| | |_) | __/ _| |_| | | | || (_) | # |_| \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ # | | # |_| # Project Name - SALESFORCE TO ANTHOLOGY - STUDENT CREATION # Recipe URL - https://app.lingk.io/a/10932/tf/18163 # Description - # This recipe will select new students from salesforce and created them into Anthology. # Recipe Steps # Select records from salesforce WHERE Sync_Status_SIS__c = 'Ready For Export' . # Reject records whith Null or blank required fields. # Create json payload for Anthology SaveNew end point to create new student by HTTP writer. # Parse Anthology HTTP writer response. # Select Id and student number from response and update in respective record into Salesforce. # Log error records. # Industry - Higher Ed # Business Process - Graduate Reporting # Systems - Salesforce # Connectors - HTTP, Salesforce # Data Flows - Single Direction # Connection Type - API # Add Recipe notes / Change log information here! # _____ _ # / ____| | | # | | ___ _ __ _ __ ___ ___| |_ ___ _ __ ___ # | | / _ \| '_ \| '_ \ / _ \/ __| __/ _ \| '__/ __| # | |___| (_) | | | | | | | __/ (__| || (_) | | \__ \ # \_____\___/|_| |_|_| |_|\___|\___|\__\___/|_| |___/ # # CONNECTORS specify what data will be pulled into the in-memory database during processing connectors: # Configure Salesforce credentials in your Environment before running this recipe # Salesforce Setup - https://help.lingk.io/en/articles/267-salesforce-connector-setup-guide ###### Start: Salesforce Connectors ####### - name: salesforceContact type: salesforceReader delayedRead: true properties: useBearerToken: true query: SELECT Id, hed__Primary_Address_Type__c, Admissions_Representative__r.RepID__c, Admissions_Representative__c, Best_Time_Contact__c, Combined_Campus_Selection__c, hed__Citizenship_Status__c, hed__Current_Address__r.hed__MailingCity__c, hed__Current_Address__r.ANU_Country__c, hed__Current_Address__r.hed__MailingCounty__c, CreatedDate, hed__Disability_Status__c, Email, Employer__c, hed__Ethnicity__c, FirstName, Field_of_Interest__c, hed__Gender__c, SyStudentID__c, LastModifiedDate, LastModifiedById, LastName, LeadDate__c, LeadSource, hed__Former_Last_Name__c, Marital_Status__c, MobilePhone, hed__Country_of_Origin__c, Phone, SIS_School_Status__c, hed__Current_Address__r.hed__MailingPostalCode__c, hed__Social_Security_Number__c, hed__Current_Address__r.State__c, hed__Current_Address__r.hed__MailingStreet__c, StuNum__c, hed__SMS_Opt_Out__c, Title, Military_Veteran__c, hed__WorkPhone__c, Work_Phone_Extension__c, hed__FERPA__c, Drivers_License_Number__c, Drivers_License_State__c, hed__Race__c, LastModifiedBy.StaffID__c, Lead_Source__c, Lead_Type__c, MiddleName, Non_Immigrant_Student__c, hed__AlternateEmail__c, hed__PreferredPhone__c, Previous_Education__c, Combined_Program_Selection__c, Salutation, Birthdate, OtherPhone, Preferred_Name__c, Suffix__c FROM Contact WHERE Sync_Status_SIS__c = 'Ready for Export' AND SyStudentID__c = null AND StuNum__c = null LIMIT 1 schema: fields: - name: 'Id' type: 'string' - name: 'hed__Primary_Address_Type__c' type: 'string' - name: Admissions_Representative__r.RepID__c type: 'string' - name: Admissions_Representative__c type: 'string' - name: 'Best_Time_Contact__c' type: 'string' - name: 'Combined_Campus_Selection__c' type: 'string' - name: 'hed__Citizenship_Status__c' type: 'string' - name: hed__Current_Address__r.hed__MailingCity__c type: 'string' - name: hed__Current_Address__r.ANU_Country__c type: 'string' - name: hed__Current_Address__r.hed__MailingCounty__c type: 'string' - name: hed__Current_Address__r.State__c type: 'string' - name: hed__Current_Address__r.hed__MailingStreet__c type: 'string' - name: 'CreatedDate' type: 'string' - name: 'hed__Disability_Status__c' type: 'string' - name: 'Email' type: 'string' - name: 'Employer__c' type: 'string' - name: 'hed__Ethnicity__c' type: 'string' - name: 'FirstName' type: 'string' - name: 'Field_of_Interest__c' type: 'string' - name: 'hed__Gender__c' type: 'string' - name: 'SyStudentID__c' type: 'string' - name: 'LastModifiedDate' type: 'string' - name: 'LastModifiedById' type: 'string' - name: 'LastName' type: 'string' - name: 'LeadDate__c' type: 'string' - name: 'LeadSource' type: 'string' - name: 'hed__Former_Last_Name__c' type: 'string' - name: 'Marital_Status__c' type: 'string' - name: 'MobilePhone' type: 'string' - name: 'hed__Country_of_Origin__c' type: 'string' - name: 'Phone' type: 'string' - name: 'SIS_School_Status__c' type: 'string' - name: hed__Current_Address__r.hed__MailingPostalCode__c type: 'string' - name: 'hed__Social_Security_Number__c' type: 'string' - name: 'State__c' type: 'string' - name: 'hed__Formula_MailingStreetAddress__c' type: 'string' - name: 'StuNum__c' type: 'string' - name: 'hed__SMS_Opt_Out__c' type: 'string' - name: 'Title' type: 'string' - name: 'Military_Veteran__c' type: 'string' - name: 'hed__WorkPhone__c' type: 'string' - name: 'Work_Phone_Extension__c' type: 'string' - name: 'hed__FERPA__c' type: 'string' - name: 'Drivers_License_Number__c' type: 'string' - name: 'Drivers_License_State__c' type: 'string' - name: 'hed__Race__c' type: 'string' - name: 'LastModifiedBy.StaffID__c' type: 'string' - name: 'Lead_Source__c' type: 'string' - name: 'Lead_Type__c' type: 'string' - name: 'MiddleName' type: 'string' - name: 'Non_Immigrant_Student__c' type: 'string' - name: 'hed__AlternateEmail__c' type: 'string' - name: 'hed__PreferredPhone__c' type: 'string' - name: 'Previous_Education__c' type: 'string' - name: 'Interest_Group__c' type: 'string' - name: 'Combined_Program_Selection__c' type: 'string' - name: 'Salutation' type: 'string' - name: 'Birthdate' type: 'string' - name: 'OtherPhone' type: 'string' - name: 'Preferred_Name__c' type: 'string' - name: 'Suffix__c' type: 'string' ###### End: Salesforce Connectors ####### ###### Start: JSON Connectors ####### # Mapping for Race - name: mappingRace type: json properties: jsonObject: > [ { "mapData": [ { "key": "American Indian or Alaska Native", "value": 2 }, { "key": "Asian", "value": 3 }, { "key": "Black or African American", "value": 8 }, { "key": "Hispanic or Latino", "value": 5 }, { "key": "Native Hawaiian or Other Pacific Islander", "value": 11 }, { "key": "White", "value": 6 }, { "key": "NULL/ELSE", "value": 12 } ] } ] ###### End: JSON Connectors ####### ###### Start: HTTP Connectors ####### # Endpoint to check duplicate records - name: httpDuplicateCheckReader type: httpReader parameterizedBy: payloadForDuplicateCheck onError: continue delayed: true properties: url: "{{env.vars.AnthologyInstanceUrl}}/api/commands/Common/Student/checkDuplicateCampusStudent" headersJson: {"Accept":"application/json","Content-Type":"application/json","ApiKey":"{{env.vars.AnthologyApiKey}}"} httpMethod: POST body: | {{var|tojson}} schema: fields: - name: '__parameters' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'payload' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'eMail' type: 'string' - name: 'firstName' type: 'string' - name: 'lastName' type: 'string' - name: 'hasError' type: 'boolean' - name: 'hasFault' type: 'boolean' - name: 'hasSecurityError' type: 'boolean' - name: 'hasValidationError' type: 'boolean' - name: 'hasValidationInformation' type: 'boolean' - name: 'hasValidationWarning' type: 'boolean' - name: 'hasWarning' type: 'boolean' - name: 'notifications' type: 'array' items: name: 'arrayElement' type: 'string' - name: 'payload' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'data' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'itemsAffected' type: 'long' - name: 'students' type: 'array' items: name: 'arrayElement' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'addr1' type: 'string' - name: 'amLeadSrcId' type: 'long' - name: 'amLeadTypeId' type: 'long' - name: 'amRepId' type: 'long' - name: 'campusName' type: 'string' - name: 'city' type: 'string' - name: 'drivLic' type: 'string' - name: 'email' type: 'string' - name: 'firstName' type: 'string' - name: 'lastName' type: 'string' - name: 'leadDate' type: 'string' - name: 'mi' type: 'string' - name: 'phone' type: 'string' - name: 'ssn' type: 'string' - name: 'state' type: 'string' - name: 'syCampusId' type: 'long' - name: 'sySchoolStatusId' type: 'long' - name: 'syStudentId' type: 'long' - name: 'workPhone' type: 'string' - name: 'zip' type: 'string' - name: 'validationMessages' type: 'array' items: name: 'arrayElement' type: 'string' # Target endpoint: Anthology create student url - name: httpSaveNewStudentWriter type: httpReader parameterizedBy: anthologySaveNewPayload onError: continue delayed: true properties: url: "{{env.vars.AnthologyInstanceUrl}}/api/commands/Common/Student/saveNew" headersJson: {"Accept":"application/json","Content-Type":"application/json","ApiKey":"{{env.vars.AnthologyApiKey}}"} httpMethod: POST body: | {{var|tojson}} schema: fields: - name: '__parameters' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'payload' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'assignedAdmissionsRepId' type: 'long' - name: 'campusId' type: 'long' - name: 'createdDateTime' type: 'string' - name: 'emailAddress' type: 'string' - name: 'firstName' type: 'string' - name: 'id' type: 'long' - name: 'lastModifiedUserId' type: 'long' - name: 'lastName' type: 'string' - name: 'leadDate' type: 'string' - name: 'rowVersion' type: 'string' - name: 'salesforceId' type: 'string' - name: 'schoolStatusId' type: 'long' - name: 'studentAddressAssociation' type: 'long' - name: 'studentNumber' type: 'string' - name: 'hasError' type: 'boolean' - name: 'hasFault' type: 'boolean' - name: 'hasSecurityError' type: 'boolean' - name: 'hasValidationError' type: 'boolean' - name: 'hasValidationInformation' type: 'boolean' - name: 'hasValidationWarning' type: 'boolean' - name: 'hasWarning' type: 'boolean' - name: 'notifications' type: 'array' items: name: 'arrayElement' type: 'string' - name: 'payload' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'data' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'addressTypeId' type: 'long' - name: 'agencyId' type: 'long' - name: 'alienNumber' type: 'string' - name: 'arAccountStatus' type: 'string' - name: 'arBalance' type: 'double' - name: 'arNextTransactionNumber' type: 'long' - name: 'assignedAdmissionsRepId' type: 'long' - name: 'assignedAdmissionsRepReassignedDate' type: 'string' - name: 'bestTimeToContact' type: 'string' - name: 'campusId' type: 'long' - name: 'citizenId' type: 'long' - name: 'city' type: 'string' - name: 'countryId' type: 'long' - name: 'countyId' type: 'long' - name: 'createdDateTime' type: 'string' - name: 'dataBlockIndicator' type: 'boolean' - name: 'deleteVeteranDetails' type: 'boolean' - name: 'disabled' type: 'string' - name: 'driverLicenseNumber' type: 'string' - name: 'driverLicenseState' type: 'string' - name: 'emailAddress' type: 'string' - name: 'employerId' type: 'long' - name: 'employmentStatusId' type: 'long' - name: 'enrolledProgramIdList' type: 'array' items: name: 'arrayElement' type: 'string' - name: 'entityState' type: 'long' - name: 'ethnicitiesList' type: 'array' items: name: 'arrayElement' type: 'string' - name: 'extendedProperties' type: 'array' items: name: 'arrayElement' type: 'string' - name: 'extraCurricularActivityId' type: 'long' - name: 'extraCurricularsList' type: 'array' items: name: 'arrayElement' type: 'string' - name: 'firstName' type: 'string' - name: 'genderId' type: 'long' - name: 'genderPronounList' type: 'array' items: name: 'arrayElement' type: 'string' - name: 'id' type: 'long' - name: 'isActive' type: 'boolean' - name: 'isBadAddress' type: 'boolean' - name: 'isBadPhone' type: 'boolean' - name: 'isDdVeteran' type: 'boolean' - name: 'isEftDefaultForStipends' type: 'boolean' - name: 'isExcludedCrmIntegration' type: 'boolean' - name: 'isInDistrict' type: 'boolean' - name: 'isSscrError11Received' type: 'boolean' - name: 'lastModifiedDateTime' type: 'string' - name: 'lastModifiedUserId' type: 'long' - name: 'lastName' type: 'string' - name: 'leadDate' type: 'string' - name: 'leadSourceId' type: 'long' - name: 'leadSourcesList' type: 'array' items: name: 'arrayElement' type: 'long' - name: 'leadTypeId' type: 'long' - name: 'maidenName' type: 'string' - name: 'maritalStatusId' type: 'long' - name: 'mi' type: 'string' - name: 'middleName' type: 'string' - name: 'mobilePhoneNumber' type: 'string' - name: 'nationalityId' type: 'long' - name: 'newAssignedAdmissionsRepId' type: 'long' - name: 'niStudent' type: 'boolean' - name: 'nickName' type: 'string' - name: 'note' type: 'string' - name: 'originalState' type: 'string' - name: 'otherEmailAddress' type: 'string' - name: 'overridePrevEducAltPellValidation' type: 'boolean' - name: 'phoneNumber' type: 'string' - name: 'postalCode' type: 'string' - name: 'preferredContactType' type: 'string' - name: 'previousEducationId' type: 'long' - name: 'programGroupId' type: 'long' - name: 'programId' type: 'long' - name: 'programsList' type: 'array' items: name: 'arrayElement' type: 'string' - name: 'rowVersion' type: 'string' - name: 'schoolStatusId' type: 'long' - name: 'secureState' type: 'string' - name: 'shiftId' type: 'long' - name: 'sourceSystem' type: 'string' - name: 'ssn' type: 'string' - name: 'state' type: 'string' - name: 'statementComment' type: 'string' - name: 'streetAddress' type: 'string' - name: 'studentAddressAssociation' type: 'long' - name: 'studentInquiryRequired' type: 'boolean' - name: 'studentNumber' type: 'string' - name: 'subscribeToSms' type: 'boolean' - name: 'titleId' type: 'long' - name: 'vendors' type: 'array' items: name: 'arrayElement' type: 'string' - name: 'veteran' type: 'string' - name: 'workPhoneNumber' type: 'string' - name: 'workPhoneNumberExtension' type: 'string' ###### End: HTTP Connectors ####### ###### Start: Salesforce Bulk Writer Connectors ####### # Salesforce contact writer - name: salesforceContactWriter type: salesforceBulkWriter onError: continue isSerial: true properties: onError: continue useBearerToken: true sfObject: Contact batchSize: 100 ###### End: Salesforce Bulk Writer Connectors ####### # ______ _ # | ____| | | # | |__ ___ _ __ _ __ ___ __ _| |_ ___ # | __/ _ \| '__| '_ ` _ \ / _` | __/ __| # | | | (_) | | | | | | | | (_| | |_\__ \ # |_| \___/|_| |_| |_| |_|\__,_|\__|___/ # FORMATS specify how files should be processed as they are being read or written to readFormats: - name: json type: json # _____ _ _ _ # / ____| | | | | | # | (___ | |_ __ _| |_ ___ _ __ ___ ___ _ __ | |_ ___ # \___ \| __/ _` | __/ _ \ '_ ` _ \ / _ \ '_ \| __/ __| # ____) | || (_| | || __/ | | | | | __/ | | | |_\__ \ # |_____/ \__\__,_|\__\___|_| |_| |_|\___|_| |_|\__|___/ # 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 salesforceContact #- statement: print salesforceContact - statement: (salesforceContactCount) => SELECT COUNT(*) FROM salesforceContact #- statement: print salesforceContactCount ## Rejected records due to missing required fileds. # 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: | (rejectedDueToMissingFields) => SELECT Id, CASE WHEN IFNULL(FirstName, '') = '' THEN "Record rejected due to blank FirstName field." WHEN IFNULL(LastName, '') = '' THEN "Record rejected due to blank LastName field." WHEN IFNULL(Email, '') = '' THEN "Record rejected due to blank Email field." WHEN IFNULL(LeadDate__c, '') = '' THEN "Record rejected due to blank LeadDate__c field." WHEN IFNULL(Admissions_Representative__c, '') = '' THEN "Record rejected due to blank admission representative field." WHEN IFNULL(`Admissions_Representative__r.RepID__c`, '') = '' THEN "Record rejected due to blank representative's Rep Id field." WHEN IFNULL(Combined_Campus_Selection__c, '') = '' THEN "Record rejected due to blank Combined_Campus_Selection__c field." ELSE '' END `RejectedReason` FROM salesforceContact WHERE IFNULL(FirstName, '') = '' OR IFNULL(LastName, '') = '' OR IFNULL(Email, '') = '' OR IFNULL(LeadDate__c, '') = '' OR IFNULL(Admissions_Representative__c, '') = '' OR IFNULL(`Admissions_Representative__r.RepID__c`, '') = '' OR IFNULL(Combined_Campus_Selection__c, '') = '' #- statement: print rejectedDueToMissingFields - statement: (rejectedDueToMissingFieldsCount) => SELECT COUNT(*) FROM rejectedDueToMissingFields #- statement: print rejectedDueToMissingFieldsCount # The 'LEFT ANTI JOIN' function retrieves the rows from salesforceContact table that do not match with the rows on the rejectedDueToMissingFields table - statement: | (finalRecordsToCreate) => SELECT sf.*, (SELECT mapData FROM mappingRace LIMIT 1) `Map` FROM salesforceContact sf LEFT ANTI JOIN rejectedDueToMissingFields rej ON TRIM(sf.Id) = TRIM(rej.Id) #- statement: print finalRecordsToCreate - statement: (finalRecordsToCreateCount) => SELECT COUNT(*) FROM finalRecordsToCreate #- statement: print finalRecordsToCreateCount # Formation of Race array # The following is a query that retrieves the Id field and the race # The value is separated by (;) char and does not have any nulls or any repetead values ---> using 'array_join' and 'array_union' respectively # Each of the hed__Race__c values work as a key in the map genereated from the same query through the 'map_from_entries' function # Using an 'inner join' function, the values retrieved can be forced to match accordingly between Source and Target - statement: | (formationOfRace) => SELECT Id `Sf_Id`, transform( split(hed__Race__c, ';'), (x, i) -> element_at( map_from_entries( Map ), trim(BOTH FROM x) ) ) `Race__c` FROM finalRecordsToCreate WHERE IFNULL(hed__Race__c, '') != '' #- statement: print formationOfRace # Formation of program list array # The 'ifnull' function retrieves the value of the second parameter in case the value of the first one is NULL, otherwise it will retrieve the current value of it # Collects and returns a list of non-unique Combined_Program_Selection__c. - statement: (programListGroup) => SELECT IFNULL(TRIM(FirstName), '') `FirstName`, IFNULL(TRIM(LastName), '') `LastName`, IFNULL(TRIM(Email), '') `Email`, collect_list( CAST(Combined_Program_Selection__c AS INT) ) `program_list` FROM finalRecordsToCreate GROUP BY FirstName, LastName, Email #- statement: PRINT programListGroup ## Create payload to check duplicate records in anthology. # the named_struct function creates a struct with the given field names and values. - statement: (payloadForDuplicateCheck) => SELECT named_struct( "firstName", IFNULL(FirstName, ''), "lastName", IFNULL(LastName, ''), "eMail", IFNULL(Email, '') ) `payload` FROM finalRecordsToCreate #- statement: print payloadForDuplicateCheck - statement: (payloadForDuplicateCheckCount) => SELECT COUNT(*) FROM payloadForDuplicateCheck #- statement: print payloadForDuplicateCheckCount - statement: REFRESH httpDuplicateCheckReader #- statement: print httpDuplicateCheckReader #### Start - Update StatusForDuplicateRecords #### - statement: (updateStatusForDuplicateRecords) => SELECT f.Id, 'Export Error' `Sync_Status_SIS__c`, 'Potential Duplicate' `SIS_Sync_Error_Message__c` FROM finalRecordsToCreate f INNER JOIN httpDuplicateCheckReader d ON TRIM(UPPER(d.__parameters.payload.firstName)) = TRIM(UPPER(f.FirstName)) AND TRIM(UPPER(d.__parameters.payload.lastName)) = TRIM(UPPER(f.LastName)) AND TRIM(UPPER(d.__parameters.payload.eMail)) = TRIM(UPPER(f.Email)) WHERE IFNULL(d.payload.data.students[0].syStudentId, '') != '' - statement: (results, errors) => UPDATE updateStatusForDuplicateRecords INTO salesforceContactWriter #- statement: PRINT results #- statement: PRINT errors #### End - Update StatusForDuplicateRecords #### ############################################################################################################## ## Formatting request payload to create student in anthology # the named_struct function creates a struct with the given field names and values. # 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: | (anthologySaveNewPayload) => SELECT named_struct( "salesforceId", IFNULL(f.Id, ''), "id", -1, "assignedAdmissionsRepId", CASE WHEN IFNULL(f.`Admissions_Representative__r.RepID__c`, '') != '' THEN f.`Admissions_Representative__r.RepID__c` END, "campusId", CASE WHEN IFNULL(f.Combined_Campus_Selection__c, '') = 'Undecided' THEN 9 ELSE f.Combined_Campus_Selection__c END, "firstName", IFNULL(f.FirstName, ''), "lastName", IFNULL(f.LastName, ''), "schoolStatusId", 1, "emailAddress", IFNULL(f.Email, ''), "leadDate", IFNULL(f.LeadDate__c, ''), "bestTimeToContact", IFNULL(f.Best_Time_Contact__c, ''), "citizenId", CAST(f.hed__Citizenship_Status__c as INT), "city", IFNULL(f.`hed__Current_Address__r.hed__MailingCity__c`, ''), "countryId", IFNULL(f.`hed__Current_Address__r.ANU_Country__c`, ''), "dataBlockIndicator", f.hed__FERPA__c, "dateOfBirth", IFNULL(date_format(f.Birthdate, 'yyyy/MM/dd hh:mm:ss'), ''), "hispanicLatino", CASE WHEN f.hed__Ethnicity__c = 'Hispanic or Latino' THEN 'Y' WHEN f.hed__Ethnicity__c = 'Not Hispanic or Latino' THEN 'N' END, "disabled", CASE WHEN IFNULL(f.hed__Disability_Status__c, '') = 'true' THEN 'Y' WHEN IFNULL(f.hed__Disability_Status__c, '') = 'false' THEN 'X' ELSE 'X' END, "driverLicenseNumber", IFNULL(f.Drivers_License_Number__c, ''), "driverLicenseState", IFNULL(f.Drivers_License_State__c, ''), "genderId", CASE WHEN IFNULL(f.hed__Gender__c, '') = 'Female' THEN 5 WHEN IFNULL(f.hed__Gender__c, '') = 'Male' THEN 6 WHEN IFNULL(f.hed__Gender__c, '') = 'Decline to State' THEN 7 ELSE 0 END, "leadTypeId", CAST(f.Lead_Type__c as INT), "leadSourceId", IFNULL(f.Lead_Source__c, ''), "maidenName", IFNULL(f.hed__Former_Last_Name__c, ''), "middleName", IFNULL(f.MiddleName, ''), "mobilePhoneNumber", IFNULL(f.MobilePhone, ''), "maritalStatusId", IFNULL(f.Marital_Status__c, ''), "niStudent", f.Non_Immigrant_Student__c, "otherEmailAddress", IFNULL(f.hed__AlternateEmail__c, ''), "previousEducationId", f.Previous_Education__c, "programGroupId", f.Field_of_Interest__c, "phoneNumber", IFNULL(f.Phone, ''), "postalCode", IFNULL(f.`hed__Current_Address__r.hed__MailingPostalCode__c`, ''), "postalCodeFirstThree", IFNULL(LEFT(f.`hed__Current_Address__r.hed__MailingPostalCode__c`, 3), ''), "preferredContactType", IFNULL(f.hed__PreferredPhone__c, ''), "programId", IFNULL(f.Combined_Program_Selection__c, ''), "sourceSystem", 'S', "ssn", IFNULL(f.hed__Social_Security_Number__c, ''), "state", IFNULL(f.`hed__Current_Address__r.State__c`, ''), "streetAddress", IFNULL(f.`hed__Current_Address__r.hed__MailingStreet__c`, ''), "subscribeToSms", CASE WHEN IFNULL(f.hed__SMS_Opt_Out__c, '') = 'true' THEN false WHEN IFNULL(f.hed__SMS_Opt_Out__c, '') = 'false' THEN true ELSE false END, "smsServiceProviderId", CASE WHEN IFNULL(f.hed__SMS_Opt_Out__c, '') = 'false' THEN 64 END, "titleId", CASE WHEN IFNULL(f.Salutation, '') = 'Mr.' THEN 1 WHEN IFNULL(f.Salutation, '') = 'Mrs.' THEN 2 WHEN IFNULL(f.Salutation, '') = 'Ms.' THEN 4 ELSE 0 END, "veteran", CASE WHEN IFNULL(f.Military_Veteran__c, '') = 'Yes' THEN 'Y' WHEN IFNULL(f.Military_Veteran__c, '') = 'No' THEN 'N' WHEN IFNULL(f.Military_Veteran__c, '') = 'Unspecified' THEN 'X' ELSE '' END, "workPhoneNumber", IFNULL(f.hed__WorkPhone__c, ''), "workPhoneNumberExtension", IFNULL(f.Work_Phone_Extension__c, ''), "otherPhoneNumber", IFNULL(f.OtherPhone, ''), "preferredName", IFNULL(f.Preferred_Name__c, ''), "suffixId", CAST(f.Suffix__c AS INT), "ethnicitiesList", race.Race__c, "programsList", p.program_list ) `payload` FROM finalRecordsToCreate f LEFT JOIN formationOfRace race ON TRIM(f.Id) = TRIM(race.Sf_Id) LEFT JOIN programListGroup p ON TRIM(UPPER(f.FirstName)) = TRIM(UPPER(p.FirstName)) AND TRIM(UPPER(f.LastName)) = TRIM(UPPER(p.LastName)) AND TRIM(UPPER(f.Email)) = TRIM(UPPER(p.Email)) LEFT ANTI JOIN updateStatusForDuplicateRecords d ON TRIM(f.Id) = TRIM(d.Id) #- statement: print anthologySaveNewPayload - statement: (anthologySaveNewPayloadCount) => SELECT COUNT(*) FROM anthologySaveNewPayload #- statement: print anthologySaveNewPayloadCount - statement: REFRESH httpSaveNewStudentWriter #- statement: print httpSaveNewStudentWriter ####################################################################################### # Get Student Id and Student Number to update in SF for successful created records. - statement: | (AnthologyStudentInfo) => SELECT payload.data.id `AnthologyId`, payload.data.studentNumber `StudentNumber`, payload.data.firstName `FirstName`, payload.data.lastName `LastName`, payload.data.emailAddress `Email` FROM httpSaveNewStudentWriter WHERE IFNULL(payload.data.id, '') != '' AND IFNULL(payload.data.studentNumber, '') != '' #- statement: print AnthologyStudentInfo - statement: (AnthologyStudentInfoCount) => SELECT COUNT(*) FROM AnthologyStudentInfo #- statement: print AnthologyStudentInfoCount ## Final success entity to update response fields into salesforce - statement: | (AnthologyStudentInfoToUpdateIntoSF) => SELECT sf.Id, ant.AnthologyId `SyStudentID__c`, ant.StudentNumber `StuNum__c`, 'Export Complete' `Sync_Status_SIS__c` FROM AnthologyStudentInfo ant INNER JOIN salesforceContact sf ON IFNULL(TRIM(UPPER(ant.FirstName)), '') = IFNULL(TRIM(UPPER(sf.FirstName)), '') AND IFNULL(TRIM(UPPER(ant.LastName)), '') = IFNULL(TRIM(UPPER(sf.LastName)), '') AND IFNULL(TRIM(UPPER(ant.Email)), '') = IFNULL(TRIM(UPPER(sf.Email)), '') #- statement: print AnthologyStudentInfoToUpdateIntoSF - statement: (success, error) => UPDATE AnthologyStudentInfoToUpdateIntoSF INTO salesforceContactWriter #- statement: print success #- statement: print error # Get records which are not created in Anthology. # The 'LEFT ANTI JOIN' function retrieves the rows from the anthologySaveNewPayload table that do not match with the rows on the second AnthologyStudentInfo - statement: | (recordsNotCreatedInAnthology) => SELECT f.payload.salesforceId, 'Not created in Anthology' `RejectedReason` FROM anthologySaveNewPayload f LEFT ANTI JOIN AnthologyStudentInfo ant ON IFNULL(TRIM(UPPER(ant.FirstName)), '') = IFNULL(TRIM(UPPER(f.payload.firstName)), '') AND IFNULL(TRIM(UPPER(ant.LastName)), '') = IFNULL(TRIM(UPPER(f.payload.lastName)), '') AND IFNULL(TRIM(UPPER(ant.Email)), '') = IFNULL(TRIM(UPPER(f.payload.emailAddress)), '') #- statement: print recordsNotCreatedInAnthology - statement: (recordsNotCreatedInAnthologyCount) => SELECT COUNT(*) FROM recordsNotCreatedInAnthology #- statement: print recordsNotCreatedInAnthologyCount - statement: (allNotCreatedRecords) => SELECT * FROM rejectedDueToMissingFields UNION SELECT * FROM recordsNotCreatedInAnthology - statement: (allNotCreatedRecordsCount) => SELECT COUNT(*) FROM allNotCreatedRecords #- statement: print allNotCreatedRecordsCount #### Start - Update ErrorStatus #### - statement: (updateErrorStatusToSf) => SELECT Id, 'Export Error' `Sync_Status_SIS__c`, RejectedReason `SIS_Sync_Error_Message__c` FROM allNotCreatedRecords #- statement: print updateErrorStatusToSf - statement: (results, errors) => UPDATE updateErrorStatusToSf INTO salesforceContactWriter #- statement: PRINT results #- statement: PRINT errors #### End - Update ErrorStatus #### # Add more statements to convert, join, aggregrate, transform, and integrate your data