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.