Datetime issue

[mdj3366]

Hi there,
I am using CR 11, SQL database.I have a report that is used to invoice customers on a monthly basis. It is capturing all payments made within a specific time period using the payment.update(datetime) field. My problem is this: say the report is run 5/28/10 at 1700 for the month of May 2010. It is possible that the payment.update(datetime) field would update for someone who recorded an entry on 5/29/10, however since the report was run on 5/28/10 this entry would not bill until June 2010. Is there a way that I can capture within a formula the 5/28/10 1700 datetime the report was run as the start datetime + 1 minute for the next month?

Thanks!

 

[Madawc]

You can do comparison tests on date and time, defining a date and time, e.g.

DateTimeValue (YYYY, MM, DD, HH, MM, SS)

You could also do it by parameters, entering the date and time of the last run.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

If you run the report at a standard point each month, e.g., the last Friday of the month at 5pm, you could use a selection formula that reflected that. If the report run datetime can vary, then no, not unless you record this as a field in the database.

-LB

 

[mdj3366]

Thanks LB. The payment.update field does capture the datetime the payment was reconciled (marked paid). Please see my example below.

Payment Date Amount Status Update datetime
03/05/2010 1,400.00 Paid 3/15/2010 4:29:24 PM
03/12/2010 1,750.00 Paid 3/15/2010 4:29:24 PM

03/19/2010 1,750.00 Paid 3/31/2010 3:55:06 PM
03/26/2010 1,750.00 Paid 3/31/2010 3:55:06 PM
04/02/2010 1,750.00 Paid 3/31/2010 3:55:06 PM March invoice would include both 3/15 and 3/31 updates

04/09/2010 1,750.00 Paid 4/15/2010 3:37:22 PM

04/16/2010 1,750.00 Paid 4/30/2010 5:09:32 PM
04/23/2010 1,750.00 Paid 4/30/2010 5:09:32 PM
04/30/2010 1,750.00 Paid 4/30/2010 5:09:32 PM April invoice would include both 4/15 and 4/30 updates

05/07/2010 1,750.00 Paid 5/14/2010 4:27:11 PM
05/14/2010 1,750.00 Paid 5/14/2010 4:27:11 PM

05/21/2010 1,750.00 Paid 5/28/2010 2:49:17 PM
05/28/2010 350.00 Paid 5/28/2010 2:49:17 PM May invoice would include both 5/14 and 5/28 updates

06/04/2010 986.46 Eligible 5/31/2010 11:03:57 AM the updatedatetime on this is the datetime the payment was activated. since this payment has not been reconciled, it will not bill until the June invoice.

So when i run the invoice for June, i want the begin date to be 5/28/2010 2:50:00 PM (the last time payments were reconciled plus 1 minute).

 

[lbass]

But when you run the report for June, the "Eligible" field will say "Paid" for the 6/4 payment, won't it? So you won't be able to tell in retrospect what the last run/reconciled date is. You need to be able to identify the logic for when the report is run each month.

-LB

 

[mdj3366]

The report is usually run on the last business day of the month. Since May 31 was a holiday, the report ran on 4/28.

 

[lbass]

Create a formula like this where you first add holidays into an array (from Ken Hamady)--note that some you will have to update each year, as for Memorial Day.

//{@lastbusdayofmo}:
BeforeReadingRecords;
DateVar Array Holidays := [
Date (year({?date}),7,4),
Date (year({?date}),12,25),
Date (year({?date}),12,31),
Date (year({?date}),1,1),
date(2010,5,31)//update each year
];
datevar maxmodt := dateserial(year({?date}),month({?date})+1,1)-1;
datevar maxbusdt;
if dayofweek(maxmodt) in 2 to 6 and
not(maxmodt in Holidays) then
maxbusdt := maxmodt else
maxbusdt := maxmodt-1;
if dayofweek(maxbusdt) = 1 then
maxbusdt := maxbusdt-2 else
if dayofweek(maxbusdt) = 7 then
maxbusdt := maxbusdt-1;
maxbusdt;

Then try a record selection formula like this:

{table.updatedatetime} > datetime({@lastbusdayofmo},time(17,0,0)

Not sure this will work with every case, but it should get you close.

-LB

 

[mdj3366]

Hi LB,

I have decided to go another route, but thanks for the suggestions and your time!

 

[lbass]

Please explain the route you decide to go, so others can benefit.

-LB