Hello Salesforce Community, I'm new in Programming (around 6 months with JavaScript and Java) and even more rookie in Marketing Cloud (going to my second week) and I'm exploring a use case for a client but i'm still confused about what (or how) it's possible to achieve with ServerSide JavaScript or AMPScript.
Some insight to the best way to achieve this result would be greatly apreciated!
The use case is the following:
I have a General Data Extension with all the contacts and with following fields:
- customer_id(text)
- email(emailAddress)
- opt_in(boolean).
Then, I have a second DE, with the exact same fields and contacts, that recieves the updated info of the contacts that changed their opt_in state.
What I want to do is the following: Using Activities from Automation Studio, update the General DE with the Second DE and change the boolean value for the ones who changed their state (meaning, from False to True and vice-versa).
In pseudo-code would be something like this:
FOREACH contact on GeneralDE THAT customer_id FROM GeneralDE IS EQUAL TO customer_id FROM SecondDE AND
optin FROM GeneralDE IS NOT EQUAL TO optin FROM SecondDE) {
UPDATE opt_in FROM GeneralDE to the value of opt_in in the SecondDE
}
I've tried two diffenrent aproaches:
A) Run a Query to bring all the different data and then add a SSJS script to iterate through all contacts and change the boolean to the opposite.
Query to filter the data (name: query1)
SELECT g.customer_id, g.email, g.opt_in
FROM Opt_In_Geral as g
INNER JOIN Opt_In_Test2 as t
ON g.opt_in <> t.opt_in
SSJS:
<script runat=server language=javascript>
Platform.Load("core","1");
var query = QueryDefinition.Init("query1");
var data = query.Perform();
if(data) {
for(var i = o; i < data.length; i++) {
var optin = data[i]["opt_in"]
if(optin == false) {
data[i].Update({"opt_in" : true });
} else {
data[i].Update({"opt_in" : false});
}
});
};
};
</script>
B)Directly update and filter the DEs's through SSJS.
var geralDE = DataExtension.Init("Opt_In_Geral");
var test2DE = DataExtension.Init("Opt_In_Test2");
var geralDEfalse = geralDE.Rows.Lookup(["opt_in"], [false]);
var geralDEtrue = geralDE.Rows.Lookup(["opt_in"], [true]);
var test2DEtrue = test2DE.Rows.Lookup(["opt_in"], [true]);
var test2DEfalse = test2DE.Rows.Lookup(["opt_in"], [false]);
for(var i = 0; i < geralDEfalse.length; i++) {
if(geralDEfalse[i]["customer_id"] === test2DEtrue[i]["customer_id"]) {
geralDEfalse.Rows.Update({opt_in:"true"}, ["customer_id"], [test2DEtrue[i]["customer_id"]]);
}
};
for(var i = 0; i < geralDEtrue.length; i++) {
if(geralDEtrue[i]["customer_id"] === test2DEfalse[i]["customer_id"]) {
geralDEtrue.Rows.Update({opt_in:"false"}, ["customer_id"], [test2DEfalse[i]["customer_id"]]);
}
};
Both of them aren't working and I'm feeling there is a much easier way to do this... Sorry about the messy code and I'm already guessing that there would be some major errors but I can't unlock it by my own or find a similar example online.
Thank you in advance to everyone who care to help in any way.