0

I would like to create a Property Alert email that showcases a selection of Apartments for Rent based on the prospect's City of interest.

It looks like I can use the LookupOrderedRows AMPscript function to pull Apartment information from the Apartment DE, which is good. However, I'm unsure how to apply additional filter criteria such as Price is within 10% of the prospect's budget.

Also, I'm unsure how to format and structure the returned data, so that the second, third, fourth, etc row is displayed in a way that shows the apartment image on the left and apartment details on the right.

Here's the script I have so far:

   %%[
    var @rows, @row, @rowCount, @numRowsToReturn, @lookupValue, @i
    
    set @lookupValue = AttributeValue("SA_City__c") /* value from attribute or DE column in send context */
    set @numRowsToReturn = 0 /* 0 means all, max 2000 */
    set @rows = LookupOrderedRows("FLTR_Apartment_Salesforce",@numRowsToReturn,"Price__c asc, Number_of_bedrooms__c, Cover_picture__c, Code__c, Available_From__c, Street__c, Country_Code__c, Number_of_allowed_residents__c, Enabled_Booking_Modes__c","City__c", @lookupValue)
    set @rowCount = rowcount(@rows)
    
    if @rowCount > 0 then
    
      for @i = 1 to @rowCount do
    
        set @row = row(@rows,@i) /* get row based on counter */
        set @Price__c = field(@row,"Price__c")
        set @Number_of_bedrooms__c = field(@row,"Number_of_bedrooms__c")
        set @Cover_picture__c = field(@row,"Cover_picture__c")
        set @Code__c = field(@row,"Code__c")
    
        ]%%
    Row %%=v(@i)=%%<br>
    Code %%=v(@Code__c)=%%<br>
    Guests %%=v(@Number_of_allowed_residents__c)=%%<br>
    Cover Picture %%=v(@Cover_picture__c)=%%<br>
    Number of Bedrooms is %%=v(@Number_of_bedrooms__c)=%%<br>
    Price %%=v(@Price__c)=%%<br><br>
        %%[ 
      next @i ]%%
    
    %%[ else ]%%
    
    No rows found
    
    %%[ endif ]%%

My guess is I could apply additional filter criteria within the "if @rowCount > 0 then" statement, like "if @rowCount > 0 AND Price__c BETWEEN X% AND Y% then", but my logic might be too complicated for what LookupOrderedRows is capable of.

My question is: Has anyone done something similar to this with email and could offer some guidance on what I should do? Perhaps it's not even possible to achieve what I'm aiming for with the LookupOrderedRows function.

  • 1
    Lookup functions only do AND operations for criteria, so you'll have to loop through all of the rows and do your conditional inside the loop. – Adam Spriggs Sep 10 at 22:04
  • 1
    I'd consider populating your "FLTR_Apartment_Salesforce" (PK on ContactID,ApartmentID) Data Extension using a Query Activity in Automation Studio where you can do the heavy lifting of performing matching logic/complex filter criteria as a bulk operation and have your LookupOrderedRows just collect the pre-selected apartment records for the contact and display them on the page. – Macca Sep 11 at 1:07
2

You are quite close to the solution. Either you pre-select only relevant apartments through SQL as macca suggests, or you can insert IF statements into the loop as adam suggests. I am going the second route in the following example.

As for formatting the email, essentially you just have to embed your variable display into an HTML structure. Email coding in general will lead us a bit too far to discuss here, but for the sake of demonstration, we can use a responsive two-column layout from here:

https://mangools.com/blog/how-to-create-responsive-two-column-email-template/

The following is a code excerpt from that example. I am just showing the relevant part for the sake of brevity. the following code only works as expected if the changed example code below is embedded back into the full code from the link as the styling definitions etc are not in the excerpt. Your variables are placed in the HTML structure like so:

<!-- Set of 2 columns -->
<!-- Set width to half of width of container and add align="left" to float the table to the left and make column of it. -->
<table class="column-1-2" width="300" align="left" style="border-collapse: collapse;" cellspacing="0" cellpadding="0" border="0">
    <tbody>
        <tr>
            <td style="padding: 200px 25px; background: #fcc50a; color: #fff; text-align: center;" valign="top">
                <img source="%%=v(@Cover_picture__c)=%%" alt="img alt text"></img>
            </td>
        </tr>
    </tbody>
</table>
<!-- The div's width is automatically 100% and previous table can float it, so it fills the second half -->
<div style="padding: 0 25px; background: #fff; text-align: center;">
    <div style="padding: 200px 0">
        Code %%=v(@Code__c)=%%<br>
        Guests %%=v(@Number_of_allowed_residents__c)=%%<br>
        Number of Bedrooms is %%=v(@Number_of_bedrooms__c)=%%<br>
        Price %%=v(@Price__c)=%%<br><br>
    </div>
</div>
<!-- End of two set columns -->

which has this effect: enter image description here

==

here is an example for an additional filter logic inside the loop. In my example, the yellow / white segment will only display if the price is "100000", otherwise it will not be shown.

