This topic contains 14 replies, has 0 voices, and was last updated by mwhite@hgyp.com 6 years, 11 months ago.

  • Author
    Posts
  • #23124 Score: 0

    v mithula sri
    • Contributions: 0
    • Level 1

    Hi Everyone,

    I have a requirement like depending on customers birth day date we send them gift coupon and that coupon is valid till next month

    so in order to do this we need those customers in a search. For example, i am a customer today is my Birthday November 20 so I should be present in the search till December 20. How can i apply this in saved searches by using formulas,Can anyone help what formula we need to use.

    Thanks!
    This is a cached copy. Click here to see the original post.

  • #23125 Score: 0

    j.j
    • Contributions: 0
    • Level 1

    create a saved search on customer – in filters select birthdate on today – this will list all customers having birthday on any day (variable) e.g. on nov 20 it will list all customers with birthday on nov 20 and the next day on nov 21 will show all with birthdate on nov 21

  • #23126 Score: 0

    v mithula sri
    • Contributions: 0
    • Level 1

    Thanks for your quick response J.J. But my requirement is like Need to pull all customers and they should be removed automatically after a month from his birth date.

  • #23127 Score: 0

    mwhite@hgyp.com
    • Contributions: 0
    • Level 1

    You should be able to use a relative date filter. Something like this:

  • #23128 Score: 0

    v mithula sri
    • Contributions: 0
    • Level 1

    Thanks for your reply mwhite@hgyp.com! It's working fine. But it won't work for past data i mean like if that customer birth data is like 20-nov-1998. Need to filter out through date and month. Can you guys help using custom formulas using (functions as decode and some other).

    Thanks!

  • #23129 Score: 0

    mwhite@hgyp.com
    • Contributions: 0
    • Level 1

    Duh! Sheesh. Can't believe I missed that. There may be something more simple, but this should do it:

    CASE WHEN TRUNC({today} – to_date(TO_CHAR({birthdayField},'MM/DD') || '/' || TO_CHAR({today},'YYYY'))) BETWEEN 0 AND 30 THEN 1 END

    This part is basically creates their birthday on the current year:

    to_date(TO_CHAR({birthdayField},'MM/DD') || '/' || TO_CHAR({today},'YYYY'))

  • #23130 Score: 0

    v mithula sri
    • Contributions: 0
    • Level 1

    Thanks mwhite!. It contains syntax error, Could please check it once

  • #23131 Score: 0

    v mithula sri
    • Contributions: 0
    • Level 1

    Thanks mwhite!. It contains syntax error, Could you please check it once

  • #23132 Score: 0

    v mithula sri
    • Contributions: 0
    • Level 1

    Hi,

    Can anyone suggest like which formula need to use for my requirement. For example i have a field called "BirthDate" in customer record (21-nov-1990). While building a search based on "birthdate", we should mention criteria like that customer has to be in search Results till (21-dec).

    Thanks!

  • #23133 Score: 0

    mwhite@hgyp.com
    • Contributions: 0
    • Level 1

    You'll want to replace {birthdayField} with whatever field you're using.

  • #23134 Score: 0

    v mithula sri
    • Contributions: 0
    • Level 1

    Yeah i have replaced that field over their (formula you have assigned). Even it's getting error like syntax error

  • #23135 Score: 0

    v mithula sri
    • Contributions: 0
    • Level 1

    We need to pass this as criteria rite?. In saved search by using formula field, we place formula in formula field and that condition we need to take?

  • #23136 Score: 0

    mwhite@hgyp.com
    • Contributions: 0
    • Level 1

    Yes. Formula (numeric) and value equal to 1

  • #23137 Score: 0

    v mithula sri
    • Contributions: 0
    • Level 1

    it's getting like this,

    Attached Files

  • #23138 Score: 0

    mwhite@hgyp.com
    • Contributions: 0
    • Level 1

    Let me test

You must be logged in to reply to this topic.