0

There is a Field called Program_Status in Data Extension (DE) [Recruiter High school Visits V2 - 2018-06-25T124653822] and I would like to update this field based on DE Program_Interest_G that has the field named [Status]

I tried the following TWO syntax:

Select 
Program_Interest_G.Status as [Program_Status]
FROM [Recruiter High school Visits V2 - 2018-06-25T124653822] v
LEFT JOIN Program_Interest_G ON v.Global_Program_of_Interest_1__c = Program_Interest_G.ProgramOfInterest

OR

Select 
Status as [Program_Status]
FROM Program_Interest_G

With both syntax error the same comment:

An error occurred saving the definition: A problem occurred creating your query definition. Please contact Customer Service for additional information. Name: Update Program 1 Status External Key: Update_Program_1_Status Error saving the Query field.Field 'Lead:Id' is a required for the Target Data Extension. Ensure this field is specified in your query text.

The issue is the first DE is just a list of Programs and the status of each program. Only the other DE that I'm trying to update has Lead.ID's

6
  • Can you post the columns in the target de? If you don’t have a lead.id then you can use that source de for the query – Samuel Dare Jun 25 '18 at 22:56
  • Target: Lead:Email Lead:Id Lead:FirstName Lead:LastName Global_School_Program_of_Interest_1__c Global_Program_of_Interest_1__c Lead:Program_Status__c ======================================== Source: SchoolProgram ProgramOfInterest ProgramCode Status – Robert Wynter Jun 26 '18 at 14:12
  • Does Lead:Id have the nullable field ticked? – Samuel Dare Jun 26 '18 at 14:28
  • No Lead:id is not Ticket nullable – Robert Wynter Jun 26 '18 at 15:32
  • Should I chnage this to ticked? – Robert Wynter Jun 26 '18 at 15:33
0
  • Create a new DE to and make sure all field that wont be part of the query are nullable.

  • Use this Query to copy the data into the new DE

    SELECT * From
    
    SchoolProgram ProgramOfInterest ProgramCode Status
    
    Target DE: Your new DE
    

Your query should work now

4
  • Hi there. Thx for the suggestion. I need the DE related to the Journey Builder updated [Recruiter High school Visits V2 - 2018-06-25T124653822] . Therefore creating a third DE would not resolve my issue. thx much though – Robert Wynter Jun 26 '18 at 20:53
  • Replace this De with the one in contact builder and use it for your journey? – Samuel Dare Jun 26 '18 at 21:13
  • alternatively you can populate the School Program DE with a Lead:ID column Select that in the join – Samuel Dare Jun 26 '18 at 21:25
  • I got it working with the JB DE and without having to create an additional DE. thx much for your help. couldn't have done it without you: [code]SELECT [Recruiter High school Visits V2 - 2018-06-25T124653822].[Lead:Id], Program_Interest_G.Status as [Program_Status] FROM [Recruiter High school Visits V2 - 2018-06-25T124653822] LEFT JOIN Program_Interest_G ON [Recruiter High school Visits V2 - 2018-06-25T124653822].Global_Program_of_Interest_1__c= Program_Interest_G.ProgramOfInterest [/code] – Robert Wynter Jun 27 '18 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.