0

I'm using AmpScript to make a custom email and I need to sort rows to display them in order of date. I saw that I can use Server Side Javascript to make this, but my variable in ampscript is a rowset, I don't know how to manipulate this kind of var in SSJS.

I've already seen this post but it doesn't work with rowset

Code : I retrieve my objects like this in ampscript

SET @sessions = RetrieveSalesforceObjects("EventApi__Schedule_Item__c", "id,Name,EventApi__Start_Time__c,EventApi__End_Time__c,MCRoomName__c,EventApi__Start_Date__c,EventApi__Event__c", "EventApi__Event__c","=",@eventId, "EventApi__Is_Active__c","=","TRUE", "FormatType__c","=","Workshop")
SET @nbsessions = rowcount(@sessions)

and I want to use it in this to sort them with a SSJS, for now I've done this but it doesn't work :

<script runat="server">
Platform.Load("Core","1");
var workshop=Variable.GetValue("@sessions");
var nbworkshop=Variable.GetValue("@nbsessions");
var rows =[];
for(i=0;i<nbworkshop;i++){
  rows.push(workshop[i]);
}
Write(Stringify(workshop));
Variable.SetValue("@debug", Stringify(nbworkshop));
Variable.SetValue("@debug", Stringify(rows));
</script>
%%=v(@debug)=%%

Example of what I retrieve in @sessions for AMPScript :

%%[
FOR @i = 1 to @nbsessions do VAR @sessionName,@date,@startTime,@endTime,@place,@sessionId 
SET @row =row(@sessions,@i) 
SET @sessionName = field(@row, "Name") 
SET @date = field(@row,"EventApi__Start_Date__c") 
SET @startTime = field(@row, "EventApi__Start_Time__c") 
SET @endTime = field(@row, "EventApi__End_Time__c") 
SET @place = field(@row, "MCRoomName__c") 
SET @eventSession= field(@row, "EventApi__Event__c") 
SET @sessionId = field(@row,"id")
]%%

%%=v(@date)=%% - %%=v(@startTime)=%% - %%=v(@endTime)=%%
 %%=v(@sessionName)=%%
o The Workshop will take place in %%=v(@place)=%%
o Topics covered : %%[
VAR @topics,@nbtopics,@rowtopic SET @topics = RetrieveSalesforceObjects("EventApi__Track_Schedule_Item__c","MCTagName__c","EventApi__Schedule_Item__c","=",@sessionId) SET @nbtopics = rowcount(@topics)

FOR @j=1 to @nbtopics do VAR @topicname SET @rowtopic = row(@topics,@j) SET @topicname = field(@rowtopic,"MCTagName__c")
]%% %%[IF @j == @nbtopics THEN]%%%%=v(@topicname)=%%%%[ELSE]%%%%=v(@topicname)=%%, %%[ENDIF]%%%%[NEXT @j]%%
%%[NEXT @i]%%

And it is like this but I would like to order them thanks to SSJS in order of date and startTime if possible:

2018-09-13 - 6:00 PM - 7:00 PM
 Gender Equality : More to Do!
o The Workshop will take place in OECD
o Topics covered :  Smart cities & mobility,  Climate


2018-09-13 - 3:30 PM - 5:00 PM
 New ways to live in Global Cities
o The Workshop will take place in OECD
o Topics covered :  Smart cities & mobility,  Climate


2018-09-12 - 4:30 PM - 6:00 PM
 Disruptive innovation in mobility: Implications for our urban public transports
o The Workshop will take place in OECD
o Topics covered :  Smart cities & mobility,  Climate

Do you have any idea how to achieve this ? thanks

1

To my knowledge, this is not possible without using a for loop to manually create a JSON string inside AMPScript and then passing the JSON string to SSJS and sorting it there.

This is very inefficient though and can eat up a lot of processing and time. It also is not recommended to be done inside of an email as it can cause significant delays and potentially time out the send.

Perhaps you could automate the RetrieveSalesforceObject to go to a DE (Script Activity in an automation) or, if possible, utilize a sync'd DE via Connector. Then do a LookupOrderedRows() to sort the DE results inside the email.

This would not provide real time results, but would allow you to sort inside the email without significant impact to processing speed.

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.