So a Contact has one Competence profile in the object: CM_Competence_Profile__c_Salesforce
This Competence profile could have multiple evidences (rows) within the object: CM_Evidence__c_Salesforce
These rows within CM_Evidence__c_Salesforce object all got a field named: Evidence_Type__c
Evidence_Type__c is a text field which can contain 255 characters max.
What i want is evaluate every row which matches the Id of CM_Competence_Profile__c_Salesforce within the CM_Evidence__c_Salesforce if they have the following values in field Evidence_Type__c: "Competence Self-Assessment" or "Technical Self-Assessment" or "Interview/Written Assignment". By the way this field Evidence_Type__c can have more different values then just the three mentioned above.
The catch is, i want to show specifically if one or more of those rows are missing.
So for instance if "Interview/Written Assignment" is not present but the other two are. I want to show the content: "Interview/Written Assignment is missing"
I tried building this ampscript, but it's not working for me. Can someone help me optimizing this?
%%[
SET @contactid = AttributeValue('Contact Id') /* Better handling of NULL values */
SET @competence = Lookup('CM_Competence_Profile__c_Salesforce','Id','Candidate__c',@contactid)
SET @medical = Lookup('CM_Competence_Profile__c_Salesforce','Medical_Certificate__c','Candidate__c',@contactid)
SET @safety = Lookup('CM_Competence_Profile__c_Salesforce','Safety_Certificate__c','Candidate__c',@contactid)
SET @education = Lookup('CM_Competence_Profile__c_Salesforce','Education_Degree__c','Candidate__c',@contactid)
SET @rows = LookupRows('CM_Evidence__c_Salesforce','Competence_Profile__c',@competence)
SET @RowCount = rowcount(@rows)
IF @RowCount > 0 THEN
FOR @i = 1 TO @RowCount DO
VAR @DEColumn
SET @row = row(@rows,@i) /* get row based on counter */
SET @DEColumn = field(@row,"Evidence_Type__c")
]%%
%%[ IF @DEColumn == 'Competence Self-Assessment' THEN ]%%
Competence Self-Assessment is NOT present<BR><BR>
%%[ ELSEIF @DEColumn == 'Technical Self-Assessment' THEN ]%%
Technical Self-Assessment is NOT present<BR><BR>
%%[ ELSEIF @DEColumn == 'Interview/Written Assignment' THEN ]%%
Interview/Written Assignment is NOT present<BR><BR>
%%[ ENDIF ]%%
%%[ NEXT @i ]%%
%%[ ELSE ]%%
No rows found
%%[ ENDIF ]%%