0

I want to loop through a DE that contains the attributes of a family, to retrieve the name and birthday of the family member who's birthday is the closest to NOW() AND is within the next 90 days.

I have below code so far. But struggling to figure out the logic finish the script.

SET @familyId = [familyId]
SET @cancellationDate = ''
SET @familyMember = LookupOrderedRows('Family',4,'dob Desc','familyId',@familyId, 'contactType', 'familyMember', 'cancellationDate', @cancellationDate)
SET @count = RowCount(@familyMember)

FOR @i = 1 TO @count DO 
   IF @break != 'True' THEN
        SET @dob = Field(Row(@familyMember, @i), "dob")
        SET @familyMemberId = Field(Row(@familyMember, @i), "userId")
        SET @dateDiffDay = DateDiff(DatePart(@dob,"D"),DatePart(NOW(),"D"),"D")
            IF @dateDiffDay < 90 THEN       
                SET @break = 'True'
            ENDIF
  ENDIF
NEXT @i

SET @familyMemberName = ProperCase(Lookup('Family','firstName','userId',@familyMemberId))

]%%

%%=v(@familyMemberName)=%%

Any help would be great. Thanks!

4
  • What is the error you are getting? Nov 24 at 12:35
  • @Swati Mishra I'm not getting an error message as such, but my logic is incorrect as the dob retrieved isn't in the next 90 days, or the closest to NOW()
    – MJB
    Nov 24 at 12:44
  • Why do you do datepart? You could just directly use datediff. Be mindful that the NOW() is in UTC -6 Nov 24 at 12:47
  • @SwatiMishra If do DateDiff directly, it will be the DateDiff between Today and the DOB i.e MM/DD/YYYY rather than the Birthday in 2021 i.e MM/DD
    – MJB
    Nov 24 at 12:51
2

To use a metaphor, you are using a Hammer for driving in a screw. While sure this can be solved in pure AMPScript, it will get pretty ugly. The real recommendation is split your process in two parts: 1 = pre-process your data with SQL and work with a better data extension, simplifying step 2 = what's left in AMPscript.

So really, you have an SQL question hidden underneath your AMPScript question :)

Why: You are attempting to do something in real-time and for each individual email you send out, which can be easily done once a day, in an asynchronous automation. Birthdays logics are really a prime example for this. Birthdays don't change IN REAL TIME at time of send, so that the message needs to calculate the latest info; that's actually rather inefficient.

Using SQL would take load off the sendout processing, has the right methods for the job and is ultimately easier to read and maintain, as it's outside of the message; it would only need to run once daily.

Your main problem is that you cannot use a lookuprows method with a date range, only with "equals" operator.

This is a non-issue in SQL:

Consider all of the following pseudocode (I can confirm it does not work, and you don't seem to have issues with syntax), just to illustrate the approach:

Build a DE "familyMembers_BirthdayWithin90d" that also includes a number field for daysToBirthday and your relevant info on the familyMember.

SELECT FROM family only those family Members with birthday within next 90 days, and calculate & write down the DateDiff between today and their birthday data extension as "daysToBirthday" for sorting, write into that second DE.

Also, in the second DE, put in a dummy field ("include") that is always "1", so you can just get all rows from the DE with LookupOrderedRows function (assuming you don't get more than 2500 records into it per day, which is the lookupOrderedRows limit).

/* pseudo pseudo pseudo, does not work */
SELECT 
userId
,familyMemberName
,DateDiff(GetDate((), dob) AS daysToBirthday
,'1' as include

FROM
Family
WHERE DateDiff(GetDate(),dob) <= 90

/* overwrite targetDE: familyMembers_BirthdayWithin90d */

familyMembers_BirthdayWithin90d is already nicely filtered for the right familyMembers, all that remains is to sort them and include an empty check, or your code will err out if nobody has birthday soon:

This remains in AMPScript, code should be functional but not 100% your usecase.

Select the top one record, after sorting ascending by daysToBirthday (= lowest number of days to birthday wins). If you find one, get its fields:

/* pseudo pseudo pseudo, does not work */
SET @preProcessed = "familyMembers_BirthdayWithin90d"
LookupOrderedRows(@preProcessed,1,"daysToBirthday ASC","include","1")
SET @count = RowCount(@familyMember)

IF @count > 0 THEN
    FOR @i = 1 TO @count DO 
        SET @dob = Field(Row(@preProcessed, @i), "dob")
        SET @familyMemberId = Field(Row(@preProcessed, @i), "userId")
    NEXT @i
ENDIF

2
  • Awesome write up @Jonas Lamberty. Thank you! I'll be implementing your solution this week. Can you expand on: "Also, in the second DE, put in a dummy field ("include") that is always "1", so you can just get all rows from the DE with LookupOrderedRows function " You would include '1' as a static value for all records in the 2nd DE?
    – MJB
    Nov 30 at 15:41
  • yes, otherwise you have nothing to write a lookupRows function on; lookuprows can only find records where something equals something. The function has a built in limit of 2500 at once, but within this limit, you can just create an arbitrary field that all rows have in common; such as "include = 1" Nov 30 at 16:00

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.