1

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 ]%%
  • based on your description, this sounds like a picklist in Salesforce. Sounds like one field could be any of three values. If that is the case and you can only have one value in the field, when would you ever have all three values? Can you give more detail on the field type in Salesforce? Is it a multiline text field? – garek007 Feb 7 at 17:25
  • can you explain how this script is not working for you? e.g. Error? Not expected results? etc. – Gortonington Feb 7 at 21:11
  • Garek007, Yes it is a picklist value. But there are more then 10 picklist values in there. So this one field can contain more then just those three values. The field type of the field "Evidence_Type__c" is text with 255 characters. Gortonington, I do not get an error. The problem with my script is that it evaluates all rows IF the value is not there it shows some text. So IF i have 3 rows where the value is not present is. The error shows 3 times. – wfrankhuizen Feb 8 at 7:22
2

So eventually i figured it out myself. I was thinking to complicated by first getting all the rows in and then check if one of those rows contain a certain value. What i now did was just a simple lookup on the CM_Evidence__c_Salesforce object with two criteria specified. So i do not have to use LookupRows at all. This is my code:

%%[
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 @tsa = Lookup('CM_Evidence__c_Salesforce','Evidence_type__c','Evidence_type__c','Technical Self-Assessment','Competence_Profile__c',@competence)
SET @csa = Lookup('CM_Evidence__c_Salesforce','Evidence_type__c','Evidence_type__c','Competence Self-Assessment','Competence_Profile__c',@competence)
SET @interview = Lookup('CM_Evidence__c_Salesforce','Evidence_type__c','Evidence_type__c','Interview/Written Assignment','Competence_Profile__c',@competence)
]%%

%%[IF EMPTY (@tsa) THEN]%%
Technical Self-Assessment not present<BR>
%%[ELSE]%%
%%[ENDIF]%%


%%[IF EMPTY (@csa) THEN]%%
Competence Self-Assessment not present<BR>
%%[ELSE]%%
%%[ENDIF]%%


%%[IF EMPTY (@tsa) THEN]%%
Interview/Written Assignment not present<BR>
%%[ELSE]%%
%%[ENDIF]%%


%%[IF EMPTY(@medical) THEN]%%
Medical Certificate not present<BR>
%%[ELSE]%%
%%[ENDIF]%%

%%[IF EMPTY(@safety) THEN]%% 
Safety Certificate not present<BR>
%%[ELSE]%%
%%[ENDIF]%%

%%[IF EMPTY(@education) THEN]%%
Education Degree not present<BR>
%%[ELSE]%%
%%[ENDIF]%%
  • Glad you got it! – garek007 Feb 8 at 21:33

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.