Creating an Overdue Formula

[mnovak3309]

I'm working on customizing an existing crystal report that was created for Sage 300 CRE. The report tracks the current RFI's for the project.

The input parameters that I'm pulling from are the required date, returned date, and print date.

Ultimately, I want the "Days Overdue" column to spit out the difference between the print date and required date if the print date is past the required date and the returned date isn't inputted. If the required date hasn't been reached yet or if the RFI has been returned then the formula can report "0".

Example:
RFI Required Date = 2/1/16
RFI Returned Date = blank (nothing entered because it hasn't been returned)
Report Print Date = 2/10/16
So the days overdue should be 9.

I currently have an extremely base formula as follows but I get an error with the second row.

IF ({PJD_DOCUMENT__RFI.Required_Date} < PrintDate
AND {PJD_DOCUMENT__RFI.Returned_Date} = False)
Then PrintDate - {PJD_DOCUMENT__RFI.Required_Date}
Else "0"

Any advice would be greatly appreciated!

 

[pmax9999]

Something like this should do the trick:

IF	(
		Isnull({PJD_DOCUMENT__RFI.Returned_Date}) and
		{PJD_DOCUMENT__RFI.Required_Date} < PrintDate
	)
THEN 	PrintDate - {PJD_DOCUMENT__RFI.Required_Date}

Hope this helps.

Cheers
Pete

 

[mnovak3309]

Awesome, thanks for the help Pete! It looks like that fixed the formula for the overdue items. I think it still needs some tweaking though.

1. Right now the output is carrying 2 decimal places, for example, the overdue would read 10.00.
Is there any way to drop to decimal places?

2. For items that aren't overdue, is there a way for the formula to leave this space blank as opposed to spitting out 0.00?

Thanks in advance!

*EDIT: Figured it out- just added totext in front of the output formula and entered the appropriate syntax. Thanks again for your help Pete!*

 

[pmax9999]

Glad it helped.

Rather than converting the result to text, I'd recommend you use formatting options to achieve the result you want. The benefit of this approach is that, as it is still a number rather than text, calculations (sum, average etc) can still be performed if needed.

Right click on the formula where it appears on the report canvas, click on Format Field, then:
[ol 1]
[li]click on the Number tab;[/li]
[li]click on the Customize button;[/li]
[li]select the Decimals you want; and[/li]
[li]place a tick in the Suppress if Zero box.[/li]
[/ol]



Cheers
Pete

ps: Welcome to Tek-Tips, I'm sure you will find it an extremely valuable resource. As a new Tek-Tips user, I'd recommend you take a look at the Posting Guidelines if you haven't already: Link