Select/If then Help 1

[Chota]

I am working on an aging report and I am new to crystal. Here is the criteral that I have to meet.

1) RM00401.RMDTYPAL if this equals 7 or 9 and it falls in the 0 to 30 day range then the amount in CURTRXAM needs to be displayed as a negative in the report.
2) RM00401.RMDTYPAL if this is not equal to 7 or 9 and it falls in the 0 to 30 day range then the amount in CURTRXAM just needs to be displayed in the report.
3) If it does not meet the date requirement then I want it to return a zero.

Here is the formula I started to work with but I run in to errors, any help?

Select Case {RM00401.RMDTYPAL}
    Case Is = 7 or 9 and {RM20101.DOCDATE} in Aged0To30Days
    Then {RM20101.CURTRXAM} * -1
    Case Is  <> 7 or 9 and {RM20101.DOCDATE} in Aged0To30Days
    Then {RM20101.CURTRXAM}
    Else 0
End Select

 

[lbass]

If you don't need the records from outside the range, then you could use a record selection formula (report->edit selection formula->record) of:

{RM20101.DOCDATE} in Aged0To30Days

Then go to the formula editor and create a formula:

if {RM00401.RMDTYPAL} in [7,9] then -{RM20101.CURTRXAM} else
{RM20101.CURTRXAM}

If you need to keep records in that are outside of the range, then create a formula:

if {RM20101.DOCDATE} in Aged0To30Days then
(
if {RM00401.RMDTYPAL} in [7,9] then
-{RM20101.CURTRXAM} else
{RM20101.CURTRXAM}
) else
0

-LB

 

[Chota]

That did not seem to work out, here was the basic code that I started with that gave me the results that I wanted minus the fact it shows all numbers as an absolute value. I really just need to find a way to add in an addition check against {RM00401.RMDTYPAL} to see if it is a 7 or 9 then I can do a * -1 to make the {RM20101.CURTRXAM} a negative number.

If {RM20101.DOCDATE} in Aged0To30Days
Then {RM20101.CURTRXAM}
Else 0

 

[lbass]

Both my formulas check out here, and make the amount negative if it meets the criteria (7 or 9) and positive otherwise (or zero if outside the range). Are you sure you implemented them as suggested?

Instead of saying something doesn't work out, it is better to report back the results you get and explain how they are different from what you are looking for. Then additional posts can build on that information.

-LB

 

[Chota]

Here is the breakdown of what I am doing. I run an aging report based on all customers or just one customer. Here is what I get when I use the basic code to so 0/30/60/90plus days for aging.

Here is an example output of the report with out checking for the 7 or 9:

http://www.mojowares.com/example.html

now what I am trying to do with the 7 or 9 check is to make a credit “7” or payment “9” so as a negative. The line item with CREDT000000000012 should be a negative since it has a 7

Now when I replaced the code with the 2nd example I got multiple listings for each document number (1st column), I think it is called a Cartesian something.

 

[lbass]

I appreciate your attempt to provide an actual sample, but it has no information in it that relates to what you are saying. Whatever field has the 7 or 9 doesn't appear, and I don't know what the other columns are for. I also do not know what you mean by the basic formula. I guess you mean by "the second example" my second suggestion. The formula should not cause multiple entries, unless using the formula, is the first time you have used fields from the second table. So, what actual formulas have you used?

-LB

 

[Chota]

Does this help you at all? This is the output from the definition file.

	Crystal Report Professional v10.0 (32-bit) - Report Definition

1.0 File Information

	Report File: 
	Version: 10.2

2.0 Record Sort Fields

3.0 Group Sort Fields

4.0 Formulas

4.1 Record Selection Formula
	

4.2 Group Selection Formula
	

4.3 Other Formulas

	Name: {@0-30 Days}
	Formula: If {RM20101.DOCDATE} in Aged0To30Days
	Then {RM20101.CURTRXAM}
	else 0
	

	Name: {@31-60 Days}
	Formula: If {RM20101.DOCDATE} in Aged31To60Days
	Then{RM20101.CURTRXAM}
	Else 0

	Name: {@61-90 Days}
	Formula: If {RM20101.DOCDATE} in Aged61To90Days
	Then{RM20101.CURTRXAM}
	Else 0

	Name: {@Over 90 Days}
	Formula: If {RM20101.DOCDATE} in Over90Days
	Then{RM20101.CURTRXAM}
	Else 0

5.0 Sectional Information

