Expand my Community achievements bar.

SOLVED

ACClassic v6.1 - Coalescing "birthday fields" into one delivery.

Avatar

Level 3

Hi everyone, I asked this a couple months back so I'm re-writing in the hopes someone can help with this.

Our company have a bespoke table that holds up to 10 friends/family members' names and birthdays:

1525869_pastedImage_0.png

And I've created a birthday journey for all of them that will send an email to them if they met my criteria (in this case their birthday is in 14 days). Problem is it looks like this:

1525843_pastedImage_27.png

Ew. Right? It's very hard to read and if one of these fails for any reason the rest won't get sent.

The dream would be to send one email that looks at all those records and pulls out the family member and birthday that meets my criteria (i.e. birthday is in 14 days) then enrich the email with that guests' personalized information (i.e. their name, their age, etc.) in a workflow that looks like this:

1525868_pastedImage_29.png

Mmm. Right? This is my single birthday journey.

Can anyone think of a way of writing this so a single delivery can be personalized from enriched data across multiple data fields?

1525870_pastedImage_1.png

Any help will be greatly appreciated.

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hello kjac.kson ,

first of all the query can look something like DateOnly(bdate) == DateOnly(current day +14 days).

And problem to have everything duplicit you can hopefully solve by using loop in your workflow. It can eg look like:

Use all 10 family bday fields in one query activity but also use field "Taken into account if". where you will basically with each loop turn differrent condition on. So that Loop No 1 will only take into account bday1 Loop No 2 will take into account bday2 and si on... until you loop all 10.

PS:

Both scebarios are not concatenating bdays so when 2 or more members fall into the selection 2 or more emails are sent to the customer.

But in case you are going to send more emails to one customer

Marcel

View solution in original post

3 Replies

Avatar

Correct answer by
Community Advisor

Hello kjac.kson ,

first of all the query can look something like DateOnly(bdate) == DateOnly(current day +14 days).

And problem to have everything duplicit you can hopefully solve by using loop in your workflow. It can eg look like:

Use all 10 family bday fields in one query activity but also use field "Taken into account if". where you will basically with each loop turn differrent condition on. So that Loop No 1 will only take into account bday1 Loop No 2 will take into account bday2 and si on... until you loop all 10.

PS:

Both scebarios are not concatenating bdays so when 2 or more members fall into the selection 2 or more emails are sent to the customer.

But in case you are going to send more emails to one customer

Marcel

Avatar

Level 3

The function you describe wouldn't work as it doesn't take into account the year.

i.e. DateOnly(today) would look like 13/07/2018 and no-one subscribed would have a birth date in 2018!

The loop function is interesting though...

Avatar

Community Advisor

Hello,

Yes forgot about this little thing.

So i would in enrichment before this check create new field bday_currentYear and checked that as I described previously. Create that new bday field by using following - ToDate(Year(GetDate()) +'-'+Month(bday)+'-'+Day(bday))

MARCEL