Help with a crystal formula 2

[cis12232004]

I am very new to Crystal Reports so I really need some assistance. I have a report that has the amount of supplies ordered and the amount received and of course the difference would be what is outstanding. The problem is that we also order cable at my company and sometimes the EXACT amount of cable ordered is not shipped so what I want is to create a formula that states if the difference between the amount ordered and the amount received is within 10% of the amount ordered (either above or below) then we should consider that order filled and no longer outstanding.

 

[Kebabmeister]

If <delivered> / <ordered> > .9 Then
"Filled"
Else
"Outstanding

 

[cis12232004]

I am very new to Crystal Reports and I have a problem with a query. I want to restrict the results to just two IDs in the report but the where clause does not work in Crystal Reports. Is there something else that I may use?

 

[Madawc]

Try making it a boolian, a formula without an if. Have a formula field called @Filled, with <delivered> / <ordered> > .9 and <delivered> / <ordered> < 1.1. This should return 'True' or 'False' - display it on detail lines to confirm it is working.

Then put it in the selection formula, as @Filled = true or just @Filled.

I am assuming the figures come from just one record or set of linked records, from a single 'row' in Crystal terms. If not, you'd need to do something more complex, involving Group Selection, the option just below Record Section.

Madawc Williams (East Anglia, UK)

 

[Naith]

CIS,

I'm not sure how a boolean helps here - I might be misunderstanding your request - but I think what you want to do is just go to the Report menu -> Selection Criteria -> Record and place an argument based on the following:

{Your.ID} in ["ID1","ID2"]

Remove the quotes if your ID field is numeric.

Hope this helps,

Naith

 

[cis12232004]

Can someone please look at this code and tell me what i'm doing wrong?

if ({PurOrdDet.QtyOrd} > {PurOrdDet.QtyRcvd} and {PurchOrd.Status} <> "X") then
{PurOrdDet.QtyOrd} - {PurOrdDet.QtyRcvd}else if ({PurOrdDet.QtyRcvd} / {PurOrdDet.QtyOrd} >.9) then
{PurOrdDet.InvtID} [30015400,20039900] is 0

 

[dgillz]

What are you trying to accomplish with the following piece of code:

{PurOrdDet.InvtID} [30015400,20039900] is 0



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[cis12232004]

I am trying to restrict the processing of the second if statement to just those two inventory numbers. If they fall within that then they should be 0

 

[cis12232004]

Those 2 inventory IDs represent cable that we use and sometimes the cable comes shorter than what we ordered or longer than what we ordered and the report as it stands now would list the difference as outstanding. Well what we want to do is change that. If the cable is within 10% of the amount ordered, we don't want the difference to show as outstanding. We want the order to show as filled or with nothing outstanding.

 

[Naith]

I thought you wanted to restrict your entire report to whatever was in the "in []" statement, which is drove how I put my initial response.

However, looking at your formula, there are a few syntactical errors which you'll have to iron out before you can use the same approach within the report canvas.

Try something like:

if 
	({PurOrdDet.QtyOrd} > {PurOrdDet.QtyRcvd} 
	and {PurchOrd.Status} <> "X") 
then 
	{PurOrdDet.QtyOrd} - {PurOrdDet.QtyRcvd}
else 
	if 
		({PurOrdDet.QtyRcvd} / {PurOrdDet.QtyOrd} >.9) 
	then	
		if
			{PurOrdDet.InvtID} in [30015400,20039900]
		then	0
		else	whatever

You may have to tweak the specifics, as the likelihood that I've misunderstood your business is pretty high, but for Crystal syntax, the above should be good for you.

Naith

 

[Naith]

In fact, you don't even need the third if.

else
if
({PurOrdDet.QtyRcvd} / {PurOrdDet.QtyOrd} >.9) and
{PurOrdDet.InvtID} in [30015400,20039900]
then 0
else whatever

works fine, and is cleaner.

 

[cis12232004]

Thanks Naith, I actually figured it out yesterday and it worked fine. I appreciate your response and your help though.