Crystal 8.5 if/then formula help

[rradelet]

I am running CR 8.5 on a SQL 2000 DB

Sample table & fields

Table1.AcctNo
Table2.AcctNo
Table2.PriceID

There are multiple records in Table2 for each AcctNo but the count of records for each AcctNo will vary.

AcctNo PriceID
1 5
1 6
1 7
1 8
2 5
2 6
2 7
2 8
3 5
3 6
3 7
3 9
4 9
5 9
5 6

I am trying to create a formula that will name each “identical” combination of PriceID’s.
For example: if the PriceID’s for a single account are 5,6,7 & 8 then “STANDARD”
if the PriceID’s for a single account are 5,6,7 & 9 then “OTHER”
if the PriceID’s for a single account are 9 then “OTHER2”
if the PriceID’s for a single account are 6 & 9 then “OTHER3”
etc.

I have tried using if/then formulas like:

If {Table2.PriceID = 5 and {Table2.PriceID = 6 and {Table2.PriceID = 7 and {Table2.PriceID = 8 then “STANDARD”
Else if {Table2.PriceID = 5 and {Table2.PriceID = 6 and {Table2.PriceID = 7 and {Table2.PriceID = 9 then “OTHER” ……

But the results are not accurate.

Can anyone help me with this?

 

[lbass]

Well, what's in the "etc."? If the sum of these values per ID are unique to the description, you could use a formula like:

if sum({table.priceID},{table.acctno}) = 26 then "Standard" else
if sum({table.priceID},{table.acctno}) = 27 then "Other" else
if sum({table.priceID},{table.acctno}) = 9 then "Other2" else
if sum({table.priceID},{table.acctno}) = 15 then "Other3" else
"Unknown"

This requires a goup on acctno.

-LB

 

[rradelet]

Unfortunately, the sum is not unique.

 

[lbass]

Then you will have to do a separate formula for each value of price ID like:

//{@5}:
if {table.priceID} = 5 then 1

//{@6}:
if {table.priceID} = 6 then 1

//etc.

Then you will need to create a formula like this:

if sum({@5},{table.acctno}) > 0 and
sum({@6},{table.acctno}) > 0 and
sum({@7},{table.acctno}) > 0 and
sum({@8},{table.acctno}) > 0 then "Standard" else

if sum({@5},{table.acctno}) > 0 and
sum({@6},{table.acctno}) > 0 and
sum({@7},{table.acctno}) > 0 and
sum({@9},{table.acctno}) > 0 then "Other" else

//etc.

-LB

 

[rradelet]

Thanks for the suggestion. This does work, but the table I am working with has over 200 distinct PriceID's, which would make for quite a cumbersome report.

Are there any other ideas how to approach this.

 

[lbass]

I'm assuming that there are however a limited number of price combos you want to name. The following example is based on three combinations, so you'd have to expand it.

First insert a group on {table.acctno}. Then create these formulas:

//{@reset} to be placed in the group header:
whileprintingrecords;
numbervar array id := 0;
numbervar i := 0;

//{@ids} to be placed in the detail section:
whileprintingrecords;
numbervar array id;
numbervar i;
numbervar j := distinctcount({table.priceID}, {table.acctno});

if not({table.priceID} in id) then (
redim preserve id[j];
i := i + 1;
if i <= j then
id := {table.priceID}
);

//{@checkarrays}:
whileprintingrecords;
numbervar array std := [5,6,7,8]; //enter your price combinations in separate arrays
numbervar array oth := [5,6,7,9];
numbervar array dif := [4,9]; //etc.

numbervar array id;
numbervar k := 0;
numbervar cnt1 := 0; //add a separate cnt variable for each combo
numbervar cnt2 := 0;
numbervar cnt3 := 0;
stringvar displ := "";

for k := 1 to ubound(std) do( //accum cnts per combo
if std[k] in id then
cnt1 := cnt1 + 1
);
for k := 1 to ubound(oth) do(
if oth[k] in id then
cnt2 := cnt2 + 1
);
for k := 1 to ubound(dif) do(
if dif[k] in id then
cnt3 := cnt3 + 1
);

if cnt1 = ubound(std) then //chk for presence of combo components in id array
displ := "Standard";
if cnt2 = ubound(oth) then
displ := "Other";
if cnt3 = ubound(dif) then
displ := "Different";
displ

This assumes that you will name an account if it contains all members of the combo in the accounts set of price IDs, but that there could be additional price IDs beyond those in the combo, e.g., an account with price IDs of 2,5,6,7,8 would appear as "Standard".

-LB