Record selection formula and changes to report?

[jlr123]

I have a formula selection parameter that needs some help please. How to rewrite to make it work?

If {IM_SalesHistoryWrk.UDF_SALESPERSON_NO} <> {AR_InvoiceHistoryHeader.UDF_NEW_SALES_REP} then {AR_InvoiceHistoryHeader.UDF_NEW_SALES_REP} else {IM_SalesHistoryWrk.UDF_SALESPERSON_NO} = {?Salesperson No}

Also, does the rest of the report which has formulas with {IM_SalesHistoryWrk.UDF_SALESPERSON_NO} have to be changed?

(example of formula)
if {@SortReportBy} = "W" then
'formula = "Total For Warehouse: " + {IM_SalesHistoryWrk.WarehouseCode} + " " + {IM_SalesHistoryWrk.WarehouseDesc}
formula = "Total for Salesperson: " + {IM_SalesHistoryWrk.UDF_SALESPERSON_NO}
elseif {@SortReportBy} = "P" then
'formula = "Total For Product Line: " + {IM_SalesHistoryWrk.ProductLine} + " " + {IM_SalesHistoryWrk.ProductLineDesc}
formula = "Total for Salesperson: " + {IM_SalesHistoryWrk.UDF_SALESPERSON_NO}
elseif {@SortReportBy} = "V" then
'formula = "Total For Vendor: " + {@PrimaryVendorNumberAndName}
formula = "Total for Salesperson: " + {IM_SalesHistoryWrk.UDF_SALESPERSON_NO}
elseif {@SortReportBy} = "C" then
'formula = "Total For Customer: " + {@CustomerNumberAndName}
formula = "Total for Salesperson: " + {IM_SalesHistoryWrk.UDF_SALESPERSON_NO}
end if

 

[kray4660]

Three things, I would be using a switch statement instead of If's. It will look a little cleaner. Second, you cannot assign a value to a field (first if statement), not sure what you are trying to accomplish. Third, The formula you give as an example does the same thing regardless of the parameter (the lines with the tick marks are comments and not executed).

I do not see the relationship between the first if statement and the formula presented.

 

[jlr123]

First of all, I am trying to be able to have a Salesperson parameter that will choose either UDF Salesperson or UDF New Salesperson based on whether there is a UDF New Salesperson or not. It may not have a New Salesperson. I do not know what a "switch" statement looks like. Can you show me one?

The second if statement is another formula within the report. I only want to know if I would have to change any further reference to UDF Salesperson if the parameter is written to choose either UDF Salesperson or UDF New Salesperson.
thanks.

 

[kray4660]

The switch is for the second formula, very well documented in Crystal Report designer (i.e., Switch({@SortReportBy} = "W" ,"Total for Salesperson: " + {IM_SalesHistoryWrk.UDF_SALESPERSON_NO},{@SortReportBy} = "P", "Total for Salesperson: " + {IM_SalesHistoryWrk.UDF_SALESPERSON_NO}....etc.). I think for the first formula just do this: If {IM_SalesHistoryWrk.UDF_SALESPERSON_NO} <> {AR_InvoiceHistoryHeader.UDF_NEW_SALES_REP} then {AR_InvoiceHistoryHeader.UDF_NEW_SALES_REP} else {IM_SalesHistoryWrk.UDF_SALESPERSON_NO}. Unless you are using Basic format, then you will have to put the 'formula=' in front of the if.

 

[pmax9999]

Personally, I always avoid IF statements and Crystal-specific functions in record selection as they are often not passed to the WHERE clause (depending on database) and therefore tend to be very inefficient.

Based on how I understand your challenge, I would do it this way:

( 
	{IM_SalesHistoryWrk.UDF_SALESPERSON_NO} <> {AR_InvoiceHistoryHeader.UDF_NEW_SALES_REP} and
	{AR_InvoiceHistoryHeader.UDF_NEW_SALES_REP} = {?Salesperson No}

)
OR
(
	{IM_SalesHistoryWrk.UDF_SALESPERSON_NO} = {AR_InvoiceHistoryHeader.UDF_NEW_SALES_REP} and
	{IM_SalesHistoryWrk.UDF_SALESPERSON_NO} = {?Salesperson No}

)

However, your second post confuses me a little. When you say "... based on whether there is a UDF New Salesperson or not ...".

If this means the {AR_InvoiceHistoryHeader.UDF_NEW_SALES_REP} field could be null, you would need to amend the test to check for nulls instead. Something like this:

( 
	Not Isnull({AR_InvoiceHistoryHeader.UDF_NEW_SALES_REP}) and
	{AR_InvoiceHistoryHeader.UDF_NEW_SALES_REP} = {?Salesperson No}

)
OR
(
	Isnill ({AR_InvoiceHistoryHeader.UDF_NEW_SALES_REP}) and
	{IM_SalesHistoryWrk.UDF_SALESPERSON_NO} = {?Salesperson No}

)

As for the 2nd part of the question, I don't really understand the what you are asking. if you still need help with that aspect, please explain the problem, ie the error you are receiving or how the result differs from what you want.

Hope this helps.

Cheers
Pete

 

[pmax9999]

And the "Isnill" in the last part of the second formula should of course be "Isnull"