0

I want to show jobs which are stored in a data extension in Marketing Cloud on a Cloudpage. I got that working, but it only shows the EXACT value. For instance a if i put in a value "Able Seaman" will show all jobs which have EXACTLY the name "Able Seaman". But i want to show all jobs which contains the words Able Seaman. So for instance "Able Seaman Offshore" and "Able Seaman hiring now". And if possible how can you add multiple values. For instance "able Seaman" and "seafarer" so it shows all jobs which have both those values?

Please find the code on my Cloudpage below:

<!--
%%[

/* HARDCODE SET vacancies */
SET @discipline_name = ""
SET @category_name = ""
SET @vacancy_name = "Able Seaman"
SET @country_name = ""

/* Default amount of vacancies to show, can be set to 3, 6, 9 or 12 */
SET @vacancy_amount = "12"

/* DEFAULT SORT ORDER FOR FOUND VACANCIES */
SET @vacancy_sort = "ASC"

/* vacancy DATA EXTENSION */
SET @de_vacancies = "Vacancy__c_Salesforce"

/* FIELDS IN vacancy DATA EXTENSION (USED IN LOOKUPS!) */
SET @discipline_field = "Discipline_Name__c"
SET @category_field = "Job_Field__c"
SET @vacancy_field = "Vacancy_Title__c"
SET @country_field = "Country_Picklist__c" 


/* DYNAMIC SET vacancies info */
IF RequestParameter("discipline") != "" THEN
 SET @discipline_name = RequestParameter("discipline")
ENDIF

IF RequestParameter("category") != "" THEN
 SET @category_name = RequestParameter("category")
ENDIF

IF RequestParameter("vacancy") != "" THEN
 SET @vacancy_name = RequestParameter("vacancy")
ENDIF

IF RequestParameter("country") != "" THEN
 SET @country_name = RequestParameter("country")
ENDIF

IF RequestParameter("amount") != "" AND RequestParameter("amount") <= 12 THEN
  SET @vacancy_amount = RequestParameter("amount")
ENDIF

IF Uppercase(RequestParameter("sort")) == "ASC" OR Uppercase(RequestParameter("sort")) == "DESC" THEN
  SET @vacancy_sort = Uppercase(RequestParameter("sort"))
ENDIF

]%%
--><script runat="server" language="JavaScript" executioncontexttype="Get">
Platform.Load("Core","1");

/* DETERMINE WHICH LOOKUP TO USE, THERE ARE MULTIPLE LOOKUPS POSSIBLE BASED ON THE SET FIELDS */
var discipline_name = Platform.Variable.GetValue("@discipline_name");
var category_name = Platform.Variable.GetValue("@category_name");
var vacancy_name = Platform.Variable.GetValue("@vacancy_name");
var country_name = Platform.Variable.GetValue("@country_name");

var vacancy_amount = Platform.Variable.GetValue("@vacancy_amount");
var vacancy_sort = Platform.Variable.GetValue("@vacancy_sort");

var discipline_field = Platform.Variable.GetValue("@discipline_field");
var category_field = Platform.Variable.GetValue("@category_field");
var vacancy_field = Platform.Variable.GetValue("@vacancy_field");
var country_field = Platform.Variable.GetValue("@country_field");

// DEFAULT ROWSET, 12 last modified vacancies:
var dataextension_name = Platform.Variable.GetValue("@de_vacancies");
var vacanciesArray = Platform.Function.LookupOrderedRows(dataextension_name, vacancy_amount, ("Publish_Date__c " + vacancy_sort), "Published__c", "1");

var filter = "";
var simpleFilterPart_Published = {
    Property: "Published__c",
    SimpleOperator: "equals",
    Value: "TRUE"
  } 

//SIMPLE (BASE) FILTERS (= sf_ prefix)
var sf_Discipline = {
  LeftOperand: {
      Property: discipline_field,
    SimpleOperator: "equals",
    Value: discipline_name
  },
  LogicalOperator: "AND",
  RightOperand: simpleFilterPart_Published
};

var sf_Category = {
  LeftOperand: {
    Property: category_field,
    SimpleOperator: "equals",
    Value: category_name
  },
  LogicalOperator: "AND",
  RightOperand: simpleFilterPart_Published
};

var sf_Vacancy = {
  LeftOperand: {
    Property: vacancy_field,
    SimpleOperator: "IN",
    Value: vacancy_name
  },
  LogicalOperator: "AND",
  RightOperand: simpleFilterPart_Published
};