5.1 Page Header Section
	Visible, Keep Together

	Document No.
		String, Visible, Left Alignment, Top Alignment, 
		Keep Together



	Document Type
		String, Visible, Left Alignment, Top Alignment, 
		Keep Together



	Document Date
		String, Visible, Left Alignment, Top Alignment, 
		Keep Together



	Due Date
		String, Visible, Left Alignment, Top Alignment, 
		Keep Together



	0-30 Days
		String, Visible, Right Alignment, Top Alignment, 
		Keep Together



	31-60 Days
		String, Visible, Right Alignment, Top Alignment, 
		Keep Together



	61-90 Days
		String, Visible, Right Alignment, Top Alignment, 
		Keep Together



	Over 90 Days
		String, Visible, Right Alignment, Top Alignment, 
		Keep Together



5.2 Page Footer Section
	Visible, New Page After, Keep Together, Print At Bottom of Page

	PageNofM
		String, Visible, Default Alignment, Top Alignment, 
		Keep Together, Using System Default Formatting, Word Wrap


5.3 Report Header Section
	Visible, New Page Before

	Accounts Receivable Aging Report

		String, Visible, Horizontal Centre Alignment, Top Alignment, 
		Keep Together


	Accounts Receivable Aging Report

		String, Visible, Horizontal Centre Alignment, Top Alignment, 
		Keep Together


	Accounts Receivable Aging Report
		String, Visible, Horizontal Centre Alignment, Top Alignment, 
		Keep Together



	MyCompany, Inc.
		String, Visible, Horizontal Centre Alignment, Top Alignment, 
		Keep Together



	DataDate
		Date, Visible, Default Alignment, Top Alignment, 
		Keep Together, Using System Default Formatting, Windows Default Type: Use Windows Short Date, Date Order: Month Day Year, Year Type: Long, Month Type: Numeric Month, Day Type: Numeric Day, Leading Day Type: None, First Separator: '/', Second Separator: '/', Leading Day Separator: ''


5.4 Report Footer Section
	Visible, New Page After

	Maximum ({@0-30 Days})
		Numeric, Visible, Default Alignment, Top Alignment, 
		Keep Together, Using System Default Formatting, 
		Leading Minus, 2 Decimal Places, Rounding: 0.01, 
		Thousands Symbol: ',', Decimal Symbol: '.'


	Maximum ({@31-60 Days})
		Numeric, Visible, Default Alignment, Top Alignment, 
		Keep Together, Using System Default Formatting, 
		Leading Minus, 2 Decimal Places, Rounding: 0.01, 
		Thousands Symbol: ',', Decimal Symbol: '.'


	Maximum ({@61-90 Days})
		Numeric, Visible, Default Alignment, Top Alignment, 
		Keep Together, Using System Default Formatting, 
		Leading Minus, 2 Decimal Places, Rounding: 0.01, 
		Thousands Symbol: ',', Decimal Symbol: '.'


	Maximum ({@Over 90 Days})
		Numeric, Visible, Default Alignment, Top Alignment, 
		Keep Together, Using System Default Formatting, 
		Leading Minus, 2 Decimal Places, Rounding: 0.01, 
		Thousands Symbol: ',', Decimal Symbol: '.'


5.5 Group Header Section #1
	Visible

	GroupName ({RM00101.CUSTNMBR})
		String, Visible, Default Alignment, Top Alignment, 
		Keep Together, Using System Default Formatting, Word Wrap


5.6 Group Footer Section #1
	Visible

	Maximum ({@0-30 Days}, {RM00101.CUSTNMBR})
		Numeric, Visible, Default Alignment, Top Alignment, 
		Keep Together, Using System Default Formatting, 
		Leading Minus, 2 Decimal Places, Rounding: 0.01, 
		Thousands Symbol: ',', Decimal Symbol: '.'


	Maximum ({@31-60 Days}, {RM00101.CUSTNMBR})
		Numeric, Visible, Default Alignment, Top Alignment, 
		Keep Together, Using System Default Formatting, 
		Leading Minus, 2 Decimal Places, Rounding: 0.01, 
		Thousands Symbol: ',', Decimal Symbol: '.'


	Maximum ({@61-90 Days}, {RM00101.CUSTNMBR})
		Numeric, Visible, Default Alignment, Top Alignment, 
		Keep Together, Using System Default Formatting, 
		Leading Minus, 2 Decimal Places, Rounding: 0.01, 
		Thousands Symbol: ',', Decimal Symbol: '.'


	Maximum ({@Over 90 Days}, {RM00101.CUSTNMBR})
		Numeric, Visible, Default Alignment, Top Alignment, 
		Keep Together, Using System Default Formatting, 
		Leading Minus, 2 Decimal Places, Rounding: 0.01, 
		Thousands Symbol: ',', Decimal Symbol: '.'


	SubTotal

		String, Visible, Horizontal Centre Alignment, Top Alignment, 
		Keep Together


	
		String, Visible, Horizontal Centre Alignment, Top Alignment, 
		Keep Together



