help with formula PLZ!!! 2

[Durango122]

Hello
CR 9 MS Sql
I have a problem and draw a blank to resolve this issue
I have a Parameter
{?Compensation}
and is define as string no link to table or field
just this in it and prompts you to select
NO
YES
BOTH

I have a formula call
@Comp
if
{?Compensation} = "Yes"
then
{transaction_history.sales_compensation} = "Y"
else
if
{?Compensation} = "No"
then
{transaction_history.sales_compensation} = "N"
else
if
{?Compensation} = "BOTH"
then
({transaction_history.sales_compensation} = "Y" and
{transaction_history.sales_compensation} = "N")

i draw a blank not sure how to make them work must be getting to old lol lol

1. do i need to link the formula to parameter?
2. should i place these 2 on the report
3. should the {compensation_flag} be link to the formula or the parameter
the field in the table is
SALES_COMPENSATION and it's varchar(1)

Can someone help me please.

thank you



Durango122
if it moves and should not used Duck Tape
if does not move and should used WD-40

 

[synapsevampire]

The formula couldn't work as written, a value can't be "Y" and "N" at the same time, right?

You state that you have a parameter, and that you have a formula, what you don't state the intent. What are you trying to do?

-k

 

[Durango122]

Hi Kai
I have a table field call SALES_COMPENSATION that has a value of varchar(1), notNull)
it contains either a "N", "Y"
the user has a parameter to select
either NO,YES,BOTH that is define in my parameter also they can choose BOTH wish means they want to see the "N" and the "Y"
so the "N" and the "Y" will be added together as a result of them BOTH together
My formula i created
@comp
@Comp
if
{?Compensation} = "Yes"
then
{transaction_history.sales_compensation} = "Y"
else
if
{?Compensation} = "No"
then
{transaction_history.sales_compensation} = "N"
else
if
{?Compensation} = "BOTH"
then
({transaction_history.sales_compensation} = "Y" and
{transaction_history.sales_compensation} = "N")

i need to add the y and the n (sum)

Durango122
if it moves and should not used Duck Tape
if does not move and should used WD-40

 

[e11a]

As synapsevampire said, you can't have {transaction_history.sales_compensation} both "Y" and "N" at the same time.

If you want records with {transaction_history.sales_compensation} = "Y" and records with {transaction_history.sales_compensation} = "N" then the last part of your formula should be

if
{?Compensation} = "BOTH"
then
({transaction_history.sales_compensation} = "Y" or
{transaction_history.sales_compensation} = "N")

E11a

 

[synapsevampire]

If this is a record selection formula, then:

(if
{?Compensation} = "Yes"
then
{transaction_history.sales_compensation} = "Y"
else
if
{?Compensation} = "No"
then
{transaction_history.sales_compensation} = "N"
else
if
{?Compensation} = "BOTH"
then
{transaction_history.sales_compensation} in ["Y","N"]
)

-k

 

[Durango122]

Thank kai and ella
will try both

Durango122
if it moves and should not used Duck Tape
if does not move and should used WD-40

 

[PatrickIRL]

Emmmm, but isn't ...

{transaction_history.sales_compensation} = "N"

... trying to update the field sales_compensation in the table transaction_history or am I missing something???

Patrick

 

[synapsevampire]

redsmooth: You can't update fields from within Crystal unless you do so via SQL in a Command Object or SP.

-k

 

[PatrickIRL]

That's what I'm saying, isn't that what the op's formula is trying to do??

Patrick

 

[KeertanaCR]

I guess, instead of having this as a formula, it should be
in selection formula >> Record.

In regard with redsmooth post:

and basically you are trying to select the rows where the value is Y, N or both. you are not updating the table field value.

It is some thing like an sql statemt with a where clause.