var sf_Country = {
  LeftOperand: {
    Property: country_field,
    SimpleOperator: "equals",
    Value: country_name
  },
  LogicalOperator: "AND",
  RightOperand: simpleFilterPart_Published
};

// COMPLEX FILTERS (COMBINED SIMPLE FILTERS) (= cf_ prefix)
var cf_sfDiscipline_AND_sfVacancy = {
  LeftOperand: sf_Discipline,
  LogicalOperator: "AND",
  RightOperand: sf_Vacancy
};

var cf_sfDiscipline_AND_sfCountry = {
  LeftOperand: sf_Discipline,
  LogicalOperator: "AND",
  RightOperand: sf_Country
};

var cf_sfVacancy_AND_sfCountry = {
  LeftOperand: sf_Vacancy,
  LogicalOperator: "AND",
  RightOperand: sf_Country
};

var cf_sfVacancy_AND_sfCategory = {
  LeftOperand: sf_Vacancy,
  LogicalOperator: "AND",
  RightOperand: sf_Category
};

var cf_sfCategory_AND_sfDiscipline = {
  LeftOperand: sf_Category,
  LogicalOperator: "AND",
  RightOperand: sf_Discipline
};

var cf_sfCategory_AND_sfCountry = {
  LeftOperand: sf_Category,
  LogicalOperator: "AND",
  RightOperand: sf_Country
};

var cf_sfDiscipline_AND_sfVacancy_AND_sfCountry = {
  LeftOperand: {
    LeftOperand: sf_Discipline,
    LogicalOperator: "AND",
    RightOperand: sf_Vacancy
  },
  LogicalOperator: "AND",
  RightOperand: sf_Country
};

var cf_sfDiscipline_AND_sfVacancy_AND_sfCategory = {
  LeftOperand: {
    LeftOperand: sf_Discipline,
    LogicalOperator: "AND",
    RightOperand: sf_Vacancy
  },
  LogicalOperator: "AND",
  RightOperand: sf_Category
};

var cf_sfDiscipline_AND_sfCountry_AND_sfCategory = {
  LeftOperand: {
    LeftOperand: sf_Discipline,
    LogicalOperator: "AND",
    RightOperand: sf_Country
  },
  LogicalOperator: "AND",
  RightOperand: sf_Category
};

var cf_sfVacancy_AND_sfCountry_AND_sfCategory = {
  LeftOperand: {
    LeftOperand: sf_Vacancy,
    LogicalOperator: "AND",
    RightOperand: sf_Country
  },
  LogicalOperator: "AND",
  RightOperand: sf_Category
};

var cf_sfDiscipline_AND_sfVacancy_AND_sfCountry_AND_sfCategory = {
  LeftOperand: {
    LeftOperand: sf_Discipline,
    LogicalOperator: "AND",
    RightOperand: sf_Vacancy
  },
  LogicalOperator: "AND",
  RightOperand: {
    LeftOperand: sf_Country,
    LogicalOperator: "AND",
    RightOperand: sf_Category
  }
};