5.7 Group Header Section #2
	Visible

	GroupName ({RM00101.CUSTNAME})
		String, Visible, Default Alignment, Top Alignment, 
		Keep Together, Using System Default Formatting, Word Wrap


5.8 Group Footer Section #2
	Hidden

5.9 Details Section
	Visible

	Subsection.1 
		Visible, Keep Together

	{RM20101.SLPRSNID}
		String, Visible, Default Alignment, Top Alignment, 
		Keep Together, Using System Default Formatting, Word Wrap


	{RM20101.DOCDATE}
		Date Time, Visible, Default Alignment, Top Alignment, 
		Keep Together, Date Time Order: Date Only, Separator: '  '


	{RM20101.DUEDATE}
		Date Time, Visible, Default Alignment, Top Alignment, 
		Keep Together, Date Time Order: Date Only, Separator: '  '


	{RM20101.DOCNUMBR}
		String, Visible, Default Alignment, Top Alignment, 
		Keep Together, Using System Default Formatting, Word Wrap


	{@0-30 Days}
		Numeric, Visible, Default Alignment, Top Alignment, 
		Keep Together, Using System Default Formatting, 
		Leading Minus, 2 Decimal Places, Rounding: 0.01, 
		Thousands Symbol: ',', Decimal Symbol: '.'


	{@31-60 Days}
		Numeric, Visible, Default Alignment, Top Alignment, 
		Keep Together, Using System Default Formatting, 
		Leading Minus, 2 Decimal Places, Rounding: 0.01, 
		Thousands Symbol: ',', Decimal Symbol: '.'


	{@61-90 Days}
		Numeric, Visible, Default Alignment, Top Alignment, 
		Keep Together, Using System Default Formatting, 
		Leading Minus, 2 Decimal Places, Rounding: 0.01, 
		Thousands Symbol: ',', Decimal Symbol: '.'


	{@Over 90 Days}
		Numeric, Visible, Default Alignment, Top Alignment, 
		Keep Together, Using System Default Formatting, 
		Leading Minus, 2 Decimal Places, Rounding: 0.01, 
		Thousands Symbol: ',', Decimal Symbol: '.'

 

[lbass]

I can see that you are not using my suggestion. You should be able to use:

if {RM20101.DOCDATE} in Aged0To30Days then
(
if {RM00401.RMDTYPAL} in [7,9] then
-{RM20101.CURTRXAM} else
{RM20101.CURTRXAM}
) else
0

You could then adapt this to your other age ranges. I see no reason why this wouldn't work.

-LB

 

[Chota]

LB,
Ok yes your code works after I find the right table to pull from. I cna not use {RM00401.RMDTYPAL} I have to use {RM20101.RMDTYPAL} otherwise I get a couple of thousand recoreds returned. So it was bad info on my part there. Thanks for your help!

 

[Chota]

Ok, new problem/challenge I am working on with this same formula. I have no been asked if I can setup the report up with a date parameter. I know how to do some basic parameters but I can not see how to get it to work with this formula. It seems the built in aging function that crystal has runs off the current date/time. Now in reading the help file it said I can click on Reports>Set Date and Time so reports can be based on different dates. Is there a way to make {?date} parameter set that with this same formula?

 

[lbass]

I wouldn't change the system date. Can you explain more about what the parameter is supposed to do? Do they want to look at 30-day ranges from a particular parameter date? If so, then change your formulas to something like:

//{@aged0to30):
if {RM20101.DOCDATE} in dateadd("d",-29,{?date}) to {?date} then
(
if {RM20101.RMDTYPAL} in [7,9] then
-{RM20101.CURTRXAM} else
{RM20101.CURTRXAM}
) else
0

//{@aged31to60}:
if {RM20101.DOCDATE} in dateadd("d",-59,{?date}) to
dateadd("d",-30,{?date}) then
(
if {RM20101.RMDTYPAL} in [7,9] then
-{RM20101.CURTRXAM} else
{RM20101.CURTRXAM}
) else
0

etc.

-LB

 

[Chota]

Ok, thanks – I was working on something similar to this and I see what I was missing now. It was not the system date I was looking to change but in Crystal (ver10) If you click on reports menu> set print date and time. The help menu said when you use aged0to30days it will directly effect the starting date for the calculation. I was hoping to find a way to prompt for that, but doing your way works also. I would be interested to see if it can be done that way still if you have any ideas.

Thanks again -

 

[Chota]

OOo sorry I kind of missed the point there. Since these formals are populating an finical aging report and they would like to run historical aging reports so that is what the date parameter will be dictating, Aging as of {?date}

 

[lbass]

I can't help you with that. Not sure how you would do it, since there is no conditional formula area for set date/time. Someone else may be able to help...

-LB

 

[Chota]

Ok, thanks for all you have done anyways, lets see if someone knows if a way exsit to set the date/time off a prompt.