Place this right before the above code (the excerpt, not the full code):

 %%[
/*
    var @rows, @row, @rowCount, @numRowsToReturn, @lookupValue, @i
    
    set @lookupValue = AttributeValue("SA_City__c") /* value from attribute or DE column in send context */
    set @numRowsToReturn = 0 /* 0 means all, max 2000 */
    set @rows = LookupOrderedRows("FLTR_Apartment_Salesforce",@numRowsToReturn,"Price__c asc, Number_of_bedrooms__c, Cover_picture__c, Code__c, Available_From__c, Street__c, Country_Code__c, Number_of_allowed_residents__c, Enabled_Booking_Modes__c","City__c", @lookupValue)
    set @rowCount = rowcount(@rows)
    
    if @rowCount > 0 then
    
      for @i = 1 to @rowCount do
    
        set @row = row(@rows,@i) /* get row based on counter */
        set @Price__c = field(@row,"Price__c")
        set @Number_of_bedrooms__c = field(@row,"Number_of_bedrooms__c")
        set @Cover_picture__c = field(@row,"Cover_picture__c")
        set @Code__c = field(@row,"Code__c")
        
        IF @Price__c == "100000" THEN /*replace this with your actually intended logic */
        */
        ]%%
<!-- Set of 2 columns ....-->

and this right below the excerpt:

<!-- ... End of two set columns -->
        %%[
        ENDIF
      next @i ]%%
    
    %%[ else ]%%
    
    No rows found
    
    %%[ endif ]%%  

hope this helps.

| improve this answer | |
1

For complex lookup needs, I would highly recommend using SSJS (Core functions or WSProxy) as they offer a ton of filtering options. Feel free to browse my blog series on using SSJS if you are new to it.

For Core functions, you are likely looking at Rows.Retrieve, where you can create a complex filter to meet your needs with between, greater than, less than, etc.

Sample:

Platform.Load("Core","1.1.1");
var birthdayDE = DataExtension.Init("birthdayDE");
var complexfilter = {
                LeftOperand:{
                    Property:"Age",
                    SimpleOperator:"greaterThan",
                    Value:20
                },
                LogicalOperator:"AND",
                RightOperand:{
                    Property:"FirstName",
                    SimpleOperator:"equals",
                    Value:"Angel"
        }};
var moredata = birthdayDE.Rows.Retrieve(complexfilter);

This would then return an array of your results that you could iterate through via a for loop and utilize it much like in AMPScript.

example return:

[
    {
        "FirstName":"Angel",
        "LastName":"Angel",
        "EmailAddress":"aruiz@example.com",
        "Age":"25",
        "Birthday":"11/29/1985 12:00:00 AM"
    }
]

example iterative for loop

for (i=1;i<moredata.length;i++) {
  var fName = moredata[i].FirstName;
  Write('<p>' + fName + '</p>');
}

The other option is WSProxy with a complex filter. This allows you to use all the SOAP filtering capabilities in a fairly easy to use and efficient manner.

Sample:

var prox = new Script.Util.WSProxy();
var cols = ["Name","CustomerKey","CategoryID","IsSendable"];
var filter = {
    LeftOperand: {
        Property: "CustomerKey",
        SimpleOperator: "equals",
        Value: "ArtistsDE"
    },
    LogicalOperator: "OR",
    RightOperand: {
        Property: "Name",
        SimpleOperator: "equals",
        Value: "LikeCounter"
    }
};
var desc = prox.retrieve("DataExtension", cols, filter);

You would just need to adjust it to pull off the correct object (DataExtensionObject) and the correct fields/columns you want returned.

This combined with the solutions @JonasLamberty, @AdamSpriggs and @Macca have provided should get you to the right one for you.

Honestly, if possible, I would highly recommend the SQL solution as preparing your data prior to the send is the most efficient and when it comes to data interaction, SQL Queries are the best option.

| improve this answer | |
  • after looking at the options, and getting Jonas' solution to work, it does look like preparing the data through SQL would be the most flexible. I'm dealing with a Contact DE and an Apartments DE, so may I ask what the process is to combine multiple Apartment options into one joint ContactApartment DE? Would I calculate it within one SQL query or are there multiple SQL queries that I need to use Automation Studio to process? – Greg Beazley Sep 22 at 17:09
  • @GregBeazley If I understand what you are asking, you might want to check out this blog post I did recently on creating a delimited list out of SQL rows in a 1:Many relationship: gortonington.com/sfmc-sql-data-into-a-delimited-string – Gortonington Sep 22 at 17:40
  • not quite, but that's a useful post for future reference :) What I've done so far is created a DE that includes the Contact info plus columns for Apartment Suggestion 1 and Apartment Suggestion 2, etc. I have one SQL that populates the DE with Contact info plus Apartment Suggestion 1, then another SQL that updates the DE with Apartment Suggestion 2, and another SQL for Apartment Suggestion 3. This works from my tests, but I'm getting a Violation of Primary Key constraint issue, which I'm stuck on. Again, I don't know if this is the right way to achieve what I want, but it works. – Greg Beazley Sep 25 at 16:16

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.