// CREATE LOGICAL CONDITIONS TO DEFINE WHICH FILTER TO USE
if (discipline_name != "" && category_name == "" && vacancy_name == "" && country_name == "") {
  // SIMPLE: DISCIPLINE
  filter = sf_Discipline;

} else if (discipline_name == "" && category_name != "" && vacancy_name == "" && country_name == "") {
  // SIMPLE: CATEGORY
  filter = sf_Category;

} else if (discipline_name == "" && category_name == "" && vacancy_name != "" && country_name == "") {
  // SIMPLE: VACANY
  filter = sf_Vacancy;

} else if (discipline_name == "" && category_name == "" && vacancy_name == "" && country_name != "") {
  // SIMPLE: COUNTRY
  filter = sf_Country;

} else if (discipline_name != "" && category_name == "" && vacancy_name != "" && country_name == "") {
  // COMPLEX: DISCIPLINE + VACANCY
  filter = cf_sfDiscipline_AND_sfVacancy;

} else if (discipline_name != "" && category_name == "" && vacancy_name == "" && country_name != "") {
  // COMPLEX: DISCIPLINE + COUNTRY
  filter = cf_sfDiscipline_AND_sfCountry;

} else if (discipline_name == "" && category_name == "" && vacancy_name != "" && country_name != "") {
  // COMPLEX: VACANCY + COUNTRY
  filter = cf_sfVacancy_AND_sfCountry;

} else if (discipline_name == "" && category_name != "" && vacancy_name != "" && country_name == "") {
  // COMPLEX: VACANCY + CATEGORY
  filter = cf_sfVacancy_AND_sfCategory;

} else if (discipline_name != "" && category_name != "" && vacancy_name == "" && country_name == "") {
  // COMPLEX: CATEGORIE + DISCIPLINE
  filter = cf_sfCategory_AND_sfDiscipline;

} else if (discipline_name == "" && category_name != "" && vacancy_name == "" && country_name != "") {
  // COMPLEX: CATEGORIE + COUNTRY
  filter = cf_sfCategory_AND_sfCountry;

} else if (discipline_name != "" && category_name == "" && vacancy_name != "" && country_name != "") {
  // COMPLEX: DISCIPLINE + VACANCY + COUNTRY
  filter = cf_sfDiscipline_AND_sfVacancy_AND_sfCountry;

} else if (discipline_name != "" && category_name != "" && vacancy_name != "" && country_name == "") {
  // COMPLEX: DISCIPLINE + VACANCY + CATEGORY
  filter = cf_sfDiscipline_AND_sfVacancy_AND_sfCategory;

} else if (discipline_name != "" && category_name != "" && vacancy_name == "" && country_name != "") {
  // COMPLEX: DISCIPLINE + COUNTRY + CATEGORY
  filter = cf_sfDiscipline_AND_sfCountry_AND_sfCategory;

} else if (discipline_name == "" && category_name != "" && vacancy_name != "" && country_name != "") {
  // COMPLEX: VACANCY + COUNTRY + CATEGORY
  filter = cf_sfVacancy_AND_sfCountry_AND_sfCategory;

} else if (discipline_name != "" && category_name != "" && vacancy_name != "" && country_name != "") {
  // COMPLEX: DISCIPLINE + VACANCY + COUNTRY + CATEGORY
  filter = cf_sfDiscipline_AND_sfVacancy_AND_sfCountry_AND_sfCategory;

}



// IF FILTER IS NOT EMPTY, THEN RETRIEVE ROWS
if (typeof(filter) == "object") {
  //Write(Platform.Function.Stringify(filter));
  // Filter data extension
  var dataextension = DataExtension.Init(dataextension_name);
  var vacanciesArray = dataextension.Rows.Retrieve(filter);

  // SORT RESULTS BASED ON DATE
  // https://stackoverflow.com/questions/40965727/sort-array-of-objects-with-date-field-by-date#answer-40965804
  vacanciesArray.sort(function compare(a, b) {
    var dateA = new Date(a.Publish_Date__c);
    var dateB = new Date(b.Publish_Date__c);

    if (vacancy_sort == "ASC") {
      return dateA - dateB;
    } else {
      return dateB - dateA;
    }
  });
}


