Formula to List Customer Call Schedule

[jaz237]

This seems so simple it's driving me crazy! I need a report to detail a call schedule around the customers join date. I have the actual join date and the anniversary month so either fields could be used.

I need to call three times annally. Once the month before the anniversary and then three months later and then three months after that.

I've worked with a few if/then formulas but I haven't gotten what I need.

Here is an example of the data:

Customer AnnivDate AnnivMonth
Abbie's Taylor 04/01/1986 4
Julie's Lounge 03/12/2002 3
Rocket Stars 12/05/1980 12
Oklahoma Diner 09/25/2007 9
Texas Two-step 05/01/2000 5

And the output should be as follows:

January
Texas Two-step, Contact, Phone, Email

February
Julie's Lounge Contact, Phone, Email
Rocket Stars, Contact, Phone, Email

March
Abbie's Taylor, Contact, Phone, Email

April
Texas Two-step, Contact, Phone, Email

May
Julie's Lounge Contact, Phone, Email
Rocket Stars, Contact, Phone, Email

June
Abbie's Taylor, Contact, Phone, Email

July
Texas Two-step, Contact, Phone, Email

August
Oklahoma Diner, Contact, Phone, Email
Julie's Lounge Contact, Phone, Email

September
Abbie's Taylor, Contact, Phone, Email

October
Texas Two-step, Contact, Phone, Email

November
Rocket Stars, Contact, Phone, Email

I am using CRXI and SQL database. If I've left anything out please let me know. Any and all help is appreciated.

 

[MrBillSC]

The problem is that you want to report a single record in three different locations of your report. I don't think you can do this within Crystal Reports. You will need a way to create 3 records for each customer, one for each month they are to be reported.

If you tell us what type of data base you have, someone may be able to guide you in getting 3 records in a file.

MrBill

 

[lbass]

Is "AnnivMonth" an actual number field? If so, you could create a command like this:

select table.`AnnivMonth`-1 as ContactMonth, table.`customer`,table.`contact`, table.`phone`, table.`email`
From table
Union all
select table.`AnnivMonth`+2, table.`customer`,table.`contact`, table.`phone`, table.`email`
From table
Union all
select table.`AnnivMonth`+5, table.`customer`,table.`contact`, table.`phone`, table.`email`
From table
Union all
select table.`AnnivMonth`+8, table.`customer`,table.`contact`, table.`phone`, table.`email`
From table

Then you should be able to group on {command.contactmonth} and get the desired output. The punctuation/syntax in the command will be specific to your datasource.

-LB

 

[jaz237]

Thanks lbass...I got that to work.

 

[jaz237]

I just applied the group and since the formula is based off a number I get 0 to 17 rather than the actual months...December is 0 and then 12 + 5 = 17. And since I'm adding three and five to the anniversary month then the numbers over 12 could represent different months. Do you see any way around this? Thank you so much for your help.

 

[lbass]

I think you could group on this {@month}:

if {command.ContactMonth} > 12 then
{command.ContactMonth} - 12 else
{command.ContactMonth}

To show the monthname for the group header, in the group expert->options->customize groupname->use a formula:

monthname({@month})

-LB

 

[jaz237]

LB -

Thank you so much for your help with this. The 0 throws everything else off. Do you know how I can make the 0 equal 12? The group header won't work unless the numbers are exactly 1 through 12.

JAZ

 

[lbass]

if {command.ContactMonth} = 0 then 12 else
if {command.ContactMonth} > 12 then
{command.ContactMonth} - 12 else
{command.ContactMonth}

-LB

 

[jaz237]

LB -

Fantastic! That was just what I needed. Thank you so much for help with this!

JAZ