// Return ARRAY to AMPscript
Platform.Variable.SetValue("@vacanciesArray", vacanciesArray);
</script><!-- %%[ IF RowCount(@vacanciesArray) > 0 THEN ]%% --><div class="background-grey jobs">
 <div class="container">
  <div class="row display-flex">
   <!-- %%[
  SET @totalCount = RowCount(@vacanciesArray)

  FOR @i = 1 TO @totalCount DO
    IF @i <= @vacancy_amount THEN
    ]%% --><script runat="server" language="JavaScript" executioncontexttype="Get">
    // JAVASCRIPT IS NEEDED TO RETRIEVE ARRAY VALUES AS THE DATA EXTENSION RESULT IS AN ARRAY
    var counter = (Platform.Variable.GetValue(&quot;@i&quot;) - 1); // JAVASCRIPT STARTS WITH 0
    Platform.Variable.SetValue(&quot;@title&quot;, vacanciesArray[counter]['Vacancy_Title__c']);
    Platform.Variable.SetValue(&quot;@country&quot;, vacanciesArray[counter]['Country_Picklist__c']);
    Platform.Variable.SetValue(&quot;@startDate&quot;, vacanciesArray[counter]['Start_Date__c']);
    Platform.Variable.SetValue(&quot;@referenceNumber&quot;, vacanciesArray[counter]['Reference_Number__c']);
    </script><!-- %%[
    SET @newHtmlRow = "FALSE"
    SET @modulo = Mod(@i, 3)

    SET @url = CONCAT("http://jobs.atlasprofessionals.com/job/", @referenceNumber)

    IF @modulo == 0 AND @i != @totalCount THEN
      /* NEW ROW IS NEEDED BECAUSE 3 ITEMS ARE SHOWN and IS NOT LAST ITEM */
      SET @newHtmlRow = "TRUE"
    ENDIF
]%% --><!-- %%[ IF @modulo == 1 THEN ]%% --><div class="col-lg-3 col-md-3 col-sm-10 col-md-offset-1 col-xs-offset-1 job-offer shadow-small">
    <a href="%%=v(@url)=%%" title="%%=v(@title)=%%"><img alt="" src="http://image.mailing.atlasprofessionals.com/lib/fe8413727c610d7f7c/m/1/96dec3c1-9005-4a4c-90e0-cb923ddb298b.png" width="150"></a><h3 class="text-bold font-size-24 text-color-dark-grey margin-bottom-40">
     <a class="text-color-dark-grey" href="%%=v(@url)=%%" title="%%=v(@title)=%%">%%=v(@title)=%%</a></h3><p class="text-regular font-size-16 text-color-light-grey">
     <a class="text-color-light-grey" href="%%=v(@url)=%%" title="%%=v(@title)=%%">%%=v(@country)=%%<br>
     %%=FormatDate(@startDate, "MMMM dd, yyyy")=%%</a></p></div><!-- %%[ ELSE ]%% --><div class="col-lg-3 col-md-3 col-sm-10 col-md-offset-0 col-xs-offset-1 job-offer shadow-small">
    <a href="%%=v(@url)=%%" title="%%=v(@title)=%%"><img alt="" src="http://image.mailing.atlasprofessionals.com/lib/fe8413727c610d7f7c/m/1/96dec3c1-9005-4a4c-90e0-cb923ddb298b.png" width="150"></a><h3 class="text-bold font-size-24 text-color-dark-grey margin-bottom-40">
     <a class="text-color-dark-grey" href="%%=v(@url)=%%" title="%%=v(@title)=%%">%%=v(@title)=%%</a></h3><p class="text-regular font-size-16 text-color-light-grey">
     <a class="text-color-light-grey" href="%%=v(@url)=%%" title="%%=v(@title)=%%">%%=v(@country)=%%<br>
     %%=FormatDate(@startDate, "MMMM dd, yyyy")=%%</a></p></div><!-- %%[ ENDIF ]%% --><!-- %%[ IF @newHtmlRow THEN ]%% --></div><div class="row display-flex">
   <!--%%[ ENDIF ]%% --><!-- %%[ 
ENDIF 
NEXT @i 
]%% --></div><!-- START BUTTON --><div class="row margin-top-55">
   <div class="col-md-4 col-sm-6 col-xs-10 col-md-offset-4 col-sm-offset-3 col-xs-offset-1">
    <a class="btn btn-primary button-2-small" href="#">BUTTON1</a></div></div><!-- END BUTTON --></div></div><!-- %%[ ENDIF ]%% -->
1

Good news is that I think it can be a simple solution.

You just change this snippet:

var sf_Vacancy = {
  LeftOperand: {
    Property: vacancy_field,
    SimpleOperator: "IN",
    Value: vacancy_name
  },

to:

var sf_Vacancy = {
  LeftOperand: {
    Property: vacancy_field,
    SimpleOperator: "like",
    Value: vacancy_name
  },

Using like will give results on anything that contains the value defined. Which I believe is what you are looking for. IN will work as a sort of 'shopping list' for all the available values.

Basically:

values of field: {"onions", "baby onions", "bananas", "onion juice", "baby carrots"}
IN values: ("onions","bananas")
like value: "onion"

Using a filter via IN would return: {"onions","bananas"}
Where using a filter with like would return: {"onions","baby onions","onion juice"}

Here is a good list of SimpleOperators available for simplefilterparts

| improve this answer | |
  • That works very well! Thank you! My additional question: how can i add multiple values? For instance: "onion" AND "banana" – wfrankhuizen Oct 9 '18 at 13:45
  • I believe you would have to use two simplefilterparts and then use a logical operator of 'OR' to form a complex filter part. E.g. food is LIKE "onion" OR food is LIKE "bannana". This will get all food that contains the words onion and all the foods that contain the word "banana". – Gortonington Oct 9 '18 at 13:53
  • I do not totally understand what you mean. How would that look like in code? – wfrankhuizen Oct 9 '18 at 14:03
  • Hi Gortonington, can you still help me with this please? – wfrankhuizen Oct 10 '18 at 6:20
  • you duplicate what you already have in your code for complex filters e.g. var cf_onion_sfp_OR_banana_sfp = { LeftOperand: onion_sfp, LogicalOperator: "OR", RightOperand: banana_sfp}; – Gortonington Oct 10 '18 at 16:42

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.